Run SQL Server BLOG – Page 7 – Just another Select of Ms SQL DBA site

4th May 20184th May 2018

Performancia – tranzakciók ideje

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.

4th May 2018

Első azure DB

Meglehetősen szkeptikus voltam az első Azure DB létrehozásával kapcsolatban. Meglehetősen túlzsúfolt felületen megcsinálni, megtalálni az adatbázis létrehozásához szükséges dolgokat.
1. lépés SQL ikon megtalálása, majd bekért adatok kitöltése: db szerver név, kapcsolódási user és jelszó, minta adatbázis létrehozása
2. rákattintva látszik, hogy kell neki hálózat, hajrá fogadjuk el amit felajánl
3. minden kész, most már csak a földi gépről is el kellene érni, tűzfal szabály létrehozása, megnézem , hogy mi az IP-m az internet felől és kivétellistába adom
4. hüm, azt írja minden kész, akkor most? Nosza vegyük elő az SSMS eszközt és írjuk be az általa generált címet és SQL-es authentikációt részt is töltsük ki.
Az eredmény:

4th May 2018

Szkript futási ideje utólagosan

Mennyi ideig futott a szkript?

set transaction isolation level read uncommitted

select 
    top 100
    creation_time,
    last_execution_time,
    execution_count,
    qs.total_elapsed_time/1000 as TotDuration,
    convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
    case 
        when sql_handle IS NULL then ' '
        else(substring(st.text,(qs.statement_start_offset+2)/2,(
            case
                when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                else qs.statement_end_offset    
            end - qs.statement_start_offset)/2  ))
    end as query_text
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by AvgDur desc

set transaction isolation level read uncommitted

select

top 100

creation_time,

last_execution_time,

execution_count,

qs.total_elapsed_time/1000 as TotDuration,

convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],

case

when sql_handle IS NULL then ' '

else(substring(st.text,(qs.statement_start_offset+2)/2,(

case

when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2

else qs.statement_end_offset

end - qs.statement_start_offset)/2 ))

end as query_text

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

order by AvgDur desc

forrás:
https://dba.stackexchange.com/questions/205649/how-to-find-the-time-taken-to-execute-a-script

2nd May 2018

Tesztelted a mentésed? SQL restore -ok időpontjai

Revovery állapotba hozott adatbázisok listája:

Mentés időpontjával, mentett szerver nevével , adatbázis névvel, agent , dátum és méret listázással:

SELECT  
rs.recovery,
bs.server_name as source_server,
@@SERVERNAME as destination_server, 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[rs].[restore_date], 
rs.stop_at,
rs.user_name,
bs.user_name as backup_agent,
 bmf.physical_device_name, bs.backup_size
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
where rs.recovery=1
ORDER BY rs.recovery DESC, [rs].[restore_date] DESC

SELECT

rs.recovery,

bs.server_name as source_server,

@@SERVERNAME as destination_server,

[bs].[backup_start_date],

[bs].[backup_finish_date],

[bs].[database_name] as [source_database_name],

[rs].[restore_date],

rs.stop_at,

rs.user_name,

bs.user_name as backup_agent,

bmf.physical_device_name, bs.backup_size

FROM msdb..restorehistory rs

INNER JOIN msdb..backupset bs

ON [rs].[backup_set_id] = [bs].[backup_set_id]

INNER JOIN msdb..backupmediafamily bmf

ON [bs].[media_set_id] = [bmf].[media_set_id]

where rs.recovery=1

ORDER BY rs.recovery DESC, [rs].[restore_date] DESC

30th April 2018

Szabad tárhely az SQL szerveren

Tárolt eljárás, amely ütemezhető rendszeres ellenőrzésre:
Continue reading “Szabad tárhely az SQL szerveren”

24th April 201824th April 2018

Powershell 6 telepítése

Powershell telepítése:

Első lépésként telepítsük a .Net Framework-ot.
Jelenleg a legutolsó verzió 4.7.1

Majd töltsük le a nyílt forráskódú telepítőt: https://github.com/PowerShell/Powershell

Windows verzió: PowerShell-6.0.2-win-x64.msi

Powershell-t akár linux-ra és macOS-re is telepíthetünk a 6-os verziótól!

powershell 6 command line — powershell 6

22nd April 201822nd April 2018

Microsoft SQL Server Notes for Professionals

Sql server tippek gyűjteménye haladóknak.

Letöltés: Microsoft SQL Server Notes For Professionals
Ingyenes digitális könyv és szabadon terjeszthető. 🙂

17th April 201817th April 2018

AOAG Synchron Commit time

Átlagos tranzakció végrehajtási ideje:

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait'
from sys.dm_os_wait_stats where waiting_tasks_count >0
 and wait_type = 'HADR_SYNC_COMMIT'

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait'

from sys.dm_os_wait_stats where waiting_tasks_count >0

and wait_type = 'HADR_SYNC_COMMIT'

SQL szerver válasz ideje ms-ban:

wait_type	waiting_tasks_count	wait_time_ms	time_per_wait
HADR_SYNC_COMMIT	10809645	135666120	12

17th April 201825th December 2019

SQL Database downgrade internal version 869 and 852 and Compatibility Level

The database ‘database_name’ cannot be opened because it is version 869. This server supports version 852 and earlier. A downgrade path is not supported.

The internal database versions for SQL Server aren’t clearly documented in the Microsoft documentation:

SQL Server Version	Internal Database Version	Database Compatibility Level
SQL Server 2017	904	150
SQL Server 2017	869	140
SQL Server 2016	852	130
SQL Server 2014	782	120
SQL Server 2012	706	110
SQL Server 2012 CTP1 (a.k.a. SQL Server 2011 Denali)	684	110
SQL Server 2008 R2	660 / 661	100
SQL Server 2008	655	100
SQL Server 2005 SP2+ with VarDecimal enabled	612	90
SQL Server 2005	611	90
SQL Server 2000	539	80
SQL Server 7.0	515	70
SQL Server 6.5	408	65

17th April 2018

Performance Monitor for SQL Server

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.