performancia – Run SQL Server BLOG

CPU és diszk teljesítmény mérésére, 40000 sor beszúrása egy táblába , mindegyik külön tranzakcióként (értsd , tranzakciós logba is beíródik egyenként!)

CREATE DATABASE [beszuras]
Go
-- DROP TABLE [szamolotabla]

use [beszuras]
CREATE TABLE [dbo].[szamolotabla](
	[uid] bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[szamolo] [bigint] NOT NULL,
	[datumido] [datetime2](7) NULL,
) ON [PRIMARY]

---------------- insert 40000 rows
 
--DECLARE @Loops INT SET @Loops = 1 
DECLARE @CPU INT SET @CPU = @@CPU_BUSY 
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()


SET NOCOUNT ON
-- use [beszuras]
DECLARE @i int = 1;
WHILE @i < 40000
BEGIN
    SET @i += 1;
	-- WAITFOR DELAY '0:00:01'; 
    INSERT INTO [dbo].[szamolotabla] ([szamolo], datumido) VALUES (@i,GetDate() );
END;

PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 
   

-----------------------  drop database
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'beszuras'
GO
USE [master]
GO
ALTER DATABASE [beszuras] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [beszuras]
GO

CREATE DATABASE [beszuras]

-- DROP TABLE [szamolotabla]

use [beszuras]

CREATE TABLE [dbo].[szamolotabla](

[uid] bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,

[szamolo] [bigint] NOT NULL,

[datumido] [datetime2](7) NULL,

) ON [PRIMARY]

---------------- insert 40000 rows

--DECLARE @Loops INT SET @Loops = 1

DECLARE @CPU INT SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

SET NOCOUNT ON

-- use [beszuras]

DECLARE @i int = 1;

WHILE @i < 40000

BEGIN

SET @i += 1;

-- WAITFOR DELAY '0:00:01';

INSERT INTO [dbo].[szamolotabla] ([szamolo], datumido) VALUES (@i,GetDate() );

END;

PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))

----------------------- drop database

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'beszuras'

USE [master]

ALTER DATABASE [beszuras] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

DROP DATABASE [beszuras]

Eredmények :
7-30 másodperc diszk alrendszertől függően SSD – lassú storage, illetve CPU órajel függvényében.

Alwayson AG
30-80 sec AO AG szinkron üzemmód.

SQL szerver és windows os performancia figyelés:

<?xml version="1.0" encodINSTANCE="UTF-16"?>
<DataCollectorSet>
	<Status>0</Status>
	<Duration>1209600</Duration>
	<Description>
	</Description>
	<DescriptionUnresolved>
	</DescriptionUnresolved>
	<DisplayName>
	</DisplayName>
	<DisplayNameUnresolved>
	</DisplayNameUnresolved>
	<SchedulesEnabled>-1</SchedulesEnabled>
	<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\SQL07_20170319-000003</LatestOutputLocation>
	<Name>SQL</Name>
	<OutputLocation>C:\PerfLogs\Admin\SQL\SQLSRV01_20180307-000001</OutputLocation>
	<RootPath>%systemdrive%\PerfLogs\Admin\SQL2</RootPath>
	<Segment>0</Segment>
	<SegmentMaxDuration>0</SegmentMaxDuration>
	<SegmentMaxSize>2000</SegmentMaxSize>
	<SerialNumber>4</SerialNumber>
	<Server>
	</Server>
	<Subdirectory>
	</Subdirectory>
	<SubdirectoryFormat>3</SubdirectoryFormat>
	<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
	<Task>
	</Task>
	<TaskRunAsSelf>0</TaskRunAsSelf>
	<TaskArguments>
	</TaskArguments>
	<TaskUserTextArguments>
	</TaskUserTextArguments>
	<UserAccount>SYSTEM</UserAccount>
	<Security></Security>
	<StopOnCompletion>0</StopOnCompletion>
	<PerformanceCounterDataCollector>
		<DataCollectorType>0</DataCollectorType>
		<Name>DataCollector01</Name>
		<FileName>SQLdatacollector01</FileName>
		<FileNameFormat>3</FileNameFormat>
		<FileNameFormatPattern>yyMMdd</FileNameFormatPattern>
		<LogAppend>0</LogAppend>
		<LogCircular>0</LogCircular>
		<LogOverwrite>0</LogOverwrite>
		<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\</LatestOutputLocation>
		<DataSourceName>
		</DataSourceName>
		<SampleInterval>5</SampleInterval>
		<SegmentMaxRecords>0</SegmentMaxRecords>
		<LogFileFormat>3</LogFileFormat>
		<Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter>
		<Counter>\Memory\Available MBytes</Counter>
		<Counter>\Memory\% Committed Bytes In Use</Counter>
		<Counter>\Memory\Free System Page Table Entries</Counter>
		<Counter>\Memory\Pages/sec</Counter>
		<Counter>\Memory\Pool Nonpaged Bytes</Counter>
		<Counter>\Memory\Pool Paged Bytes</Counter>
		<Counter>\System\Processor Queue Length</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</Counter>
		<Counter>\PhysicalDisk(*)\Disk Reads/sec</Counter>
		<Counter>\PhysicalDisk(*)\Disk Writes/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Received/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Sent/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Total/sec</Counter>
		<Counter>\Processor(_Total)\% Processor Time</Counter>
		<Counter>\Processor(_Total)\% Interrupt Time</Counter>
		<Counter>\Processor(_Total)\% User Time</Counter>
		<Counter>\MSSQL$INSTANCE:General Statistics\User Connections</Counter>
		<Counter>\MSSQL$INSTANCE:Memory Manager\Memory Grants PendINSTANCE</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Compilations/sec</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Re-Compilations/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Buffer Manager\Buffer cache hit ratio</Counter>
		<Counter>\MSSQL$INSTANCE:Buffer Manager\Page life expectancy</Counter>
		<Counter>\MSSQL$INSTANCE:Locks(_Total)\Lock Waits/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Locks(_Total)\Number of Deadlocks/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Latch Waits/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Total Latch Wait Time (ms)</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Average Latch Wait Time (ms)</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\Batch Requests/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Transactions\Longest Transaction RunnINSTANCE Time</Counter>
		<Counter>\Process(sqlservr)\% Privileged Time</Counter>
		<Counter>\Process(sqlservr)\% Processor Time</Counter>
		<Counter>\Process(sqlservr)\% User Time</Counter>
		<Counter>\Process(sqlservr)\Private Bytes</Counter>
		<Counter>\Process(sqlservr)\Thread Count</Counter>
		<Counter>\Process(sqlservr)\Virtual Bytes</Counter>
		<Counter>\Process(sqlservr)\Handle Count</Counter>
	</PerformanceCounterDataCollector>
	<DataManager>
		<Enabled>0</Enabled>
		<CheckBeforeRunnINSTANCE>0</CheckBeforeRunnINSTANCE>
		<MinFreeDisk>0</MinFreeDisk>
		<MaxSize>0</MaxSize>
		<MaxFolderCount>0</MaxFolderCount>
		<ResourcePolicy>0</ResourcePolicy>
		<ReportFileName>report.html</ReportFileName>
		<RuleTargetFileName>report.xml</RuleTargetFileName>
		<EventsFileName>
		</EventsFileName>
	</DataManager>
</DataCollectorSet>

100

101

102

103

104

105

<?xml version="1.0" encodINSTANCE="UTF-16"?>

</Description>

</DescriptionUnresolved>

</DisplayName>

</DisplayNameUnresolved>

<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\SQL07_20170319-000003</LatestOutputLocation>

<OutputLocation>C:\PerfLogs\Admin\SQL\SQLSRV01_20180307-000001</OutputLocation>

<RootPath>%systemdrive%\PerfLogs\Admin\SQL2</RootPath>

</Server>

</Subdirectory>

<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>

<Task>

</Task>

</TaskArguments>

</TaskUserTextArguments>

<UserAccount>SYSTEM</UserAccount>

<Name>DataCollector01</Name>

<FileName>SQLdatacollector01</FileName>

<FileNameFormatPattern>yyMMdd</FileNameFormatPattern>

<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\</LatestOutputLocation>

</DataSourceName>

<Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter>

<Counter>\Memory\Available MBytes</Counter>

<Counter>\Memory\% Committed Bytes In Use</Counter>

<Counter>\Memory\Free System Page Table Entries</Counter>

<Counter>\Memory\Pages/sec</Counter>

<Counter>\Memory\Pool Nonpaged Bytes</Counter>

<Counter>\Memory\Pool Paged Bytes</Counter>

<Counter>\System\Processor Queue Length</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</Counter>

<Counter>\PhysicalDisk(*)\Disk Reads/sec</Counter>

<Counter>\PhysicalDisk(*)\Disk Writes/sec</Counter>

<Counter>\Network Interface(*)\Bytes Received/sec</Counter>

<Counter>\Network Interface(*)\Bytes Sent/sec</Counter>

<Counter>\Network Interface(*)\Bytes Total/sec</Counter>

<Counter>\Processor(_Total)\% Processor Time</Counter>

<Counter>\Processor(_Total)\% Interrupt Time</Counter>

<Counter>\Processor(_Total)\% User Time</Counter>

<Counter>\MSSQL$INSTANCE:General Statistics\User Connections</Counter>

<Counter>\MSSQL$INSTANCE:Memory Manager\Memory Grants PendINSTANCE</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Compilations/sec</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Re-Compilations/sec</Counter>

<Counter>\MSSQL$INSTANCE:Buffer Manager\Buffer cache hit ratio</Counter>

<Counter>\MSSQL$INSTANCE:Buffer Manager\Page life expectancy</Counter>

<Counter>\MSSQL$INSTANCE:Locks(_Total)\Lock Waits/sec</Counter>

<Counter>\MSSQL$INSTANCE:Locks(_Total)\Number of Deadlocks/sec</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Latch Waits/sec</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Total Latch Wait Time (ms)</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Average Latch Wait Time (ms)</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\Batch Requests/sec</Counter>

<Counter>\MSSQL$INSTANCE:Transactions\Longest Transaction RunnINSTANCE Time</Counter>

<Counter>\Process(sqlservr)\% Privileged Time</Counter>

<Counter>\Process(sqlservr)\% Processor Time</Counter>

<Counter>\Process(sqlservr)\% User Time</Counter>

<Counter>\Process(sqlservr)\Private Bytes</Counter>

<Counter>\Process(sqlservr)\Thread Count</Counter>

<Counter>\Process(sqlservr)\Virtual Bytes</Counter>

<Counter>\Process(sqlservr)\Handle Count</Counter>

</PerformanceCounterDataCollector>

<ReportFileName>report.html</ReportFileName>

<RuleTargetFileName>report.xml</RuleTargetFileName>

</EventsFileName>

</DataManager>

</DataCollectorSet>

Felesleges részek kivétele: pl Network Interface.

Tag: performancia

Performancia – tranzakciók ideje

Performance Monitor for SQL Server