How to determine the cost of index creation?
I have a table that I want to create an index on to improve the performance of a couple of SELECT
queries that use the table.
How can I test whether the index has had any significant detrimental impact on DUI operations on the table?
The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT
or UPDATE
query against that table before and after creating the desired index?
sql-server performance sql-server-2014 index
add a comment |
I have a table that I want to create an index on to improve the performance of a couple of SELECT
queries that use the table.
How can I test whether the index has had any significant detrimental impact on DUI operations on the table?
The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT
or UPDATE
query against that table before and after creating the desired index?
sql-server performance sql-server-2014 index
3
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago
add a comment |
I have a table that I want to create an index on to improve the performance of a couple of SELECT
queries that use the table.
How can I test whether the index has had any significant detrimental impact on DUI operations on the table?
The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT
or UPDATE
query against that table before and after creating the desired index?
sql-server performance sql-server-2014 index
I have a table that I want to create an index on to improve the performance of a couple of SELECT
queries that use the table.
How can I test whether the index has had any significant detrimental impact on DUI operations on the table?
The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT
or UPDATE
query against that table before and after creating the desired index?
sql-server performance sql-server-2014 index
sql-server performance sql-server-2014 index
edited 12 hours ago
Josh Darnell
6,66522039
6,66522039
asked 18 hours ago
SEarle1986SEarle1986
492316
492316
3
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago
add a comment |
3
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago
3
3
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago
add a comment |
1 Answer
1
active
oldest
votes
If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress. Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.
In addition to additional overhead for your DUI operations, another consideration is blocking. You might have different blocking patterns than you're used to, since some SELECT
queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.
I only point this out to make the point that DUI "overhead" is not the only consideration. Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.
Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like. I'll set up a "LogTable" in a new database:
USE [master];
GO
CREATE DATABASE [232113];
GO
ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [232113];
GO
CREATE TABLE dbo.LogTable
(
Id INT PRIMARY KEY IDENTITY(1,1),
Col1 DATETIME NOT NULL,
Col2 CHAR(4) NOT NULL
);
Then I use these two queries to simulate the INSERT / UPDATE workload:
-- insert a row
INSERT INTO dbo.LogTable
(Col1, Col2)
VALUES
(GETDATE(), 'val2');
-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
FROM dbo.LogTable
ORDER BY NEWID());
Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution. This completes in about 25 seconds with just the clustered index in place.
Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:
CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);
Running the test again, it took basically the same amount of time (25 seconds). Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).
Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal. But hopefully it demonstrates the concept.
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
add a comment |
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%2f232113%2fhow-to-determine-the-cost-of-index-creation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress. Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.
In addition to additional overhead for your DUI operations, another consideration is blocking. You might have different blocking patterns than you're used to, since some SELECT
queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.
I only point this out to make the point that DUI "overhead" is not the only consideration. Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.
Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like. I'll set up a "LogTable" in a new database:
USE [master];
GO
CREATE DATABASE [232113];
GO
ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [232113];
GO
CREATE TABLE dbo.LogTable
(
Id INT PRIMARY KEY IDENTITY(1,1),
Col1 DATETIME NOT NULL,
Col2 CHAR(4) NOT NULL
);
Then I use these two queries to simulate the INSERT / UPDATE workload:
-- insert a row
INSERT INTO dbo.LogTable
(Col1, Col2)
VALUES
(GETDATE(), 'val2');
-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
FROM dbo.LogTable
ORDER BY NEWID());
Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution. This completes in about 25 seconds with just the clustered index in place.
Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:
CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);
Running the test again, it took basically the same amount of time (25 seconds). Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).
Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal. But hopefully it demonstrates the concept.
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
add a comment |
If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress. Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.
In addition to additional overhead for your DUI operations, another consideration is blocking. You might have different blocking patterns than you're used to, since some SELECT
queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.
I only point this out to make the point that DUI "overhead" is not the only consideration. Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.
Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like. I'll set up a "LogTable" in a new database:
USE [master];
GO
CREATE DATABASE [232113];
GO
ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [232113];
GO
CREATE TABLE dbo.LogTable
(
Id INT PRIMARY KEY IDENTITY(1,1),
Col1 DATETIME NOT NULL,
Col2 CHAR(4) NOT NULL
);
Then I use these two queries to simulate the INSERT / UPDATE workload:
-- insert a row
INSERT INTO dbo.LogTable
(Col1, Col2)
VALUES
(GETDATE(), 'val2');
-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
FROM dbo.LogTable
ORDER BY NEWID());
Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution. This completes in about 25 seconds with just the clustered index in place.
Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:
CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);
Running the test again, it took basically the same amount of time (25 seconds). Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).
Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal. But hopefully it demonstrates the concept.
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
add a comment |
If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress. Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.
In addition to additional overhead for your DUI operations, another consideration is blocking. You might have different blocking patterns than you're used to, since some SELECT
queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.
I only point this out to make the point that DUI "overhead" is not the only consideration. Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.
Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like. I'll set up a "LogTable" in a new database:
USE [master];
GO
CREATE DATABASE [232113];
GO
ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [232113];
GO
CREATE TABLE dbo.LogTable
(
Id INT PRIMARY KEY IDENTITY(1,1),
Col1 DATETIME NOT NULL,
Col2 CHAR(4) NOT NULL
);
Then I use these two queries to simulate the INSERT / UPDATE workload:
-- insert a row
INSERT INTO dbo.LogTable
(Col1, Col2)
VALUES
(GETDATE(), 'val2');
-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
FROM dbo.LogTable
ORDER BY NEWID());
Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution. This completes in about 25 seconds with just the clustered index in place.
Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:
CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);
Running the test again, it took basically the same amount of time (25 seconds). Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).
Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal. But hopefully it demonstrates the concept.
If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress. Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.
In addition to additional overhead for your DUI operations, another consideration is blocking. You might have different blocking patterns than you're used to, since some SELECT
queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.
I only point this out to make the point that DUI "overhead" is not the only consideration. Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.
Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like. I'll set up a "LogTable" in a new database:
USE [master];
GO
CREATE DATABASE [232113];
GO
ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [232113];
GO
CREATE TABLE dbo.LogTable
(
Id INT PRIMARY KEY IDENTITY(1,1),
Col1 DATETIME NOT NULL,
Col2 CHAR(4) NOT NULL
);
Then I use these two queries to simulate the INSERT / UPDATE workload:
-- insert a row
INSERT INTO dbo.LogTable
(Col1, Col2)
VALUES
(GETDATE(), 'val2');
-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
FROM dbo.LogTable
ORDER BY NEWID());
Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution. This completes in about 25 seconds with just the clustered index in place.
Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:
CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);
Running the test again, it took basically the same amount of time (25 seconds). Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).
Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal. But hopefully it demonstrates the concept.
answered 16 hours ago
Josh DarnellJosh Darnell
6,66522039
6,66522039
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
add a comment |
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
1
1
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
Great answer - thanks! I hadn't thought of using SQL Query Stress
– SEarle1986
16 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
@SEarle1986 Thanks! I'm glad I could help!
– Josh Darnell
15 hours ago
add a comment |
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%2f232113%2fhow-to-determine-the-cost-of-index-creation%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
3
This is far too broad a question the way you've asked it. You should post the table definition and potential index definition, because those will likely be some deciding factors. For instance, if your logging table has a MAX column, and you wanna have that as included column, it's probably a dumb idea.
– Erik Darling
16 hours ago