November 7, 2011 at 2:47 am
Hi,
I have a query that is causing me problems. I restore the db run the sp and it takes 20+ seconds. I do nothing but Update stats with fullscan and it runs in approx 1/4 of a second. All fine - I then recompile the sp and it takes 20+ seconds. Looking at the query plan - I notice one table with estimated rows of 1 and actual rows of 280875!!! The stats for this table have just been updated.
Help!!
November 7, 2011 at 3:37 am
When you update stats you force a recompile for the procedure, so I don't uderstand what the difference might be after you recompile it manually.
Are you sure you're not running into bad parameter sniffing?
-- Gianluca Sartori
November 7, 2011 at 4:16 am
Hi Gianluca,
Silly question but how do I identify bad parameter sniffing? I am running the sp with identical parameters on each run and get massively different performance.
Regards
Andy
November 7, 2011 at 4:32 am
Identical parameters? I would exclude parameter sniffing then.
Can you post tables + indexes definition and good + bad actual execution plans?
-- Gianluca Sartori
November 7, 2011 at 4:47 am
There are a number of tables / indexes in use here - What I don't get is if I've just updated stats how it can be so massively wrong
November 7, 2011 at 5:43 am
Can you post the plans at least?
-- Gianluca Sartori
November 8, 2011 at 9:20 am
Andy Reilly (11/7/2011)
Hi,I have a query that is causing me problems. I restore the db run the sp and it takes 20+ seconds. I do nothing but Update stats with fullscan and it runs in approx 1/4 of a second. All fine - I then recompile the sp and it takes 20+ seconds. Looking at the query plan - I notice one table with estimated rows of 1 and actual rows of 280875!!! The stats for this table have just been updated.
Help!!
estimate 1 actual bajillion is often from a) parameter sniffing (already mentioned), b) table variable usage or c) UDF usage. Are any of those second two in play?
also, how can you expect us to help you without seeing code and table definitions (with row counts and indexes) and execution plans? We are good here but not THAT good! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2011 at 9:23 am
TheSQLGuru (11/8/2011)
Andy Reilly (11/7/2011)
Hi,I have a query that is causing me problems. I restore the db run the sp and it takes 20+ seconds. I do nothing but Update stats with fullscan and it runs in approx 1/4 of a second. All fine - I then recompile the sp and it takes 20+ seconds. Looking at the query plan - I notice one table with estimated rows of 1 and actual rows of 280875!!! The stats for this table have just been updated.
Help!!
estimate 1 actual bajillion is often from a) parameter sniffing (already mentioned), b) table variable usage or c) UDF usage. Are any of those second two in play?
also, how can you expect us to help you without seeing code and table definitions (with row counts and indexes) and execution plans? We are good here but not THAT good! :hehe:
Stop underestimating the power of the Force :-D.
November 8, 2011 at 10:08 am
Ninja's_RGR'us (11/8/2011)
TheSQLGuru (11/8/2011)
Andy Reilly (11/7/2011)
Hi,I have a query that is causing me problems. I restore the db run the sp and it takes 20+ seconds. I do nothing but Update stats with fullscan and it runs in approx 1/4 of a second. All fine - I then recompile the sp and it takes 20+ seconds. Looking at the query plan - I notice one table with estimated rows of 1 and actual rows of 280875!!! The stats for this table have just been updated.
Help!!
estimate 1 actual bajillion is often from a) parameter sniffing (already mentioned), b) table variable usage or c) UDF usage. Are any of those second two in play?
also, how can you expect us to help you without seeing code and table definitions (with row counts and indexes) and execution plans? We are good here but not THAT good! :hehe:
Stop underestimating the power of the Force :-D.
Is not "DBA" a synonym for "telepathist"? 😀
November 8, 2011 at 10:11 am
Alexander Suprun (11/8/2011)
Ninja's_RGR'us (11/8/2011)
TheSQLGuru (11/8/2011)
Andy Reilly (11/7/2011)
Hi,I have a query that is causing me problems. I restore the db run the sp and it takes 20+ seconds. I do nothing but Update stats with fullscan and it runs in approx 1/4 of a second. All fine - I then recompile the sp and it takes 20+ seconds. Looking at the query plan - I notice one table with estimated rows of 1 and actual rows of 280875!!! The stats for this table have just been updated.
Help!!
estimate 1 actual bajillion is often from a) parameter sniffing (already mentioned), b) table variable usage or c) UDF usage. Are any of those second two in play?
also, how can you expect us to help you without seeing code and table definitions (with row counts and indexes) and execution plans? We are good here but not THAT good! :hehe:
Stop underestimating the power of the Force :-D.
Is not "DBA" a synonym for "telepathist"? 😀
Well any programmers really!
November 8, 2011 at 10:55 am
Sorry guys / girls,
I was going to post execution plans etc - But there's something odd going on with our dev server at the mo - so not sure they'll be any use - Had index fragmentation of 99% on majority of indexes - lack of meaningful stats. So corrected them and I'm getting completely inconsistent results without adding a new index.
Cheers
Andy
November 8, 2011 at 11:06 am
Andy Reilly (11/8/2011)
Sorry guys / girls,I was going to post execution plans etc - But there's something odd going on with our dev server at the mo - so not sure they'll be any use - Had index fragmentation of 99% on majority of indexes - lack of meaningful stats. So corrected them and I'm getting completely inconsistent results without adding a new index.
Cheers
Andy
Andy,
how many developers work with your dev server? And what are the chances that while you were trying to tune the query somebody run something really expensive or even cleaned the buffer cache with DBCC DROPCLEANBUFFERS command?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply