Stored procedures fine tuning

Question

Stored procedures fine tuning

meriantok

Valued Member

Points: 63
More actions
September 3, 2009 at 5:57 am

#385306

Hi all.
I am facing a problem with delays in my application
and trying to find out what is going on, i set Sql Profiler
to trace the stored procedures with duration more than 5 seconds.
So i got a list of procedure with big durations.
My problem is that some procedures with big delays does not always delay.
I mean that there are procedures which should not be locked
by other processes because they contain only
"select with(nolock)" statements into a temporary table
and then some updates to this temporary table
and finally returns the data from the temporary table.
I suppose that there is no way , the procedure to be locked
by another process.
So i think that the delay comes from the select statements
and the where clauses
Sometimes these procedures have a duration of 40 or 50 seconds
But if i re-run these procedures WITH EXACTLY the same arguments
after 0.5 or 1 minute they return the data immediately.
So i cannot find a way to fine tune my procedures because i cannot
figure out which statement exactly generates the problem.
Are there any ideas , how to solve these problems, or maybe some utilities to help me out find the problematic statements?
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

meriantok (9/3/2009)
Sometimes these procedures have a duration of 40 or 50 seconds
But if i re-run these procedures WITH EXACTLY the same arguments
after 0.5 or 1 minute they return the data immediately.

Try Clearing sqlserver's cache by using DBCC DROPCLEANBUFFERS.

Then you should get a consistent time, then try a storedprocedure statement trace with SQLProfiler to find which statement.

Obviously if its a blocking issue then this wont help.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

Running a server side trace is the best thing you do for yourself. Capture the queries as they're made and see which ones run slow and when. Read Gails articles for details.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Dave Ballantyne (9/3/2009)
Try Clearing sqlserver's cache by using DBCC DROPCLEANBUFFERS.

Only if it's a dev/test server. Emptying the data cache on a busy production server's likely to cause performance problems and worsen existing ones.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 5

It would be good to see some example code for this one.

I wonder if shared (global) temporary tables are being used...? (Just one possible cause from many)

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi