March 23, 2011 at 4:01 pm
I have a procedure with a handful of parameters which takes 2 seconds to run on a data warehouse. But if I simply call the same procedure with an EXEC, it takes two and a half minutes. The EXEC is just run by itself and not into a table of any kind. Also, this is new behavior. This was fine before today. What can cause this?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 23, 2011 at 4:22 pm
mtillman-921105 (3/23/2011)
I have a procedure with a handful of parameters which takes 2 seconds to run on a data warehouse. But if I simply call the same procedure with an EXEC, it takes two and a half minutes.
Can you elaborate a little more on this?
The EXEC is just run by itself and not into a table of any kind. Also, this is new behavior. This was fine before today. What can cause this?
Parameter sniffing, index usage tipping points due to data growth, or stale statistics are the usual culprits for suddenly changed bad performance.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 23, 2011 at 4:29 pm
Thanks for the reply. The procedure's over 1,000 lines long, so including it here isn't very viable. Also, I'm running it with the same parameter values in each case. So it's not like it can be running different code for each or on different data.
I suspected parameter sniffing (which is ironic since I changed my "signature" statement before finding this issue). But I wasn't sure that it could be possible in this scenario.
p.s. 'Got to run for now, but I'll check back in tomorrow.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 24, 2011 at 4:47 am
It runs one way in the datawarehouse and one way when you call it using EXEC. Are these two different applications making the call? If so, I'd check the connection settings. They can affect the execution plans created.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2011 at 9:32 am
Grant Fritchey (3/24/2011)
It runs one way in the datawarehouse and one way when you call it using EXEC. Are these two different applications making the call? If so, I'd check the connection settings. They can affect the execution plans created.
Hi Grant, thanks for the info.
But in this case, for both situations, I'm manually running them from the same server and database in SSMS. For one case, the procedure is opened and run, and in the other the SP is run with EXEC.
I just ran the EXEC today again and the same response time, about 2 and a half minutes. How odd.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 24, 2011 at 11:02 am
mtillman-921105 (3/24/2011)
Grant Fritchey (3/24/2011)
It runs one way in the datawarehouse and one way when you call it using EXEC. Are these two different applications making the call? If so, I'd check the connection settings. They can affect the execution plans created.Hi Grant, thanks for the info.
But in this case, for both situations, I'm manually running them from the same server and database in SSMS. For one case, the procedure is opened and run, and in the other the SP is run with EXEC.
I just ran the EXEC today again and the same response time, about 2 and a half minutes. How odd.
Oh wait, you said open and run, you mean that you ran the code within the stored procedure for one call and you executed the procedure itself for the other? Then it's probably parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2011 at 3:53 pm
Thanks again Grant, I will have to dig into this further when I have time. I'd like to understand why parameter sniffing wasn't an issue before, but it is now for that particular procedure.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 25, 2011 at 9:18 am
mtillman-921105 (3/24/2011)
Thanks again Grant, I will have to dig into this further when I have time. I'd like to understand why parameter sniffing wasn't an issue before, but it is now for that particular procedure.
Data changes over time? Different distributions from what it used to be?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply