Wierd one..
-
thats the wierd thing there is no redical difference in hardware...
I've even tried restoring on to my sandbox (small client machine)...using the backup from the night before..and it takes two seconds to return the same query, same params.
-
Krypto, did you check the link Wayne added about parameter sniffing?
-
krypto69 (10/25/2010)
if this was parameter sniffing .wouldn't I see scans in the execution plan?
I don't see scans...all seeks..the execution plan matches the one in stage which runs fast..
I have no proof, but i think that's what the problem is with parameter sniffing.
an execution plan is used/created, and used no matter what. personally i always assumed that a bad plan due to parameter sniffing ends up rbeing ecursively used over and over on a per-row basis, so big MillionRowTables get the same plan called a million times, hence the huge time difference...whether that assumption is really true or not, the results are the same... it takes too long.
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! -
wouldn't the dbcc freeproccache command clear out the cache
and therefore clear out any parameter sniffing issue?
-
krypto69 (10/25/2010)
wouldn't the dbcc freeproccache command clear out the cacheand therefore clear out any parameter sniffing issue?
Not if the next time your run the proc, you use parameters that have better plans with scans... and then all other parameters are better off using seeks.
The point of sniffing the parameters is to have a plan that will work for all (most) possible parameter values. This avoids redundant recompiles and improves performance.... untill you save the wrong plan.
-
Not if the next time your run the proc, you use parameters that have better plans with scans... and then all other parameters are better off using seeks.
I cleared the cache and then ran the 'troubled proc' using same accoutn number...and it showed no scans all seeks..
maybe this will be a clue...this proc is for deposit history on a bank account...if I switch the bank account number it's fine...(same number of rows) returns in seconds..it's only this one account number that's slow.
-
krypto69 (10/25/2010)
Not if the next time your run the proc, you use parameters that have better plans with scans... and then all other parameters are better off using seeks.
I cleared the cache and then ran the 'troubled proc' using same accoutn number...and it showed no scans all seeks..
maybe this will be a clue...this proc is for deposit history on a bank account...if I switch the bank account number it's fine...(same number of rows) returns in seconds..it's only this one account number that's slow.
That's why it sounds like parameter sniffing, or possibly statistics, or some combination of the two. They're related.
"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 -
for statistics - can I just run exec sp_updatestats?
not sure if this helps but I created same proc called myproc2...and ran that using same acount number and got the same slow result.
-
Are the patches that has been applied on prod and staging same?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
krypto69 (10/25/2010)
for statistics - can I just run exec sp_updatestats?not sure if this helps but I created same proc called myproc2...and ran that using same acount number and got the same slow result.
No, it's still the same indication that something must be up with the statistics. Exact same code on the exact same system with the exact same parameters should result in the exact same stored procedure.
sp_updatestats will do a sampled update of your statistics and it's a good starting point. You may find individual stats, tables or indexes, that need a full scan rather than the sampled. In that case you have to use UPDATE STATISTICS WITH FULL SCAN.
"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 -
Thanks Grant ....that did it!!!
THANKS TO EVERYONE THAT HELPED ON THIS THREAD!!
Good karma to you all..
If any of you are in s.florida ...I'll buy you a beer....or two..
-
I know I'm coming in on the rather late. There have been times when I had to open the stored procedure, then copy/paste it to a new SSMS window in order to play with it. Declare all the variables passed in as parameters and set them to what you are passing to the stored procedure.
The play with it a little and observe the various execution plans. What happens if you hard code the values in the query with constants rather than variables? What happens when you use all variables and assign them before running the query.
If you hard code constants in place of the variables and run the query, does it run much faster?
I've found a few instances of very bad parameter sniffing using this type of methodology.
Todd Fifield
-
tfifield (10/26/2010)
I know I'm coming in on the rather late. There have been times when I had to open the stored procedure, then copy/paste it to a new SSMS window in order to play with it. Declare all the variables passed in as parameters and set them to what you are passing to the stored procedure.The play with it a little and observe the various execution plans. What happens if you hard code the values in the query with constants rather than variables? What happens when you use all variables and assign them before running the query.
If you hard code constants in place of the variables and run the query, does it run much faster?
I've found a few instances of very bad parameter sniffing using this type of methodology.
Todd Fifield
The answers to all those questions are "it depends." Parameter sniffing is absolutely not automatically bad. In fact, it's frequently great! You have to assume that it's working fine except when it isn't. I would not say, ever, that local variables are always better or that hard coding values is always better. They're not. It depends on the statistics in question and the parameters against them to determine what the optimizer will do.
"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 -
As per my understading when I do a backup and restore of a database, all statistics are restored as it is. So how come updating the stats helped here?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
SureshS_DBA (10/27/2010)
As per my understading when I do a backup and restore of a database, all statistics are restored as it is. So how come updating the stats helped here?Yeah, stats move with the db... best guess, something updated the stats on one system but not the other.
"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 - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply