Update Stats for SQL Server 2005

Question

Update Stats for SQL Server 2005

fais

Mr or Mrs. 500

Points: 559
More actions
September 23, 2008 at 8:46 am

#191225

Hi all,
Below is the script which is built for SQL Server 2000 ( This can also be used for SQL Server 2005). But the requirement is to convert this script by using DMV's with all existing conditions ( as can be seen in where clause).
Please help.
Script:
CREATE PROCEDURE upd_database_stats
@numberofrows int = 10000,
@percentchange int = 15,
@samplerate int = 10
AS
SET NOCOUNT ON
DECLARE @StatsUpdate TABLE
(id int identity(1,1),
TableName sysname,
IndexName sysname)
DECLARE@max-2 int,
@tablename nvarchar(100),
@indexname nvarchar(150),
@sql nvarchar(4000)
INSERT INTO @StatsUpdate
(TableName, IndexName)
SELECT
TableName=OBJECT_NAME(id),
IndexName=name
FROM sysindexes with (nolock)
WHERE
OBJECTPROPERTY(id,'IsSystemTable')=0
AND indid>0
AND indid<255
AND rowcnt > @numberofrows
AND CAST(rowmodctr as numeric (9,0)) / CAST(rowcnt as numeric(9,0)) * 100 > @percentchange
select @max-2 = max(id) from @StatsUpdate
while @max-2 <> 0 begin
select @tablename = TableName, @indexname = IndexName from @StatsUpdate where id = @max-2
print 'Updating Statistics for ' + @tablename + ' ' + @indexname
select @sql = 'UPDATE STATISTICS ' + @tablename + ' ' + @indexname + char(13)
+ ' WITH SAMPLE ' + CONVERT(nvarchar(3), @samplerate) + ' PERCENT'
EXEC sp_executesql @sql
select @max-2 = @max-2 - 1
end

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Most of that you can get by using sys.indexes instead of sysindexes. The row count you can get from sys.dm_db_index_physical_stats.

The one thing you won't be able using the DMVs is the rowmodctr. In 2000 that was kept at a per-table level. In SQL 2005, the count of modifications is kept at a per-column level and is not exposed in any of the DMVs or system views.

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

fais Mr or Mrs. 500 Points: 559 More actions · Answer 2

You hit the bird at the right spot (i.e.) no rowmodctr in SQL Server 2005.

But how is this possilbe, There should be some way where we can convert the script by using DMV and also have something similar to rowmodctr where we have more control with the script rather than updating without seeing rowmodctr.

I see some columns in sys.dm_db_index_operational_stats like leaf_insert_count,leaf_delete_count,leaf_update_count......................... Have microsoft changed prior functionality into this............Please let us know if you know something (or) anyway to modify the script.............(or) the answer is simple....."We cannot convert into DMV with existing condtions".

Thanks.

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

fais (9/23/2008)
I see some columns in sys.dm_db_index_operational_stats like leaf_insert_count,leaf_delete_count,leaf_update_count......................... Have microsoft changed prior functionality into this............Please let us know if you know something (or) anyway to modify the script.............(or) the answer is simple....."We cannot convert into DMV with existing condtions".

I think the answer is closer to 'cannot be converted'. I don't know of anything that replaces the rowmodctr. I even tried looking in the system tables a while back. It's not there.

The index operational stats don't record changes since last rebuild or last stats update.

According to BoL

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

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

thirumala_kommineni Grasshopper Points: 14 More actions · Answer 4

select * from sys.system_internals_partition_columns

look @ modified_count column this represent sysindexes.rowmodcnt .

sys.system_internals_partition_columns (this view derived from one of the hidden views sys.sysrowsetcolumns.rcmodified)

-- This value never reset to 0 even if u run update stats on the table..

Let me know if you guys find information how modified_count can be used to run update statas..?

jigar_pcs Grasshopper Points: 18 More actions · Answer 5

The surprising fact here is that SQL 2005 users are trying to replace sysindexes where as Microsoft is not. Check the code for sp_updateStats and look for @ind_rowmodctr. This procedure uses sys.sysindexes which is old view.

Jigar Patel