Category: Database Administration

SQLServerCarpenter Tools

Over the past couple of years, I've developed several tools that I've been using during my Performance Tuning and other related assignments. I thought to share it with the community but due to my other commitments, I couldn't write any new posts in the past couple of months. Here are the tools (essentially the stored … Continue reading SQLServerCarpenter Tools →

usp_SQLServerCarpenter_Tools_Estimate_Nonclustered_Indexes_Clashing_With_Clustered_Index

This procedure will list down all the nonclustered indexes, which includes key columns as that of the clustered index. It will also list the key columns of both – nonclustered index, and the corresponding clustered index, along with the key ordinal of the clustered index key columns in the respective nonclustered index. It is now up to you to decide which one makes sense, and which doesn’t. Take the decision accordingly and delete the ones that do not make sense. This procedure may be helpful in performance tuning assignments.

usp_SQLServerCarpenter_Tools_Get_Costliest_Queries

This procedure can get you the list of queries being executed frequently and for duration greater/less than a particular time. You may also find it very useful if you are doing multiple things to optimize the database (such as server or database setting changes, indexing, stats or code changes etc.) and you would like to track the duration to see if you are really making progress. You can create a job with this procedure and dump the output in some table. Job can be scheduled to run in certain frequency. Later, you can plot trend out of the data tracked.