---

Diving into the world of SQL Server management, one stumbling block you might encounter is the vexing issue of high page splits. These splits happen when there’s simply no room left on a data page for new information, forcing SQL Server to divide the data across two pages. This can crank up I/O operations and lead to fragmentation, which, frankly, is a performance nightmare. This guide aims to arm you with the know-how to spot tables suffering from this plight using the SQL Server Assessment API and some nifty T-SQL tricks.

Decoding Page Splits

Imagine you’re trying to fit one last book into an already crammed bookshelf and end up needing a whole new shelf for that one book. That’s pretty much a page split. These occur during new inserts or when updating data expands row size. While a few page splits are part and parcel of the OLTP environment hustle, an overload of them can cause issues.

Keeping an Eye on Page Splits

SQL Server isn’t leaving you in the dark here; it offers tools like dynamic management views (DMVs) and system functions to monitor page splits. The Page Splits/sec counter in SQL Server Performance Monitor is your friend, albeit a general one. To really zero in on the troublemakers—specific tables and indexes—you need to dig a bit deeper.

Spotting the Culprits

To find which tables are cracking under the pressure of high page splits, you can turn to T-SQL queries against DMVs. Here’s a step-by-step guide, complete with code snippets, to get you started.

Step 1: Consult the sys.dm_db_index_operational_stats Function

This function is like the Sherlock Holmes of index operations, offering real-time clues on page splits.

SELECT 
    OBJECT_NAME(ios.object_id) AS TableName,
    i.name AS IndexName,
    ios.leaf_insert_count,
    ios.leaf_delete_count,
    ios.leaf_update_count,
    ios.leaf_page_merge_count,
    ios.page_split_count
FROM 
    sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) ios
JOIN 
    sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
WHERE 
    ios.page_split_count > 0
ORDER BY 
    ios.page_split_count DESC;

This magic spell lists tables and their indexes, along with a tally of page splits and other metrics. A high page_split_count is a red flag.

Step 2: Examine Index Usage and Fragmentation

After pinpointing the indexes throwing tantrums, gauge their usage and fragmentation levels to decide on the next steps, like tweaking the fill factor or opting for index reorganization/rebuilding.

SELECT 
    dbschemas.[name] as 'Schema', 
    dbtables.[name] as 'Table', 
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN 
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN 
    sys.indexes dbindexes on dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent > 30 -- Customize this value based on your needs
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC;

This query helps you sniff out the most fragmented indexes, which are likely suffering from high page splits.

Tackling High Page Splits

Identified the troublemakers? It’s time to consider strategies like adjusting the fill factor, reorganizing/rebuilding indexes, or even revisiting index and table designs to combat high page splits.

Wrapping Up

High page splits can drag your SQL Server databases down. Armed with the SQL Server Assessment API and these T-SQL examples, you’re now equipped to identify and tackle high page splits, steering your databases towards improved performance and stability.

---

This guide is your starting point for wrestling down high page splits in SQL Server, with T-SQL examples at your side to diagnose and fix issues efficiently.