We have a 1TB table that gets queried a lot. I have suggested we use Data Compression to save on space as well as help put more in memory. Because of the concern of CPU overhead, I have been tasked with proving our system can handle the rise in CPU.
I have got a test server that matches our memory of the production server where the 1TB resides. My plan has been to use a compressed and uncompressed version of the database on the test server to use the most active stored procedure against this 1TB table to capture Logical Reads, Physical Reads and CPU to use as comparisons for data compression efficiency.
I have captured this stored procedure using an RPC Completed Extended Event and exported to a table so that I have the statements to use on the testing server.
Questions
Should/Can I convert Extended Events data from a source into a Trace File to use with Distributed Replay?
Rather than using thousands of the sample stored proc, should I simply focus on running 1 stored procedure call say 10 or 15 times using the non-compressed db and then the compressed db and compile the results this way?
If there is a better solution or recommendation to this problem, I would like any suggestions.
SQL Server 2012 Enterprise Edition. The 1TB is all clustered and non-clustered indexes. This is a table holding all of our products within our catalog so most heavily used table within the database. The table receives a lot of reads as well as inserts and updates.
