Post reply

Clustered Index Scan: How to fix ?

  • February 8, 2010 at 2:12 pm

    #229808

    I attached a zip folder with a mdf and code to find out why I get a clustered index scan, when I have index and PK and FK working.

    I would like the SCAN to be a SEEK, based on this info here:

    http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

    Any ideas...

  • February 8, 2010 at 4:33 pm

    #1115863

    I am unable to load your database into my environment.

    Please provide create scripts and data insert scripts.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 8, 2010 at 4:47 pm

    #1115865

    This is code.. script...

    attached

  • February 8, 2010 at 5:06 pm

    #1115870

    Please also provide the test data that you are using to create this scenario.

    Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 8, 2010 at 6:51 pm

    #1115886

    Data...

    here we go..

  • February 8, 2010 at 9:31 pm

    #1115907

    Digs (2/8/2010)

    Data...

    here we go..

    Heh... not exactly a "readily consumable" format Digs. Take a look at the article in the first link in my signature for how to get better help faster.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 8, 2010 at 9:51 pm

    #1115913

    Try a nonclustered index on (BlogID, Page, Deleted) on the DIM_Blog table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2010 at 1:59 am

    #1115976

    Tried that still get the same Execution plan...

    and tried this...

    CREATE NONCLUSTERED INDEX [IND_DIMBlog_BlogIDPageDeleted] ON [dbo].[DIM_Blog]

    (

    [BlogID] ASC,

    [BlogUserID] ASC,

    [Page] ASC,

    [Deleted] ASC

    )

  • February 9, 2010 at 2:52 am

    #1115999

    Please post table definitions, index definitions and actual execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    The exec plan that you posted earlier is the estimated plan and is missing certain key information.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2010 at 11:54 am

    #1116307

    All you ask for is in this thread, just in different posts.:-)

  • February 9, 2010 at 12:09 pm

    #1116314

    Digs (2/9/2010)

    All you ask for is in this thread, just in different posts.:-)

    The only execution plan I see is in your first post. That's an estimated plan. I need to see the actual execution plan. Click the 'include actual execution plan' button on the toolbar and run the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2010 at 1:00 pm

    #1116345

    oops ok here it is:-D

  • February 9, 2010 at 4:11 pm

    #1116453

    You have a composite key as your primary key in Dim_Blog and don't use half the key.

    If you add

    And A.BlogTransID = 1

    To your where clause, then a clustered index seek is used.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 9, 2010 at 5:51 pm

    #1116484

    thanks..

    Interesting...

    HOW do I a SELECT TOP 120 * FROM <table> records on the BlogUserID auto field

    ????

  • February 9, 2010 at 5:58 pm

    #1116491

    Digs (2/9/2010)

    thanks..

    Interesting...

    HOW do I a SELECT TOP 120 * FROM <table> records on the BlogUserID auto field

    ????

    And maintain a clustered index seek?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply