Liten udda snurra för att testa hur snabb en SQL Server installation är på en viss typ av last:


USE [master]
GO

-- Duration i millisekunder        Duration i sekunder        Antal trans per sekund
-- 117743    117    8547  --> DB på D: / LOG på D:
-- 133223    133    7518  --> DB på C: / LOG på C:
-- 134710    135    7407  --> DB på D: / LOG på C:
-- 120130    120    8333  --> DB på C: / LOG på D:

CREATE DATABASE [PerformanceTestDB] ON PRIMARY
( NAME = N'PerformanceTestDB', FILENAME = N'C:\PerformanceTestDB.mdf' , SIZE = 78848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'PerformanceTestDB_log', FILENAME = N'C:\PerformanceTestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Finnish_Swedish_CI_AS
GO

ALTER DATABASE [PerformanceTestDB] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [PerformanceTestDB].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

ALTER DATABASE [PerformanceTestDB] SET RECOVERY FULL
GO

ALTER DATABASE [PerformanceTestDB] SET  MULTI_USER
GO

ALTER DATABASE [PerformanceTestDB] SET PAGE_VERIFY CHECKSUM  
GO

USE [PerformanceTestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Performance Message Table](
    [TransID] [int] NULL,
    [TransTime] [datetime] NULL,
    [Message] [nvarchar](30) COLLATE Finnish_Swedish_CI_AS NULL
) ON [PRIMARY]

GO

USE [PerformanceTestDB]

CREATE UNIQUE CLUSTERED INDEX [PK TransID] ON [dbo].[Performance Message Table]
(
    [TransID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

create procedure testa_prestanda as
begin
set nocount on
    declare @loopa as int
    declare @maxvalue as int
    declare @starttid as datetime
    declare @stoptid as datetime

    set @loopa = 1
    
    DBCC FREEPROCCACHE WITH NO_INFOMSGS
    DBCC DROPCLEANBUFFERS  WITH NO_INFOMSGS
    
    select @starttid = getdate()

    begin transaction
    INSERT INTO [PerformanceTestDB].[dbo].[Performance Message Table]
               ([TransID] ,[TransTime] ,[Message])
         VALUES (1,  GETDATE(), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
    commit transaction
    
    while @loopa < 1000001
    begin

        begin transaction

            SELECT @maxvalue = MAX([TransID])
              FROM [PerformanceTestDB].[dbo].[Performance Message Table]

            INSERT INTO [PerformanceTestDB].[dbo].[Performance Message Table]
                       ([TransID] ,[TransTime], [Message])
                 VALUES (@maxvalue + 1, GETDATE(), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')

            set @loopa = @loopa +1

        commit transaction

    end

    select @stoptid = getdate()

    select
    datediff(MILLISECOND,@starttid,@stoptid) [Duration i millisekunder],
    datediff(SECOND,@starttid,@stoptid) [Duration i sekunder],
    1000000/datediff(SECOND,@starttid,@stoptid) [Antal trans per sekund]

end
go

exec testa_prestanda
go

use master
go

drop database [PerformanceTestDB]
go