June 2008 - Posts

Performance Dashboard for SQL 2008

Just in case you haven't seen this   http://sqlblogcasts.com/blogs/thepremiers/archive/2008/06/20/sql-server-2008-performance-studio.aspx

then have a read.  As I actually create dashboards and performance reporting for sql 2000 and sql 2008, and have been for many years, I'm seeing some of my work made redundant but there's other new features in 2008 I can concentrate on so it's not all bad < grin >

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!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Dates - especially when SQL Server can't be British!

Just like memory config dates never seem to go away and I've had some discussions with our BI team recently on why things were not working quite as expected, especially on the 13th of the month; and I noticed the same issue had arisen for a member of the UK SSUG -  Anyway rather than spend time writing a doc for the guys here Tibor has a really good post on this http://www.karaszi.com/SQLServer/info_datetime.asp it's really good paper and I'd advise anyone to print it out and add it to their essential SQL Server documents.

I actually find US English really annoying, it's our language ( British ) not theirs and I think US English should be a subset of English, not the other way around, I also get irritated that o/s installs always default to the US, and I get really really irritated when people build servers for me and leave them in the US or elsewhere - web farms with mixed locales can give some really strange results - you can use profiler to track the set options to make sure they're all the same btw.