[Solved] SQL Server performance very very LOW in UPDATE/INSERT operations (but not always)

EverSQL Database Performance Knowledge Base

SQL Server performance very very LOW in UPDATE/INSERT operations (but not always)

Database type:

I have a very simple database with this table

This table is used to generate counters for some operations (Order N°, Invoice Num ecc.)

The problem is that on my developer machine the generation of 1000 counters needs 0.3s instead on a Virtual machine with SQL Server on Windows Azure needs 8 seconds more than 20 times slower.

The script I used is this:

    SET STATISTICS IO ON; 
    SET STATISTICS TIME ON;

    CREATE DATABASE mytest;
    GO

    USE mytest;

    CREATE TABLE [dbo].[Counters]
    (
        [SequenceName] [varchar](100) NOT NULL,
        [LastUsed] [bigint] NULL,
      CONSTRAINT [PK_Counters] PRIMARY KEY CLUSTERED ([SequenceName] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
      GO

        Declare @counter int
        declare @LastUsed as BIGINT
        declare @SequenceName as varchar(100)

        Set @counter = 1

        set @SequenceName = 'AA00TEST-DELETE-THIS'

        set Nocount  on;

        DELETE FROM Counters

        INSERT INTO [dbo].[Counters] ([SequenceName] ,[LastUsed]) VALUES ('AA00TEST-DELETE-THIS', 0)


        While @counter <= 1000
        Begin

             UPDATE Counters
                     SET LastUsed = LastUsed + 1,
                            @LastUsed = LastUsed + 1
                     WHERE [email protected]

            Set @counter = @counter + 1

        End

        SELECT LastUsed From Counters WHERE [email protected]

DROP TABLE Counters
GO

USE master
GO

DROP DATABASE mytest
GO

Note tha if I replace the UPDATE istruction with this one the execution time is 0.3s:

SELECT @LastUsed = LastUsed + 1 FROM Counters WHERE [email protected]

Note that I test this behaviour on many servers and in all I got the same performance.

I test on Azure VM SQL Server with 8 CPU, SQL 2014... 8 Sec.

I test on Azure DB... 8 Sec.

I test on a VMWare virtual machine, SQL 2005 8 sec.

I test on a phisical SQL Server server with 16 CPU, SQL 2008... 8 Sec.

On my MacBook pro with Windows 8 Pro SQL 2012... 0,3sec On my PC with Windows 8 Pro SQL 2012... 0,3sec

The script is executed directly on the server with Managemant Studio connected to localhost. No network roundtrip is present and no application present, I'm directly on the server and on SQL Server core.

This are statistics on my PC (1 iteration):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
Table 'Counters'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Counters'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

This are statistics on Server (1 iteration):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 11 ms.
Table 'Counters'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 18 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Counters'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

All PC And server have the same "Recovery model"

I have found no way to boost it up

Thank You!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX counters_idx_sequencename ON Counters (SequenceName);
The optimized query:
SELECT
        @LastUsed = Counters.LastUsed + 1 
    FROM
        Counters 
    WHERE
        Counters.SequenceName = @SequenceName

Related Articles



* original question posted on StackOverflow here.