I ended my last post on fill factors with following question..
What are the other things that need to be monitored after setting a fill factor?
How do we decide on the fill factor when we are not too sure about the random insert frequency?
But before answering that question, I felt its essential to write about the technique to detect the occurrence of high page splits and directly finding the object having maximum page splits. A table with a high fragmentation value indicates a very very likely chance of it having high page splits. This post will talk about the method to find the object that experienced high page splits within a certain period. I wouldn’t call this post a digression from last topic, but it doesn’t exactly answer the questions with which I ended my last post. But its important for one to know to detect high page splits occurrence this way other than checking it thro fragmentation numbers.
So Whats the starting point..As always in any performance tuning exercise the starting point is Perfmon Counters.I will do a full length post on Perfmon counters later ( may even be a series of posts ) but for the time being I will give a brief 2 line intro of Perfmon, just in case if you are not aware of it. Perfmon is a utility provided along with Microsoft windows, which helps you to monitor various system parameters like CPU/Memory/IO usage/Queue length/ and SQL Server parameters(counters) like Lock waits, Number of transactions,Number of Deadlocks, Number of User connections, Buffer cache hit ratio to name a very few. You can find the list of SQL Server related counters using the DMV/view
Select * from sys.dm_os_performance_counters : if on 2k5
Select * from dbo.sysperfinfo : if you are on 2k
One such counter called Pagespilts/sec under 'SQLServer:Access Methods' object is useful in finding the page splits happening on your server. If you average more than 10 with around( 50-100 active users ) during the day then I really start to worry and perhaps look at options to bring it down. Note the number provided is at server level and its a cumulative figure across all tables and databases. So if you have multiple databases working for multiple applications and each one with different range of users then you are better off monitoring regularly to identify your peak and off peak numbers.
As mentioned the Perfmon counter tells us page splits/sec across server and not which database or table. When a high Page split/sec is noticed the next step would be to find table is most likely responsible for it. For that there is cheeky way of
doing it. That is reading the SQL Server transaction log thro an undocumented function. The function fn_dblog can read a SQL Servers T-Log. It can be used in the following way.
Select * from ::fn_dblog(null,null)
Reading the transaction from the result thrown is bit difficult and cryptic as the function is undocumented and known only to microsoft. But we can make a few intelligent guesses using the columns and values displayed. The following queries can help you identify the object that had high page splits
Select [Object Name], [Index Name], count(1)
from ::fn_dblog(null, null)
Where Operation = N'LOP_DELETE_SPLIT'
Group By [Object Name], [Index Name]
Order By Count(1) desc
The object name would be object you were looking for. Count here refers to the number of rows that were moved because of a split.
Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context
Where operation = 'LOP_DELETE_SPLIT'
Group By AllocUnitName, Context
Order by NumberOfSplits desc
This one tells us the number of splits, where AllocUnitName refers to the object name.With these two commands one can find which object is experiencing high page splits. As these commands rely on Transaction log its better you record these values before running Backups against your database or executing a Backup log
So, now we have identified our object with high page splits. But, one can ask why would one need to do this as the Fragmentation Script can detect table with high fragmentation. Consider the following scenario. A table starts after the maintenance with 95% fill factor. Undergoes heavy page splits and becomes 50% full at the middle of maintenance cycle. After that having obtained many inserts it can again become say 90% full before the next maintenance window. So when you record the table fragmentation using the percentage used in fragmentation script it would tell you its 90% full which can mislead a DBA to think that fill factor is working.Fill factor is not doing the best as for more than half the time the table is only half or little over half full. In such a scenario though the internal fragmentation doesn’t indicate the correct picture. Logical fragmentation in the Fragmentation Script should provide hints on problem happening. But anyways, its better to have direct method to monitor high occurrence of page splits as shown above. So, its always important to measure fragmentation levels ( using my script please :) ), pagesplit/sec counter and number of pages on each table.Number of pages on each table can help in identifying the growth of the table/ increase in page count since last maintenance which is very very likely to be due to page splits. When one correlates these parameters, one can clearly confirm the object is involved in high page splits.
So, after confirming the object experiencing high page splits, one can get into the next step of estimating how many random inserts are happening in the table, without having any knowledge about the application.