SQL Server Performance Forum – Threads Archive

SQL 2005 takes much longer time to execute a SP

Hi I got a Problem with a SP procedure execution which runs perfectly
fine in SQL 2000 within 1 sec and same query when i run on SQL 2005
it takes 3 min 45 sec +- 2 seconds
I got SQL 2000 std and SQL 2005 std RTM running on same machine on
a Windows server 2003 std editon ,
I have a staging envirnoment and its the same there takes much
longer time in SQL 2005
I have couple of more queries which takes longer in SQL 2005 than
SQL 2000
all SPs are taking parameters and executes a select statement
Did the following :
Update stats,rebuilded index ,CL level changed to 80/90 ,did index
defrag but no luck
ANy help is highly appreciated
Thanks
Shree

Why not upgrade SQL 2005 instance to latest service pack? have you checked for execution plan and sort of parallelism issue during this query execution. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

dont forget to refresh stats if this was a recent conversion<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "I have a lovely bunch of coconuts" – Benny Hill

Hello thanks for the reply satya : what i see in the text_plan which interests me is this part of the whole Plan : Like to get some suggesstion on this : |–Table Insert(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />@tblResults), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OmnibusID] = [OurDatabase].[dbo].[tblOmnibus].[OmnibusID] as [o].[OmnibusID],[ClientID] = [OurDatabase].[dbo].[tblOmnibus].[ClientID] as [o].[ClientID],[Title] = [RelationshipAdvoca |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1020]=getidentity((373576369),(2),N’@tblResults’))) |–Table Spool |–Top(ROWCOUNT est 0) |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblHierarchies].[HierarchyTypeID] as [h].[HierarchyTypeID]=[Expr1024])) |–Assert(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />CASE WHEN [Expr1023]>(1) THEN (0) ELSE NULL END)) | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1023]=Count(*), [Expr1024]=ANY([OurDatabase].[dbo].[tblHierarchyTypes].[HierarchyTypeID]))) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblHierarchyTypes].[PK_tblHeirarchyTypes]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblHierarchyTypes].[HierarchyTypeCode]=’COLLS’)) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[AssociationLinkID])) |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibusAssociations].[AssociationTypeID] as [a].[AssociationTypeID]=[Expr1022])) | |–Assert(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />CASE WHEN [Expr1021]>(1) THEN (0) ELSE NULL END)) | | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1021]=Count(*), [Expr1022]=ANY([OurDatabase].[dbo].[tblAssociationTypes].[AssociationTypeID]))) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblAssociationTypes].[tblAssociationTypes_PK]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblAssociationTypes].[AssociationTypeCode]=’OMNITOH | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[MetaContentID])) | |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibus].[OmnibusID] as [o].[OmnibusID]=[OurDatabase].[dbo].[tblOmnibusAssociations].[OmnibusID] as [a].[OmnibusID])) | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[MetaContentID] ASC)) | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibus].[ClientID] as [o].[ClientID]>CASE WHEN CASE WHEN [@eq_ClientID] IS NOT NULL THEN [@eq_ClientID] ELSE (-5) END=(-5) THEN CONVERT(int,[Rela | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibus].[tblOmnibus_PK] AS [o]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[OmnibusID] > CASE WHEN CASE WHEN [@eq_OmnibusID] IS NOT NULL THEN [@eq_OmnibusID] | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibusAssociations].[OmnibusID] as [a].[OmnibusID]>CASE WHEN CASE WHEN [@eq_OmnibusID] IS NOT NULL THEN [@eq_OmnibusID] ELSE (-5) END=(-5) THEN CONVER | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblOmnibusAssociations].[tblOmnibusAssociations_PK] AS [a])) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblMetaContent].[tblMetaContent_PK] AS [mc]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[mc].[MetaContentID]=[OurDatabase].[dbo].[tblOmnibus].[MetaContentID] as [o].[Me |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblHierarchies].[Title] as [h].[Title] like CASE WHEN CASE WHEN [@contains_CollateralType] IS NOT NULL THEN [@contains_CollateralType] ELSE ‘%’ END=’%’ THEN ‘%’ ELSE (‘% |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OurDatabase].[dbo].[tblHierarchies].[tblHierarchies_PK] AS [h]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[h].[HierarchyID]=[OurDatabase].[dbo].[tblOmnibusAssociations].[AssociationLinkID] as i have no idea why SQL 2005 treats this differently where the same query runs within a sec in 2000 and these statements utilises Full text catalogs This is what i get from SQL 2005 DETA for this SP Workload Analysis: Date4/29/2007 Time10:40:37 AM ServerServer name Database(s) to tune: Database Name Workload: Inline Maximum tuning time59 Minutes Time taken for tuning1 Minute Expected percentage improvement: 0.00 Maximum space for recommendation (MB):1784 Space used currently (MB): 832 Space used by recommendation (MB): 832 Number of events in workload: 3 Number of events tuned:3 Number of statements tuned: 4 Percent SELECT statements in the tuned set: 100

How about service pack testing in this case?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Ok a Service Upgrade is sceduled on wednesday just want to make sure that is there any settings which might affect the way the query runs in SQL 2005 version . thanks a lot will definetly share the result cheers
Shree

http://sqlserver-qa.net/blogs/perftune/archive/2007/04/30/how-to-find-that-a-query-could-benefit-from-an-index.aspx
http://sqlserver-qa.net/blogs/perft…-optimizer-reuses-for-better-performance.aspx
http://sqlserver-qa.net/blogs/perft…owcount-affects-the-performance-sql-2005.aspx
..check the above blog references for further information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>