From my query bank here is another useful script which can help a Database Administrator to list down stored procedures with tables names which are used in stored procedures but don't contain and non clustered index.
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/kb/2492381/en-us.
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.
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.
User Seek + User Scans will decide which index are useful and which are just burden for database. Indexes with less seek + scan should be removed for better DML operations performance.
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite penciles.