Testing Data Compression
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.
sql-server sql-server-2012 compression extended-events distributed-replay
edited 12 mins ago
Paul White♦
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
add a comment |
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.
sql-server sql-server-2012 compression extended-events distributed-replay
edited 12 mins ago
Paul White♦
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
add a comment |
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.
sql-server sql-server-2012 compression extended-events distributed-replay
edited 12 mins ago
Paul White♦
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
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.
sql-server sql-server-2012 compression extended-events distributed-replay
sql-server sql-server-2012 compression extended-events distributed-replay
edited 12 mins ago
Paul White♦
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
edited 12 mins ago
Paul White♦
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
edited 12 mins ago
Paul White♦
50.8k14277447
edited 12 mins ago
Paul White♦
50.8k14277447
edited 12 mins ago
Paul White♦
50.8k14277447
50.8k14277447
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
asked Aug 14 '18 at 17:46
Corey HambrickCorey Hambrick
92
92
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214917%2ftesting-data-compression%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214917%2ftesting-data-compression%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
