Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query Performance Help ! Expand / Collapse
Author
Message
Posted Tuesday, March 08, 2011 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:08 AM
Points: 45, Visits: 410
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 !
Post #1074846
Posted Tuesday, March 08, 2011 8:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
Execution plans please.

Why did you disable parameter sniffing?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1074852
Posted Tuesday, March 08, 2011 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:08 AM
Points: 45, Visits: 410
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.

  Post Attachments 
ExecutionPlan_WithVariable.sqlplan (37 views, 39.66 KB)
ExecutionPlan_WithoutVariable.sqlplan (16 views, 40.52 KB)
Post #1074876
Posted Tuesday, March 08, 2011 9:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 1,949, Visits: 8,292
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.






Clear Sky SQL
My Blog
Kent user group
Post #1074893
Posted Tuesday, March 08, 2011 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:08 AM
Points: 45, Visits: 410
Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?

Post #1074913
Posted Tuesday, March 08, 2011 10:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 1,052, Visits: 863
On the front page of sql central is a link to erland sommerskogs page.

Suggest you have a read of it, it may help.



Post #1074947
Posted Tuesday, March 08, 2011 10:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 1,052, Visits: 863
Thats : http://www.sommarskog.se/query-plan-mysteries.html
if you missed it!




Post #1074948
Posted Tuesday, March 08, 2011 7:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 423, Visits: 806
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.
Post #1075263
Posted Wednesday, March 09, 2011 1:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 1,949, Visits: 8,292
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.




Clear Sky SQL
My Blog
Kent user group
Post #1075374
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse