SQL Server Performance Forum – Threads Archive

performance problem

Hi i have a strange performance problem on my PPT env. a stored procedure is behving really badlly (16 s of execution time). When i restore this database on others local macines this stored procedure behave as usual (less than 1 s execution time). i identified that this long execution time is due to a select with a join in the stored procedure. If i extract the select from the SP and run with query analyser it run well (less than 1 sec) stats updated, dbcc checkdb and index repopulated thanks for your help
Aymeric

Could you post de sp?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi i have more information (all of this code is in a stored procedure): Select *
FROM firsttable B
LEFT OUTER JOIN secondtable F
ON B.CoreSiteCode = F.CoreSiteCode
AND B.SrvCode = F.SrvCode
AND B.PNRID = F.PNRID
WHERE B.CoreSiteCode = @DefaultProfilecoreSiteCode (with @DefaultProfilecoreSiteCode=’0AFF’) AND B.SiteCode = @DefaultProfileSiteCode
AND B.TravelerID = @TravelerID the execution plan shows that the secondtable table seek takes 4,000,000 rows! and the execution time is 13s. this bad behavior only occurs on this server
impossible to reproduce on different local servers with a backup of this database. if i use this code Select *
FROM firsttable B
LEFT OUTER JOIN secondtable F
ON B.CoreSiteCode = F.CoreSiteCode
AND B.SrvCode = F.SrvCode
AND B.PNRID = F.PNRID
WHERE B.CoreSiteCode = ‘0AFF’
AND B.SiteCode = @DefaultProfileSiteCode
AND B.TravelerID = @TravelerID the execution plan shows that the secondtable table seek takes 690 rows and the execution time is 1s i found a workaround by putting: Select *
FROM firsttable B
LEFT OUTER JOIN secondtable F
ON B.CoreSiteCode = F.CoreSiteCode
AND B.SrvCode = F.SrvCode
AND B.PNRID = F.PNRID
WHERE B.CoreSiteCode = @DefaultProfilecoreSiteCode
AND B.SiteCode = @DefaultProfileSiteCode
AND B.TravelerID = @TravelerID
and f.CoreSiteCode = @DefaultProfilecoreSiteCode the execution time is 1s It makes no sense to me , i guess its a bug in SQL server SP processing but i cant take the risk of a guess, it occured on PPT server fortunaly,
thanks for your help
Aymeric

Obviously different exectution plans are used. Are @DefaultProfilecoreSiteCode, @DefaultProfileSiteCode and @TravelerID local variables or parameters?

no, the execution plans are identical the only difference is concerning secondtable clustered index seek bad behavior
row count: 4805160
number of execute 690
estimated row count:69 good behavior
row count: 690
number of execute 690
estimated row count:1 @DefaultProfileSiteCode and @DefaultProfilecoreSiteCode
are local variables and @TravelerID a parameter

Hi some more interresting information concerning this problem. in fact the secondtable index seek execution plans are different . For some reason only one field of the 3 join fields are taken into account by the engine to perform the index seek when it behave badly. StmtText |–Nested Loops(Left Outer Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[secondtable].[SrvCode]=[firsttable].[SrvCode] AND [secondtable].[PNRID]=[firsttable].[PNRID])) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />STARTUP EXPR([@DefaultProfileCoreSiteCode]>=’ A’))) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ETV_AERGOASIA_PSGetPNRInfo].[dbo].[firsttable].[UK_firsttable]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[firsttable].[CoreSiteCode]=[@DefaultProfileCoreSiteCode] AND [firsttable].[SiteCode]=[@DefaultProfileSiteCode] AND [firsttable].[TravelerID]=[@TravelerID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />((((((((([firsttable].[GDSCode]=[@GDSCode] OR [@GDSCode]=NULL) OR [@FilterPNROnGDS]=’N’) AND ([firsttable].[PNRID]=[@PNRID] OR [@PNRID]=NULL)) AND ([firsttable].[PNRLocator]=[@PNRLocator] OR [@PNRLocator]=NULL)) AND ([firsttable].[OfficeID]=[@OfficeID] OR [@OfficeID]=NULL)) AND (([firsttable].[ActiveCoreSiteCode]=[@CoreSiteCode] AND [firsttable].[ActiveSiteCode]=[@SiteCode]) OR [@PNRSharing]=’YES’)) AND ([@LastName]=NULL OR [firsttable].[LastName]=[@LastName])) AND ([@AgencyID]=NULL OR [firsttable].[AgencyID]=[@AgencyID])) AND ([@AgentID]=NULL OR [firsttable].[AgentID]=[@AgentID])) AND [firsttable].[IsHidden]=NULL) ORDERED FORWARD) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />STARTUP EXPR([@DefaultProfileCoreSiteCode]>=’ 0′))) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ETV_AERGOASIA_PSGetPNRInfo].[dbo].[secondtable].[PK_secondtable]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[secondtable].[CoreSiteCode]=[@DefaultProfileCoreSiteCode]) ORDERED FORWARD) StmtText |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[firsttable].[SrvCode], [firsttable].[PNRID])) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />STARTUP EXPR([@DefaultProfileCoreSiteCode]>=’ A’))) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ETV_AERGOASIA_PSGetPNRInfo].[dbo].[firsttable].[UK_firsttable]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />STARTUP EXPR([@DefaultProfileCoreSiteCode]>=’ 0′))) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ETV_AERGOASIA_PSGetPNRInfo].[dbo].[secondtable].[PK_secondtable]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[secondtable].[CoreSiteCode]=[@DefaultProfileCoreSiteCode] AND [secondtable].[SrvCode]=[firsttable].[SrvCode] AND [secondtable].[PNRID]=[firsttable].[PNRID]) ORDERED FORWARD)

What about parameter/local variable question?

Hi @DefaultProfileSiteCode and @DefaultProfilecoreSiteCode
are local variables and @TravelerID a parameter Aymeric

Is it possible to make local variables parameters? Having local variables as query parameters sometimes leads to less then optimal execution plan.

for information i found the problem. The secondtable was corrupted. By droping and recreating it i solved this bad behavior. DBCC with repair rebuild didnt see/corrected anything Aymeric

]]>