SQL is not utilizing an index it told me to create

Question

Post reply

SQL is not utilizing an index it told me to create

SQLOzzie

Default port

Points: 1442
More actions
August 8, 2012 at 8:04 am

#279342

So we had one SP totally dogging it. I CTRL-L it - and it says
--The Query Processor estimates that implementing the following index could improve the query cost by 49.29%.
CREATE NONCLUSTERED INDEX [IX_tblInvoices_Invoice Total_DateAdded] ON [EPData].[dbo].[tblInvoices]
(
[Invoice Total],
[DateAdded]
)
I add the index.
I CTRL-L once again - it says I need to add the same index. What's up? Do I need a REBUILD STATISTICS WITH FULLSCAN - or any idea why this index would now not be pulled in as part of the solution?
2008 R2 SP2
Thanks,
SQLOzzie

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

There's a bug which means SQL will continue to suggest the index, even after you've created it.

After you create the index, has the execution plan changed to use it?

Cheers

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

And take the suggestions for indexes with a grain of salt. The suggestions are not always good ones. You need to test actual performance before and after adding those indexes.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Sean Lange (8/8/2012)
And take the suggestions for indexes with a grain of salt. The suggestions are not always good ones. You need to test actual performance before and after adding those indexes.

Seconded!

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

The easiest, though probably most annoying, solution would be to shut down SSMS, if that's what you're using to implement your query tests, and restart it. Changes to databases don't register until it's restarted.

SQLOzzie Default port Points: 1442 More actions · Answer 5

Gazareth - no - it didn't - that was the source of my post 😉

SQLOzzie Default port Points: 1442 More actions · Answer 6

Donalith - I disagree - usually the second I create the index they are used an not 'resuggested.'

I'm going to reboot, run

DBCC dbreindex ([tblInvoices])

UPDATE STATISTICS [tblInvoices] WITH FULLSCAN

And see what happens.

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 7

Alexander Suprun

SSCertifiable

Points: 6253

August 8, 2012 at 2:58 pm

#1523447

You can try

DBCC FREEPROCCACHE

Alex Suprun

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 8

I wouldn't recommend freeing the ENTIRE PROC cache...as you'll get a performance hit the next time every new statement runs. I'd recommend you use the same DBCC command, but with some specific options so that you only drop the plan from the cache for the query you are running (up to you though)

Try something like this

<< Your query --> SELECT * FROM DB.Table >>SELECT

plan_handle,

st.text

FROM

sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE

text LIKE N'<< Your query --> SELECT * FROM DB.Table >>%';

GO

Find your query plan handle and then execute the DBCC commandDBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

That should drop only the cached plan for the query you are running, ensuring you'll get a BRAND NEW one the very next time you run it

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Douglas Osborne-229812 (8/8/2012)
Donalith - I disagree - usually the second I create the index they are used an not 'resuggested.'
I'm going to reboot, run
DBCC dbreindex ([tblInvoices])
UPDATE STATISTICS [tblInvoices] WITH FULLSCAN
And see what happens.

Note missing index info is cleared when SQL is rebooted, so the suggestion may go away then anyway.

If the new index isn't being used then I guess the optimiser doesn't consider it the best choice!