CODEWITHSUNDEEP
sqlsql-server
Conrad Jagger
Conrad Jagger

Reputation: 2333

SQL Server - Stored procedure execution time issue

Recently on our PRODUCTION SERVER - we have started to face issues - where query which use to take just 5 mins are taking more than 30 mins to complete.

And sometimes they are back to normal. I have checked nothing change in Data volume but still query run sometimes slow and sometimes fast.

How to diagnose these type of issues.

Regards

Upvotes: 0

Views: 746

Answers (3)

Captain Jack Sparrow
Captain Jack Sparrow

Reputation: 449

put the following line after each step in your proc it will help you find out which part is taking how much time. So you can figure out the reasons. It helps me all the time

print convert(varchar(50),getdate(),109)

P.S. after executing your proc see the message tab there it will show you datetime with millisecs

Upvotes: 0

gbn
gbn

Reputation: 432667

Sounds like parameter sniffing (several SO links)

The quick way to check is run sp_updatestats and see if it fixes the issue. This will invalidate all query plans and confirm (or not) parameter sniffing

For more, read "Slow in the Application, Fast in SSMS?" by Erland Sommarskog

Upvotes: 2

Hari Gillala
Hari Gillala

Reputation: 11926

Did you see , the break down of query by using, estimated execution plan in SQL Management Studio for your query.. While running your Query? It will give the % of time spent in executing your query.

Upvotes: 0

Related Questions