SQL High Rate Page Split

Question

Hello,

I have more and more "High Rate Page Split" alerts

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

SQL Server Instance CONFIGMGR on VRPSCCMSQL01 is experiencing a high rate of page splits at 398.5278 per second, placing the instance into warn state.
Excess page splitting can cause excessive disk I/O and contribute to slow performance.
If consistently high, consider reducing the fill factor of your indexes, and/or rebuilding your index/indexes to restore the physically sequential order of the index pages.

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

I followed the article:
https://social.msdn.microsoft.com/forums/sqlserver/en-US/c4c1ee9d-9370-4068-a3f3-e9242bf80acb/page-splits-per-second-very-high

Virtual Machine VMware
MEMCM 2103 + Hotfixes KB10036164 + KB10582136 + KB10589155
SQL 2016 SP3 (Build 13.0.6300.2)
Windows Server 2016
CPU: 4
Memory Server: 192
Memory SQL: 114
30,000+ Clients
Installation date: July 2017
Reindex 1 / week
SQL CPU 85-90% Memory 60-65%
Storage

Any recommendations?

Thanks,
Dom

Answer 1

If only the alerts are the problem, the easiest way may be to disable them, or raise the threshold to a higher limit.

When Tom asked if you have any problems, he meant real problems. That is, users screaming about poor performance, or other indications that your system are not performing well enough.

If that is the case, these alerts may give you an idea of the problem.

But if the system is running well, I am not sure that there cause for alarm.

The page splits are kind of interesting, as it suggest that there is a table with an index structure that is not the best. Maybe the clustered index should be something else. Maybe it should just have a lower fill factor. I looked around, but unfortunately, I was not able to find a good way to find in which table the split occurred. So that again goes back to, do you see problems elsewhere?

Answer 2

That query does not return page split data, it returns highest queried table data. It does not in any way represent a problem.

Are you having a problem of some kind?

Also, please post the results of SELECT @@VERSION.

Answer 3

Hi Dominique DUCHEMIN,

As I wrote in the comment, You can identify the entity which are splitting by reading the transaction log records or the backup transaction log. Basically you need to search for 'LOP_DELETE_SPLIT' in the records in the transaction log and check which object is related to this transaction.

I have several posts in my blog where I examine the transaction log content. These can help you get started. I do not have specific post in searching for 'LOP_DELETE_SPLIT' but it is basically all the same. Trak the action which related to the event you want to examine ('LOP_DELETE_SPLIT' in your case) -> next you identify the related object.

Check if this post, where I examine something else with the same tools can help you:
https://ariely.info/Blog/tabid/83/EntryId/303/SQL-Server-Who-created-the-database.aspx

After you identify the problematic index then you can enforce different solutions.

Answer 4

page splits at 398.5278 per second

That value looks odd, is the thousand separater right?
Some small calculations:
A data/index page is 8 KB in size, if a split cause the half of it to move to a new page it would be 4 KB.
4 KB * 3.985.278 (?) = 15,2 GB per second; you must have very good performing hardware to handle the amount in a fashion way.

Answer 5

CONFIGMGR and the indexes you reference sound like the SCOM back end database. I suggest you make sure you have all the SCOM updates installed.

If this is the SCOM database, you should not be making changes to a 3rd party product. It will likely be reverted the next update.

The CPU and RAM would not affect page splits.