How to check page splits number?

Question

How to check page splits number?

SQL Guy 1

SSCoach

Points: 15981
More actions
May 11, 2015 at 8:15 am

#300443

I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

In SQL 2008, with a huge amount of difficulty. The page splits perfmon counter doesn't track page splits but tracks page allocations. The extended event (if it was even in SQL 2008) does the same.

The only real way in sQL 2008 is to read the transaction log, which requires that the DB be in full recovery or the piece you're monitoring be in a transaction so that the log doesn't get reused. You're looking for LOP_DELETE_SPLIT operations in the transaction log.

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

SQL Guy 1 SSCoach Points: 15981 More actions · Answer 2

Thanks, Gail. That's good advice. So I used fn_dblog(null,null) and DBCC log. They are pretty much identical, however the former is better because I can apply Where clause.

But my next question is different: none of them shows timestamp. Is there any way to figure it out? For example by using LSN, Transaction Id?

Thanks in advance.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

There's a time on the LOP_BEGIN_XACT (begin transaction), so if you can tie the split back to a transaction you can get a time. I did say with a lot of difficulty and you DO NOT want to do this on a production server, you'll bring it to its knees (I know, I tried it once)

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

SQL Guy 1 SSCoach Points: 15981 More actions · Answer 4

Thanks again.

Here is the query that I came up with:

select f1.Operation,

f1.AllocUnitName,

f1.[Page Id],

f1.[Slot Id],

f1.[Transaction Id],

f2.[Transaction name],

f2.[Begin Time]

from fn_dblog(null,null) f1 left join fn_dblog(null,null) f2

on f1.[Transaction Id] = f2.[Transaction Id]

where f1.Operation = 'LOP_DELETE_SPLIT'

and f2.operation = 'LOP_BEGIN_XACT'

I do it on a dev server. I reproduced production ETL process on dev. And I found really lots of page splits caused by update commands. Now my goal is to optimize fill-factor to that level when number of page splits will be 0 or very minimal. Is this right tactics? Should I take care about anything else besides fill-factor?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

SQL Guy 1 (5/11/2015)
Thanks again.
Here is the query that I came up with:
select f1.Operation,
f1.AllocUnitName,
f1.[Page Id],
f1.[Slot Id],
f1.[Transaction Id],
f2.[Transaction name],
f2.[Begin Time]
from fn_dblog(null,null) f1 left join fn_dblog(null,null) f2
on f1.[Transaction Id] = f2.[Transaction Id]
where f1.Operation = 'LOP_DELETE_SPLIT'
and f2.operation = 'LOP_BEGIN_XACT'
I do it on a dev server. I reproduced production ETL process on dev. And I found really lots of page splits caused by update commands. Now my goal is to optimize fill-factor to that level when number of page splits will be 0 or very minimal. Is this right tactics? Should I take care about anything else besides fill-factor?

I would say that you need to find out why the updates are causing the splits and if there's a way to get around that before changing the FILL FACTOR especially if it's on the clustered index. For example, if you have a VARCHAR(10) column that either starts out NULL or only has a couple of characters and it get's updated to 8, 9, or 10 characters on a regular basis, it would stop the page splits in their tracks if you changed that column to a CHAR(10) and it would likely take less room than a different FILL FACTOR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101200 More actions · Answer 6

Jeff Moden (5/11/2015)
SQL Guy 1 (5/11/2015)
Thanks again.
Here is the query that I came up with:
select f1.Operation,
f1.AllocUnitName,
f1.[Page Id],
f1.[Slot Id],
f1.[Transaction Id],
f2.[Transaction name],
f2.[Begin Time]
from fn_dblog(null,null) f1 left join fn_dblog(null,null) f2
on f1.[Transaction Id] = f2.[Transaction Id]
where f1.Operation = 'LOP_DELETE_SPLIT'
and f2.operation = 'LOP_BEGIN_XACT'
I do it on a dev server. I reproduced production ETL process on dev. And I found really lots of page splits caused by update commands. Now my goal is to optimize fill-factor to that level when number of page splits will be 0 or very minimal. Is this right tactics? Should I take care about anything else besides fill-factor?
I would say that you need to find out why the updates are causing the splits and if there's a way to get around that before changing the FILL FACTOR especially if it's on the clustered index. For example, if you have a VARCHAR(10) column that either starts out NULL or only has a couple of characters and it get's updated to 8, 9, or 10 characters on a regular basis, it would stop the page splits in their tracks if you changed that column to a CHAR(10) and it would likely take less room than a different FILL FACTOR.

True. Best would be if we could see the table DDL and the query.

You can tune the query to reduce overhead when looking for splits as well, avoiding two calls of the function and the join:

SELECT 'LOP_DELETE_SPLIT' AS Operation,

MAX(CASE WHEN fd.Operation = 'LOP_DELETE_SPLIT' THEN fd.AllocUnitName END) AS AllocUnitName,

MAX(CASE WHEN fd.Operation = 'LOP_DELETE_SPLIT' THEN fd.[Page Id] END) AS [Page Id],

MAX(CASE WHEN fd.Operation = 'LOP_DELETE_SPLIT' THEN fd.[Slot Id] END) AS [Slot Id],

MAX(CASE WHEN fd.Operation = 'LOP_DELETE_SPLIT' THEN fd.[Transaction Id] END) AS [Transaction Id],

MAX(CASE WHEN fd.Operation = 'LOP_BEGIN_XACT' THEN fd.[Transaction name] END) AS [Transaction name],

MAX(CASE WHEN fd.Operation = 'LOP_BEGIN_XACT' THEN fd.[Begin Time] END) AS [Begin Time]

FROM fn_dblog(null,null) fd

WHERE fd.Operation IN ('LOP_DELETE_SPLIT', 'LOP_BEGIN_XACT')

GROUP BY fd.[Transaction ID]

HAVING count(*) = 2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".