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!
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX counters_idx_sequencename ON Counters (SequenceName);
SELECT
@LastUsed = Counters.LastUsed + 1
FROM
Counters
WHERE
Counters.SequenceName = @SequenceName