June 19, 2012 at 11:30 am
/*
Hi Gurus,
I am using following piece of code to update statistics everyday mid night on SQL server 2008 R2 server by using following statement to generate the required indexes.
*/
USE <DBNAME>
GO
SELECT
OBJECT_NAME([id])as tblname,
SI.name as idxname,
SCHEMA_NAME(Schema_ID)as schemaname,
SI.rowcnt as RowCnt,
CAST(((SI.rowmodctr*1.0/isnull(nullif(SI.rowcnt,0),1))*100) AS INT) as RowChg
FROM dbo.SYSINDEXES SI (NOLOCK)
INNER JOIN SYS.OBJECTS T ON T.object_id = SI.[id]
WHERE T.TYPE = 'U'
AND SI.[name] not like '_WA_Sys_%'
AND SI.rowcnt > 1000
AND SI.dpages > 8
AND CAST(((SI.rowmodctr*1.0/isnull(nullif(SI.rowcnt,0),1))*100) AS INT) >= 5
-- Im wondering if everything is ok with above select?
-- " CAST(((SI.rowmodctr*1.0/isnull(nullif(SI.rowcnt,0),1))*100) AS INT)" gives rowchg. If rowchg is greater than 5 % then the index is elgible for update statistics.
/*
Based on the above result from Select statement,Im generating update statistics statements using while loop.
Final execution part looks like this.
if RowCnt >5000000
UPDATE STATISTICS [dbo].[test] [IX_test] WITH SAMPLE 50 PERCENT, INDEX, NORECOMPUTE;
else
UPDATE STATISTICS [dbo].[testA] [IX_testA] WITH FULLSCAN, INDEX, NORECOMPUTE;
Question :
Im wondering if Im doing everything correct or is this the best approach of updating statistics across all databases
*/
June 20, 2012 at 7:30 am
sqlbee19 (6/19/2012)
FROM dbo.SYSINDEXES SI (NOLOCK)
INNER JOIN SYS.OBJECTS T ON T.object_id = SI.[id]
Instead of using sysindexes, use sys.indexes and sys.objects
The first thing you want to do is to get the date and time the stats were last updated. Do this using the following query
SELECT t.name
, i.name AS index_name
, STATS_DATE(i.object_id, i.index_id) AS statistics_update_date
FROM sys.objects t inner join sys.indexes i on t.object_id = i.object_id
where t.is_ms_shipped <> 1
order by statistics_update_date desc
Once you have this you may then update stats for the required objects
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2012 at 8:42 am
Perry Whittle (6/20/2012)
sqlbee19 (6/19/2012)
FROM dbo.SYSINDEXES SI (NOLOCK)
INNER JOIN SYS.OBJECTS T ON T.object_id = SI.[id]
Instead of using sysindexes, use sys.indexes and sys.objects
Perry - Thanks for your response.
I am using sysindexes table because of column "rowmodctr" is only available in sysindexes.
The first thing you want to do is to get the date and time the stats were last updated. Do this using the following query
SELECT t.name
, i.name AS index_name
, STATS_DATE(i.object_id, i.index_id) AS statistics_update_date
FROM sys.objects t inner join sys.indexes i on t.object_id = i.object_id
where t.is_ms_shipped <> 1
order by statistics_update_date desc
Once you have this you may then update stats for the required objects
The problen with this solution is I have to update tables even if there is no activity on the table.
You think rowmodctr from sysindexes is not accrurate or is it not a best practice to use this column to update stats?
June 20, 2012 at 2:46 pm
sqlbee19 (6/20/2012)
Perry - Thanks for your response.I am using sysindexes table because of column "rowmodctr" is only available in sysindexes.
That is correct, remember though that sysindexes is provided for backwards compatability only. This view does not support partitions and since all tables\indexes are by default partitioned in SQL Server 2005 onwards you shouldn't be using it.
sqlbee19 (6/20/2012)
The problen with this solution is I have to update tables even if there is no activity on the table.
why?
Be aware that updating statistics too frequently can have an adverse impact and cause excessive query compilation.
sqlbee19 (6/20/2012)
You think rowmodctr from sysindexes is not accrurate or is it not a best practice to use this column to update stats?
OMG, there are soooooo many topics on this. Let's look at where you should start apart from STATS_DATE()
DBCC SHOW_STATISTICS
offers valuable information about the distribution of your data. There is a also a DMV which tracks insert, update and delete activity counts on your indexes, check out
sys.dm_db_index_operational_stats
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2012 at 5:31 pm
You could just make things simple and execute this in your database every night:
execute sp_updatestats
sp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804(v=sql.105).aspx
"...sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows..."
You could also add a SQL Server maintenance plan to do a full update statistics weekly or monthly, maybe at the same time you reorganize or rebuild indexes.
June 20, 2012 at 7:04 pm
Michael Valentine Jones (6/20/2012)
You could just make things simple and execute this in your database every night:
execute sp_updatestatssp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804(v=sql.105).aspx
"...sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows..."
You could also add a SQL Server maintenance plan to do a full update statistics weekly or monthly, maybe at the same time you reorganize or rebuild indexes.
If you are updating statistics after index rebuilds, you are doing double the work. Rebuilding an index updates the statistics. No need for the separate operation.
June 21, 2012 at 8:02 am
Michael Valentine Jones (6/20/2012)
You could just make things simple and execute this in your database every night:
execute sp_updatestatssp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804(v=sql.105).aspx
"...sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows..."
You could also add a SQL Server maintenance plan to do a full update statistics weekly or monthly, maybe at the same time you reorganize or rebuild indexes.
Setting up a job to hit the stats for key objects, yes Michael you could, but not every object in every database. While that may be helpful for smaller systems it may not suit for larger estates. Updating the stats too frequently can have a negative impact on query compilation. You may end up negating any performance benefits from the stats updates by experiencing excessive query compilations.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 21, 2012 at 8:46 am
Thank you folks for valuable advices.
If you are updating statistics after index rebuilds, you are doing double the work. Rebuilding an index updates the statistics. No need for the separate operation.
Thomas - "reorganzing indexes" also update the statistics automatically ?
June 21, 2012 at 1:10 pm
Thomas Stringer (6/20/2012)
Michael Valentine Jones (6/20/2012)
You could just make things simple and execute this in your database every night:
execute sp_updatestatssp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804(v=sql.105).aspx
"...sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows..."
You could also add a SQL Server maintenance plan to do a full update statistics weekly or monthly, maybe at the same time you reorganize or rebuild indexes.
If you are updating statistics after index rebuilds, you are doing double the work. Rebuilding an index updates the statistics. No need for the separate operation.
An index rebuilds updates the statistics on index columns, but not columns that are not included in indexes. Automatic or manual statistics created on the table (instead of an index) are not updated.
June 21, 2012 at 1:17 pm
Perry Whittle (6/21/2012)
Michael Valentine Jones (6/20/2012)
You could just make things simple and execute this in your database every night:
execute sp_updatestatssp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804(v=sql.105).aspx
"...sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows..."
You could also add a SQL Server maintenance plan to do a full update statistics weekly or monthly, maybe at the same time you reorganize or rebuild indexes.
Setting up a job to hit the stats for key objects, yes Michael you could, but not every object in every database. While that may be helpful for smaller systems it may not suit for larger estates. Updating the stats too frequently can have a negative impact on query compilation. You may end up negating any performance benefits from the stats updates by experiencing excessive query compilations.
I'm not really sure what you are talking about, but I never suggested updating the statistics for every object in every database, or even in a single database.
sp_updatestats only updates the statistics in the database it is run in, and only where there is a need to update the the statistics.
June 25, 2012 at 8:27 am
Thanks Jones.
March 4, 2014 at 10:48 pm
Hi,
Is it possible to update stats for a specified date range, like for a month? in SQL Server 2008?
If so, please can you guys share the query..with an example
thanks,
Rinu
March 5, 2014 at 1:10 am
You can query to find the date stats were last updated, then initiate a rebuild
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2014 at 8:27 am
An index rebuilds updates the statistics on index columns, but not columns that are not included in indexes. Automatic or manual statistics created on the table (instead of an index) are not updated.
This is true. You can run sp_updatestats on a nightly basis. It only updates the statistics that need updates and has less overhead and runtime.
--
SQLBuddy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply