Same Query Diff Exec Plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Same Query Diff Exec Plan

Author

Topic

KPBSF
Starting Member

4 Posts

Posted - 2009-06-22 : 21:39:23

I have a paramiterized query that produces different execution plans when I run then via Query Analyzer or our .net APP. I have a query so simplicity sake exec sp_executesql N'select * form foo where blah=@dor'@dor=1 - The actual query is a little more complicated..but in profiler I see this query doing 3 million reads - I paste the exact query into QA and run it and performance is very slow. I take just the sql part of this query out Select * from foo where blah=1 and run it.. bam..back in 3ms - with 150000 reads. I drop and clean buffer, dropproccache and freesystemcahe etc.. rerun the exec sp_sql... via QA and now my query plan matches and the query returns in 3ms perfect I think! Run this via the our .net application.. and slam back to 3 million some reads and take 2 minutes to return. I refreshed all the stats on all the tables used.. recycled my app pool in IIS.. what is caching and where to cause SQL to use a bad execution plan - the code just does a normal ado.net connection and calls the sql.. nothing fancy..

In profiler i see a cache hit for the query that runs slow - but I tak the same query and run in QA and works like a champ.. and I droppend and cleaned cache buffers etc.. making me pull out my hair! - any ideas? The only thing I see I can think of is where my app is connecting from and the 'application' name that profiler see's but using the same user / pass - ideas?!

asgast
Posting Yak Master

149 Posts

Posted - 2009-06-23 : 08:52:34

I think you should start by showing the query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 09:19:48

1. Parameter sniffing
2. Caching

E 12°55'05.63"
N 56°04'39.26"

KPBSF
Starting Member

4 Posts

Posted - 2009-06-23 : 13:25:40

I have made some movement on this problem, but some oddities are still showing.. if I run dbcc freeproccache on the db where the problem is happening - I get immediate response back when I try the query.... but after 5 minutes I get a bad execution plan and my query takes 2 minutes. As a work around are are going to add with recompile to the SP - but this shouldn't be happening... any idea why after a few minutes sql is using a old / bad execution plan.. the sql is very big I'll paste it below - but its a lot of text..


exec sp_executesql N'SELECT   Campaign . CampaignName ,   CampaignDistribution . CampaignDistributionID ,   CampaignDistribution . LastModifiedDate  AS  SentDate ,   CampaignDistribution . SentCount ,   MailingListMapping . MailingListFriendlyName  AS  MailingList ,   Segmentation . SegmentationName ,   CampaignDistribution . DistributionSubject  AS  Subject , COUNT(  HardBounce . HardBounceID ) AS  HardBounced , COUNT(  SoftBounce . SoftBounceID ) AS  SoftBounced ,   CampaignDistribution . DistributionSubject  AS  EmailName ,   CampaignDistribution . ContentID ,  LPA_L2 . VersionTypesID ,   CampaignDistribution . FriendlyFromAddressID  AS  FriendlyFromAddressId ,   CampaignDistribution . FromAddressID  AS  FromAddressId ,   SuppressionListMapping . Name  AS  SuppressionListName  FROM (((((((((   Campaign  (nolock) INNER JOIN   CampaignDistribution  (nolock) ON    Campaign . CampaignID =  CampaignDistribution . CampaignID ) LEFT JOIN   MailingListMapping  (nolock) ON    MailingListMapping . MailingListMappingID =  CampaignDistribution . MailingListMappingID ) LEFT JOIN   Segmentation  (nolock) ON    Segmentation . SegmentationID =  CampaignDistribution . SegmentationID ) LEFT JOIN   DistributionDetail  (nolock) ON    CampaignDistribution . CampaignDistributionID =  DistributionDetail . CampaignDistributionID ) LEFT JOIN   HardBounce  (nolock) ON    DistributionDetail . DistributionDetailID =  HardBounce . DistributionDetailID ) LEFT JOIN   SoftBounce  (nolock) ON    DistributionDetail . DistributionDetailID =  SoftBounce . DistributionDetailID ) INNER JOIN   Content_Email   LPA_L2  (nolock) ON   LPA_L2 . ContentID =  CampaignDistribution . ContentID ) INNER JOIN   Content   LPA_L1  (nolock) ON   LPA_L1 . ContentID = LPA_L2 . ContentID  AND  LPA_L1 . ContentID = LPA_L2 . ContentID ) LEFT JOIN   SuppressionListMapping  (nolock) ON    SuppressionListMapping . SuppressionListID =  CampaignDistribution . supressionlistid ) WHERE ( ( (   CampaignDistribution . CampaignDistributionID  = @CampaignDistributionId1))) GROUP BY   Campaign . CampaignName ,   CampaignDistribution . CampaignDistributionID ,   CampaignDistribution . LastModifiedDate ,   CampaignDistribution . SentCount ,   MailingListMapping . MailingListFriendlyName ,   Segmentation . SegmentationName ,   CampaignDistribution . DistributionSubject ,   CampaignDistribution . DistributionSubject ,   CampaignDistribution . ContentID ,  LPA_L2 . VersionTypesID ,   CampaignDistribution . FriendlyFromAddressID ,   CampaignDistribution . FromAddressID ,   SuppressionListMapping . Name ',N'@CampaignDistributionId1 bigint',@CampaignDistributionId1=78

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 14:29:59

