Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to check why a query is sometimes slow

Status
Not open for further replies.

joacker

Programmer
Sep 26, 2008
22
AT
Hello,

i wrote a client/server application. Atm i am in the test phase, so i am the only user of the system.

The problem is that one of my queries is sometimes really slow, or maybe it doesn't return (i inserted a timeout so i cann't say).

Now i want to analyze why that happens. So what can i do here? (e.g. log file?)

Thank your for your help.
 
Can you show the query in question and can you see its Execution plan?

Also it may help to run SET STATISTICS IO ON before running the query.
 
The first thing to do is to optimize the query in general--such that it runs fine whether or not you have a lot of system activity. Run the query in Management Studio and look at the execution plan. If you have any table scans, you would probably be served well by adding an index or more.

Secondly, you can use SQL Server Profiler to look at the activity at different times while your query is executed.
 
Thx for your inputs.

The RDMBS is sql server 2008. the query is part of a stored procedure that uses dynamic sql to build up the query. It also uses temp-tables which lead to an error when i wanted to take a look at the execution plan.

It's just a bit strange because "normally" the query is done in 1 second. But as i told u sometimes it takes ages... (for the same data amount and input).
 
Please post the code if you want us to help you. Why are you using dynamic SQl, it is usually a poor programming technique when accessing a database.

"NOTHING is more important in a database than integrity." ESquared
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top