数据库插入性能测试脚本

use master 
go
create database test
go
use [test]
 --只有聚集索引
CREATE TABLE Department(
 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 Name NVARCHAR(200) NOT NULL,
 GroupName NVARCHAR(200) NOT NULL,
 Company NVARCHAR(300),
 ModifiedDate datetime NOT NULL  DEFAULT (getdate())
)
 

--truncate table Department
 
use [test]
go

DECLARE @tts DATETIME
DECLARE @tte DATETIME
DECLARE @i int
SET @i=1

SET @tts=GETDATE()

while @i<50000
begin 
INSERT INTO Department(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组')
set @i=@i+1
end

SET @tte=GETDATE()

PRINT DATEDIFF(ss,@tts,@tte)
544

南基插入用了：1186 second

山西用了：554 second

两台服务器磁盘队列对比图

南基

山西

查询速度

 set statistics time on 
 SELECT COUNT(*)
     FROM   ClassifyResult
     WHERE  ClassifyRuleID = 49705
            AND ArchiveID = 246206412
 set statistics time off

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

脚本比较

USE [sss]
--DROP TABLE testperformance

SELECT * INTO testperformance FROM  [sys].[dm_os_performance_counters]


CREATE CLUSTERED INDEX cix_testperformance ON [dbo].[testperformance]([object_name])





--方法一：EXISTS
SET STATISTICS TIME ON
IF EXISTS ( SELECT  1
            FROM    testperformance
            WHERE   [counter_name] = 'Memory broker clerk size'
                    AND [cntr_value] = 3171 )
    BEGIN 
        PRINT 'nihao'
    END
ELSE
    BEGIN
        PRINT  2
    END 

SET STATISTICS TIME OFF


-- SQL Server 执行时间:
--   CPU 时间 = 1970 毫秒，占用时间 = 41457 毫秒。
--2

-- SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。





--方法二：COUNT(*)
SET STATISTICS TIME ON
IF ( SELECT COUNT(*)
     FROM   testperformance
     WHERE  [counter_name] = 'Memory broker clerk size'
            AND [cntr_value] = 3171
   ) > 0
    BEGIN 
        PRINT 'nihao'
    END
ELSE
    BEGIN
        PRINT  2
    END 

SET STATISTICS TIME OFF


-- SQL Server 执行时间:
--   CPU 时间 = 2203 毫秒，占用时间 = 63261 毫秒。
--2

-- SQL Server 执行时间:
--   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

山西内存：14G

南基内存：60G

使用扩展事件

-- =============================================
-- Author:      <桦仔>
-- Blog:        <http://www.cnblogs.com/lyhabc/>
-- Create date: <2014/10/18>
-- Description: <扩展事件跟踪SQL语句等待和执行时间>
-- Link : http://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files/
-- =============================================
USE [master]
GO

CREATE EVENT SESSION [TrackSQLWait] ON SERVER
ADD EVENT sqlserver.sql_statement_starting (
    ACTION ( sqlserver.session_id, sqlserver.database_id,sqlserver.sql_text ,package0.collect_system_time)
    WHERE ( sqlserver.database_id = 7 ) ), --★Do
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.session_id, sqlserver.database_id, sqlserver.sql_text ,package0.collect_system_time)
    WHERE ( sqlserver.database_id = 7 ) ), --★Do
ADD EVENT sqlserver.error_reported (
    ACTION ( sqlserver.session_id, sqlserver.database_id, sqlserver.sql_text ,package0.collect_system_time)
    WHERE ( sqlserver.database_id = 7 ) ), --★Do
