SQL Server Performance

Query Tuning with ShowPlan_Text

Discussion in 'Performance Tuning for DBAs' started by sonnysingh, Oct 11, 2006.

  1. sonnysingh Member

    Hi Folks <br /><br />I have unusual(I think) problem with tuning queries that two identical queries are showing different SHOWPLAN_TEXT..look at this and Please tell me how I sort this out. I have checked the data types, indexes on require columns, Reindex them as well, data existing in refer columns in queries etc.. but no luck and could able to locate why same queries with same indexes showing different plan. Please help....<br /><br />Query 1:<br />UPDATETR<br />SETTR.ValidConsumed = 6 <br />FROMPL_TransactionData_Receipts TR <br />INNER JOIN PL_SUPPLIER S ON TR.Supplier_Code = S.Supplier_Code<br />WHERES.Invalid_date IS NOT NULL AND TR.ValidConsumed = 0<br /><br /><pre id="code"><font face="courier" size="2" id="code">|--Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_TransactionData_Receipts].[NCIX_Supplier_Code]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Status1009]=[PL_TransactionData_Receipts].[Status], [ValidConsumed1008]=RaiseIfNull([TR].[ValidConsumed]), [Supplier_Code1007]=[TR].[Supplier_Cod<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[TR].[Supplier_Code] ASC, [TR].[ValidConsumed] ASC, [PL_TransactionData_Receipts].[Status] ASC, [Bmk1003] ASC, [Act1005] ASC))<br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_TransactionData_Receipts].[CIX_ValidConsumed_Status]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PL_TransactionData_Receipts].[Consumed_Date]=[PL_TransactionData_Receipts].[Consumed_Date], [PL_TransactionData_Receipt<br /> |--Table Spool<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[TR].[ValidConsumed] ASC, [PL_TransactionData_Receipts].[Status] ASC, [Bmk1000] ASC, [Act1005] ASC))<br /> |--Split<br /> |--Top(ROWCOUNT est 0)<br /> |--Parallelism(Gather Streams)<br /> |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />.[Supplier_Code]=[TR].[Supplier_Code]))<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_TransactionData_Receipts].[CIX_ValidConsumed_Status] AS [TR]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[TR].[ValidConsumed]=0) ORDERED FORWARD)<br /> |--Table Spool<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_Supplier] AS ))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_Supplier].[NCIX_Invalid_Date] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />.[Invalid_Date] IsNotNull) ORDERED FORWARD)</font id="code"></pre id="code"> <br /><br />Query 2:<br />UPDATE TC<br />SETTC.ValidConsumed = 6 <br />FROMPL_TransactionData_Commitments TC <br />INNER JOIN PL_SUPPLIER S ON TC.Supplier_Code = S.Supplier_Code<br />WHERES.Invalid_date IS NOT NULL AND TC.ValidConsumed = 0<br /><pre id="code"><font face="courier" size="2" id="code"> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_TransactionData_Commitments].[CIX_ValidConsumed_Status]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PL_TransactionData_Commitments].[ValidConsumed]=RaiseIfNull(6)))<br /> |--Top(ROWCOUNT est 0)<br /> |--Sort(DISTINCT ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000] ASC))<br /> |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />.[Invalid_Date]&lt;&gt;NULL))<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_Supplier] AS ))<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[TC].[Supplier_Code]))<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_TransactionData_Commitments].[CIX_ValidConsumed_Status] AS [TC]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[TC].[ValidConsumed]=0) ORDERED FORWARD)<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[IXAppDB_Prod].[dbo].[PL_Supplier].[NCIX_Supplier_Code] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />.[Supplier_Code]=[TC].[Supplier_Code]) ORDERED FORWARD)</font id="code"></pre id="code"><br /><br />Thanks in Advance.....
  2. Chappy New Member

    But these two queries arent identical. They both reference a different table

    Assuming for a moment, that the two tables have identical schema, different query plans could result from column statistics, and from the amount and nature of the data in the tables.

    Is the fact that the query plans differ, causing you an actual performance problem?
  3. sonnysingh Member

    Thanks Chappy

    Both queries are identical except one table use in the queries. but columns and data is identical and indexes on the columns are same. Like I said before that I did use reindex, update stats, swap the query before and after in the store procedure and make sure the data types of the columns and above all mak sure that indexes on these columns are same types.

    But could not able locate the why first query take more time than second one??? can you tell by looking at execution plan???

    Thanks...

Share This Page