SQL Server Performance

Execution Plan Changes on Query Values?

Discussion in 'Performance Tuning for DBAs' started by Diesel Jay, Jul 22, 2004.

  1. Diesel Jay New Member

    Hello Guys & Gals!

    I've got a SQL 2000 query we're running that seems to bog down based on the value of a single criteria. This query grabs some sales tax data from a variety of tables, and is really only dependent on the Date Range and the State selected. Here is a sample of the query:

    Select Customer.Account, Customer.Contract, CustLs, TaxRecv.ChargeType, RDate, TaxableNet as TP, NonTaxableNet as NTP,
    Zip, State, StTax, County, CnTax, City, CyTax, OtTax
    From Customer, Receipts, ReceiptsDetail, TaxRecvDetail, TaxRecv, Codes, Contract, ContractTaxes
    Where Receipts.Rpaytype not in ('NonCash')
    And Receipts.Company = ReceiptsDetail.Company
    And Receipts.Account = ReceiptsDetail.Account
    And Receipts.Contract = ReceiptsDetail.Contract
    And Receipts.RID = ReceiptsDetail.ReceiptID
    And TaxRecvDetail.Company = ReceiptsDetail.Company
    And TaxRecvDetail.Account = ReceiptsDetail.Account
    And TaxRecvDetail.Contract = ReceiptsDetail.Contract
    And TaxRecvDetail.TaxID = ReceiptsDetail.TaxId
    And TaxRecvDetail.InvID = ReceiptsDetail.InvID
    And TaxRecvDetail.Company = TaxRecv.Company
    And TaxRecvDetail.Account = TaxRecv.Account
    And TaxRecvDetail.Contract = TaxRecv.Contract
    And TaxRecvDetail.TaxId = Tax.TaxID
    And Customer.Company = Receipts.Company
    And Customer.Account = Receipts.Account
    And Customer.Contract = Receipts.Contract
    And Receipts.Rdate between '6/1/2004' and '6/30/2004'
    And Customer.Company = Codes.Company
    And Customer.Account = Codes.Account
    And Customer.Contract = Codes.Contract
    And Customer.Company = Contract.Company
    And Customer.Account = Contract.Account
    And Customer.Contract = Contract.Contract
    And Customer.Company = ContractTaxes.Company
    And Customer.Account = ContractTaxes.Account
    And Customer.Contract = ContractTaxes.Contract
    And Customer.Company=1
    And TaxRecvDetail.State>='MO'
    And TaxRecvDetail.State<='MO' A
    And ContractTaxes.LTaxReportStatus='Y'

    Now, using a State criteria of 'MO', this query will return about 6-8000 records in only a few seconds. What's curious is that if we use a state value of 'TN', which will return only about 200 records, the execution time will extend out past 5 or 6 minutes!

    Furthermore, if I paste this query into QA and run a Sample Execution plan, the plan changes when I alter the State value from MO to TN. [I don't know how to copy/paste the plan here, otherwise I would include it].

    Now, TaxRecvDetail.State is not an Indexed field. However, it seems as if almost every other State value runs quickly, even those states where there are only a handful of records. Hence, I'm not certain that it's related to the actual quantity of records that are retrieved. What I really can't figure out is why the execution plan is changing based upon the value of the TaxRecvDetail.State field. The table joins are all exactly the same, so why would SQL approach it differently?

    Thanx & Best Regards,
    Jay
  2. Luis Martin Moderator

    May be is because statistics.
    How about ITW? Any index recomendations?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. rlahoty New Member

    "SET SHOWPLAN_TEXT ON" in QA and paste the query plans here. Luis, why do you think that this is because of statistics? If statistics was the reason, the query plan would have been bad for all states, not specifically for "TN", right?
  4. Diesel Jay New Member

    Here are the side-by-side queries and execution plans. The following is the actual SQL text and uses the true table names & fields. The text above was copied from an email discussion where I was trying to make the logic of the query "plainer" to some of the users by sustituting "friendly" names for the tables & fields. Let me know what you think...<br /><br /><font color="red"><b>Query for MO State</b></font id="red"><br /><br />1. SQL Text<br /><br />Select LCustomer.Anum,LCustomer.Lnum,CustLs,PTax.ChargeType, RDate,TaxableNet as TP,NonTaxableNet as NTP,Zip,State, StTax,County,CnTax,City,CyTax,OtTax from LCustomer,Preceipts,PreceiptsTaxInv,PtaxInv,Ptax, LInternal, LContract,LTaxes where Preceipts.Rpaytype not in ('NonCash') and Preceipts.Pnum=PreceiptsTaxInv.Pnum and Preceipts.Anum=PreceiptsTaxInv.Anum and Preceipts.Lnum=PreceiptsTaxInv.Lnum and Preceipts.RID=PreceiptsTaxInv.ReceiptID and PtaxInv.pnum=PReceiptsTaxInv.Pnum and PtaxInv.Anum=PReceiptsTaxInv.Anum and PtaxInv.Lnum=PReceiptsTaxInv.Lnum and PtaxInv.PtaxID=PReceiptsTaxInv.PtaxId and PtaxInv.InvID=PReceiptsTaxInv.InvID and PtaxInv.pnum=Ptax.Pnum and PtaxInv.Anum=PTax.Anum and PtaxInv.Lnum=PTax.Lnum and PtaxInv.PtaxId=PTax.PTaxID and Lcustomer.Pnum =Preceipts.Pnum and Lcustomer.Anum =Preceipts.Anum and Lcustomer.Lnum =Preceipts.Lnum and Preceipts.Rdate between '6/1/2004' and '6/30/2004' and Lcustomer.Pnum =LInternal.Pnum and Lcustomer.Anum =LInternal.Anum and Lcustomer.Lnum =LInternal.Lnum and Lcustomer.Pnum =LContract.Pnum and Lcustomer.Anum =LContract.Anum and Lcustomer.Lnum =LContract.Lnum and Lcustomer.Pnum =LTaxes.Pnum and Lcustomer.Anum =LTaxes.Anum and Lcustomer.Lnum =LTaxes.Lnum and Lcustomer.pnum=1 and PTaxInv.State&gt;='MO' and PTaxInv.State&lt;='MO' and LTaxes.LTaxReportStatus='Y'<br /><br />2. Execution Plan<br /><br /> |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum], [LContract].[LNum])=([LTaxes].[Anum], [LTaxes].[Lnum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum]=[LTaxes].[Anum] AND [LContract].[LNum]=[LTaxes].[Lnum]))<br /> |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum], [LContract].[LNum])=([PTax].[Anum], [PTax].[Lnum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />([PTax].[Anum]=[LContract].[ANum] AND [PTax].[Lnum]=[LContract].[LNum]) AND [PTaxInv].[PtaxID]=[PTax].[PtaxID]))<br /> | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum], [LContract].[LNum])=([LInternal].[ANum], [PReceipts].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[ANum]=[LContract].[ANum] AND [PReceipts].[LNum]=[LContract].[LNum]))<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LContract].[pk_LContract] AS [LContract]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[PNum]=1) ORDERED FORWARD)<br /> | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum], [LCustomer].[LNum])=([LInternal].[ANum], [PReceipts].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum]=[LInternal].[ANum] AND [LCustomer].[LNum]=[PReceipts].[LNum]))<br /> | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum] ASC, [LCustomer].[LNum] ASC))<br /> | | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LCustomer]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[PNum]=1))<br /> | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[ANum], [PReceipts].[LNum], [PReceipts].[RID], [PTaxInv].[PtaxID], [PTaxInv].[InvID])=([PReceiptsTaxInv].[Anum], [PReceiptsTaxInv].[Lnum], [PReceiptsTaxInv].[ReceiptID], [PReceiptsTaxInv].[<br /> | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[LNum], [LInternal].[ANum]) WITH PREFETCH)<br /> | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[ANum] ASC, [PReceipts].[LNum] ASC, [PReceipts].[RID] ASC))<br /> | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[ANum], [PReceipts].[LNum])=([LInternal].[ANum], [LInternal].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[ANum]=[PReceipts].[ANum] AND [LInternal].[LNum]=[PReceipts].[LNum]))<br /> | | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[ANum] ASC, [PReceipts].[LNum] ASC))<br /> | | | | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PReceipts]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(([PReceipts].[PNum]=1 AND [PReceipts].[RDate]&gt;='Jun 1 2004 12:00AM') AND [PReceipts].[RDate]&lt;='Jun 30 2004 12:00AM') AND [PReceipts].[RPayType]&lt;&gt;'NonCas<br /> | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LInternal].[PK_LInternal] AS [LInternal]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[PNum]=1) ORDERED FORWARD)<br /> | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PTaxInv].[PK_PTaxInv]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[Pnum]=1 AND [PTaxInv].[Anum]=[LInternal].[ANum] AND [PTaxInv].[Lnum]=[PReceipts].[LNum]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[State]='MO') ORDERED FORWARD)<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PReceiptsTaxInv].[PK_PReceiptsTaxInv]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceiptsTaxInv].[Pnum]=1) ORDERED FORWARD)<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTax].[Anum] ASC, [PTax].[Lnum] ASC))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PTax]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTax].[Pnum]=1))<br /> |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LTaxes].[LTaxReportStatus]='Y'))<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1012]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LTaxes] AS [LTaxes]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LTaxes].[PK_LTaxes] AS [LTaxes]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LTaxes].[Pnum]=1) ORDERED FORWARD)<br /><br /><br /><font color="red"><b>Query for TN State</b></font id="red"><br /><br />1. SQL Text<br /><br />Select LCustomer.Anum,LCustomer.Lnum,CustLs,PTax.ChargeType, RDate,TaxableNet as TP,NonTaxableNet as NTP,Zip,State, StTax,County,CnTax,City,CyTax,OtTax from LCustomer,Preceipts,PreceiptsTaxInv,PtaxInv,Ptax, LInternal, LContract,LTaxes where Preceipts.Rpaytype not in ('NonCash') and Preceipts.Pnum=PreceiptsTaxInv.Pnum and Preceipts.Anum=PreceiptsTaxInv.Anum and Preceipts.Lnum=PreceiptsTaxInv.Lnum and Preceipts.RID=PreceiptsTaxInv.ReceiptID and PtaxInv.pnum=PReceiptsTaxInv.Pnum and PtaxInv.Anum=PReceiptsTaxInv.Anum and PtaxInv.Lnum=PReceiptsTaxInv.Lnum and PtaxInv.PtaxID=PReceiptsTaxInv.PtaxId and PtaxInv.InvID=PReceiptsTaxInv.InvID and PtaxInv.pnum=Ptax.Pnum and PtaxInv.Anum=PTax.Anum and PtaxInv.Lnum=PTax.Lnum and PtaxInv.PtaxId=PTax.PTaxID and Lcustomer.Pnum =Preceipts.Pnum and Lcustomer.Anum =Preceipts.Anum and Lcustomer.Lnum =Preceipts.Lnum and Preceipts.Rdate between '6/1/2004' and '6/30/2004' and Lcustomer.Pnum =LInternal.Pnum and Lcustomer.Anum =LInternal.Anum and Lcustomer.Lnum =LInternal.Lnum and Lcustomer.Pnum =LContract.Pnum and Lcustomer.Anum =LContract.Anum and Lcustomer.Lnum =LContract.Lnum and Lcustomer.Pnum =LTaxes.Pnum and Lcustomer.Anum =LTaxes.Anum and Lcustomer.Lnum =LTaxes.Lnum and Lcustomer.pnum=1 and PTaxInv.State&gt;='TN' and PTaxInv.State&lt;='TN' and LTaxes.LTaxReportStatus='Y'<br /><br />2. Execution Plan<br /><br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LTaxes].[Anum], [LTaxes].[Lnum])=([LContract].[ANum], [LContract].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum]=[LTaxes].[Anum] AND [LContract].[LNum]=[LTaxes].[Lnum]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LTaxes] AS [LTaxes]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LTaxes].[Pnum]=1 AND [LTaxes].[LTaxReportStatus]='Y'))<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum], [LContract].[LNum], [PTaxInv].[PtaxID])=([PTax].[Anum], [PTax].[Lnum], [PTax].[PtaxID]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />([PTax].[Anum]=[LContract].[ANum] AND [PTax].[Lnum]=[LContract].[LNum]) AND [PTaxInv].[PtaxID]=[PT<br /> |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[ANum], [LContract].[LNum])=([LInternal].[ANum], [PReceipts].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[ANum]=[LContract].[ANum] AND [PReceipts].[LNum]=[LContract].[LNum]))<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LContract].[pk_LContract] AS [LContract]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LContract].[PNum]=1) ORDERED FORWARD)<br /> | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum], [LCustomer].[LNum])=([LInternal].[ANum], [PReceipts].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum]=[LInternal].[ANum] AND [LCustomer].[LNum]=[PReceipts].[LNum]))<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[ANum] ASC, [LCustomer].[LNum] ASC))<br /> | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LCustomer]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LCustomer].[PNum]=1))<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[ANum] ASC, [PReceipts].[LNum] ASC, [PReceiptsTaxInv].[PtaxID] ASC))<br /> | |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[Anum], [PTaxInv].[Lnum])=([LInternal].[ANum], [LInternal].[LNum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[Anum]=[LInternal].[ANum] AND [PTaxInv].[Lnum]=[LInternal].[LNum]))<br /> | |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[Anum], [PTaxInv].[Lnum])=([PReceiptsTaxInv].[Anum], [PReceiptsTaxInv].[Lnum]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />((([PReceiptsTaxInv].[Anum]=[PTaxInv].[Anum] AND [PReceiptsTaxInv].[Lnum]=[PTa<br /> | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[LNum], [PReceipts].[ANum]) WITH PREFETCH)<br /> | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceipts].[ANum] ASC, [PReceipts].[LNum] ASC))<br /> | | | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PReceipts]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(([PReceipts].[PNum]=1 AND [PReceipts].[RDate]&gt;='Jun 1 2004 12:00AM') AND [PReceipts].[RDate]&lt;='Jun 30 2004 12:00AM') AND [PReceipts].[RPayType]&lt;&gt;'NonCas<br /> | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PTaxInv].[PK_PTaxInv]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[Pnum]=1 AND [PTaxInv].[Anum]=[PReceipts].[ANum] AND [PTaxInv].[Lnum]=[PReceipts].[LNum]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTaxInv].[State]='TN') ORDERE<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PReceiptsTaxInv].[PK_PReceiptsTaxInv]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PReceiptsTaxInv].[Pnum]=1) ORDERED FORWARD)<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[LInternal].[PK_LInternal] AS [LInternal]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[LInternal].[PNum]=1) ORDERED FORWARD)<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CTL].[dbo].[PTax]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PTax].[Pnum]=1))<br /><br /><br /><br />Thanx & Best Regards,<br />Jay
  5. Diesel Jay New Member

    Anybody have any ideas on this?

    Regards,
    Jay
  6. dhilditch New Member

    Diesel, if you look at the execution plan you should be able to see the index being used under both circumstances.

    When you have found which index provides the results in 6 seconds, use a query hint to instruct the query optimiser to use that index all the time. Try running the query with the other state that was running slowly but with the query hint and see what happens. 5-6 minutes sounds like a table scan to me if your other queries are returning in seconds.

    Dave.
  7. vaxman New Member

    Not that it would help, but the query plans are being truncated in your post. Customer.Company is not referenced at all (and it should be = 1)

    Do you want to know why the plans differ, or how to fix the problem?

    Try

    SET STATISTICS PROFILE ON

    and run the queries.

    Scroll way out to the right in the query plan and see the costs for the various operations. Still probably won't answer many questions though.

    You might add Receipts (FASTFIRSTROW) to the FROM clause.
  8. Diesel Jay New Member

    Hey Guys:

    Thanx for your patience on this. I know enough SQL to get my way around the data, but I'll admit that I have a poor understanding of 'how' SQL works things internally. Looking at the execution plans confuses me, more than anything, but I'm learning as I go here.

    I'm interested both in Why the plan changes, as well as how to address it. I 'SET STATISTICS PROFILE ON' and then ran the Queries. However, the output from there is rather large. Is there a better way/format to present this outside of just copy/paste from QA?

    Thanx,
    Jay
  9. dhilditch New Member

    That's a tricky one because, well for me at least, I prefer to look at the graphical output, and use the stats from the other options just for checking Disk Input/Output and actual time taken to execute the query.

    The main thing to learn when you have the execution plan is that it reads from bottom right to top left - so the top left is the final result. It will perform many things to try and limit the data in the first place, and the most common of these are 'Scans' and 'Seeks' - If you have a Scan, it is having to read all of the data in the relevant item it is scanning. If it is a Seek, it is looking up the info it needs quickly/efficiently in an index. You want 'seeks' FAR more than scans.

    Oh yeah, if you haven't used the Execution Plans before, make sure when you are looking at the graphical output to point your mouse to each icon - it will pop up a lot more info and tell you what kind of process you are looking at. The execution plans are quite complicated, but the great thing about the graphical viewer is that a novice can look at them and get an idea of where his problems lie. For example, you should be able to see a percentage amount under each process - the highest percentage amount is the one you want to deal with first obviously - point to it, find out what process it is, if it's a scan, try and change it to a seek. There's obviously a lot more to it than this, but you can get a lot out of it without having to know it in too much detail.

    Dave.
  10. Diesel Jay New Member

    dhilditch:

    Thanx for the detailed reply -- that makes sense. I'll spend some more time studying the plans. I guess what I really don't understand is 'why' SQL is changing the plan, and making such a poor adjustment? I understand that the estimated number of rows is much smaller for the 'TN' query. What I don't understand is why it would run so much slower and/or pick a much less efficient plan.

    Regards,
    Jay
  11. dhilditch New Member

    Well there are a couple of things - SQL Server maintains statistics about the data contained within its tables. These tell it the likelihood of data being certain values which assists the Query Optimizer in choosing a good execution plan. So it 'could be' that your statistics are out of date - have a look in SQL Books on Line at 'DBCC SHOW_STATISTICS' (type this in at the index tab). You'll see in the detailed text there that it will decide upon its indexes based on the statistics it has collected of the underlying table. You can update the statistics manually if you think they are out of date - this may solve your problem.

    The second thing I would say that could be the cause of the Query Optimizer choosing a bad execution plan is plain and simple that it's getting old and can't be right all the time. I believe that the query optimizer might have been updated at some point in one of the service packs for SQL Server so make sure you have the latest service pack. But even then, it's a very complicated algorithm for the Query Optimizer to guess the best execution plan EVERY time, which is why we are provided with the ability to provide hints for the Query Optimizer. These query hints allow you to say 'here, use this index I've created - it'll work well for this query' and the Query Optimizer will follow your lead and you'll see the difference in the execution plan.

    Have a read of the text at DBCC SHOW_STATISTICS first, then try UPDATE STATISTICS - you'll see a link to that from the first page you go to anyway, and if that doesn't help, go with the query hints.

    Dave.

Share This Page