May 2018 – Run SQL Server BLOG

25th May 2018

Index méretek

Index size

--  USE <databasename>
SELECT i.[name] AS IndexName
                ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                AND s.[index_id] = i.[index_id]
-- where i.[name] like 'mix%'
GROUP BY i.[name]
ORDER BY IndexSizeKB Desc, i.[name]
GO

-- USE <databasename>

SELECT i.[name] AS IndexName

,SUM(s.[used_page_count]) * 8 AS IndexSizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

AND s.[index_id] = i.[index_id]

-- where i.[name] like 'mix%'

GROUP BY i.[name]

ORDER BY IndexSizeKB Desc, i.[name]

Az egyes indexek méretei. Az adott adatbázisra kell használni, szükség esetén szűrni.

18th May 2018

Restore ellenőrzése, avagy mentés és visszaállítási információk

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

15th May 2018

SQL futási idők mérése

Mennyi idő alatt fut le a query?

  SET STATISTICS IO,TIME ON
  GO

  -- lekérdezési rész:

  -- kikapcsolása:
  GO
  SET STATISTICS IO,TIME OFF
  GO

SET STATISTICS IO,TIME ON

-- lekérdezési rész:

-- kikapcsolása:

SET STATISTICS IO,TIME OFF

Magyarázat:

SET STATISTICS TIME ON az adott session-re vonatkozik, tehát SSMS-ben a nyitott kapcsolatra.
A végrehajtási idő nem csak a szerver hw erőforrásainak , hanem az éppen aktuális leterheltségnek is függvénye, ezért SQL optimalizációkor nem ezt használjuk, hanem a SET STATISTICS IO ON -t, mivel ez utóbbi megmonja, hogy a query optimizernek hány logikai “page” olvasásra van szüksége a végrehajtás során.

4th May 2018

Restore – SQL Server Maintenance Solution

Amennyiben szkriptből állítjuk be a mentéseket – mert hát milyen jó, hogy nem összekattingatjuk és mindenütt másképp néz ki -, akkor a dba-k többsége az Ola Hallengren szkriptet használja. SQL Server Maintenance Solution
https://ola.hallengren.com/

Visszaállítani az adatbázisokat már macerásabb, alkönyvtárak tömege..
Megoldás a Restore-DbaDatabase powershell szkript futtatása.

Működő példa:

Restore-DbaDatabase -SqlServer STSQLBCK05\SQL2017 -path \\stsqlbck05\RESTORE\ -MaintenanceSolutionBackup -DestinationDataDirectory D:\SQLDATA\MSSQL14.SQL2017\MSSQL\DATA -WithReplace

1	Restore-DbaDatabase -SqlServer STSQLBCK05\SQL2017 -path \\stsqlbck05\RESTORE\ -MaintenanceSolutionBackup -DestinationDataDirectory D:\SQLDATA\MSSQL14.SQL2017\MSSQL\DATA -WithReplace

A ps szkritp letölthető: https://dbatools.io/

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