Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize?

Question

I have a SQL Server 2008 machine that is running backed by a SAN (not entirely certain of the SAN configuration). I've been noticing that some queries are slow to respond, so have been running some tests trying to optimize/index the slow parts. I am not a SQL Server DBA (background is more MySQL centric), but have the task of improving this performance.

My tests are the following. I created 2 tables:

CREATE TABLE "Z_SIMULATION_0" 
(
    "ID" INT NOT NULL,
    "VALUE" INT NOT NULL,
    PRIMARY KEY ("ID")
);

CREATE TABLE "Z_SIMULATION_1_TABLE" 
(
    "ID" BIGINT NOT NULL,
    "ELEMENT" BIGINT NOT NULL,
    "flag" INT NULL DEFAULT NULL,
    "IS_ACTIVE" BIT NOT NULL DEFAULT b'0',
    PRIMARY KEY ("ID", "ELEMENT")
);

The indexes for the Z_SIMULATION_1_TABLE as exported by SSMS are:

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_1_IS_ACTIVE] 
    ON [dbo].[Z_SIMULATION_1_TABLE] ([IS_ACTIVE] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_INDEX_ACTIVE] 
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC, [IS_ACTIVE] ASC)
    INCLUDE ([ELEMENT], [flag]) 
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ELEMENT] 
    ON [dbo].[Z_SIMULATION_1_TABLE]( [ELEMENT] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ID] 
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

ALTER TABLE [dbo].[Z_SIMULATION_1_TABLE] 
  ADD CONSTRAINT [PK_Z_SIMULATION_1_TABLE] 
  PRIMARY KEY NONCLUSTERED ([ID] ASC, [ELEMENT] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
            SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, 
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

I then populated the Z_SIMULATION_0 table with 600K random (non-repeating) ID/Value pairs (IDs are between 1 & 600k).

Finally, I executed this query:

INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) 
    SELECT 2, value, 0 
    FROM Z_SIMULATION_0 
    WHERE ID>=1 AND ID <= 500000;

This query took 14s to complete, which I find is significantly too long. I don't see why an insert into a blank table should take this long, and more importantly, I'm not sure how to optimize things to make it any faster.

.

I see the Sort (for the index I presume?) is taking the bulk of the time, but not sure what to do about that.

How can I improve the performance/reduce the amount of time it takes to do the insert? I'm open to changing/modifying/deleting indexes as required or necessary.

Answer 1

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on.

When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or disabling all the indexes except the clustered index (if the table has one) before inserting the rows then recreating the indexes afterwards is often more efficient.

Update

If you can't remove the indexes because they may be used, then do some analysis as @ypercube implies to ensure that they are indeed required and useful and remove any that are of doubtable benefit. You may of course be able to temporarily drop some of the indexes if not all. Experiment to see if you would benefit from a clustered index (rules of thumb say no in this example, but rules of thumb do not always apply so running your own application specific benchmarks is often a good idea).

Having said that, if you are only doing a partial update in real life the issue is moot: if you are only inserting enough data to increase the table by 1% in size then dropping the indexes would never be more efficient anyway. Also it would be worth performing your benchmarks on realistically sized data rather than an empty table if the table in real life is not going to be empty.

If you have ruled out index changes, or done as much as you can in that direction, the only thing that you can do from there is to improve the IO performance. As we know nothing of your IO subsystem we can't help you much at the moment, but a few general tips include keeping tempdb on different drives to your main database, for some IO patterns keeping logs on different spindles can help too, or look to spreading the indexes between filegroups on different drive, and so forth. If you have enterprise edition so can use partitioning and your insert blocks map to a nice partitioning scheme, you could use partition switching to do the inserts faster then switch the whole block in in one do. If your data stores are SAN based, try moving tempdb to local drives. If you have control to change the hardware perhaps have some/all structures on SSDs. Adding more ram may help but probably not: you'd have to add enough that all those index sort operations could all happen in RAM all at the same time and SQL Server might still spool to disk (because it considered the RAM better used for something else) and even if you can arrange for the whole sorting operation to be done in RAM the data still has to be written to physical media at the end, there is no avoiding that.

There are a lot of articles (and whole books) out there on the subject of optimising your database IO system unfortunately. If you list your current disk technolog{y|ies} and layout someone might be able to make suggestions for practical things to try first. No matter what you try, remember to run realistic application benchmarks: artificial benchmarks are useful as a quick and easy gauge of what to expect, but sometimes a complexity of your real workload will mean that a massive benefit seen in artificial benchmarks in dev/tes is all but destroyed in production.

Answer 2

Be aware - I posted this before index definitions were posted

Insert in the order of the PK to keep fragmentation down

INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) 
SELECT 2, value, 0 
FROM Z_SIMULATION_0 
WHERE ID >= 1 
  AND ID <= 500000 
ORDER BY value; 

or

INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) 
SELECT top (500000) 2, value, 0 
FROM Z_SIMULATION_0 
ORDER BY value;

also try taking a tablock

have to assume the Z_SIMULATION_1_TABLE index is on IS_ACTIVE

ALTER INDEX IX_Z....1 ON dbo.Z_SIMULATION_1_TABLE DISABLE;
ALTER INDEX IX_Z....2 ON dbo.Z_SIMULATION_1_TABLE DISABLE;
ALTER INDEX IX_Z....3 ON dbo.Z_SIMULATION_1_TABLE DISABLE;
ALTER INDEX IX_Z....4 ON dbo.Z_SIMULATION_1_TABLE DISABLE;
INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) 
SELECT top (500000) 2, value, 0 
FROM Z_SIMULATION_0 
ORDER BY value;
ALTER INDEX IX_Z....1 ON dbo.Z_SIMULATION_1_TABLE ENABLE;
ALTER INDEX IX_Z....1 ON dbo.Z_SIMULATION_1_TABLE ENABLE;
ALTER INDEX IX_Z....1 ON dbo.Z_SIMULATION_1_TABLE ENABLE;
ALTER INDEX IX_Z....1 ON dbo.Z_SIMULATION_1_TABLE ENABLE;

you really need a composite PK of bigint?
you have ID that is not an Identity
it appears you have an index on a bit
it is not clear to me what you are trying to accomplish

I've been noticing that some queries are slow to respond
I hope your queries are not inserting 500000 rows in a table

Now responding to the indexes posted
Those indexes are just crazy
That is a level of indexing I would expect on static table
An index has overhead to add, delete, and update and you are adding 500,000 rows
I think you are lucky to be getting 500,000 in 14 seconds
Add Disable / Enable to all but the PK
Do not disable the PK
And try a composite PK of Int, Int clustered