This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(179.20000000000002, 28.999999999999996%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBK%3C/text%3E%3C/svg%3E)
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.
Hi @Burt King ,
Agree with pituach.
Quote from the blog Tracking Page Compression Operations, which share you the detail examples about this.
>There isn’t really a whole lot of information about the operations of database compression that is documented as being available in the DMV’s or SQL Trace. Paul Randal pointed out on Twitter today that sys.dm_db_index_operational_stats() provides the page_compression_attempt_count and page_compression_success_count available. Beyond that the only other documented information for monitoring Data Compression are the Page Compression Attempts/sec and Pages Compressed/sec Performance Counters of the SQL Server:Access Methods object in Perfmon
Hi,
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 was thinking more along the lines of finding the event in dm_os_wait_stats. Is that available? I am trying to correlate processor usage to row compression on large tables. It would appear sqlserver.page_compression_attempt_failed and sqlserver.page_compression_tracing will not do so.
Hi,
dm_os_wait_stats provides the information about the waits encountered including these related to compression, but sqlserver.page_compression_attempt_failed provide the exact information only about the compression failure. Much simpler to get the "reason" out of this.
You can get information on PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE and HADR_COMPRESSED_CACHE_SYNC from the dm_os_wait_stats, but a compression can lead to other type of waits in-directly, which are not unique to compression, so how can you differentiate between them? Using the sqlserver.page_compression_attempt_failed provide direct information related only to compression.
Remember that activating compression lead to more CPU uses. In addition, the more CPU core we have, the more work the server have to do to coordinate their efforts and combine their results. This might result with more CXPACKET waits for example. So... if you have sqlserver.page_compression_attempt_failed failure event, then you know that this is related to the compassion, but if you monitor many CXPACKET waits in the dm_os_wait_stats then it gives nothing directly (you can poll the data indirectly probably, for example using sqlserver.page_compression_attempt_failed which take us back to the start). To make the relation between the waits and the compression you will need to cross more information
I don't have any compression failures - I'm not looking for that. I'm trying to track how much CPU I'm consuming because I'm using compression.
OK, now it is clear. and this is probably not related to Extended Event but to monitoring the OS CPU (perfmon might help)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
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 suggest you read this:
https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-ver15#when-page-compression-occurs
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.