Post reply

Performance issues with SQL Server Stored Procedures

  • January 7, 2009 at 11:01 am

    #922744

    ALTHOUGH...

    Now that I think about it, it would probably have to be a compound index using both columns and probably another column because, based on the data types and the data represented, these columns by themselves are unlikely to be selective enough to support an index.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 7, 2009 at 1:53 pm

    #922898

    Eeep. That's a lot of execution plans.

    I'm not looking at all of them. That'll take hours.

    A few highlights:

    Exec plan 3:

    The country table needs an index. CountryID INCLUDE name, or, if CountryID is supposed to be the row identifier, make it the primary key

    Exec plan 3, 6, 17:

    The ContactUs table could really use an index. On ID would be a good start, perhaps including Country and StudyWhere. Those table scans look unpleasant.

    If that's supposed to be the identifier for the row, maybe it should be the PK?

    Exec plan 19:

    The index idx_Leads_LeadID could be widened to INCLUDE the Lead_WorkflowID column

    Exec plan 32:

    Looks like a lot of tables do not have a clustered index. Workflow, workflowstate, workflowTransition. Is this intentional?

    idx_Wktr_Deleted could maybe be widened to INCLUDE TransitionID, WorkflowID, StateID and NextStateID, and Deleted needs to be added to the index key

    idx_Work_workflowID could be widened with Work_Enabled added to the key

    WorkflowState needs an index. To start with, (Deleted, StateID)

    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

Viewing 2 posts - 16 through 16 (of 16 total)

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