October 31, 2011 at 9:24 am
Hi
DBCC SHOWCONTIG (PA) . I got the following results . Can someone explain what this means ? Whether its really urgent to rebuild the indexes. The table has an identity coulumn as primary ket but Created as a unique non- clustered index . Should it be converted to Clusted index? The table has 65254947 rows.
DBCC SHOWCONTIG scanning 'PA' table...
Table: 'ProductAdders' (647786786); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 387720
- Extents Scanned..............................: 48953
- Extent Switches..............................: 48952
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.00% [48465:48953]
- Extent Scan Fragmentation ...................: 29.38%
- Avg. Bytes Free per Page.....................: 61.2
- Avg. Page Density (full).....................: 99.24%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 31, 2011 at 9:37 am
You've got a heap here, so it can't really be rebuilt. That's for indexes.
Don't use showcontig, it's deprecated and has been for 6 years. Use sys.dm_db_index_physical_stats
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 9:38 am
most of the documentation says if fragmentation is less than 30% then a reorganize is the way to go and if its above 30% then rebuild
what is the page split rate on the table as the page density is quite high, what fill factor was used to create the indexes, how often is data updated / inserted into the table etc as to get a rough fill factor
as for the clustered index, is there one on the table already? does this clustered index suit the most frequently used queries against the table, if so then leave it as is, I would rarely say that the PK should be clustered as in my experience it has only been used for an unique identifier of the row, not to enforce business logic where a clustered index is better suited on a different range of columns other than the PK column
if you do lots of insert updates etc, then I would rebuild with a fill factor and keep your eye on the fragmentation and density to avoid fragmenting the indexes sooner
do you need the table to be online while the rebuild is happening, if so you need Enterprise edition, otherwise the index will be rebuilt offline, so might be one to do out of hours if you only have Standard edition
October 31, 2011 at 9:42 am
anthony.green (10/31/2011)
most of the documentation says if fragmentation is less than 30% then a reorganize is the way to go and if its above 30% then rebuild
Except this is a heap (index id 0) and hence can't be reorganised and rebuild is not what it is for an index, and the only fragmentation a heap gets is extent fragmentation as it can never be subject to page splits
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 9:48 am
Thanks Gail, I missed that.
Looks like a clustered index is needed to switch it from a heap to a tree.
Just to decide which columns the clustered index is better suited on, the PK column or some other columns for better searching etc.
October 31, 2011 at 10:11 am
Fill-factor used was 0 . Talked with the dba. He said they are in the process of using the most appropriate one soon . But now due to space issues, they are using a default fill factor of 0 .
There is no clusterd index. There is one identity field (ID) . Its being used as the PRIMARY key nut as a UNIQUE NON CLUSTERED index. I dont see any use of the ID in the stored procedure that I am working on. There is a UNIQUE NON Clustered index on Key and Code . This could be a candidate for the Clusterd index
Lot of Inserts takes place in this table From tables like Transactions and Products which also have millioons of data in them .
October 31, 2011 at 10:23 am
The first thing you need to do is create a clustered index, take a look through sys.dm_db_missing_index_details for a list of indexes which SQL thinks you would benefit from, but please take them with a pinch of salt as they may or may not be relivant any more and it is only queries which have been executed since the last restart of SQL so might not have all of the queries you run monthly / quarterly / annually in its cache. Then you can see which columns 'could'[/i][/u] possibly suit a better clustered index than key and code
Create the index with a fill factor, 70% say, keep your eye on fragmentation, if you start to get a high density or high fragmentation before your next maintenance window to rebuild then increase the fill factor 60% etc and keep going until you get to a sweet spot
This is Gail's expert area so would be good to see if she agree's with the above.
October 31, 2011 at 10:48 am
Thanks!
October 31, 2011 at 1:26 pm
anthony.green (10/31/2011)
Take a look through sys.dm_db_missing_index_details for a list of indexes which SQL thinks you would benefit from
No, please no. Missing index DMV is for nonclustered indexes. Stuff in there often makes bad nonclustered indexes, could be terrible, terrible clusters.
Take a read through this: http://www.sqlservercentral.com/articles/Indexing/68563/
The main point of a cluster doesn't have to be for queries, I prefer a cluster to organise the table primarily, if I can get one that also helps with queries then so much the better
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 3:12 pm
Thanks Gail, I have to say I didn't know that about the dmv.
I have always been told to try and get a cluster which best suits the business, but I take your expert advise, many thanks.
October 31, 2011 at 3:27 pm
anthony.green (10/31/2011)
I have always been told to try and get a cluster which best suits the business, but I take your expert advise, many thanks.
There are 2 main schools of thought on the clustered index.
1) Cluster on the most commonly searched column(s), sometimes with an emphasis on range queries
2) Use the cluster to organise the table and leave the work of supporting queries to the nonclusters
I generally hold to the 2nd, if I can get a cluster to do both then I'm very happy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply