Performance Issue

  • 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 ?

  • 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.

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

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

  • Any suggestions ?

  • 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.

  • Can anyone please check and advice.

  • 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

  • Thanks Syed. I'll try this and reply.

  • 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

  • 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

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

  • 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