Monitor Page splits using Extended Events session in SQL Server 2012

There are several ways to configure and start monitoring page splits on SQL Server. But now in 2012, we could make use of extended events to easily track down page splits.

If you do not want to go through the session wizard, I am sharing the script with which you could create the session in one go.

Below I have explained how to configure the extended events to start monitoring page splits through the Management Studio Wizard.

  1. Connect to the target SQL Server Instance from SQL Server management studio.
  2. Navigate to Management > Extended Events > Sessions
  3. Right click “Sessions” and select “New Session Wizard”.
  4. Click NextClick Next.
  5. Session NameProvide a Session name and hit next.
  6. Hit NextHit Next
  7. Select Page Split EventType page split select “page_split” click ‘>’ and hit Next.
  8. Select CountersTick checkboxes for client_hostname, client_pid, context_info, database_id, database_name, nt_username, session_id, sql_text, username and any other counters that you are interested in. Hit Next.
  9. Hit NextHit Next.
  10. Save Output to FileTick “Save data to file…” checkbox and verify or change the path for the file. Hit Next.
  11. FinishTick the checkboxes and hit Close button.

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply