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 (https://blog.sqlauthority.com)
15 Comments. Leave new
There are two more things you have to definitely check: parallelism on OLTP and the human factor (is there some user running 30 sec queries by connecting from Excel to SQL Server).
Good list. Just one thing came to mind when I was examining fragmentation of one of our databases: fragmentation! :)
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.
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?
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?
If you use profiler at the time of execution you can find out. Other look at dynamic management views in sql server help file
One more can be checked:
Out of date statistics on indexed columns.
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?
Are you sure it is running more than two minutes when you execute it again?
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.
hi
i am fresher to dba side i need a help
what are thigs to be check first if a query is executing slowly in a sql server
if query performance is slow than we have to
check blocking , index fragmentation,log space,jobs ,server disk space,query optimization.
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.
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
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