Find Best Clustered Index
The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics. If a clustered in…
Find Table without Clustered Index / Primary Key
One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not…
Using TRY...CATCH to Rollback a Transaction in case of an error
As you all might know, one of the downsides of the @@ERROR variable approach to catch the error is that we must check the value of this variable after each and every DML /DDL statement to determi…
Currently Executing SQL Statements
Here is a script to find out the currently executing sql statements, of course you can use the ‘sp_who2’, but I use this one very often select C.client_tcp_port, T. text from sys.dm_exec_conn…
Find the Index fragmentation on SQL Server 2005 /2008
Here is the script to find out the average index fragmentation levels. Based on this value you can either Rebuild or Reorganize the indexes. Index should be rebuild when index fragmentation is great …