There is absolutely nothing in this query that justify the need of dynamic SQL.

SELECT		Campaign.CampaignName,
		CampaignDistribution.CampaignDistributionID,
		CampaignDistribution.LastModifiedDate AS SentDate,
		CampaignDistribution.SentCount,
		MailingListMapping.MailingListFriendlyName AS MailingList,
		Segmentation.SegmentationName,
		CampaignDistribution.DistributionSubject AS Subject,
		COUNT(HardBounce.HardBounceID) AS HardBounced,
		COUNT(SoftBounce.SoftBounceID) AS SoftBounced,
		CampaignDistribution.DistributionSubject AS EmailName,
		CampaignDistribution.ContentID,
		LPA_L2.VersionTypesID,
		CampaignDistribution.FriendlyFromAddressID AS FriendlyFromAddressId,
		CampaignDistribution.FromAddressID AS FromAddressId,
		SuppressionListMapping.Name AS SuppressionListName
FROM		Campaign WITH (NOLOCK)
INNER JOIN	CampaignDistribution WITH (NOLOCK) ON CampaignDistribution.CampaignID = Campaign.CampaignID
LEFT JOIN	MailingListMapping WITH (NOLOCK) ON MailingListMapping.MailingListMappingID = CampaignDistribution.MailingListMappingID
LEFT JOIN	Segmentation WITH (NOLOCK) ON Segmentation.SegmentationID = CampaignDistribution.SegmentationID
LEFT JOIN	DistributionDetail WITH (NOLOCK) ON DistributionDetail.CampaignDistributionID = CampaignDistribution.CampaignDistributionID
LEFT JOIN	HardBounce WITH (NOLOCK) ON HardBounce.DistributionDetailID = DistributionDetail.DistributionDetailID
LEFT JOIN	SoftBounce WITH (NOLOCK) ON SoftBounce.DistributionDetailID = DistributionDetail.DistributionDetailID
INNER JOIN	Content_Email AS LPA_L2 WITH (NOLOCK) ON LPA_L2.ContentID = CampaignDistribution.ContentID
INNER JOIN	Content AS LPA_L1 WITH (NOLOCK) ON LPA_L1.ContentID = LPA_L2.ContentID
LEFT JOIN	SuppressionListMapping WITH (NOLOCK) ON SuppressionListMapping.SuppressionListID = CampaignDistribution.supressionlistid
WHERE		CampaignDistribution.CampaignDistributionID = @CampaignDistributionId1
GROUP BY		Campaign.CampaignName,
		CampaignDistribution.CampaignDistributionID,
		CampaignDistribution.LastModifiedDate,
		CampaignDistribution.SentCount,
		MailingListMapping.MailingListFriendlyName,
		Segmentation.SegmentationName,
		CampaignDistribution.DistributionSubject,
		CampaignDistribution.DistributionSubject,
		CampaignDistribution.ContentID,
		LPA_L2.VersionTypesID,
		CampaignDistribution.FriendlyFromAddressID,
		CampaignDistribution.FromAddressID,
		SuppressionListMapping.Name

E 12°55'05.63"
N 56°04'39.26"

KPBSF
Starting Member

4 Posts

Posted - 2009-06-23 : 14:41:00

well actually there is.. we dynamically create this query for different db's and different schemas so it created dynamically.. so any idea why the execution plan is going bad after a few minutes?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 14:44:51

That's the reason Microsoft introduced SYNONYMs.
There is no reason for you to involve dynamic sql at all in the query above.

E 12°55'05.63"
N 56°04'39.26"

KPBSF
Starting Member

4 Posts

Posted - 2009-06-23 : 15:04:44

Well we are using LLBL to generate our DAL and we simply swap out the schema to target a different account table set - how does how we reference the table affect query execution plans?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources