Update index stats on 1 table fixed performance problem?

Question

Update index stats on 1 table fixed performance problem?

SkyBox

SSCarpal Tunnel

Points: 4288
More actions
August 9, 2012 at 12:05 pm

#262541

I did a full index rebuild last night, which updates statistics, right? I also have auto update stats enabled.
All of a sudden I had a perf problem on a form/query beginning today. I used an SSMS add on tool that gives exec plan suggested improvements.
PROBLEM:
Number of actual (1524) and estimated (1) rows differ by more than 10%. Usual reason is bad cardinality estimates due to outdated or missing index statistics.
Can you help my understand why the stats were out of date?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

rebuilding an index only builds the statistics on the columns included in the index; it doesn't do the whole table. so if the statsitics are out of whack for a different column in the table that has no indexes, a bad plan could get created;

auto update statsitics requires 500 rows + 20% of the rows in the table to be modified before the auto updates get kicked automatically;

on a big table, that is often a lot more rows than it takes to make the statistics "stale" and give poorer execution plans.

so updating statistics seperately, or for certain tables much more often that other tables add a significant performance boost.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Is this table fairly active regarding Update/Inserts/Deletes? It is possible that the statistics were automatically updated by SQL Server using a sample of the data, whereas the index rebuild basically rebuilds statistics using a full scan. You may want to rebuild the statistics again (not the index) using a full scan.

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 3

you van use rowmodctr column in sys.sysindexes to check the number of rows inserted,updated or deleted in the index since the last time stats were updated on that index.from SQL Server 2005 onward this value is not accurate but at least gives you a good idea

Pooyan

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 4

Lowell (8/9/2012)
rebuilding an index only builds the statistics on the columns included in the index; it doesn't do the whole table. so if the statsitics are out of whack for a different column in the table that has no indexes, a bad plan could get created;
auto update statsitics requires 500 rows + 20% of the rows in the table to be modified before the auto updates get kicked automatically;
on a big table, that is often a lot more rows than it takes to make the statistics "stale" and give poorer execution plans.
so updating statistics seperately, or for certain tables much more often that other tables add a significant performance boost.

Thanks for the clear explanation. I hadn't considered that only the indexed columns were getting stats updated during the index rebuild.

I will definitely be re-enabling my "update all stats" job to run once per week! So should I update stats AFTER an index rebuild?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

pooyan_pdm (8/9/2012)
you van use rowmodctr column in sys.sysindexes to check the number of rows inserted,updated or deleted in the index since the last time stats were updated on that index.from SQL Server 2005 onward this value is not accurate but at least gives you a good idea

Realizing that sys.sysindexes is depreciated and will be removed from a future version of SQL Server as its there for backward compatibility to SQL Server 2000. I would not use this for any development.

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 6

Lynn Pettis (8/9/2012)
Is this table fairly active regarding Update/Inserts/Deletes? It is possible that the statistics were automatically updated by SQL Server using a sample of the data, whereas the index rebuild basically rebuilds statistics using a full scan. You may want to rebuild the statistics again (not the index) using a full scan.

It is a very active table regarding inserts. I updated the stats on that table with a full scan.

Is it better to disable auto update of stats, if you update them with a full scan weekly?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

SkyBox (8/9/2012)
Lynn Pettis (8/9/2012)
Is this table fairly active regarding Update/Inserts/Deletes? It is possible that the statistics were automatically updated by SQL Server using a sample of the data, whereas the index rebuild basically rebuilds statistics using a full scan. You may want to rebuild the statistics again (not the index) using a full scan.
It is a very active table regarding inserts. I updated the stats on that table with a full scan.
Is it better to disable auto update of stats, if you update them with a full scan weekly?

I have seen systems where specific statistics on a single table needed to by updated every 4 hours to keep the system performing acceptably. It may be worthwhile to turn off autoupdate on specific statisics and rebuild those on a scheduled basis.

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 8

Lynn Pettis (8/9/2012)
pooyan_pdm (8/9/2012)
you van use rowmodctr column in sys.sysindexes to check the number of rows inserted,updated or deleted in the index since the last time stats were updated on that index.from SQL Server 2005 onward this value is not accurate but at least gives you a good idea
Realizing that sys.sysindexes is depreciated and will be removed from a future version of SQL Server as its there for backward compatibility to SQL Server 2000. I would not use this for any development.

Me neither.But there is no replacement for this column after 2000

Pooyan

Lowell SSC Guru Points: 323518 More actions · Answer 9

I thought sysindexes is deprecated.

and that made sys.sysindexes the replacement?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 10

No I don't think so

from MSDN:

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Pooyan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Lowell (8/9/2012)
I thought sysindexes is deprecated.
and that made sys.sysindexes the replacement?

If you check here you'll see that there are actually 4 system views/DMV's that replace sysindexes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Lowell (8/9/2012)
I thought sysindexes is deprecated.
and that made sys.sysindexes the replacement?

sysindexes (or fully qualified sys.sysindexes) is deprecated.

The replacement is sys.indexes

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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