SQL Server 2008 Performance troubleshooting basics

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!

This entry was posted in SQL Server DBA Stuff and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s