SQL SERVER – Checklist for Analyzing Slow-Running Queries

I am recently working on upgrading my class Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning with additional details and more interesting examples. While working on slide deck I realized that I need to have one solid slide which talks about checklist for analyzing slow running queries. A quick search on my saved book mark link come up with interesting book online link.

This link very clearly suggests: To save time, consult this checklist before you contact your technical support provider.

I strongly suggest you to do the same, first consult this checklist and if you still further need help, leave comment here.

Checklist for Analyzing Slow-Running Queries

There are a number of common reasons for slow-running queries and updates:

  • Slow network communication.
  • Inadequate memory in the server computer, or not enough memory available for SQL Server.
  • Lack of useful statistics
  • Lack of useful indexes.
  • Lack of useful indexed views.
  • Lack of useful data striping.
  • Lack of useful partitioning.

Continue reading this over here Checklist for Analyzing Slow-Running Queries (BOL).

If you are new to SQL Server and preparing for SQL Server Interview Q & A, I suggest to refer this SQL Interview Q & A as good start.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

15 thoughts on “SQL SERVER – Checklist for Analyzing Slow-Running Queries

  1. Good list. Just one thing came to mind when I was examining fragmentation of one of our databases: fragmentation! :)

  2. A good list, one of things i have heard/practiced is to extract the columns which are needed and use explicit column list. When using selects go after the columns that are needed and not use select * on a wide table.

  3. Hi Pinal,

    I need to find out from a database what tables are being queried, who queried and when was the last time it was queried for the past 6 months. Is there a query which I can use?

  4. I need to find out from a database what tables are being queried, who queried and when was the last time it was queried for the past 6 months. Is there a query which I can use?

  5. Hi Dave,

    Thanks for the great site. I hope you can help me. This works fine:

    SELECT DISTINCT Column1 FROM tbl1 WHERE Column2 IS NOT NULL
    SELECT DISTINCT Column1 FROM tbl1 WHERE Column3 IS NOT NULL

    All three columns are indexed and I just reorganized them to be sure. Results are returned in 1 second or less.

    This doesn’t finish after a couple of minutes:

    SELECT DISTINCT Column1 FROM tbl1 WHERE Column2 IS NOT NULL OR Column3 IS NOT NULL

    Any ideas?

      • The problem persisted for at least an hour, probably more than two hours. I repeatedly tried the queries in SSMS (no results after minutes of waiting) and from an external application (ODBC timeout). Reorganizing the indexes didn’t help but it shouldn’t have mattered because the worst level of fragmentation was 15% on one of them. I did not want to rebuild the index as this is a production database but perhaps I should have looked at statistics. After desperately recoding the external application, the OR query just worked again.

        Perhaps it was outdated statistics but I would have thought that SQL Server (2005 64 bit on 2003 Server 64 bit with 16 GB RAM and 8 GB db data size) should recognize and fix this after a couple of attempts. Even still, I’ve seen similar behavior on other databases where a simple OR in the WHERE clause takes much longer (>100x) than separately run queries.

  6. Hi Pinal,
    I have Windows 2008 server 64 bit and Sqlserver 2008 64 bit, query performance is very slow and runs on windows and Sqlserver 32 bit version it runs perfectly.
    I have searched this problem but didn’t find the solution.

  7. Hi Sir,

    I had big problem for slow executing sql server. I have a jar file. i need to run this on Command line. In this java code i am conecting to the sql server locally. When i was using my system which has SQL SERVER 2008, its working fine. If i use this different system which has SQL server 2005, its executing 20 mins for 500 records. Locally it takes around 10 seconds. so what is the problem in another machine. why its taking so much time

  8. Hello

    My Application is Running very slow,users could n’t able to access,they started troubling me
    Please give me a suggestion and get me out of this problem

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s