February 9, 2011 at 5:12 pm
Hello All,
I'm new to performance issues so please excuse my ignorance.
I had a bad performing query, it was taking 90 or seconds or so. I reviewed the query and saw I could add an additional criteria to one of the joins. The revised query was running in about a second.
I decided to try to expand my knowledge by running some side-by-side tests.
I first ran:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
When I ran my queries again they both ran in about a second. I was quite surprised that the un-optimized query was now running quickly. Is there any possible explanation for this?
Thanks
February 9, 2011 at 5:20 pm
A number of them. To answer your first question, yes, a bad execution plan can get stuck in cache. Look up 'parameter sniffing', you'll find an eyeful. 🙂
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
February 9, 2011 at 5:49 pm
Interesting...I'll continue to read up on this.
The proc is fairly lengthy. I can't post it.
CREATE PROCEDURE [dbo].[myProc]
@a varchar(50),
@B_Master_ID int,
@C int
AS
--Record the session
--Do some logging
--Do an insert into a main table using the paramaters, don't see any problems here.
--Do some more logging
--Write to a history table
--Do some analysis and maybe delete from the main table
--Do some more analysis and write to the history table
Here's where I have/had the problem which I can see by the gaps in the logs.
INSERT INTO HISTORY (...)
SELECT
@a,
@b-2,
other fields
FROM MainTable
INNER JOIN...
WHERE Status = 'SoAndSo' --hard coded text, no parameter
So the only place I use the parameters is in the SELECT not the WHERE. Would that still be considered to be parameter sniffing? Or some type of variant of parameter sniffing?
February 10, 2011 at 1:18 am
Chrissy321 (2/9/2011)
<snip>So the only place I use the parameters is in the SELECT not the WHERE. Would that still be considered to be parameter sniffing? Or some type of variant of parameter sniffing?
Nope. Parameter sniffing is when the optimizer decides that for some value of @parameter the where clause needs to scan instead of seek.
Not applicable in this case, though your description reeks of the probability of this occuring in a previous statement in the same code.
Best I can offer is to log each independent call in the same procedure to a logging table. When someone complains about it taking too long, you'll have a record of the exact statement in the larger procedure that's causing the timeout/issue. Once you're at that stage, you can examine the specific execution plan(s), and try to nail down the cause.
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
February 10, 2011 at 6:32 pm
It's also possible, even though the plan changed, that you ran into resource contention or blocking or something else entirely.
Without lots more detail, it's hard to know. Based on everything that proc is doing... writes, reead, writes, reads, you could be looking at all sorts of different issues. It's just hard to know.
"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
February 11, 2011 at 7:37 am
if it REALLY is the insert that is taking so long then either a) you are doing large scans in that SELECT that drives the INSERT or b) your insert itself (or the SELECT that drives it) is getting blocked. Or both! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 11, 2011 at 9:51 am
After clearing the cache the whole procedure is running 5-7 seconds vs 2-7 minutes.
I do believe it was that INSERT based on the logging I was doing and the fact then when I isolated the SELECT driving the INSERT it was running 2+ minutes. After the cache purge it was sub second.
I appreciate your responses given that I have not provided and real code, definitions, sample data etc. Doing so could create what I call a RGE, resume generating event.
Even though it is running fine now I have also subsequently refined one of my joins on the SELECT to hopefully prevent whatever happened previously from happening again.
thanks all.
February 11, 2011 at 10:44 am
It could also be that your procedure plan was compiled against stale statistics and then cached.
Did some index/statistics maintenace run meanwhile?
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply