This is one of the incident at our clients side and does not happen regularly. A stored procedure(stp) which usually executes in few seconds took 10 mins to execute. i want to analyse why that stp took time to execute or which other factors were the cause of slow execution of the stp. we have got the db restored. which query should we execute to know the same.
Nothing you do is going to show that. A newly restored database has no performance metrics stored with it of any kind.
In order to diagnose this kind of behavior, you have to set up a capture of some kind ahead of when that behavior occurs. Assuming you're in 2008 since we're on the 2008 forum, your only choice is to set up a server side trace on the client's machine and then consume that data. If you were on 2012 or better you could use Extended Events, which are much more lightweight and offer better filtering. If you were on 2016 or better you could add in Query Store, but it's not terribly granular, so you might not see why the query ran long.
I'd also suggest getting a blocked process report set up. You could be looking at one of two situations. One, bad parameter sniffing is causing poor performance until a recompile resolves it. Two, some process is blocking the procedure, making it run long occasionally.
However, until you do these things, there's literally no way to know from where you're currently sitting. Sorry.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
This is one of the reasons why I use RAISERROR('Section: %s Duration(ms): %u',0,0,@SectionName,@Duration) WITH NOWAIT: in virtually ever section of stored procedures I write to write section name, duration and, sometimes, rowcount to the output of the job or to the screen depending on what's running it. Depending on the proc, I might also use multiple RAISERRORs to list the input variables (if any) so I can do a test with the variables that caused the run to go haywire.
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)