You may wonder what these three things have in common. Before you try to play Carnac the Magnificent, humor me for a quick and high-level review of each.
A read-only filegroup is one from which data can only be read, not written or updated. Fairly obvious, but it’s always good to define terms. Before marking a filegroup as read-only, it is considered best practice to rebuild indexes for the filegroup with a fill factor of 100%. With a fill factor of 100%, SQL Server will put as many rows as possible on each page, which minimizes the amount of space used by the index on disk and in memory.
Page Splits/sec is a SQL Server Access Methods counter within Windows Performance Monitor which tracks the “Number of page splits per second that occur as a result of overflowing index pages”. (Definition pulled from my Performance Monitor session on Windows 7, SQL 2008 SP1 installed.) Every time an index page has to split because a new row needs to be the added to the page, or existing row is updated and it won’t fit, it is noted in this counter. What is noteworthy is that this counter also tracks any time a new page is allocated to an index even if it is not from a page split (e.g. the last page in a clustered index is full, a new page is needed to accommodate the next row).
If you are monitoring Page Splits/sec to try and see if you need to adjust fill factors for your indexes, remember that new pages are being counted as well. There was a Connect request to have new page allocations tracked separately, but it was marked as closed and will not fix.
The 825 Error is logged in SQL Server’s Event Log when a page is requested from disk and the request fails. SQL Server will then request the page again, up to three more times, before it reports an 823 or 824 error, which are much higher severity. Realize that if the read succeeds on any of re-tries, the user never knows the problem…unless you’re monitoring for the 825 error, as recommended in this post of Paul Randal’s.
I have provided a minimal summary of each topic and I know that in-depth posts and article have been written for each topic; my point here wasn’t to divulge anything new. And while it may not seem like it, these three things do have something in common: they were three of the topics I answered a question about on SQLCruise to win a netbook. I will be the first to admit that I have been fortunate this year, and I am not taking it for granted. Thank you again to the SQLCruise sponsors for an amazing week, and thank you MSSQLTips.com for sponsoring the Day 4 netbook!
Good post! I liked how you summarized each topic. It was easy to follow and makes sense. Again, congrats on the netbook! 🙂
A very belated thank you! And congrats to YOU on the netbook. I still don’t know what to do with myself; so fortunate!