September 15, 2010 at 8:33 am
Hi everyone, I need someone help please, this is our Databases Maintenance that we run 2 times a week. Can you please take a look and tell me if this code doing maintance jobs. There is (CheckDatabaseIntegrity:ReorganizeIndex:,ShrinkDatabase) where this coming from I can't understand.Thank you
Code:
@ExcludeDBs VARCHAR(MAX) = '''tempdb''',
@Verbose BIT = 0,
@Fragmentation INT = 50,
@FreeSpace INT = 10
AS
SET NOCOUNT ON
DECLARE @CVVCDatabases TABLE ([name] sysname)
INSERT INTO @CVVCDatabases
EXEC('SELECT [name] FROM [master].[sys].[databases]
WHERE ([name] NOT IN (' + @ExcludeDBs + '))
AND ([state_desc] NOT IN (''OFFLINE'', ''RESTORING''))
AND ([is_in_standby] <> 1)
AND ([source_database_id] IS NULL)
AND ([is_read_only] <> 1)')
DECLARE @ErrorMessage VARCHAR(MAX)
DECLARE @CVVCDatabaseMaintenance_DBName SYSNAME
DECLARE CVVCDatabaseMaintenance_Cursor CURSOR FOR
SELECT [name] FROM @CVVCDatabases
OPEN CVVCDatabaseMaintenance_Cursor
FETCH NEXT FROM CVVCDatabaseMaintenance_Cursor
INTO @CVVCDatabaseMaintenance_DBName
WHILE @@FETCH_STATUS = 0
BEGIN
CheckDatabaseIntegrity:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Checking Database Integrity on: ' + @CVVCDatabaseMaintenance_DBName
EXEC ('DBCC CHECKDB (N''' + @CVVCDatabaseMaintenance_DBName + ''') WITH NO_INFOMSGS')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Check Database Integrity Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('DBCC CHECKDB (N''' + @CVVCDatabaseMaintenance_DBName + ''') WITH NO_INFOMSGS')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Check Database Integrity Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
IF (@@ERROR = 0) GOTO ShrinkDatabase ELSE GOTO NextDatabase
ShrinkDatabase:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Shrinking Database: ' + @CVVCDatabaseMaintenance_DBName
EXEC ('DBCC SHRINKDATABASE(N''' + @CVVCDatabaseMaintenance_DBName + ''', ' + @FreeSpace + ', NOTRUNCATE) WITH NO_INFOMSGS')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Shrink Database Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('DBCC SHRINKDATABASE(N''' + @CVVCDatabaseMaintenance_DBName + ''', 10, NOTRUNCATE) WITH NO_INFOMSGS')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Shrink Database Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
IF (@@ERROR = 0) GOTO ReorganizeIndex ELSE GOTO NextDatabase
ReorganizeIndex:
DECLARE @DatabaseID INT
SET @DatabaseID = db_id(@CVVCDatabaseMaintenance_DBName)
DECLARE @CVVCTablesIndexes TABLE([TableViewName] SYSNAME, [IndexName] SYSNAME)
INSERT INTO @CVVCTablesIndexes
EXEC('SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([Frag].[object_id], [Frag].[database_id])) + ''.'' +
QUOTENAME(OBJECT_NAME([Frag].[object_id], [Frag].[database_id])) AS [TableViewName],
[Indx].[name] AS [IndexName]
FROM [master].[sys].[dm_db_index_physical_stats] ('+ @DatabaseID +', NULL, NULL, NULL, NULL) AS [Frag]
INNER JOIN [' + @CVVCDatabaseMaintenance_DBName + '].[sys].[indexes] AS [Indx] ON [Indx].[object_id] = [Frag].[object_id] AND [Indx].[index_id] = [Frag].[index_id]
WHERE
([Frag].[avg_fragmentation_in_percent] >= ' + @Fragmentation + ')
AND ([Frag].[index_id] > 0) ')
DECLARE @CVVCTableViewName SYSNAME
DECLARE CVVCTableViewName_Cursor CURSOR FOR
SELECT DISTINCT [TableViewName] FROM @CVVCTablesIndexes
OPEN CVVCTableViewName_Cursor
FETCH NEXT FROM CVVCTableViewName_Cursor
INTO @CVVCTableViewName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @CVVCIndexName SYSNAME
DECLARE CVVCIndexName_Cursor CURSOR FOR
SELECT DISTINCT [IndexName] FROM @CVVCTablesIndexes
WHERE [TableViewName] = @CVVCTableViewName
OPEN CVVCIndexName_Cursor
FETCH NEXT FROM CVVCIndexName_Cursor
INTO @CVVCIndexName
WHILE @@FETCH_STATUS = 0
BEGIN
ReoragnizeIndexes:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Reorganizing Index: ' + @CVVCIndexName + ' for Table: ' + @CVVCTableViewName + ' on Database: ' + @CVVCDatabaseMaintenance_DBName
EXEC ('ALTER INDEX [' + @CVVCIndexName + '] ON [' + @CVVCDatabaseMaintenance_DBName + '].' + @CVVCTableViewName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Reorganizing Index Task for Index: ' + @CVVCIndexName + ' on Table: ' + @CVVCTableViewName + ' in Database: ' + @CVVCDatabaseMaintenance_DBName + ': ' + ERROR_MESSAGE()
PRINT ('ALTER INDEX [' + @CVVCIndexName + '] ON [' + @CVVCDatabaseMaintenance_DBName + '].' + @CVVCTableViewName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Reorganizing Index Task for Index: ' + @CVVCIndexName + ' on Table: ' + @CVVCTableViewName + ' in Database: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
UpdateStatistics:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Updating Statistics for Table: ' + @CVVCTableViewName + ' on Database: ' + @CVVCDatabaseMaintenance_DBName
EXEC('UPDATE STATISTICS [' + @CVVCDatabaseMaintenance_DBName + '].' + @CVVCTableViewName + ' [' + @CVVCIndexName + '] WITH FULLSCAN')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Update Statistics Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('UPDATE STATISTICS [' + @CVVCDatabaseMaintenance_DBName + '].' + @CVVCTableViewName + ' [' + @CVVCIndexName + '] WITH FULLSCAN')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Update Statistics Task: ' + @CVVCDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
FETCH NEXT FROM CVVCIndexName_Cursor
INTO @CVVCIndexName
END
CLOSE CVVCIndexName_Cursor
DEALLOCATE CVVCIndexname_Cursor
FETCH NEXT FROM CVVCTableViewName_Cursor
INTO @CVVCTableViewName
END
CLOSE CVVCTableViewName_Cursor
DEALLOCATE CVVCTableViewName_Cursor
GOTO NextDatabase
NextDatabase:
DELETE FROM @CVVCTablesIndexes
FETCH NEXT FROM CVVCDatabaseMaintenance_Cursor
INTO @CVVCDatabaseMaintenance_DBName
END
CLOSE CVVCDatabaseMaintenance_Cursor
DEALLOCATE CVVCDatabaseMaintenance_Cursor
September 15, 2010 at 9:15 am
It is doing maintenance. Look at these lines:
EXEC ('DBCC CHECKDB (N''' + @CVVCDatabaseMaintenance_DBName + ''') WITH NO_INFOMSGS')
EXEC ('DBCC SHRINKDATABASE(N''' + @CVVCDatabaseMaintenance_DBName + ''', ' + @FreeSpace + ', NOTRUNCATE) WITH NO_INFOMSGS')
EXEC ('ALTER INDEX [' + @CVVCIndexName + '] ON [' + @CVVCDatabaseMaintenance_DBName + '].' + @CVVCTableViewName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )')
Note that shrinking databases regularly is a bad idea: http://www.sqlservercentral.com/blogs/steve_jones/archive/2010/09/14/common-sql-server-mistakes-_2D00_-shrinking-databases.aspx
September 15, 2010 at 9:16 am
Why are you doing a shrink database ? There are many posts on this site about the problems you will cause to your indexes by doing a DB shrink.
Shrinking a database is usually a one time occurrence when disk space is critical and there is no other option, or a static DB with unused space that is not expected to grow in the future..
September 15, 2010 at 9:51 am
Steve and Homebrew are correct. Never do Shrink. It causes fragmentation and causes performance problems.
Shrink should be done only when there is an disk space issue.
Thank You,
Best Regards,
SQLBuddy
September 15, 2010 at 9:53 am
This someone created code and I will be removing it.
Otherwise is the code ok?
This is what I understand:
1.Checking on any errors for the database.The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.
2.Reorginizing and updating indexes(why are we doing that?)Is this improving index scanning performance?
3.Updating Statistics(what is the purpose of that/)
Thank you
September 15, 2010 at 10:07 am
Hi Krsavita,
1. You are right.
2. Indexes get fragmented over time due to the the DML activites on them like inserts etc.. This happens due to the page splits.
To defragment the indexes you need to either reorg or rebuild the indexes. Usually general practice is
If Fragmentation -- Is between 5-30% -- Do Reorg
Is > 30% Do Rebuild
This is not a hard and fast rule. Again this depends on the environment.
3. Statistics are used by the query optimizer to generate optimal execution plans. So they have to be updated regularly. Again this depends on the environment.
Even If you have right indexes created if you have oudated statistics, SQL Server query optmize will produce poor execution plans and would result in slow running queries or poorly performing queries.
This is just an outline.
Thank You,
Best Regards,
SQLBuddy
September 15, 2010 at 10:30 am
Thank you so much,in my code I have listed fragmantation =50 then rebuild, how often what it get to 50 and how whould I know how often job rebuild indexes. Thank you
September 15, 2010 at 10:56 am
There is no way in which we can say that when the index fragmentation will reach 50%. It all depends on the activity going on the table.
But a good practise is to do Index Rebuilds during the weekend and Reorgs and Update Stats every night.
Also please note that Update statistics needn't be done after an Index rebuild bcs Index rebuild does automatically update the statistics
Thank You,
Best Regards,
SQL Buddy
September 16, 2010 at 10:32 am
Thank you,
How can I found out if fragmantation happend if there is no way in which we can say that when the index fragmentation will reach 50%.
Thank You,
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply