Wierd one..
-
I have a proc that runs very slowly on my prod server, it take over 3 minutes to return results.
If I make a backup of the DB and move it to my stage server the same exact procs run in two seconds.
I'm assuming this must mean that the slowness is caused by something in my prod environment?
If so, what can I look for?
Is it safe to say it's nothing in the DB, since it runs fine on stage?
btw- the prod environment is not very busy...cpu, disk io, etc all very low levels..
-
Compare both execution plans to find the difference.
It's likely a parameter sniffing issue (plenty of articles in google about that)
-
the execution plans appear to be the same...
-
make sure you are comparing the actual execution plans, and not the estimated plans....
the estimated woudl probably be teh same on both, but the performance issue is in the actual...where something goes wrong.....
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
okay so I followed your advise ...ran the execution plan on both prod and stage boxes..
On my prod box (query 4) I have a clustered index scan..cost of 81 percent
I don't have that on my stage box..
-
It sounds like a parameter sniffing issue. A quick fix would be to recompile the procedure on prod and see if the plan changes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Is the memory configuration different on prod and dev?
If so, this could perhaps be caused by this bug http://support.microsoft.com/kb/2413549
-
Did the suggestion from Grant helped you?
-
I re-complied but slowness still exists..
We are gonna try bouncing the server this weekend.
-
You may drop and recreate the proc or use the "with recompile" option.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
There are a few ways. You can modify the proc to include WITH RECOMPILE as a hint, I don't suggest that though. Another way is to get the plan handle from sys.dm_exec_query_stats (or wherever plan handles are stored, there are lots of places to get them) and then run DBCC freeproccache (<guid>). Easiest, you can execute the procedure and say WITH RECOMPILE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Is it returning same data on both machines? I mean are u using same parameters on both machines?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
Grant
I believe DBCC freeproccache (<guid>). will work on 2008 only....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
SureshS_DBA (10/22/2010)
GrantI believe DBCC freeproccache (<guid>). will work on 2008 only....
Confirmed, doesn't work on sql 2005 standard SP 2
-
Oops. I spend most of my time in 2008 these days. I tend to bleed that stuff back down the chain sometimes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply