0

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.

This is a screenshot of the Execution Plan from SQL Server Management Studio.

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.

11
  • Can you also add the index definitions on Z_SIMULATION_1_TABLE in your question? – Mark Sinkinson Jul 4 '16 at 14:50
  • @MarkSinkinson i just realized that the indexes weren't listed. I've added those in. – Eric B. Jul 4 '16 at 16:00
  • 2
    So the table has 5 indexes, all non-clustered? Some of them seem not very useful. Like the (id) index which is covered by the (id, element). And the (is_active) index which is probably not useful at all. Unless the distribution is heavily one-sided (for example 95% 1 and 5% 0), in which case, a partial index would be better (smaller). – ypercubeᵀᴹ Jul 4 '16 at 16:02
  • @ypercubeᵀᴹ Very likely, some of the indexes are not useful. Some were created hoping to speed up views, but are likely useless. Can you explain why the (is_active) index is not useful? Do bit fields not need indexes if trying to search by bit status? – Eric B. Jul 4 '16 at 16:08
  • If the query select * from table where is_acive = b'0'; is going to bring half the table (or even 20%+) then a full table scan is probably more fast so the index won't be used anyway. if you have more complex conditions that use both the bit column and other columns, then a composite index might be useful, yes. I was only commenting on the (is_active) single-column index. – ypercubeᵀᴹ Jul 4 '16 at 16:10
4

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.

2
  • Unfortunately, in production, I'm not truly rebuilding the table's contents. I'm just trying some simulations right now to reproduce some production queries. And yes - there are production queries that insert some 400k records into the table in a single transaction. So disabling the indexes during those inserts wouldn't be easily feasible. – Eric B. Jul 4 '16 at 15:43
  • Thanks for the update. It confirms some of my thoughts as well. However, before I start dragging my IT infrastructure guys into the equation, I was hoping to optimize query levels/DB structures the most I could. If I've gone as far as I can, then the next step will have to be hardware. – Eric B. Jul 5 '16 at 15:58
1

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

8
  • Unfortunately, yes - I have some queries that are inserting 400-500k records into a table. There is some significant refactoring that must be done at a code level, but given the impact these changes would bring, it is not something that will happen quickly. In the meantime, I'm looking to see if there are ways at a DB level that I can introduce some improvements. – Eric B. Jul 4 '16 at 15:40
  • My ID is not unique for the table, but the ID/Element combo is. If there is a way of creating a non-composite PK for the table with that constraint, please let me know. The index on the bit is to help do queries where the bit is set to 1 or 0. – Eric B. Jul 4 '16 at 15:41
  • I know what a composite key is. You really need a composite key of BIGINT - that is 340282366920938000000000000000000000000. I know what an index does. It also slows down insert. I answered the stated question. Did you actually try any of those inserts? – paparazzo Jul 4 '16 at 15:55
  • @Paparzzi - no offense meant; my knowledge here is limited. I tried the insert with Order by value as well as order by id, value, but neither made an impact. I haven't tried disabling the index yet (as suggested by @DavidSpillett as well), as I am not sure what impact that will have to be done during regular production seeing that this table is updated quite often by multiple threads. However, I will test it out to see the index's impact. I also edited the question to add the indexes. I'm not sure I understand how to create a composite key of BIGINT; can you please expand? – Eric B. Jul 4 '16 at 16:06
  • @EricB. It is not about how. It is about WHY. BIGINT is big. Do you really need that overhead. Do you really need to allow for 340282366920938000000000000000000000000 rows. If a row is 1K that would be 83076749736557200000000000000000000 GB. No offense meant but your question just does not make sense. – paparazzo Jul 4 '16 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.