How to check why a query is sometimes slow

[joacker]

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.

 

[markros]

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.

 

[RiverGuy]

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.

 

[joacker]

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).

 

[markros]

Would you be able to avoid using temp tables?

 

[SQLSister]

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