optimising makes it 10 times worse?

Question

optimising makes it 10 times worse?

george sibbald

SSC Guru

Points: 104210
More actions
October 12, 2010 at 4:18 pm

#147279

I have a query that runs in about 40 seconds. when a dbreindex is run on the database the execution goes out to 10min 30 seconds. Updating stats with fullscan makes no difference. the tables involved have clustered indexes.
The query is built by a business objects report and its horrendous so I won't post it for now but just ask if anyone has seen this sort of behaviour before?
A nested loop and a filter operation is added to the execution plan and this appears to be what kills it.
This IS SQL 2000 (SP4)
---------------------------------------------------------------------

Viewing 15 posts - 1 through 15 (of 22 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I saw it once in a blue moon on an equivalent system. Vendor built, horrendous structure, etc.

What we eventually puzzled out (without proof, might I add, this is anecdotal) was that the old cache'd plan that would eventually settle out after multiple runs from parameter sniffing/etc was not the one it would recompile with after the statistics changed from the rebuild.

You might try to hint it, but I never did get a clean way to make it behave except for using a very specific 'first run/compile' set of parameters that would force a good plan on the recompile. They were data specific and I made sure each parameter forced the selectivity to nearly 1 row per table in the structure.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

george sibbald SSC Guru Points: 104210 More actions · Answer 2

thanks for the reply craig.

couple of things have been found, restored a different backup, ran a dbreindex script (not a maint plan one) and query was not affected, ran in same time. So I am thinking a data distribution issue?

On the DB where the query was slow we managed to get it performing again by dropping and recreating indexes until it worked. doing this to the clustered index of the largest table fixed it. This index is not very selective at all but would be useful IF they do a lot of range searches on date.

going to do more testing tomorrow to see if there is a safe way to optimise this database going forward.

---------------------------------------------------------------------

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I'm facing the exact same problem in my DB (SQL2005 sp3).

In my scenario, often it's due to bad parallelism. The serial query runs 10 times faster, but the optimizer seems to prefer a parallel query when the indexes are 100% contiguous and statistics completely up to date.

-- Gianluca Sartori

george sibbald SSC Guru Points: 104210 More actions · Answer 4

good point Gianluca.

Its not parallelism in our case, Query runs serial.

I forgot to mention Index was rebuilt with 100% fillfactor when it was 90% before (accidentally :-), but if its fixed......)

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 5

thanks for replying guys and as your reward you get to see some info that might help you one day.......

Reran optimisations in various ways on the 'fixed' database with the following results

Query run time on databases as is45secs

Run maintenance plan dbreindex keep original free space44secs

Run maintenance plan dbreindex set free space to 10%12mins 55sec

Run scripted dbreindex (all tables)45secs

Run sp_updatestats49secs

Run update statistics ALL sampled(all tables)2mins 01 secs- for index stats only 1min 55secs

run update statistics ALL with fullscan (all tables)11mins 51secs- for index stats only 1 min 54secs

which backs up what happened when the clustered index on the largest table was dropped and recreated (its the only one with 100% fillfactor). the maintenance plan had been set up with the 'change free space to 10%' option, which is naughty.

so it seems query is very sensitive to statistics and perhaps the size of the index? What worries me now is that as the table continues to grow we will reach a point where it will always run bad in its current form.

---------------------------------------------------------------------

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Thanks for the feedback, George.

I'm still facing the problem and I checked the maintenance plan. It sets fillfactor 90% as you mentioned.

Let me ask you, since I'm not expert on this topic, how does this affect SELECT performance?

Why is 10% free space naughty?

My index rebuild job updates statistics with sp_udatestats.

-- Gianluca Sartori

george sibbald SSC Guru Points: 104210 More actions · Answer 7

its naughty because it is arbitrarily setting all fillfactors for all indexes to 90%. The index may not have been originally created with that fillfactor and there may have been a good reason for that. 90% is just a 'rule of thumb' setting.

For instance it bit me because this index for this query works better with 100% fill factor.

It affects selects in that you fit more rows per page so the index and therefore the table is smaller. A range search will have to do less IO.

the downside is increased page splitting because there is no free space to insert new rows, therefore the index will fragment quicker. You would not use 100% fillfactor if the table was clustered on a GUID for example. So you have to know the activity on the table and how data is inserted.

In my example this difference seems to be enough to cause the optimiser to choose a less optimum query plan. I gave not come across this before but it goes to show what can happen.

Unfortunately all your indexes will have been rebuilt to 90% so you won't know the original fillfactor. the option to keep original free space should always be used, to my mind 'keep the original free space' should be the default action.

---------------------------------------------------------------------

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

optimising makes it 10 times worse?

Then I would suggest that someone sucks at optimizing :hehe:

I'm glad you got this figured out.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

Gianluca Sartori (10/14/2010)
My index rebuild job updates statistics with sp_udatestats.

Just in case you did not realise Gianluca this step may be superfluous. If your index rebuild is the default maintenance plan or an explicit dbreindex script this will be rebuilding your stats at a sampling rate SQL determines as 'sufficient'.

sp_updatestats does the same thing, but as you can see the results might not be as good (your mileage may vary)

---------------------------------------------------------------------

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Fortunately this is not what I'm doing. I'm setting fillfactor only for two tables, that are the most important tables in my database. They're heavily read and written, most updated but also inserted (with a lower rate).

For all other tables I'm leaving fillfactor as it is.

Performance counters record a high forwarded record read / page split rate, so I thought that changing fillfactor to 90% would have solved the issue.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

Ninja's_RGR'us (10/14/2010)
optimising makes it 10 times worse?
Then I would suggest that someone sucks at optimizing :hehe:
I'm glad you got this figured out.

:hehe::hehe::hehe::hehe: This is defintely the case with me!

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

george sibbald (10/14/2010)
Gianluca Sartori (10/14/2010)
My index rebuild job updates statistics with sp_udatestats.
Just in case you did not realise Gianluca this step may be superfluous. If your index rebuild is the default maintenance plan or an explicit dbreindex script this will be rebuilding your stats at a sampling rate SQL determines as 'sufficient'.
sp_updatestats does the same thing, but as you can see the results might not be as good (your mileage may vary)

I'm running updatestats because I'm not rebuildng all indexes, but just those that are fragmented more than 30%. All the indexes with a lower fragmentation are just defragmented, so they need updatestats.

-- Gianluca Sartori

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Gianluca Sartori (10/15/2010)
Ninja's_RGR'us (10/14/2010)
optimising makes it 10 times worse?
Then I would suggest that someone sucks at optimizing :hehe:
I'm glad you got this figured out.
:hehe::hehe::hehe::hehe: This is defintely the case with me!

I couldn't let that one go. I hope I didn't offend anyone :-D.

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

When you're telling the truth, nobody should feel offended! 🙂

-- Gianluca Sartori