Microsoft SQL Server Tuning

There are many areas that can be looked at for Microsoft SQL Server performance tuning, only a few of which are considered here as potential key areas. A DBA should understand these and many other areas that can be monitored to improve performance given a specific usage profile (depending on the design of their BPM applications).

There are a number of useful resources on SQL Server monitoring and maintenance:

Database Engine Tuning Advisor (http://msdn.microsoft.com/en-us/library/hh231122.aspx), supplied with SQL Server, can analyze your database and recommend ways that you can optimize query performance. Activity Monitor (http://technet.microsoft.com/en-us/library/hh212951.aspx), also supplied with SQL Server, is useful to monitor the expensive SQL queries, resources waits, and file I/O. You can also view the Explain Plan for top SQL queries. Microsoft also supplies a Best Practices Analyzer for SQL Server (http://www.microsoft.com/en-us/download/details.aspx?id=15289). You can use this tool to scan your SQL Server systems and verify that common best practices have been implemented.

There is information available that can help you to find out the usage of indexes on your system. It can give you statistics on seeks, scans, lookups, and updates, which can help to identify heavily-used indexes as well as indexes that are unused or duplicated:

For information about missing indexes on SQL Server, see the following: