We are using v12.01.3942.

Today we encountered a subqquery on a table with 15+ million rows that has been running very well for many years but is very suddenly running very slowly. I fixed by forcing index usage by adding "WITH INDEX(index_name)" clause.

My question is why all of the sudden would this change - I think it happened mid-day and is there any preventative measures to keep thinks working reliably. Dropping/re-creating statistics or any other ideas.

Thanks, Brian

asked 25 Nov '13, 17:58

bgreiman's gravatar image

bgreiman
345151624
accept rate: 0%

Just wanted to add another fact that I just remembered. I updated about 1 million of these rows this weekend to add some data to a column that was previously null. This is a blob column with average image loaded of about 45k. Could this have caused table fragmentation that would have altered the statistics enough to start using different plans?

(26 Nov '13, 15:33) bgreiman
Replies hidden

Could this have caused table fragmentation

Yes - if rows were shuffled across page boundaries as a result of the additional row data, page fragmentation may have occurred.

that would have altered the statistics enough to start using different plans?

Not by the addition of the data itself, no - unless your queries also started using the new data as part of their sargable predicates.


If you drop the 'WITH INDEX' clause, can you collect detailed plans with statistics from the two situations? Have you tried running 'CREATE STATISTICS' on the tables involved to see if this changes the behaviour?

If the query without a forced index is using a table scan, yes, it is quite possible the statistics may be off and the statistics governor hasn't had time to catch up - the detailed plans with statistics would tell us more about why this situation is occurring.

(26 Nov '13, 20:48) Jeff Albion

Try to analyse query plan, and you find, why server choose another strategy. May be will be better, if after serious data updates, you will run UPDATE STATISTICS instead of index hinting.

permanent link

answered 26 Nov '13, 17:31

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26

question asked: 25 Nov '13, 17:58

question was seen: 675 times

last updated: 26 Nov '13, 21:32