April 14, 2008 at 9:39 am
I am using Wansync to replicate my databases and I have a reindex maintenance job that runs once a week. The reindex job is creating a very large logfile (40gb) which is causing a very large backlog in the Wansync spool.
Would an reorg index reduce the log file size?
Should I reindex one a week?
Any other suggestions?
Thanks in advance
April 14, 2008 at 10:47 am
Which version of SQL Server you are using?
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 14, 2008 at 11:24 am
I am using SQL Server 2005 enterprise ed.
April 14, 2008 at 11:32 am
If you're on SQL Server 2005 you could check the index fragmentation levels with sys.dm_db_index_physical_stats. This way you only have to do something about the indexes with a high fragmentation level. You could also dynamically do a rebuild or a reorganize based on the fragmentation level. Microsoft has some recommendations on this.
http://msdn2.microsoft.com/en-us/library/ms189858.aspx
I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
April 14, 2008 at 3:28 pm
andrew6123 (4/14/2008)
I am using Wansync to replicate my databases and I have a reindex maintenance job that runs once a week. The reindex job is creating a very large logfile (40gb) which is causing a very large backlog in the Wansync spool.Would an reorg index reduce the log file size?
Should I reindex one a week?
Any other suggestions?
Thanks in advance
To start off, do not re-index every single index in your database. That's not necessary.
Re-index only those indexes that are above a certain level of fragmentation. What that level is will depend on your system requirements (eg. down time restrictions), but some general guidelines are:
IF:
avg_fragment_size_in_pages < 8;
OR
avg_fragmentation_in_percent > 30
THEN:
Do index rebuild
IF:
avg_fragmentation_in_percent between 5 and 20
THEN:
Do index re-organize
Also it's important to keep in mind that index fragmentation incurs a performance penalty only on queries performing an ordered index scan. Point queries (seeks) do not suffer from index fragmentation.
My source for this information is Kalen Delaney's book "INSIDE MICROSOFT SQL SERVER 2005: THE STORAGE ENGINE", Solid Quality Learning, pgs 322-323
__________________________________________________________________________________
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]
April 14, 2008 at 3:38 pm
Also, keep in mind that re-indexing updates index statistics as well, whereas index re-organization does not update index stats.
Run the following statement at least once a week on every index in your database whose stats needs to be updated:
UPDATE STATISTICS schemaName.tblname idxName WITH FULLSCAN;
To determine which indexes need updating, run the following:
USE dbName;
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]
July 8, 2008 at 11:24 am
Hi,
Your stored procedure looks great for what I am trying to do but when I tried to run indexoptimize on my user database I got this error
Msg 195, Level 15, State 10, Procedure IndexOptimize, Line 91
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.
Can you please help on how to fix this?
Do I need need to create this sp on the master database or do something else?
July 8, 2008 at 11:35 am
You need to install SP2. The function OBJECT_SCHEMA_NAME is new in SP2.
I have some information about this in the Frequently Asked Questions section.
http://ola.hallengren.com/frequently-asked-questions.html
You could create the objects in any database. It just has to be in compatibility level 90.
Ola Hallengren
July 8, 2008 at 1:34 pm
Thanks for the quick reply. I thought SQL service packs are part of windows update when you select custom but this does not seem to be the case for sp2. I installed sp2 and was able to run the sp but got this new message on my user database as well as master:
"Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.CommandExecute'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."
I am not sure if this is a problem or not but would be great to have it fixed. Thanks
July 8, 2008 at 1:52 pm
You need to download and create the objects CommandExecute and DatabaseSelect too or use the installation script MaintenanceSolution.sql.
http://ola.hallengren.com/scripts/MaintenanceSolution.sql
I would create all objects in the master database or in a custom administrative database.
Ola Hallengren
July 8, 2008 at 2:27 pm
Thanks, everything works fine now without any errors. I have SQL Server 2005 standard edition which does not support online reindexing. Will your sp check that or should I modify it to always use offline mode?
July 8, 2008 at 2:33 pm
I am using standard edition of sql server which cannot run reindexing in online mode. Will your sp handle that or do I need to modify it?
July 8, 2008 at 2:49 pm
Sry about the double post. I did not see my first was not sure if it actually go added.
I had ran this query "SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30"
before running
"EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING', @FragmentationLow_NonLOB = 'NOTHING', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000 "
and the number of rows returned by my select was reduced by only 1 and most of the indexes had the same average fragmentation in percentage. I even tried the sql server maintenance plan and it did not help either. Can you please help?
July 8, 2008 at 2:50 pm
The stored procedure is checking for this. INDEX_REBUILD_ONLINE is only allowed when SERVERPROPERTY('EngineEdition') = 3.
You could run it like this.
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000
Do some tests first in Management Studio to get an understanding for how it works. (If you do tests on a small database, try setting the @PageCountLevel to 10 or something.)
Ola Hallengren
July 8, 2008 at 2:59 pm
I think that a small index (low page_count) could have a fragmentation that does not go away even if you rebuild the index.
In addition fragmentation on small indexes has a very little impact on performance.
Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Ola Hallengren
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply