• Facebook - Black Circle
  • Twitter Round
  • Instagram Black Round
  • LinkedIn - Black Circle

SEARCH BY TAGS: 

Please reload

RECENT POSTS: 

FOLLOW ME:

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle

Page Splits, should I worry?

August 8, 2018

I wish page splits were delicious. 

 

 

What are page splits?

 

Page splits occur when a data page gets filled with data and a new page is needed to make room for new data.  There are times when data modification causes data to be inserted in the middle of a page, this causes the page to "split". 

 

When this happens the data can get "out of order", thus slowing down queries with the increase amount of IO needed to gather the data. 

 

Now, this is a normal occurrence within databases, but too much of it can cause some severe fragmentation if the fill factor isn't set correctly.

 

(*Note that this is a very high level explanation, for a more detailed article go here.)

 

How do I track them?

 

There are MANY different ways to track page splits, just scour the internet and you'll find a ton of options. 

 

The simplest way is to utilize perfmon, Page splits/sec counter (or use the sys.dm_os_performance_counters dmv). 

 

Another way is to query the Sys.dm_db_index_operationl_stats DMV (see more here). Check out this article at microsoft for a helpful query.

 

My favorite way is to utilize extended events. I'm slowly beginning to love extended events.  Simply create an extended event and set it to capture the sqlserver.page_split event. 

 

 

Share on Facebook
Share on Twitter
Please reload