If it's been running well every other day, I want to know two things first:
1. did they make a recent change to it or any of the databases/tables/procedures/functions that it uses?
2. what else was running at that time?
For #2, I recently had a user complain a report was taking a long time to run. #1 turned up nothing. #2 showed there was a reindexing job added to the server that was running at the same time. I stopped that and the report ran quickly again. Sometimes you need to tweak schedules.
Another time, a report was running slow...sp_who2 showed there was an insert happening on the database and it was a huge one. Then I checked the report script and they were not using (NOLOCK) on the script. I suggested they use that if they could afford 'dirty' reads for their report. Otherwise, they would have to wait until the insert finished.
Basically, if a script has been running fine and all of a sudden isn't...I look for the issue to be somewhere else. Although sometimes I've done a lot of searching only to have the user finally say...well I did make a change to the script, but it was a small one and wouldn't have caused this issue...yeah right.
As for when I check a script...I'll see if I can run it. If so, that gives me a timeline of how long it takes. Now I know that if it runs faster than that, I've improved it. Then I look through the script for obvious issues. I might look to see if columns are heavily used - maybe an index is needed and it wasn't before (this may also indicate the amount of data has drastically increased in the table/database). Then I'll get the execution plan. Next, I may run portions of the script to see where the slowness is. I've found scripts where one subselect slowed the entire thing down. As part of my testing, I'll use hard-coded values instead of parameters...sometimes that will speed it up. If there is a function that does something with the data, try to remove it and hard-code what it does - for example, one script had a function that took a string (a,b,c,d) and converted it for use with an WHERE .... IN. So the script portion looked like this:
WHERE something IN dbo.userfn_fix_string @string
I hard-coded the statement as:
WHERE something IN ('a', 'b', 'c', 'd')
and the script ran much quicker - from 20 minutes down to less than one minute. Bingo! there was the issue.
-SQLBill
-SQLBill
The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875