SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query Performance Help !


SQL Query Performance Help !

Author
Message
sqldba20
sqldba20
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 445
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 !
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224999 Visits: 46321
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

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


sqldba20
sqldba20
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 445
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.
Attachments
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7172 Visits: 8370
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
sqldba20
sqldba20
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 445
Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?
RichB
RichB
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 1065
On the front page of sql central is a link to erland sommerskogs page.

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



RichB
RichB
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 1065
Thats : http://www.sommarskog.se/query-plan-mysteries.html
if you missed it!



JKG
JKG
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 933
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.
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7172 Visits: 8370
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 Wink

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search