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?

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.

StackExchange Database Administrators Q#232113, answer score: 10