March 28, 2012 at 8:21 am
Hello SQL Experts,
I need help on identifying the performance of a procedure at different time(hours) of the day.
When I execute this procedure by 10 AM it produces the expected result in 2 secs. The same procedure when executed at 11+ AM produces the expected result in ~25 secs.
Note: The test was carried out with the same amount of data in the affected table(s).
Now, I need to frame a test script to execute this procedure every hour and check if the procedure's execution time is exceeding the threshold time limit. if yes, then at the same time, I need to query the database for the running jobs or any other executions (you can suggest) and list them in the output.
I need to make this test to identify,
1. At what hour of the day, the execution takes more time ~ slower performance
2. At what hour of the day, the execution takes lesser time ~best performance
3. what are the jobs that are running in the SQL server during the slower performance hour.
4. Is there any of the job that runs during the slower performance hour tries to call this procedure.
p.s: our environment is clumsy with too many databases and Also, I dont have admin privilege over the given server to perform certain actions or to execute some queries. Please consider this too.
I was posting this help after trying out different options (known to me) and eventually everything failed 🙁
Thanks in advance!!!! 🙂
March 28, 2012 at 8:24 am
Pretty sparse on the details here. Could you be dealing with parameter sniffing?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/[/url]
It is pretty hard to say what the issue is without some a lot more information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 30, 2012 at 2:39 pm
Well to help out a bit with trying to see what might be going on at the different times I would suggest the following or something similar.
First have variables starttime and endtime, set starttime then execute procedure. Next set endtime and compare to get your execution time.
If is more than what you want to see or in the range you want to collect some data, select everything from dm_exec_requests and but it in a table. This of course you will want to also have some sort of execution_ID of your job so you can look at what was going on at a specific time and also keep a history of what is most commonly running during the longer time and not running during the quicker times.
In order to select from sys.dm_exec_requests you will require view server state permissions which is a low level server secruable (otherwise you will only see what your user is executing) but might take some persuading from you DBA. I am hesitant to give any permissions away but would be willing to set up a job that runs with the proper permissions to execute. Or even have the procedure signed with the proper permissions so that only that specific called procedure will run with those permissions.
The problem with this is you will not see if the particular procedure you are executing is being blocked by something else that was running, this could be a common cause. You will see if there might be a heavy load on the server at that time (assuming everything is still running after the proc finishes and you begin to collect your data) and can even pull out sql text and execution plans for what is running.
March 30, 2012 at 2:46 pm
Your analysis is going to have to include a look at what is going on at the disk layer as well, i.e. what is the disk latency during that time. The issue could well be that the other activity has slowed the read / write time for that procedure. Performance Monitor counters avg disk sec /read and avg disk sec / write under physical disk would be at least two that I would check out.
As the previous poster mentioned, you will need to ensure that there is no blocking during that time. That should give you a good start. Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 10, 2012 at 10:18 pm
Try this
Create Table #tmpExecution (Id int Identity, dtStart Datetime, dtEnd Datetime)
Declare @dtStart DateTime, @dtEnd DateTime
While (1=1)
Begin
Select @dtStart = GETDATE()
Execute 'Stored Procedure'
Select @dtEnd = GETDATE()
Insert into #tmpExecution (dtStart, dtEnd) Values (@dtStart, @dtEnd)
WAITFOR DELAY '00:60:00'
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply