Post reply

Performance Issue

  • June 29, 2011 at 6:15 am

    #240056

    Performance issue with one query. There is one select query from a view which does union all from three queries. Out of those the third query is a simple select from one big table. In execution plan, it shows clustered index seek with over 90% participation. There is already a primary key on this table with 6 columns. Index fragmentation of that table is below 30% and statistics are last updated this week only. What else can be issue ?

  • June 29, 2011 at 6:21 am

    #1345849

    If you post the actual query plan please, experts will be able to help in a more expeditious manner.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • June 29, 2011 at 6:46 am

    #1345872

    Usually doing way more work than necessary is the problem with perf. Sounds like you could have a case like that on your hands.

  • June 29, 2011 at 7:24 am

    #1345914

    Here is the attachment. I've scratched my head blue and black. 🙁

  • June 29, 2011 at 8:24 am

    #1345958

    Any suggestions ?

  • June 29, 2011 at 8:29 am

    #1345964

    Don't have time to tune this ATM. NOt a 2 sec job.

    You have a miss. index warning, you could start with that.

    Try to move the where a little sonner and try to whack the distinct.

    I don't see anything else real obvious but I haven't really had time to analyze this.

  • June 29, 2011 at 11:05 pm

    #1346313

    Can anyone please check and advice.

  • June 30, 2011 at 2:26 am

    #1346374

    create the nonclustered index as

    Cobdate asc

    domainname asc

    row_number asc

    including BookName

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • June 30, 2011 at 4:33 am

    #1346425

    Thanks Syed. I'll try this and reply.

  • June 30, 2011 at 6:55 am

    #1346508

    Just remember, those costs are just estimates, not actual measurements. This means there is no correlation between index fragmentation and what you're seeing in the execution plan and what is actually happening when the query executes.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 30, 2011 at 7:03 am

    #1346519

    sqlnaive (6/29/2011)

    Here is the attachment. I've scratched my head blue and black. 🙁

    The optimizer is timing out on this query, so whatever plan you have is less than optimal.

    Why all the DISTINCT operations. Each of these is an aggregate operation which adds a lot of overhead. Can't you join between tables without a DISTINCT operation? Is your DRI broken or non-existant? I'd work to eliminate those first.

    Also, this is an estimated plan. Can you get an actual plan so that all the stats are available?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 30, 2011 at 8:41 am

    #1346581

    How come Grant ? I saved it from the Execution plan tab once the query ran successfully.

  • June 30, 2011 at 8:55 am

    #1346596

    sqlnaive (6/30/2011)

    How come Grant ? I saved it from the Execution plan tab once the query ran successfully.

    Plz try again.

Viewing 13 posts - 1 through 12 (of 12 total)

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