I am trying to track the expense of page compression in sql server with wait stats. What events should I be looking for? Ideally I'd like to understand CPU consumption.
Note: You can use sys.dm_db_index_operational_stats() which provides the page_compression_attempt_count and page_compression_success_count available.
The events which you can track are: sqlserver.page_compression_attempt_failed and sqlserver.page_compression_tracing
I do not believe there is a wait state for that information.
Page compression only operates on an 8k page at a time. So it has very little impact, unless you are doing bulk inserts and many pages are changing.
I don't see why page compression as such would produce more waits. After all, page compression is something that happens on the CPU, and wait stats reflects what happens outside the CPU. Well, with the exception for the preemptive waits, that is, when SQL Server runs code outside SQL Server, but that is not the case here.
However, increased CPU usage does exhibit in one way in the page stats, you get more SOS_SCHEDULER_WAITs. This wait type is registered every time a process yields voluntarily to let other processes execute. If there is idle CPU, the wait as such will be short, but the number of waits will go up by one.
But I am not sure that this is the best way to track CPU usage.
16 people are following this question.