October 22, 2009 at 6:47 pm
In one of the databases with lot of inserts and updates everyday, I have set up in Maintenance Task to rebuild every night for all the tables and index objects, and once a week in the weekend for the rebuild.
Is this a good interval to do it, or is it a overkill.
The rebuild takes care of the index statistics, but is it recommended I do a regular update of the non-index statistics using 'sp_createstats', using a job once a week?
Thanks
Dan
October 22, 2009 at 8:40 pm
How large is the Database and How long is the Job taking to run? Any Idea ?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 22, 2009 at 11:11 pm
Also you can rebuild the indexes which are poor i.e. run dbcc showcontig and if Scan Density is below 80% reindex.
Thus you can save time as well as reduce burden on the server.
Now sp_createstats will creates statistics for eligible columns; however it will not touch the columns already having statistics. That means if your database designs changes heavily or very frequently and/or create and changes stored procedures heavily than you can think to use sp_createstats frequently.
But if AUTO_UPDATE_STATISTICS option is enabled SQL server will take care of take care of creating missing statistics as well as build it as and when required.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 23, 2009 at 3:36 am
repent_kog_is_near (10/22/2009)
The rebuild takes care of the index statistics, but is it recommended I do a regular update of the non-index statistics using 'sp_createstats', using a job once a week?
Well, sp_createstats doesn't update statistics. It creates them. You're looking for sp_updatestats.
free_mascot (10/23/2009)
But if AUTO_UPDATE_STATISTICS option is enabled SQL server will take care of take care of creating missing statistics as well as build it as and when required.
Not exactly.
If AUTO_CREATE_STATISTICS is enabled, SQL will create missing statistics when it needs the,. The setting of AUTO_UPDATE_STATISTICS doesn't affect the creation at all.
AUTO_UPDATE_STATS controls whether or not SQL will automatically update statistics that it considers out of date. Even with that on, some manual updates may be required, the threshold for the auto-update to kick off is pretty high. (20% of rows changed)
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
October 23, 2009 at 7:11 am
Bru
It is 2GB; takes 20 minutes to run the maintenance tasks.
free_mascot, I will consider putting selected tables through a job for rebuild. Thanks for the tip.
Gail, thanks for mentioning sp_updatestate- that is what I need, and also better/more accurate than AUTO_CREATE_STATISTICS. Am i right in assuming that the overhead for sp_updatestate should be low?
October 23, 2009 at 7:15 am
Auto_Create_statistics allows stats to be created
sp_updatestats updates statistics
They do not do the same thing!
Overhead may be low or may not. You'll have to check. Otherwise you could use the maintenance plan task to update just column statistics or code your own update job using UPDATE STATISTICS
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
October 23, 2009 at 8:30 am
Gail
sorry, I was getting the 2 mixed up.
I meant to quote that sp_updatestats is perhaps better than Auto_update_stats
Dan
October 23, 2009 at 9:18 am
Hi
The script I have pasted below is one I use after clearing out and inserting a lot of data into a database.
It uses sys.dm_db_index_physical_stats to get the fragmentation of the indexes and sp_updateStats to update the sats. In SQL 2005 this will only update the stats that SQL says are out of date. GilaMonster is trying to point out to you that the overhead depends on how big the table is and what stats are out of date.
You will need to tinker with this a bit.
DECLARE @databaseName as Varchar(250);
SET @databaseName = 'NameOfYourDataBase';
use tempdb;
if not object_id('indexData') is null
drop table [indexData];
CREATE TABLE [dbo].[indexData](
[dbName] varchar(250) Not NULL,
[di] int Not NULL,
[oi] varchar(150) NOT NULL,
[Frag] int not null,
[pages] int not null,
[iType] varchar(250) not null,
[oName] varchar(250) NULL,
[schemaName] varchar(250) NULL);
DECLARE @dbID as int
SELECT @dbID = database_id
FROM master.sys.databases
WHERE [Name] = @databaseName;
INSERT INTO [indexData]([dbName],[di],[oi],[Frag],[pages],[iType])
SELECT Distinct DB_NAME ( @dbID ), @dbID, ps.object_id,ps.avg_fragmentation_in_percent,ps.Page_Count,ps.index_type_desc
FROM sys.dm_db_index_physical_stats (@dbID, NULL, NULL, NULL, 'detailed') ps
LEFT OUTER JOIN master.sys.databases AS sd
ON ps.database_id = sd.database_id
Where (ps.database_id > 4) AND sd.is_Read_only = 0 AND sd.state_desc = 'online'
AND ps.Page_Count > 50
AND ps.avg_fragmentation_in_percent > 15
AND ps.index_type_desc <> 'heap'
AND sd.database_id = @dbID;
DECLARE @sql as NVarchar(500);
SET @sql = 'Use [' + @databaseName + ']'
Set @sql = @sql + 'SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON;'
Set @sql = @sql + ' Update tempdb..[indexData] '
Set @sql = @sql + ' SET oName = object_Name(oi), '
Set @sql = @sql + ' schemaName = (select schema_Name(sobj.schema_id) from sys.objects sobj where sobj.object_id = oi) '
Set @sql = @sql + ' WHERE dbName = ''' + @databaseName + ''';';
Exec sp_executesql @stmt = @sql;
Use tempdb;
DECLARE @dbName2 varchar(250);
DECLARE dbCursor2 CURSOR Local Fast_Forward FOR
SELECT Distinct '[' + dbName + '].[' + schemaName + '].[' + oName + ']' as databaseObject
FROM [indexData];
OPEN dbCursor2;
FETCH NEXT FROM dbCursor2 INTO @dbName2;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql2 as nVarchar(2000);
Set @sql2 = 'ALTER INDEX ALL ON ' + @dbName2
Set @sql2 = @sql2 + ' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);';
Print @sql2
Exec sp_executesql @sql2;
FETCH NEXT FROM dbCursor2 INTO @dbName2;
END;
CLOSE dbCursor2;
DEALLOCATE dbCursor2;
Use tempdb;
Drop Table [indexData];
DECLARE @sqlStats as NVarchar(500);
SET @sqlStats = 'Use [' + @databaseName + ']; '
Set @sqlStats = @sqlStats + 'EXEC sp_updateStats;';
Exec sp_executesql @stmt = @sqlStats;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply