SQL Server: Script to Find Tables in Stored Procedures, That Are Without NonClustered Index

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.

Query Output:

SQL Server: CMEMTHREAD, High Wait Values and Solution

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.

SQL Server: Indexes List With Key and Involved Columns Name Alongwith Usage Statistics

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.

Script output

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.