ADD EVENT sqlos.wait_info (
    ACTION ( sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text,sqlserver.plan_handle ,package0.collect_system_time)
    WHERE 
    ( duration > 1000
      AND ( ( wait_type > 31    -- Waits for latches and important wait resources (not locks) 
                            -- that have exceeded 10 seconds. 
              AND ( ( wait_type > 47
                      AND wait_type < 54
                    )
                    OR wait_type < 38
                    OR ( wait_type > 63
                         AND wait_type < 70
                       )
                    OR ( wait_type > 96
                         AND wait_type < 100
                       )
                    OR ( wait_type = 107 )
                    OR ( wait_type = 113 )
                    OR ( wait_type > 174
                         AND wait_type < 179
                       )
                    OR ( wait_type = 186 )
                    OR ( wait_type = 207 )
                    OR ( wait_type = 269 )
                    OR ( wait_type = 283 )
                    OR ( wait_type = 284 )
                  )
            )
            OR ( duration > 30000        -- Waits for locks that have exceeded 30 secs.
                 AND wait_type < 22
               )
          )
    ) )
ADD TARGET package0.asynchronous_file_target (  SET filename = 'E:\ExtendedEvent\TrackSQLWait.xel' ,
                                                metadatafile = 'E:\ExtendedEvent\TrackSQLWait.xem' )
WITH ( MAX_MEMORY = 4 MB ,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS )
GO


ALTER EVENT SESSION [TrackSQLWait]
ON SERVER
STATE=START




-- Query the Event data from the Target.
SELECT  event_data.value('(event/@name)[1]', 'varchar(50)') AS [event_name] ,
        event_data.value('(event/data[@name="source_database_id"]/value)[1]',
                         'int') AS [source_database_id] ,
        OBJECT_NAME(event_data.value('(event/data[@name="object_id"]/value)[1]',
                                     'int')) AS [object] ,
        event_data.value('(event/data[@name="object_type"]/value)[1]',
                         'varchar(60)') AS [object_type] ,
        event_data.value('(event/data[@name="state"]/text)[1]', 'varchar(50)') AS [state] ,
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration] ,
        event_data.value('(event/data[@name="cpu"]/value)[1]', 'bigint') AS [cpu] ,
        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads] ,
        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes] ,
        event_data.value('(event/data[@name="error"]/value)[1]', 'bigint') AS [error] ,
        event_data.value('(event/data[@name="severity"]/value)[1]', 'int') AS [severity] ,
        event_data.value('(event/data[@name="user_defined"]/value)[1]',
                         'varchar(5)') AS [user_defined] ,
        event_data.value('(event/data[@name="message"]/value)[1]',
                         'varchar(max)') AS [message] ,
        event_data.value('(event/data[@name="wait_type"]/text)[1]',
                         'varchar(250)') AS wait_typetype ,
        event_data.value('(event/data[@name="wait_type"]/value)[1]',
                         'varchar(250)') AS wait_typevalue ,
        event_data.value('(event/data[@name="max_duration"]/value)[1]',
                         'bigint') AS max_duration ,
        event_data.value('(event/data[@name="completed_count"]/value)[1]',
                         'bigint') AS [completed_count] ,
        event_data.value('(event/action[@name="plan_handle"]/value)[1]',
                         'varchar(max)') AS [plan_handle] ,
        DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
                event_data.value('(event/action[@name="collect_system_time"]/text)[1]',
                                 'datetime2')) AS [system_time] ,
        event_data.value('(event/action[@name="session_id"]/value)[1]',
                         'bigint') AS [session_id] ,
        event_data.value('(event/action[@name="sql_text"]/value)[1]',
                         'varchar(max)') AS [sql_text]
FROM    ( SELECT    CAST(event_data AS XML) AS event_data
          FROM      sys.fn_xe_file_target_read_file('E:\ExtendedEvent\TrackSQLWait_*.xel',
                                                    'E:\ExtendedEvent\TrackSQLWait_*.xem',
                                                    NULL, NULL)
        ) AS tab
WHERE   event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') > 100
        AND event_data.value('(event/@name)[1]', 'varchar(50)') LIKE 'wait_info'
ORDER BY [system_time]






ALTER EVENT SESSION [TrackSQLWait]
ON SERVER
STATE=STOP
GO


DROP EVENT SESSION [TrackSQLWait] ON SERVER