Tracking problem indexes in SQL 2000

  • It’s all so easy(ish) to work within SQL 2005 but the reality is that there are still more SQL 2000 databases than SQL 2005, so I’m told, and I’m supporting one of them right now.
  • I’ve been contemplating on how to get a handle on which of my indexes are fragmenting too quickly and where the high levels of page splits are occurring.
  • So I know you’ll all say well you can run dbcc showcontig  and  of course this is true, but not on a very busy production server when it’s running hot – and it’s at times like these that I want this information not after the busy times or when the server is off-line.
  • Well the simple way to handle this is to create a log shipped database and run your commands on the log shipped server, I run a dbcc showcontig several times a day and put the data into a table, the job retries several times on failure as it has to run within the log restore intervals. It’s my own log shipping so I have a certain amount of flexibility in this.
  • So capturing dbcc showcontig allows me to build up a picture of my fragmenting indexes, it can be interesting to note that some indexes have 80% fragmentation within a few hours of being totally rebuilt.

Here’s the query:-

 

insert into DBADatabase.dbo.Mydbfraglist(ObjectName,   ObjectId ,   IndexName,   IndexId ,   Lvl ,   CountPages ,   CountRows ,   MinRecSize ,   MaxRecSize ,   AvgRecSize ,   ForRecCount ,   Extents ,   ExtentSwitches ,   AvgFreeBytes ,   AvgPageDensity ,   ScanDensity ,   BestCount ,   ActualCount ,   LogicalFrag ,   ExtentFrag)

exec ('dbcc showcontig with all_indexes,tableresults,NO_INFOMSGS')

 

Here’s the table:-

 

CREATE TABLE [dbo].[ Mydbfraglist] (            [ObjectName] [char] (255) NULL ,            [ObjectId] [int] NULL ,            [IndexName] [char] (255) NULL ,            [IndexId] [int] NULL ,            [Lvl] [int] NULL ,            [CountPages] [int] NULL ,            [CountRows] [int] NULL ,            [MinRecSize] [int] NULL ,            [MaxRecSize] [int] NULL ,            [AvgRecSize] [int] NULL ,            [ForRecCount] [int] NULL ,            [Extents] [int] NULL ,            [ExtentSwitches] [int] NULL ,            [AvgFreeBytes] [int] NULL ,            [AvgPageDensity] [int] NULL ,            [ScanDensity] [decimal](18, 0) NULL ,            [BestCount] [int] NULL ,            [ActualCount] [int] NULL ,            [LogicalFrag] [decimal](18, 0) NULL ,            [ExtentFrag] [decimal](18, 0) NULL ,            [TheDate] [datetime] NULL ) ON [PRIMARY]GO ALTER TABLE [dbo].[ Mydbfraglist] ADD  CONSTRAINT [DF_ Mydbfraglist _TheDate] DEFAULT (getdate()) FOR [TheDate]GO Run this query to extract the datetimes of the showcontig select distinct thedate from DBADatabase.dbo. Mydbfraglist  order by thedate desc; --cut and paste the required datetime into this query and adjust for scan density ( here set to 50% ) select countrows,rtrim(objectname)+'.'+rtrim(indexname) as tableindex, indexid,scandensity from DBADatabase.dbo. Mydbfraglistwhere scandensity<50 and objectid>14 and countpages>16 and thedate='2008-05-07 18:48:32.587' order by thedate desc; ============================================================================================ so this will allow you to see which indexes are fragmenting badly but how about page splits? 
  • Well you can do a similar trick with dbcc updateusage , but it’s not so easy to put into a table and you can’t run it against a read only database. You can break your log shipping or run it against production.
  • ( I’m actually log shipping to a separate server for just this type of reason, I don’t suggest you break your DR log shipping )
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 2):
  • USED pages: Changed from (10435) to (11025) pages.
  • RSVD pages: Changed from (10441) to (11026) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 3):
  • USED pages: Changed from (10457) to (11884) pages.
  • RSVD pages: Changed from (10466) to (11891) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 4):
  • USED pages: Changed from (10449) to (11388) pages.
  • RSVD pages: Changed from (10458) to (11395) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 5):
  • USED pages: Changed from (12719) to (14058) pages.
  • RSVD pages: Changed from (12729) to (14066) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 38):
  • USED pages: Changed from (12730) to (13859) pages.
  • RSVD pages: Changed from (12738) to (13874) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 45):
  • USED pages: Changed from (20121) to (21955) pages.
  • RSVD pages: Changed from (20129) to (21962) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 46):
  • USED pages: Changed from (21831) to (23740) pages.
  • RSVD pages: Changed from (21841) to (23746) pages.
  • DBCC UPDATEUSAGE: sysindexes row updated for table 'SampleTable' (index ID 1):
  • RSVD pages: Changed from (280893) to (280899) pages.

 

 
Make sure you set output to text rather than grid, and make sure you save the results as this is non repeatable for this instant in time. 
  • So here’s the results from a real table ( with it’s name changed ) what we’re interested in here is the increase in pages, index 45 has jumped by  1,834 pages , or  14.4Mb in size, now if this is only a few hours after the index was rebuilt this could be worrying..
  • The index in question contains two foreign keys, two dates and a bit, this probably indicates this index suffers from batch inserts which will force page overflows
  • A change of  index key order ( assuming it doesn’t stop the index being used ) may help, e.g. swap first two keys.
  • Or more likely a fill factor.
  • We can calculate the key width at 25 bytes + the clustered key which is an int in this case + the key overhead.
  • Dividing table rows by pages would give the change of density, here we’ve got around 208 entries per page, with the index rebuilt this figure is around 230, it could be that an 80% fill factor might give us some relief for this index.
 Use this query to find your index:- select * from dbo.sysindexes where id=object_id('SampleTable') and indid = 45 
  • You could write a nice query to list the index columns but for now we can look in our log shipped database to find out what the columns are. 
  • I haven’t gone into too much detail and sadly this doesn’t resolve our problems but hopefully it may help with some diagnosis and remedial work.
  • The techniques will work unchanged on SQL 2005 too. 
  • Apolgies for part strange formatting - it all looks perfect until you hit publish then your entire post gets reformatted!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
Published Tuesday, June 10, 2008 8:30 PM by GrumpyOldDBA

Comments

No Comments