Actual estimated execution plan recommends index to apply and increase perf by 25%, but doesn't work

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Actual estimated execution plan recommends...

Actual estimated execution plan recommends index to apply and increase perf by 25%, but doesn't work

Rate Topic

Display Mode

Topic Options

Author

Message

davidsalazar01

Posted Sunday, January 13, 2013 9:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:49 PM
Points: 170, Visits: 333

All,

When I run a actual estimated execution plan for a query it will recommend an non-clustered index to apply that will increase performance by 25%, but after I apply the index it still recommends the same index. Does anybody have insight on why SQL would do this? The process query is still running very slow and I need some help.

Thanks in advance!

-Dave

Post #1406557

Lynn Pettis

Posted Sunday, January 13, 2013 9:35 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:31 PM
Points: 21,832, Visits: 27,854

Hard to say, we can't see what you see. Please read the second article I reference below in my signature block regarding assistance with performance problems. It will show you what you should post and how to post it to get the best help for solving performance issues.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1406562

GilaMonster

Posted Monday, January 14, 2013 1:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

There's a bug with missing index DMV where it recommends an index that already exists.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1406583

Grant Fritchey

Posted Monday, January 14, 2013 4:57 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

Bug aside, the only way to be sure what's happening is to read the execution plan to understand why it is doing each operation that it is using to retrieve your data. Then you can figure out if you need to adjust code or structure or both in order to speed up the query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1406657

MyDoggieJessie

Posted Monday, January 14, 2013 8:34 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063, Visits: 3,787

In furthering what has been said regarding the execution plans: look for any lookups and/or scans on large tables, clustered indexes, etc) paying special attention to the ones that have thick arrows - for certain a solution can be found if you attached the execution plan to this thread so everyone can take a look at it.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1407011

« Prev Topic | Next Topic »

Permissions