April 5, 2010 at 2:19 am
Hi,
There is drastic increase in log size during rebuilding indexes even in Bulk recovery mode.
Any way to reduce the log size???
Regards,
Saravanan
April 5, 2010 at 2:47 am
If you mean the size of the log backups, then the answer is no.
The BULK_LOGGED recovery model allows the index rebuild to complete faster, since it will be minimally logged, but the next log backup after the operation completes will include all the data pages from the rebuild, for recovery purposes. There's really no way around this, if you want to maintain a restore path.
If that does not answer your question, please give more details of the problem you are seeing.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 3:20 am
Hi Paul,
I have configured log shipping. My bandwidth is just 2 Mbps.Yesterday i rebuild and reorganized the indexes which made log backup to increase gradually which in turn increased the network traffic .All my copy jobs still executing . We have around 6500 indexes. How to run maintenance plan without effecting log shipping.
Regards,
Saravanan
April 5, 2010 at 3:43 am
General recommendations:
1. Get a faster link 😉
2. Only rebuild or reorganize indexes that absolutely require it
3. Spread the bare-minimum of rebuilds and reorganizations out over time
4. Compress the log backups before shipping them across the link
5. Consider partitioning large indexes (Enterprise only) and rebuilding at partition level
Essentially, with such a slow link, you need to have a smarter maintenance script - an SSMS/SSIS maintenance plan is not suitable. Compressing log backups will require you to handle log-shipping manually, since the built-in support is quite basic.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 3:50 am
Paul,
We are using standard editions. I cant make use of 4th and 5th point since it is not supported .Already i have increased the bandwidth from 1Mbps to 2 Mbps.Im rebuilding and reorganizing only the indexes which it required.
Still there is a increase in log. Any other options ????
Regards,
Saravanan
April 5, 2010 at 4:28 am
Saravanan T (4/5/2010)
I'm rebuilding and reorganizing only the indexes which it required.
Are you sure? If you are using a Maintenance Plan, the answer is almost certainly 'no'.
Remember that fragmentation only affects performance when performing an ordered scan. Unless you have performed a detailed analysis to identify structures that are regularly scanned in logical order, and confirmed that fragmentation causes measurably poor performance...you are probably rebuilding indexes unnecessarily.
My suggestion was to identify indexes that absolutely require maintenance. Part of that assessment requires that you can show a concrete benefit. Given the bandwidth restriction, you might have to accept that you can only rebuild a few heavily-scanned indexes, and that you will have to write a script to spread these operations out over time.
Compressing the log does not require Enterprise Edition. You cannot do that in any edition of SQL Server 2005 (backup compression is available in SQL Server 2008 EE).
My suggestion is that you set up manual log shipping, and use a utility compression program on the log backups before copying them across the link.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 4:42 am
Thks for your time . Will Update statistics also increase the log size?
Regards,
Saravanan
April 5, 2010 at 4:55 am
Saravanan T (4/5/2010)
Thks for your time . Will Update statistics also increase the log size?
Update statistics does not perform any logged operations, so no.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 2:07 pm
Saravanan T (4/5/2010)
Im rebuilding and reorganizing only the indexes which it required.
something i have seen before, ensure you dont have a maint plan with a rebuild and a reorganise task defined
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 6, 2010 at 12:01 am
Im not using Maintenace plan. Im using a SP which reorganize or rebuild the indexes depending on the fragmentation level.
Regards,
Saravanan
April 6, 2010 at 2:17 am
Saravanan T (4/6/2010)
Im not using Maintenace plan. Im using a SP which reorganize or rebuild the indexes depending on the fragmentation level.
Good. Consider modifying your script to only process indexes where you know that fragmentation definitely impacts on production performance (ordered scans). Take a look at the sys.dm_db_index_operational_stats dynamic management view.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 2:30 am
Im taking fragmentaion percentage in this DMV sys.dm_db_index_physical_stats.
wat column i shd make use in sys.dm_db_index_operational_stats.Any article on sys.dm_db_index_operational_stats???
Regards,
Saravanan
April 9, 2010 at 12:15 am
Also look into lowering the fill factors of your indexes so that they do not get fragmented as often.
See discussion in http://www.sqlservercentral.com/Forums/Topic892938-1550-2.aspx#bm898207.
I use this query to find the usage statistics of indexes (user_scans column will tell you if an index needs to be defragmented):
SELECT
OBJECT_NAME( s.[object_id] ) AS TableName
,i.name AS IndexName
,s.user_updates
,s.user_seeks
,s.user_scans
,s.user_lookups
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
OBJECT_NAME( s.[object_id] ) = '<table_name_goes_here>'
AND db_name(s.database_id) = db_name(db_id())
AND objectproperty(i.object_id, 'IsIndexable') = 1
ORDER BY
OBJECT_NAME( s.[object_id] )
__________________________________________________________________________________
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]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply