Tips to improve your SQL performance queries

If you are a SQL Server Developer or DBA, here are few things you should consider focusing on for your SQL Server


1) Wait Statistics You must check your SQL Wait Statistics and see what is the primary reason, your SQL Server is running slow. Wait Stats shows the key reason for slowness of your performance. You can read more about over here: https://blog.sqlauthority.com/2011/02/01/sql-server-wait-stats-wait-types-wait-queues-day-0-of-28-2/
 

2) Missing Indexes

You should check if your SQL is missing any critical indexes or not. There are chances that your slow running query can get performance boost by just creating few missing indexes. You can read more about that over here: https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/
 

3) Unused Indexes

One of the most ignored feature. Not many people know that having indexes on your table which are not used can slow down your system multiple times. The indexes which are not used should be dropped. You can read more about that over here: https://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/
 

4) Stop Shrinking Database

From last many years I have been discussing with users that they should not shrink the database but it seems DBAs loves this task very much and they keep on shrinking their database. You can read over here why one should stop shrinking their database: https://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/ 
 

5) Identifying Expensive Queries

Every system have few queries which are very expensive. DBA and developer often are so busy that they forget to optimize the queries once they are deployed. Here is the script which can help you identify the expensive queries which you should prioritize in your next release cycle. https://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/