473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to identify fragmented index for rebuild

Hi,

I want to find out what is a good way to identify indexes that need
rebuilding. I tried to run DBCC showcontig and identify them based on
the scan density %. And according to a Microsoft Tech Net - All About
SQL Server Indexes discussion, the expert stated that based on the
Density (below 20%), I can pick out those indexes associated to the
table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
rebuild all indexes associated to the specific table. The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!

Thanks in advance.
Jul 20 '05 #1
1 3300
Check out the white paper on SQL 2000 Index Defrag Best Practices:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!
You can create an clustered index on a heap (a table with no clustered
index) in order to reorg the table. You can drop it afterward if it is not
needed. It's usually a good idea to have a clustered index on every table
unless you have a specific reason not to.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"xo55ox" <xo****@hotmail.com> wrote in message
news:ab**************************@posting.google.c om... Hi,

I want to find out what is a good way to identify indexes that need
rebuilding. I tried to run DBCC showcontig and identify them based on
the scan density %. And according to a Microsoft Tech Net - All About
SQL Server Indexes discussion, the expert stated that based on the
Density (below 20%), I can pick out those indexes associated to the
table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
rebuild all indexes associated to the specific table. The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!

Thanks in advance.

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: xo55ox | last post by:
Hi, I want to find out what is a good way to identify indexes that need rebuilding. I tried to run DBCC showcontig and identify them based on the scan density %. And according to a Microsoft...
8
by: DraguVaso | last post by:
Hi, I want my application do different actions depending on the exception it gets. For exemple: I have an SQL-table with a unique index. In case I try to Insert a record that's alreaddy in it I...
3
by: kalpesh.shah | last post by:
kalpesh.s...@gmail.com Feb 1, 6:50 am show options Newsgroups: comp.databases.informix From: kalpesh.s...@gmail.com - Find messages by this author Date: 1 Feb 2005 06:50:21 -0800 Local:...
6
by: Tom | last post by:
Given: A binary data file of records. Task: Random access using seekg(). I've never found any documentation stating that the offset from begin file marker is safe on a fragmented file. I've...
10
by: wackyphill | last post by:
After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT...
5
by: vj_dba | last post by:
Hi group, I have a table say with 10000 records and 10 columns initially, I created an index say i1, after few days I altered the table by adding 10 new columns and 90000 rows of records summing...
4
by: NancyJ | last post by:
Currently we have a database with a main table containing 3 million records - we want to increase that to 10 million but thats not a possibility at the moment. Nearly all 3 million records are...
6
by: Ryan Liu | last post by:
Hi, If I want to uniquely identify a computer. I can read CPU ID or Mac Address. I heard, but is this true: some BIOS can block CPU ID from being read? (In this case, will I get an exception,...
1
by: digitalox | last post by:
All efforts to rebuild indexes fails. Not with an error, but they still show high fragmentation. The environment: SQL Server 2005 in 2000 compatibility mode non-clustered indexes on tables that...
1
by: dsdevonsomer | last post by:
Hello all, I am new to managing indexes on large tables and need some help. Hopefully, I am not repeating question here. I searched as much as I can, but not finding relatively best answer.. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.