Looking for page split operations in a non-recovered database txn log.

Question

Looking for page split operations in a non-recovered database txn log.

Paul Duffett

Mr or Mrs. 500

Points: 536
More actions
September 7, 2011 at 3:30 am

#247427

Hi all,
First time post so please be gentle 🙂
I am looking after a very sensitive SQL Server 2000 instance which is the back-end for an online gaming web-site. Before someone shouts "migrate!" we are about to move this to a newly-provisioned SQL Server 2008 R2 instance but before we shift this over to it's new home I've been asked to bottom-out some performance issues.
One thing that I have noticed is the high number of page splits and through the use of the fn_dblog function I can see that the LOP_DELETE_SPLIT operation is running into the thousands between log truncates (scheduled txn log backup every 15 minutes). I believe this is leading to increased fragmentation throughout the day which in turn is leading to poor read performance, not to mention the poor write performance due to the page split itself.
I thought about writing a small USP to "capture" the txn log on the production database prior to the txn log backup job and then analyse the information, but I'm concerned about causing performance degradation.
My train of thought led me on to the transaction log backups that are already occurring on the production database which in turn led me to the following queston:
What if I had an unrecovered copy of the production database on a separte instance and "replayed" the transaction log backups, could I then query the transaction log of the unrecovered database? I suspect I would have to use the WITH STANDBY option.
Theoretically it should be entirely possible but before I suggest going down this route I wanted to share it with the forum to see if anybody has any experience of this.
Many thanks
Paul Duffett

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

I'd check fill-factor on the indexes, particularly those that show high fragmentation on a regular basis.

Also what are the clustered indexes set-up on? Are they on monotonic columns or ones that can have random inserts/updates? This can also cause a lot of page splits.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Paul Duffett (9/7/2011)
What if I had an unrecovered copy of the production database on a separte instance and "replayed" the transaction log backups, could I then query the transaction log of the unrecovered database? I suspect I would have to use the WITH STANDBY option

No, because replaying the log records doesn't itself log. Once the DB is restored what'll be in the log is just the operations since the restore.

Since you're on SQL 2000, RedGate has a free log reader. See if it works with log backups...

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul Duffett Mr or Mrs. 500 Points: 536 More actions · Answer 3

GilaMonster (9/7/2011)
Paul Duffett (9/7/2011)
What if I had an unrecovered copy of the production database on a separte instance and "replayed" the transaction log backups, could I then query the transaction log of the unrecovered database? I suspect I would have to use the WITH STANDBY option
No, because replaying the log records doesn't itself log. Once the DB is restored what'll be in the log is just the operations since the restore.
Since you're on SQL 2000, RedGate has a free log reader. See if it works with log backups...

Thanks Jack - I'm going to use the page split operations in the txn log file to help identify the indexes that need a more appropriate fill factor. The issue, at first glance, is with the non-clustered indexes as the clustered indexes are on identity columns. It'll then be a case of finding that balance between reducing page-splits and keeping page reads to a minimum.

Gail - that's exactly the information I was looking for and I was 50/50 about whether or not they would be logged; I didn't find anything during my search of the usual suspect websites and thought I'd open it up for discussion. I've never actually queried the log file of a warm standby server and given that we don't have any here I was going down the path of setting a log-shipping operation on my laptop tonight. I might still do that, depends if there's anything on TV 😀

Thanks all.

Paul

Paul Duffett Mr or Mrs. 500 Points: 536 More actions · Answer 4

Just a quick update on this one in case anyone out there is following.

I eventually got around to creating a warm stand-by database and queried the transaction log after a log restore (keeping the db in a non-recovered, stand-by state) and this is what you get:

Current LSNOperationContext

------------------------------------------------------------

0000002a:000001d4:0001LOP_BEGIN_CKPTLCX_NULL

0000002a:000001d5:0001LOP_END_CKPTLCX_NULL

0000002a:000001d6:0001LOP_END_RECOVERYLCX_NULL

@Gail - Not that I ever doubted you, I was just interested in seeing the logged operations for a log restore.

Next stop Red Gate Log Rescue, after I've managed to scramble around and find a copy of SQL 2000, now that's been a while! 🙂

Thanks

Paul

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

What is your actual issue

Are you trying to reduce the Pagesplits ?

Or

Are you trying to find the Cause of Page Splits ?

Thank You,

Best Regards,

SQLBuddy