February 15, 2010 at 11:28 am
We have SQL server 2005 EE x64 with SP3 having BizTalk server 2006 R2 databases. I went through the below link to perform rebuild indexes
http://support.microsoft.com/kb/917845
and I'm rebuilding the Indexes for BizTalkDTADb using the procedure dtasp_RebuildIndexes and I got the alarm from Spot light monitoring tool as below:
Page life Expectancy: buffer cache page life expectancy is 61 seconds
Is this normal to get low page life expectancy value while rebuilding indexes? or do I need to conider to look at any other items?
thanks
February 15, 2010 at 4:47 pm
rambilla4 (2/15/2010)
We have SQL server 2005 EE x64 with SP3 having BizTalk server 2006 R2 databases. I went through the below link to perform rebuild indexeshttp://support.microsoft.com/kb/917845
and I'm rebuilding the Indexes for BizTalkDTADb using the procedure dtasp_RebuildIndexes and I got the alarm from Spot light monitoring tool as below:
Page life Expectancy: buffer cache page life expectancy is 61 seconds
Is this normal to get low page life expectancy value while rebuilding indexes? or do I need to conider to look at any other items?
thanks
Rebuilding indexes can be a resource intensive operation, and Page Life expectancy (PLE) can be an indicator of memory pressure.
It seems you have your hands tied with regard to rebuilding indexes on a biztalk database and according to the KB you should only rebuild during downtime. So nobody should be affected by this rebuild operation.
How big is the DB?
How much RAM does the server have?
How big are the indexes being rebuilt?
Gethyn Elliswww.gethynellis.com
February 15, 2010 at 9:49 pm
How big is the DB?
How much RAM does the server have?
How big are the indexes being rebuilt?
The database size is 7 GB
We have 16 GB RAM (But We have 3 instances on the same server for 3 BizTalk Instances and have set the Max memory for each instance as 4 GB and left 4GB for OS)
Indexes size, I need to verify.
Question:
Is it normal to have less page life expectancy when we rebuild Indexes? Because this is the only time I'm getting low page life expectancy and rest of the time it's OK
thanks for your help
February 16, 2010 at 3:54 am
Are you rebuilding all indexes regardless or are you doing it based on fragmentation?
February 16, 2010 at 4:51 am
rambilla4 (2/15/2010)
How big is the DB?
How much RAM does the server have?
How big are the indexes being rebuilt?
The database size is 7 GB
We have 16 GB RAM (But We have 3 instances on the same server for 3 BizTalk Instances and have set the Max memory for each instance as 4 GB and left 4GB for OS)
Indexes size, I need to verify.
Question:
Is it normal to have less page life expectancy when we rebuild Indexes? Because this is the only time I'm getting low page life expectancy and rest of the time it's OK
thanks for your help
I think index rebuilds can have an impact on memory, PLE represents the average time a page stays in cache and if the rebuild in 'touching' all data and index pages then I guess they could be switched out more frequently during the index rebuild process.
Another thing to look at when PLE takes a dive is the Buffer Cache Hit ratio this indicates the percentage of data pages found in the buffer cache as opposed to disk, if this goes down too then your users could be noticing a difference and would be an indicator of memory pressure during rebuilds...I'm guessing though if you are following your document, there are no users on the system/or data being processed during this maintenance task anyhow.
edit: Do you have AWE enabled on each instance or are you using 64 bit technology sorry i see you have 64 bit edition.
Have a look at this link: http://sqlserverpedia.com/wiki/Index_Maintenance_in_SS_2005 it mentions index rebuilds on partitioned tables can be memory intensive...Do you have any partitioned tables?
Gethyn Elliswww.gethynellis.com
February 16, 2010 at 9:58 am
Rebuilding all the index on a DB probably isn't helping with the PLE. Have a look at the link below as this rebuilds indexes on fragmentation criteria.
February 16, 2010 at 10:23 am
I'm using the stored procedure dtasp_RebuildIndexes provided by BizTalk and it's only rebuilds indexes for 10 tables inside the database BizTalkDTA.
February 16, 2010 at 10:44 am
rambilla4 (2/16/2010)
I'm using the stored procedure dtasp_RebuildIndexes provided by BizTalk and it's only rebuilds indexes for 10 tables inside the database BizTalkDTA.
For 'normal' SQL server database it is usually good practice to only rebuild or re-organise indexes as they need doing...That is as they get more fragmented. Microsoft actually provide a nice little script that will help you with that. http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
That said I'm not sure how that fits in with Biz Talk.
Gethyn Elliswww.gethynellis.com
April 20, 2010 at 10:00 am
I'm curious about this, too, but nobody has really answered the original poster's question about whether or not it's normal for PLE to drop during an index rebuild.
My guess would be that an index rebuild (particularly on a clustered index) might reduce PLE because any index pages in cache would be flushed during the rebuild, to be replaced later by new index pages. Also, it could be that dirty pages in cache would have to be flushed to disk during an index rebuild in order to maintain data integrity. It's possible that a number of index rebuilds, executed one right after the other, would cause some of the same data to be moved into and out of cache if the indexes affect the same columns or you're rebuilding a clustered index using ALTER INDEX ALL...REBUILD... If that's true, then it seems the impact to PLE could be reduced by specifying individual indexes rather than using the ALL keyword.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply