Troubleshoot slow SQL Server performance
Disk and server configuration
Make sure to place data and log files on separate logical drives. Make sure to place backup files and tempdb on other drives too.
Use 64KB disk formatted for datafile and log files, and SSD for the tempdb files.
Make sure the Defrag option on Windows is not scheduled (Defragment and Optimize Drives).
Verify and choose the High performance power plan.
Verify the I/O wait time (average read on datafiles and average write for transaction log files) and make sure the response time doesn't go high.
check the CPU and memory configuration (minimum configuration for production).
Instance and database parameter
check the max degree of parallelism if the parameter is not misconfigured according to the number of CPUs available on the server.
Check the memory configuration, especially the max memory server and min memory server parameters to implement a limit of memory consumption (important when you have a multiple instances installed on the same server, or when the application is also installed on it). You can also see if the PLE (Page Life Expectancy) is low.
Verify the datafiles autoextension value. For those whose increment step is in percentage (10% for instance), adjust it by setting a fixed increment step instead (512 MB for instance).
Check the compatibility level of the databases. The highest level is the best. Make sure with the editor that the application is compatible before making the change.
Make sure the auto_close and auto_shrink options are disabled !
Query analysis
Check in the plan cache for redundant queries, that is, the same query with different filter values. In this case, the optimizer calculates a new plan for each query, which can take up space in the cache, resulting in multiple plans for the same query. You can change this behavior by using Plan Guides (Parameterization to FORCED).
Depending on the type of activity, you may also end up with single use plans in the cache. If it's the case, you can adjust the option Optimize for Ad Hoc Workloads.
Ensure that the big tables have clustered indexes (HEAP table aren't very good for performance).
Check the most resource-consuming queries and analyze their execution plans.
Maintenance Plan
Every instance should have its maintenance plan which must include :
Index maintenance (Rebuild and Reorganization)
Statistics maintenance (Update)
Integrity Check
Ensure the indexes aren't fragmented.
Wait events
It's important sometimes to look at the wait events and confirm symptoms of a performance issue (high WRITELOG wait time or too much RESOURCE_SEMAPHORE for instance)
Define the scope
The most important think before starting a performance analysis is to define the scope.
Ask yourselft or the client :
Since when have the slowdowns been noticed ?
On which database exactly ?
Are the slowdowns general or specific to a particular query ?
Are the slowdowns experienced throughout the day or only at a specific time ?
