Thursday, November 29, 2012
Wednesday, November 28, 2012
Wait stats is the first place when where we start analyzing health of a production database server. Recently, we have found that a new database production server is not performing up to mark and queries response getting slower and slower in peak hours.
On executing, well known query by Paul Randal to get wait stats, we have found that a time for strange wait “CMEMTHREAD” is too high for said server.
According to BOL “CMEMTHREAD, occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.”
On trying a lot, but totally in vain, I thought asking Paul Randal would be better, as his blog on SQLSkills is one of the big resources from where I have learned about waits and wait types. Paul replied that “My guess is ad hoc plans being inserted into the plan cache. Try turning on 'optimize for ad hoc workloads'.” (That’s what we have already tried)
Skimming through articles and forums, I came a across to Microsoft support team article http://support.microsoft.com/
Which stats that, it could be occurring due to a bug in SQL Server 2008 R2. On
said production server, we have found that NO service pack is installed and it
still contains RTM.
Without any second thought we have created a ticket for upgradation to ServicePack2 and after that we have found that problem is resolved and server start working normally.
Monday, November 19, 2012
A year back I have shared a script from query bank, which can be helpful to get indexes list of a database, with key and involved columns. Being a DBA, I never remember a day, without using this script.
Getting detail of indexes on a database most of the time I also need indexes usage statistics, through which I can figure out which indexes are being used and which indexes can be discarded.
To avoid to use two separate scripts, let me share following script which brings both, usage and structural information for all indexes of a database or a given table.