December 9, 2009 at 5:58 pm
Hello!
I am in the process of familiarizing myself with SQL Server 2008 features. We have just installed and configured SQL Server 2008 64-bit Standard Edition Active/Active cluster. I was trying to run some query tests while monitoring perfmon counters and wait stats information. I was trying to use track_waitstats_2005 and get_waitstats_2005 stored procedures from SQL Server 2005, but apparently they do not report correct info in 2008. For example, CPU resource waits % is consistently 36% indicating CPU bottleneck, although CPU usage on the box was barely 5-10 %. get_waitstats_2005 consistently report high resource wait % for LOGMGR_QUEUE,FT_IFTS_SCHEDULER_IDLE_WAIT.
I was wondering what is the proper way of monitoring wait stats in SQL Server 2008 since old stored procedure from 2005 do not work correctly. I am trying to understand how to interpret high resource wait % for two aforementioned wait types.
Help on this matter is greatly appreciated,
Igor
December 10, 2009 at 2:03 am
Other than getting lots more waits, the mechanism for monitoring hasn't really changed. It's sys.dm_os_wait_stats (for the cumulative ones), sys.dm_os_waiting_tasks for the current waits.
There are quite a few waits in SQL where high values are to be expected, because they're part of some process that waits, does something, then waits again. I think that the LOGMGR_QUEUE and FT_IFTS_SCHEDULER_IDLE_WAIT are among those. You just need to filter out the useless waits. There may be something in Books Online for that. Otherwise, search for Andrew Kelly, he writes a lot about waits and should have a list somewhere of the useless ones.
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
December 11, 2009 at 9:30 pm
Thanks Gail! I actually found nice script in Itzik's book.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply