SQL Server Performance

Query Tuning

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by EasuBoy, Jan 17, 2011.

  1. EasuBoy New Member

    /*
    set statistics time on
    set statistics io on
    */
    select distinct a.keyinstn, e.keyperson
    from snl_new..instn a
    inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
    and e.keyanalysttype = 0
    and e.updoperation < 2
    left join snl_new..instnreg f on a.keyinstn = f.keyinstn
    and f.keyownershipstructure in (1,5,9)
    and a.instndoesnotprice = 1
    and d.keyindustry = 35
    and a.gaapdomainclass <> 12
    and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    inner join snl_new..finleop g on a.keyinstn = g.keyinstn
    inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
    inner join snl_new..sourcetaggingdataitem i on ((g.keyfinleop = i.oid and i.keytable = 560) or (h.keyfinl = i.oid and i.keytable = 107))
    inner join snl_new..sourcetagging j on i.keysourcetaggingdataitem = j.keysourcetaggingdataitem
    inner join lookup..fiscalperiod k on h.fiscalquarter = k.fiscalquarter
    inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
    and j2.keytable = 560
    and j2.firstnormalapproval = 1
    and j2.statustrackingdate >= '2009-12-04'
    and j2.statustrackingdate < '2010-03-20'
    and j2.updoperation < 2
    where d.keyindustry = 34
    and a.gaapdomainclass not in (12,13)
    and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
    and j.keysourcetaggingconstanttype in (7,8,9)
    and k.lookupindicatorforderivedrow = 0
    and a.updoperation < 2
    and d.updoperation < 2
    and g.updoperation < 2
    and h.updoperation < 2
    and i.updoperation < 2
    and j.updoperation < 2
    and k.updoperation < 2
    and j2.updoperation < 2
    and f.keyinstn is null
    union
    select distinct a.keyinstn, e.keyperson
    from snl_new..instn a
    inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
    and e.keyanalysttype = 0
    and e.updoperation < 2
    left join snl_new..instnreg f on a.keyinstn = f.keyinstn
    and f.keyownershipstructure in (1,5,9)
    and a.instndoesnotprice = 1
    and d.keyindustry = 35
    and a.gaapdomainclass <> 12
    and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    inner join snl_new..finleop g on a.keyinstn = g.keyinstn
    inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
    inner join internaluseonly_new..questionnairetracking p on h.keyfinl = p.keyfinl
    and p.keyquestionnaire in (select distinct keyquestionnaire from internaluseonly_new..questionnaires where questionnairefinancial = 1 and updoperation < 2)
    and p.keyquestionnairefinalized in (0,4,7)
    and p.updoperation < 2
    inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
    and j2.keytable = 560
    and j2.firstnormalapproval = 1
    and j2.statustrackingdate >= '2009-12-04'
    and j2.statustrackingdate < '2010-03-20'
    and j2.updoperation < 2
    where d.keyindustry = 34
    and a.gaapdomainclass not in (12,13)
    and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
    and a.updoperation < 2
    and d.updoperation < 2
    and g.updoperation < 2
    and h.updoperation < 2
    and f.keyinstn is null
    order by e.keyperson


    Here is the logical read details. Its taking nearly 6 minutes to fetch just 62 rows.
    I have attached execution plan details for the same.
    Can anyone let me know what can be done to reduce logical read and time?

    Table 'FiscalPeriod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Industry'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FinlEOP'. Scan count 535, logical reads 1789, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Instn'. Scan count 10, logical reads 2222, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'StatusTracking'. Scan count 10, logical reads 46402, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 4, logical reads 132511338, physical reads 2108, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SourceTaggingDataItem'. Scan count 8, logical reads 191188, physical reads 7, read-ahead reads 37, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Finl'. Scan count 46319, logical reads 173089, physical reads 641, read-ahead reads 992, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SourceTagging'. Scan count 11, logical reads 131, physical reads 10, read-ahead reads 100, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'AnalystsInstn'. Scan count 65, logical reads 761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Questionnaires'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FinlEOPFilter'. Scan count 0, logical reads 159734, physical reads 200, read-ahead reads 799, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'QuestionnaireTracking'. Scan count 5, logical reads 3036, physical reads 10, read-ahead reads 2762, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    /*
    set statistics time on
    set statistics io on
    */
    select distinct a.keyinstn, e.keyperson
    from snl_new..instn a
    inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
    and e.keyanalysttype = 0
    and e.updoperation < 2
    left join snl_new..instnreg f on a.keyinstn = f.keyinstn
    and f.keyownershipstructure in (1,5,9)
    and a.instndoesnotprice = 1
    and d.keyindustry = 35
    and a.gaapdomainclass <> 12
    and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    inner join snl_new..finleop g on a.keyinstn = g.keyinstn
    inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
    inner join snl_new..sourcetaggingdataitem i on ((g.keyfinleop = i.oid and i.keytable = 560) or (h.keyfinl = i.oid and i.keytable = 107))
    inner join snl_new..sourcetagging j on i.keysourcetaggingdataitem = j.keysourcetaggingdataitem
    inner join lookup..fiscalperiod k on h.fiscalquarter = k.fiscalquarter
    inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
    and j2.keytable = 560
    and j2.firstnormalapproval = 1
    and j2.statustrackingdate >= '2009-12-04'
    and j2.statustrackingdate < '2010-03-20'
    and j2.updoperation < 2
    where d.keyindustry = 34
    and a.gaapdomainclass not in (12,13)
    and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
    and j.keysourcetaggingconstanttype in (7,8,9)
    and k.lookupindicatorforderivedrow = 0
    and a.updoperation < 2
    and d.updoperation < 2
    and g.updoperation < 2
    and h.updoperation < 2
    and i.updoperation < 2
    and j.updoperation < 2
    and k.updoperation < 2
    and j2.updoperation < 2
    and f.keyinstn is null
    union
    select distinct a.keyinstn, e.keyperson
    from snl_new..instn a
    inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
    and e.keyanalysttype = 0
    and e.updoperation < 2
    left join snl_new..instnreg f on a.keyinstn = f.keyinstn
    and f.keyownershipstructure in (1,5,9)
    and a.instndoesnotprice = 1
    and d.keyindustry = 35
    and a.gaapdomainclass <> 12
    and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
    inner join snl_new..finleop g on a.keyinstn = g.keyinstn
    inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
    inner join internaluseonly_new..questionnairetracking p on h.keyfinl = p.keyfinl
    and p.keyquestionnaire in (select distinct keyquestionnaire from internaluseonly_new..questionnaires where questionnairefinancial = 1 and updoperation < 2)
    and p.keyquestionnairefinalized in (0,4,7)
    and p.updoperation < 2
    inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
    and j2.keytable = 560
    and j2.firstnormalapproval = 1
    and j2.statustrackingdate >= '2009-12-04'
    and j2.statustrackingdate < '2010-03-20'
    and j2.updoperation < 2
    where d.keyindustry = 34
    and a.gaapdomainclass not in (12,13)
    and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
    and a.updoperation < 2
    and d.updoperation < 2
    and g.updoperation < 2
    and h.updoperation < 2
    and f.keyinstn is null
    order by e.keyperson


    Here is the logical read details. Its taking nearly 6 minutes to fetch just 62 rows.
    I have attached execution plan details for the same.
    Can anyone let me know what can be done to reduce logical read and time?

    Table 'FiscalPeriod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Industry'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FinlEOP'. Scan count 535, logical reads 1789, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Instn'. Scan count 10, logical reads 2222, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'StatusTracking'. Scan count 10, logical reads 46402, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 4, logical reads 132511338, physical reads 2108, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SourceTaggingDataItem'. Scan count 8, logical reads 191188, physical reads 7, read-ahead reads 37, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Finl'. Scan count 46319, logical reads 173089, physical reads 641, read-ahead reads 992, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SourceTagging'. Scan count 11, logical reads 131, physical reads 10, read-ahead reads 100, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'AnalystsInstn'. Scan count 65, logical reads 761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Questionnaires'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FinlEOPFilter'. Scan count 0, logical reads 159734, physical reads 200, read-ahead reads 799, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'QuestionnaireTracking'. Scan count 5, logical reads 3036, physical reads 10, read-ahead reads 2762, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    I am not sure how to attach Query execution plan.
    Most of the indexes doing Index seek and few of them are index Scan.
    I have tried updating statistics on SourceTagging DataItem.
    First of the query is taking 6 minutes to execute. Second part of the query is just taking 2-3 second.
    Please let me know for any help
    Thanks
    Harry

  2. satya Moderator

    Welcome to the forums.
    What will be rowcount from this query execution?
    How about underlying indexes for all the tables involved in this query?
  3. RamJaddu Member


    First step you should look to fine tune this query is avoid the physical reads. Physical read indicates total number of data pages that are read from disk. In case no data in data cache.
    Tables like Worktable, Finl, FinlEOPFilter ect having lots of physical reads. You might look to add some covering indexes, also amount of data the query was processing looks huge - we don't know row size for each tables from your information.
    You should find a way processes rows which you exactly required ie avoid pulling unwanted rows from big tables like Worktable.
  4. Shehap MVP, MCTS, MCITP SQL Server

    Such information above isn't enough to describe performance cirmustances of a query ...

    Anyway, kindly please assure the following by the same order:

    None of the select columns above are computed columns.

    If so , assure they are Persisited Dererministic Columns to gain quite much performance.

    If so, assure there are covering compound indexes on each table ( In simple meaning , each index on each table has :

    1- All select columns in "include columns" part of the index

    2- All Inner join + Where columns are in the "Key column " part of the index.

    3- Then ,you could optimize the index by :

    Changing Key column order to reach the least index Scan + More index Seek with the least CPU + I/O cost
    Also , use the same above where conditions in the index Filter part ( new 2008 technology)

    4- Eventaully , you should check activity monitor to verfiy no CXPacket waits caused by it even it takes 1 sec since it might cause CPU overload while peak up times.

    If any more help, please let me know
  5. preethi Member


    Few additional points:
    • You have DISTINCT and UNION on the query. This may execute multiple sort operations on them. I prefer to place the correct conditions, filters and remove these two operations. (You need to find where the duplicates happen and add the filters to remove them) In some cases, insert all into a temp table and performing DISTINCT could help.
    • I see a few tables scanned multiple times. This generally happens when there is an index seek/key look up operation connected to another operation joined with nested loop. Unless we know the size of the tables, we can't verify whether this is the right behavior. You need to check that
    • I also see a couple of work tables. from my initial reading, it happens because you are joining tables from a different database (Lookup). I prefer to have these tables in the same database (You can think of replicating the tables..) Cross database join is an expensive operation.
    • You have OR conditions, bit wise and operations, NOT IN clause, LEFT JOINS and sub queries. All are leading to possible scans, sub optimal operations. You need to re think of these operations.
    • Most importantly your single query joins 19 tables. Oh.. this is too much. You need to think of getting some of the data into variables, or temp tables and joining them. While I can't say it will improve the performance always, I have seen significance improvement while breaking the queries.
    • At least, you can think of getting the data from common tables (Used in both queries of the UNION Clause) and them perform against the different tables could reduce the complexity and get a much cleaner, simple and effective plan. Remember this: When your query is complex, the possibility of executing a sub optimal plan is high.
    • In some cases, multiple index seeks, perform poorly, compared to single index scan (or even clustered index scan)
    Hope this (too) helps.
  6. preethi Member

    By the way, Welcome to the forums.
    Couple of additional points
    If none of the above suggestions work and you need more help, you need to supply the following.
    1. Structure of the tables involved: column list, their data types lengths
    2. Indexes on these tables
    3. Row size of the tables ( or sp_spaceUsed to get the space involved)
    4. Execution plan
    You may also need to check whether the indexes are defragmented and statistics are up to date

Share This Page