Getting error when updating stats of a system table

Question

Getting error when updating stats of a system table

Marios Philippopoulos

SSC Guru

Points: 57030
More actions
May 16, 2008 at 7:43 am

#154359

When querying sys.sysindexes of my user database, I see a bunch of system tables.
One of them is sysrowsetcolumns.
I run the following to update its statistics:
USE dbName;
UPDATE STATISTICS sysrowsetcolumns WITH FULLSCAN;
I get this error:
Msg 2706, Level 16, State 6, Line 2
Table 'sysrowsetcolumns' does not exist.
Anyone know why this is? I guess I should not be updating the stats of system tables?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

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

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 1

Here is the modified query I use to determine which indexes in my database to run UPDATE STATS on:

SELECTid
,indid
,OBJECT_NAME(id)
,[name]
,rowcnt
,rowmodctr
,STATS_DATE(id, indid) AS Date_LastUpdated
FROM sys.sysindexes WITH ( NOLOCK )
WHERE indid > 0 AND indid < 255 AND
id > 1000 AND -- excluding system tables
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0 AND
rowmodctr <> 0;

Notice the filter "id > 1000". I use this to exclude system tables from processing.

Is this the right way of doing it? Any suggestions?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

Y, dude, you can't really mess with system tables.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 3

I didn't have this problem in SQL 2000.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 4

I would suggest to select only tables from sysobjects where type = 'U' if you want to update statistics by table (why not using sp_updatestats? This operation is pretty improved in 2005)

Wilfred
The best things in life are the simple things

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 5

Wilfred van Dijk (5/17/2008)
I would suggest to select only tables from sysobjects where type = 'U' if you want to update statistics by table (why not using sp_updatestats? This operation is pretty improved in 2005)

Thanks for the response.

Yes, I know that sp_updatestats in SQL 2005 updates only those indexes whose stats has changed since the last update. However, I want to use UPDATE STATISTICS WITH FULL SCAN.

How can I run sp_updatestats with FULLSCAN?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 6

That's a good one! Is seems Microsoft onnly implemented the @resample option :hehe:

However, it's possible to create a modified sp_updatestats (with the fullscan option). The sourcecode for sp_updatestats is not encrypted:

sp_helptext sp_updatestats

Looking forward to see all the update statistics options implemented in this version 😉

Wilfred
The best things in life are the simple things

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 7

Wilfred van Dijk (5/17/2008)
That's a good one! Is seems Microsoft onnly implemented the @resample option :hehe:
However, it's possible to create a modified sp_updatestats (with the fullscan option). The sourcecode for sp_updatestats is not encrypted:
sp_helptext sp_updatestats
Looking forward to see all the update statistics options implemented in this version 😉

I've noticed that FULLSCAN has resolved some of the performance issues we have been having in the past; that's why we have decided to implement it widely in our environment.

I'll take a look at the sp_updatestats code and see if I can customize it for this need... 😎

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

lara31 SSCommitted Points: 1802 More actions · Answer 8

this may not help but i when i get a message "does not exist" the soluton that i use is to fully qualify the stored procedure name.

hope that works

[font="Comic Sans MS"][/font]It is what it is.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

In SQL 2005 the system tables are 'hidden'. You cannot query them or affect them in any way. The SQL parser acts as though they aren't there.

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

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 10

GilaMonster (5/19/2008)
In SQL 2005 the system tables are 'hidden'. You cannot query them or affect them in any way. The SQL parser acts as though they aren't there.

Thank you for the response, that makes sense.

Don't these tables need their stats updated though, just like user tables?

I see that some of them have not been updated since 2005 (!), if that makes any sense...

SELECTid
,indid
,OBJECT_NAME(id)
,[name]
,rowcnt
,rowmodctr
,STATS_DATE(id, indid) AS Date_LastUpdated
FROM sys.sysindexes WITH ( NOLOCK )
WHERE indid > 0 AND indid < 255 AND
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0 AND
rowmodctr <> 0

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 11

From BOL on UPDATE STATISTICS:

UPDATE STATISTICS WITH RESAMPLE updates all the statistics on a table at the current sampling rate. This means that statistics tied to indexes, which are created with full scan when the index is built, require the whole table scan to be refreshed. This potentially can be a very time consuming operation, especially when it involves large partitioned tables with many indexes. Refreshing each statistic requires reading lots of data. To avoid this problem, consider using sp_updatestats (Transact-SQL). This updates statistics only when they are required.

What I read from this is that the resample option will perform a FULL SCAN for statistics created on indexes. For statistics that are manually created, the resample option will perform the same sampling that was used when the statistics were created.

Jeff

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 12

jeff.williams3188 (5/20/2008)
From BOL on UPDATE STATISTICS:
UPDATE STATISTICS WITH RESAMPLE updates all the statistics on a table at the current sampling rate. This means that statistics tied to indexes, which are created with full scan when the index is built, require the whole table scan to be refreshed. This potentially can be a very time consuming operation, especially when it involves large partitioned tables with many indexes. Refreshing each statistic requires reading lots of data. To avoid this problem, consider using sp_updatestats (Transact-SQL). This updates statistics only when they are required.
What I read from this is that the resample option will perform a FULL SCAN for statistics created on indexes. For statistics that are manually created, the resample option will perform the same sampling that was used when the statistics were created.
Jeff

Thank you, I was thinking of this earlier, but wasn't sure if it was equivalent to the "FULL SCAN" option.

I'm still not sure though how an index is CREATED with FULLSCAN... There is no such option in the "CREATE INDEX" statement that I am aware of.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

Statistics on indexes are automatically created with full scan and will remain that way unless you update statistics with a sampling after the indexes are created.

Well - that is how I read it.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

Marios Philippopoulos (5/20/2008)
Thank you for the response, that makes sense.
Don't these tables need their stats updated though, just like user tables?
I see that some of them have not been updated since 2005 (!), if that makes any sense...

Possibly because the data in the tables hasn't been modified significantly since 2005. Don't worry about stats on the system tables. They're generally small and the optimiser probably has special rules to deal with them. Besides, you can't modify them.

Add a filter to your query to ensure you only get user tables

... AND OBJECTPROPERTY(id,'IsUserTable') = 1

Be aware that sysindexes is a compatibility view, is deprecated and will be dropped in future versions. Also,

Books Online
In SQL Server 2005, rowmodctr is not fully compatible with earlier versions.
In earlier versions of SQL Server, the database engine maintained row-level modification counters. In SQL Server 2005, such counters are maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

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