March 8, 2011 at 8:22 am
Folks:
When I execute the following SQL I get the output in 5 secs.
DECLARE @dt DATETIME
SET @dt = '03/04/2011'
SELECT Value FROM vwAssetData
WHERE ADate = @dt
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1
When I execute the same SQL but without passing the Date as variable (hardcoding the date), I get the output in 1 sec.
SELECT Value FROM vwAssetData
WHERE ADate = '03/04/2011'
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1
Any Idea what might be the problem? I thought it might be parameter sniffing but I have also disabled parameter sniffing on the server by enabling Trace Flag 4136
Any help on this is appreciated... I really need the 1 sec. output because it makes a difference when the same SQL is executed 1000 times for different symbols.
Thanks !
March 8, 2011 at 8:28 am
Execution plans please.
Why did you disable parameter sniffing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 8:56 am
Attached are the 2 execution plans (with and without variable). Regarding disbaling parameter sniffing, after we upgraded our databases to SQL 2008 R2 from SQL 2005 version many of our SPs were victim of Parameter sniffing and it was not possible to change all the Production SPs so we thought on disabling parameter sniffing by enabling the Trace flag.
March 8, 2011 at 9:08 am
Hmm , you probably dont want to hear this , but your underlying design is wrong.
An EAV system will , pretty much, always cause this sort ofperformance issue.
Until that is fixed , you will be continually fighting this sort of problem.
March 8, 2011 at 9:25 am
Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?
March 8, 2011 at 10:06 am
On the front page of sql central is a link to erland sommerskogs page.
Suggest you have a read of it, it may help.
March 8, 2011 at 10:07 am
Thats : http://www.sommarskog.se/query-plan-mysteries.html
if you missed it!
March 8, 2011 at 7:42 pm
When you use a local variable, SQL has to estimate the selectivity of the 'where' clause, because it doesn't know the value of the variable when it optimizes the query. If you hard code the value, this problem goes away. If you look at the estimated rows for tblData in both execution plans, you'll see that the counts vary between the 2 plans and a different index is used in each case; this is probably what's causing the discrepancy in query times.
Have a look at 'Compile-Time Expression Evaluation for Cardinality Estimation' here: http://msdn.microsoft.com/en-us/library/ms175933(v=sql.90).aspx - similar issue is discussed there.
March 9, 2011 at 1:41 am
sqldba20 (3/8/2011)
Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?
Erm, this sort of problem you have posted about 😉
If you were to post the view definition we would easily be able to see this is an EAV (Entity , Attribute , Value) system. http://en.wikipedia.org/wiki/Entity-attribute-value_model
These are bad. Here's quite a nice write up on that http://weblogs.sqlteam.com/davidm/articles/12117.aspx. Thomas Kejser of sqlcat stated at sqlbits a while back,that a majority of performance issues they see (cant remember the exact number) are due to EAV designed systems.
Anyway back to the problem in hand....
Parameter sniffing goes some way to explaining what has happened here , but is not the whole story.
Parameter sniffing problems are caused when 'bad' values get applied to an existing plan in cache, causing that plan to run slow. Simplified, google for a more full explanation.
These plans are radically different, though both are 'good enough' and have similar io costs. The issue is probably the difference between estimated and actual rows returned. If you post the actual plans (as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ ) that will probably confirm that statement.
Why is there a difference between actual and estimated ? , first stop are statistics ,are they out of date ? , use UPDATE STATISTICS to rebuild them.
Post the View definition , table DDL and actual plans and ill see if i can expand further.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply