In this post I would like to mention the basics of troubleshooting performance issues in sql server. When a customer calls to inform that the database is slow one of the first things the dba’s do is verify if this is an application issue or really a database issue. Here are some of the tips/checklist items that I check if the database is the cause for the slowness.
*Check for blocking
*Check for statistics out of date (and perform update statistics)
*Check for wait types
*Identify fragmentation % (and then do an index reorg or rebuild accordingly)
*Run profiler and identify deadlocks (or enable deadlock traceflags) and any slow running queries
*Check the query execution plans for the slow running queries and see if there are any table scans and add indexes appropriately (adding/dropping indexes should be something that needs to be done in a staging/test/dev env. and be monitored for few days before moving to production)
*Create a baseline for how your stored procs should perform so that we can compare and see if there is a change that triggered the performance downgrade
*Run missing indexes script and check if any indexes are required ( again any new indexes shd be tested on test/staging env before adding them to prod databases)
*Run unused indexes script and check for indexes with 0 reads and delete them
*Identify queries that are taking longer time to complete and tune them with DTA and add recommended indexes or statistics in a test env. first and move it to prod database
*Check for IO bottlenecks by running the perfmon with appropriate counters from a remote machine
*Verify with the network admins if there was a change in the network
Hope this helps!