Użycie CPU przez bazy danych SQL


USE master
GO

-- ===========================================================
-- Author: Eli Leiba
-- Create date: 19-09-2019
-- Description: Get the CPU usage percentage for the given database.
-- Result should be a decimal between 0 and 100
-- ===========================================================
CREATE FUNCTION dbo.udf_Get_DB_Cpu_Pct (@dbName sysname)
RETURNS decimal (6, 3)AS
BEGIN

DECLARE @pct decimal (6, 3) = 0

SELECT @pct = T.[CPUTimeAsPercentage]
FROM
(SELECT
[Database],
CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 /
SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]
FROM
(SELECT
dm_execplanattr.DatabaseID,
DB_Name(dm_execplanattr.DatabaseID) AS [Database],
SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds
FROM sys.dm_exec_query_stats dm_execquerystats
CROSS APPLY
(SELECT
CONVERT (INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)
WHERE attribute = N'dbid'
) dm_execplanattr
GROUP BY dm_execplanattr.DatabaseID
) AS CPUPerDb
) AS T
WHERE T.[Database] = @dbName

RETURN @pct
END
GO

Commands completed successfully.

Obciążenie CPU dla pojedynczej bazy


USE master
GO
SELECT dbo.udf_Get_DB_Cpu_Pct ('AdventureWorks2012') as usagepct
GO

usagepct
---------------------------------------
8.909

(1 row affected)

Procentowo dla wszystkich baz w instancji

USE master
GO
SELECT d.name,dbo.udf_Get_DB_Cpu_Pct (d.name) as usagepct
FROM sysdatabases d
ORDER BY usagepct desc
GO

name			usagepct
-------------------------------------------
master			79.462
AdventureWorksLT2012	10.666
AdventureWorks2012	7.886
msdb			0.076
SSISDB			0.000
tempdb			0.000
model			0.000
Northwind		0.000
TSQL2012		0.000
Nauka_CSharp		0.000
AdventureWorksDW2012	0.000
Nauka_SSIS		0.000
test			0.000
BULKDB			0.000

(14 rows affected)

I inne podobne zapytanie

WITH DB_CPU_Stats
AS
(
SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (
SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName,
[CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

row_num              DatabaseName		CPU_Time_Ms		CPUPercent
-------------------- -------------------------------------------------------------
1                    master			230357			87.47
2                    AdventureWorksLT2012	18869			7.17
3                    AdventureWorks2012		13480			5.12
4                    msdb			643			0.24

(4 rows affected)

I jeszcze jedno


DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

database		system cpu	database cpu	%
---------------------------------------------------------------------
master			968		624		64.5
AdventureWorksLT2012	968		220		22.7
msdb			968		124		12.8

(3 rows affected)

Źródło : https://www.mssqltips.com/sqlservertip/6195/sql-server-function-to-measure-cpu-usage-per-database/

Zaszufladkowano do kategorii Bez kategorii | Dodaj komentarz

Sumulacja obciążenia CPU na serwerze SQL

Czasami dla testów potrzebujemy wykonać wymuszone obciążenie procesora. Poniżej przedstawię prosty sposób aby nasze wykresy obciążenia CPU powędrowały pod sufit.

Na początek tworzymy procedurę składowaną dbo._keep_it_100 np. w bazie master

CREATE OR ALTER PROCEDURE dbo._keep_it_100
AS
BEGIN

WITH e1(n) AS
(
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
),
e2(n) AS (SELECT TOP 2147483647 NEWID() FROM e1 a, e1 b, e1 c, e1 d, e1 e, e1 f, e1 g, e1 h, e1 i, e1 j)
SELECT MAX(ca.n)
FROM e2
CROSS APPLY
(
SELECT TOP 2147483647 *
FROM (
SELECT TOP 2147483647 *
FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
UNION ALL SELECT * FROM e2
) AS x
WHERE x.n = e2.n
ORDER BY x.n
) AS ca
OPTION(MAXDOP 0, LOOP JOIN, QUERYTRACEON 8649);

END;

Commands completed successfully.

Następnie uruchamiamy procedurę

exec dbo._keep_it_100

Obciążenie procesora wzrasta do 100% do czasu kiedy nie zatrzymamy działania naszej procedury.

W naszym serwerze posiadamy 4 CPU i wszystkie cztery są obciążone na 100%. Możemy zmienić konfigurację i do pracy „zatrudnić” tylko dwa z nich. Możemy to zmienić w właściwościach instancji SQL:

lub wydając polecenie

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 1,3
GO

Jeśli wydamy to w trakcie działania naszej procedury dwa procesory zostaną wyłączone z SQL-a.

Na koniec zatrzymujemy naszą procedurę i ją usuwamy oraz zmieniamy opcję procesorów na AUTO.

USE [master]
GO
DROP PROCEDURE [dbo].[_keep_it_100]
GO
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
GO

Commands completed successfully.

Poniżej przestawiam jeszcze inne możliwe zapytania obciążające na 100% CPU.


USE master

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id JOIN sys.objects o3 ON o1.object_id < o3.object_id SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt)) FROM #temp o1 JOIN #temp o2 ON o1.MyInt < o2.MyInt DROP TABLE #temp

Obciążenie CPU na czas 30 sekund


DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,30,@T)>GETDATE()
SET @F=POWER(2,30);

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Lista tabel w bazie danych

Poniższy skrypt zwraca listę tabel w bazie danych posortowanych od największej (zawierającej najwięcej wierszy).


SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages)* 8 AS TotalSpaceKB,
SUM(a.used_pages)* 8 AS UsedSpaceKB,
(SUM(a.total_pages)-SUM(a.used_pages))* 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id= a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
p.rows desc

TableName			RowCounts            TotalSpaceKB         UsedSpaceKB          UnusedSpaceKB
----------------------------------------------------------------------------------------------------------------------
SalesOrderDetail		121317               15816                15776                40
TransactionHistory		113443               10128                10016                112
TransactionHistoryArchive	89253                7904                 7840                 64
WorkOrder			72591                6168                 6104                 64
WorkOrderRouting		67131                6720                 6712                 8
SalesOrderHeader		31465                8232                 8168                 64
SalesOrderHeaderSalesReason	7647                720                  704                  16
BusinessEntity			20777                1424                 1336                 88
EmailAddress			19972                3600                 3496                 104
Password			19972                1928                 1896                 32
Person				19972                32032                32016                16
PersonPhone			19972                2256                 2176                 80
Customer			19820                2464                 2296                 168

(13 rows affected)

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Backup bazy z datą wykonania

Po niżej skrypt do wykonania backupu bazy z nazwą bazy oraz datą wykonania w nazwie pliku backupu.
Aby wykonać backup należy wybrać w górnym menu bazę danych a następnie uruchomić skrypt.

SET NOCOUNT ON;

DECLARE @MyBackupFile varchar(100)
DECLARE @DBName varchar(20)
DECLARE @DataBak varchar(10)

SELECT @DBName = (Select DB_NAME())
SELECT @DataBak = (convert(varchar(500),getdate(),112))
SELECT @MyBackupFile = (SELECT 'C:\SQLBackup\'+@DBName+'_'+@DataBak+'_CopyOnly'+'.bak');

BACKUP DATABASE @DBName TO DISK=@MyBackupFile WITH COPY_ONLY, NOFORMAT, INIT, NAME = 'Backup CopyOnly', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 27136 pages for database 'BULKDB', file 'BULKDB' on file 1.
100 percent processed.
Processed 2 pages for database 'BULKDB', file 'BULKDB_log' on file 1.
BACKUP DATABASE successfully processed 27138 pages in 1.606 seconds (132.012 MB/sec).

Completion time: 2021-03-20T21:05:39.9796616+01:00

Zostanie utworzony plik backupu o nazwie : BULKDB_20210320_CopyOnly.bak

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Lista nieużywanych baz SQL

Poniższe polecenie pokazuje listę baz które zostały użyte (poprzez wykonanie zapytania, update itd) od ostatniego uruchomienie bazy danych (restartu).

Najpierw sprawdzimy od kiedy działa nasza instancja

SET NOCOUNT ON;
SELECT
@@SERVERNAME AS [instance_name],
[si].[sqlserver_start_time],
CONVERT(varchar, DATEDIFF(SECOND, [si].[sqlserver_start_time], GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, [si].[sqlserver_start_time], GETDATE()) % 86400, '19000101'), 108) AS [up_time (d.hh.mm.ss)]
FROM sys.dm_os_sys_info AS [si];

instance_name		sqlserver_start_time		up_time (d.hh.mm.ss)
------------------------------------------------------------------------------
MICEK1968		2021-03-15 08:58:05.930 	5.11:45:00

Completion time: 2021-03-20T20:47:06.7053501+01:00

A teraz sprawdzamy użycie baz od ostatniego startu serwisu MSSQLSERVER


SELECT
[db].[name] AS [database_name],
MIN(lu.[last_usage_time]) AS [last_usage_time],
MIN([lu].[ago]) AS [ago (d.hh.mm.ss)],
CASE
WHEN [agdb].[is_primary_replica] IS NULL THEN N'N/A'
WHEN [agdb].[is_primary_replica] = 1 THEN [aggroup].[name] + N' - Primary Replica'
WHEN [agdb].[is_primary_replica] <> 1 THEN [aggroup].[name] + N'- Secondary Replica'
END AS [always_on_ag],
CASE
WHEN [agdb].[is_primary_replica] IS NULL AND MIN(lu.[last_usage_time]) IS NULL THEN N'This database was not used by users since SQL Server service started'
WHEN [agdb].[is_primary_replica] <> 1 THEN N'Take a look at Primary Replica to find out last usage time'
END AS [description]
FROM
sys.databases AS [db]
LEFT JOIN (
SELECT
[database_id],
MAX([last_user_seek]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_seek]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_seek]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_scan]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_scan]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_scan]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_lookup]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_lookup]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_lookup]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_update]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_update]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_update]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]
) AS [lu]
ON [db].[database_id] = [lu].[database_id]
LEFT JOIN sys.dm_hadr_database_replica_states AS [agdb] ON [db].[database_id] = [agdb].[database_id] AND [agdb].[is_local] = 1
LEFT JOIN sys.availability_groups [aggroup] ON [agdb].[group_id] = [aggroup].[group_id]

WHERE [db].[name] NOT IN (N'master', N'model', N'msdb', N'tempdb', N'distribution')

GROUP BY [db].[name], [agdb].[is_primary_replica], [aggroup].[name]

ORDER BY [db].[name];

database_name			last_usage_time         	ago (d.hh.mm.ss)	always_on_ag	description
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AdventureWorks2012		2021-03-20 20:38:42.080 	0.00:07:48		N/A		NULL
AdventureWorksDW2012		NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
AdventureWorksLT2012		NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
BULKDB				2021-03-20 20:38:57.700 	0.00:07:33		N/A		NULL
Nauka_CSharp			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Nauka_SSIS			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Northwind			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
SSISDB				2021-03-20 17:44:29.540 	0.00:09:13		N/A		NULL
TSQL2012			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Warning: Null value is eliminated by an aggregate or other SET operation.


Completion time: 2021-03-20T20:46:30.9598673+01:00
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Lista backupów bazy MsSQL

Poniższe zapytanie wyświetla wykonane w ostatnim czasie backupy baz naszej instancji

SELECT
substring(CONVERT(CHAR(100), 'TEST-Server'),1,20) AS Server,
substring(msdb.dbo.backupset.database_name,1,25) as datatabse_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS
backup_type,
msdb.dbo.backupset.backup_size,
substring(msdb.dbo.backupmediafamily.logical_device_name,1,20) as logical_dev_name,
substring(msdb.dbo.backupmediafamily.physical_device_name,1,40) as physical_dev_name,
substring(msdb.dbo.backupset.name,1,20) AS backupset_name,
substring(msdb.dbo.backupset.description,1,20) as description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date,
102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.backup_finish_date desc

Server               datatabse_name            backup_start_date       backup_finish_date      expiration_date         backup_type backup_size                             logical_dev_name     physical_dev_name                        backupset_name       description
-------------------- ------------------------- ----------------------- ----------------------- ----------------------- ----------- --------------------------------------- -------------------- ---------------------------------------- -------------------- --------------------
TEST-Server          ZWR                       2017-11-15 20:24:22.000 2017-11-15 20:26:04.000 NULL                    Database    5582769152                              NULL                 VNBU0-14676-276-1510773845               NULL                 NULL
TEST-Server          TELP                      2017-11-15 20:23:59.000 2017-11-15 20:24:00.000 NULL                    Database    15148032                                NULL                 VNBU0-14676-18152-1510773822             NULL                 NULL
TEST-Server          SCRE                      2017-11-15 20:22:34.000 2017-11-15 20:23:37.000 NULL                    Database    3311795200                              NULL                 VNBU0-14676-15376-1510773738             NULL                 NULL
...
TEST-Server          AVA_PTK                   2017-11-09 18:06:16.000 2017-11-09 18:06:17.000 NULL                    Database    51852288                                NULL                 VNBU0-13596-9328-1510247160              NULL                 NULL
TEST-Server          AVA_DPD                   2017-11-09 18:05:35.000 2017-11-09 18:05:55.000 NULL                    Database    342338560                               NULL                 VNBU0-13596-5340-1510247119              NULL                 NULL
TEST-Server          AVA                       2017-11-09 18:00:42.000 2017-11-09 18:05:13.000 NULL                    Database    8517797888                              NULL                 VNBU0-13596-428-1510246826               NULL                 NULL

(210 row(s) affected)

Jeśli byśmy chcieli sprawdzić kiedy był wykonany ostatni backup pełny, różnicowy lub backup logów uruchamiamy zapytanie:

use msdb
go
-- D = Full, I = Differential and L = Log.
SELECT bks.database_name ,
adb.recovery_model_desc,
MAX(CASE WHEN bks.type = 'D' THEN bks.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN bks.type = 'I' THEN bks.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN bks.type = 'L' THEN bks.backup_finish_date ELSE NULL END) AS LastLog
FROM backupset as bks
JOIN sys.databases as adb ON bks.database_name = adb.name
GROUP BY bks.database_name, adb.recovery_model_desc
ORDER BY bks.database_name ASC

database_name			recovery_model		LastFullBackup          LastDifferential        LastLog
-----------------------------------------------------------------------------------------------------------
AdventureWorks2012		SIMPLE			2020-11-25 20:40:46.000 	NULL		NULL
AdventureWorksDW2012		SIMPLE			2020-11-25 20:40:35.000 	NULL		NULL
AdventureWorksLT2012		SIMPLE			2020-11-25 20:40:10.000 	NULL		NULL
Database1			SIMPLE			2020-12-07 17:01:38.000 	NULL		NULL
model				SIMPLE			2020-01-19 14:38:07.000 	NULL		NULL
Nauka_CSharp			FULL			2020-12-03 08:34:46.000 	NULL		2020-12-01 20:16:39.000
Nauka_SSIS			FULL			2020-12-03 08:34:56.000 	NULL		2020-12-03 08:33:31.000
Northwind			FULL			2020-11-22 13:14:14.000 	NULL		2019-02-07 23:13:49.000
SSISDB				SIMPLE			2020-11-22 13:11:53.000 	NULL		NULL
TSQL2012			SIMPLE			2020-11-25 20:39:57.000 	NULL		NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(10 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Otagowano | Dodaj komentarz

Zdublowane wiersze w tabeli

Czasami się zdarza że w tabeli jest sporo takich samych wiersz które niepotrzebnie zaśmiecają tabelę, a czasami powodują „błędy” podczas odczytu danych. Dziś w pracy się spotkałem z taką tabelą gdzie przez pomyłkę został uruchomiony skrypt z insertami do tabeli wrzucający kilkaset wierszy. Dziś pokażę jak w prosty sposób ich się pozbyć pozostawiając unikalne niezdublowane wiersze.

Na początek stworzymy sobie prostą tabelę zawierającą trzy kolumny.

USE [Database1]
GO
CREATE TABLE [dbo].[duplikaty](
[randomID] [int] NULL,
[nazwa] [nchar](10) NULL,
[region] [nchar](10) NULL
) ON [PRIMARY];
GO

W pierwszej kolumnie będzie wpisana losowa liczba w zakresie 0-10. W drugiej i trzeciej kolumnie będzie string połączony w losową liczbą od 1 do 10. A więc napełniamy tabelę 10.000 wierszami

DECLARE @intCounter as INT = 1;
DECLARE @naz1 as INT = 1;
DECLARE @reg1 as INT = 1;
WHILE @intCounter <= 10000 BEGIN SET @naz1 = ROUND(rand()*10,0); SET @reg1 = ROUND(rand()*10,0); --PRINT 'Bieżąca wartość licznika to: '+ CAST(@intCounter AS VARCHAR); INSERT INTO [dbo].[duplikaty]([randomID],[nazwa],[region]) VALUES (ROUND(rand()*10,0),'nazwa'+CAST(@naz1 as nvarchar(10)),'region'+CAST(@reg1 as nvarchar(10))) SET @IntCounter = @IntCounter + 1; END;

Sprawdzamy czy dane się zapisały

select * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(10000 rows affected)

W tabeli jest 10.000 wierszy ale z pewnością wiele wierszy się powtarza. Sprawdzamy ile jest unikalnych wierszy

select DISTINCT * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(1316 rows affected)

Tak więc czeka nas usunięcie ponad 8.684 wierszy

Możemy jeszcze sobie sprawdzić ile jest wierszy dla każdego randomID

SELECT randomID, COUNT(*) as count
FROM [Database1].[dbo].[duplikaty]
GROUP BY randomID
order by randomID;

randomID    count
----------- -----------
0           488
1           993
2           1024
3           949
4           982
5           1028
6           984
7           1055
8           1004
9           1030
10          463

(11 rows affected)

Poniższe zapytanie przypisuje każdemu wierszowi kolejny numer. W przypadku zdublowanych wierszy będą to kolejne liczby np

10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3 

SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty];

randomID    nazwa      region     DuplicateCount
----------- ---------- ---------- --------------------
0           nazwa0     region0    1
0           nazwa0     region1    1
0           nazwa0     region1    2
0           nazwa0     region10   1
0           nazwa0     region10   2
0           nazwa0     region2    1
...
10          nazwa9     region8    3
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3

(10000 rows affected)

naszym zadaniem będzie teraz usunięcie wszystkich wierszy z DuplicateCount większym niż 1. Przed uruchomieniem DELETE najpierw sprawdzamy co nam wykona polecenie (dobra praktyka!)

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
SELECT * FROM CTE WHERE DuplicateCount > 1;

randomID    nazwa      region     duplicatecount
----------- ---------- ---------- --------------------
0           nazwa0     region1    2
0           nazwa0     region10   2
0           nazwa0     region2    2
0           nazwa0     region2    3
0           nazwa0     region3    2
...
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    2
10          nazwa9     region9    3

(8684 rows affected)

Select wzrócił nam 8683 wiersze do usunięcia. Zamieniamy SELECT na DELETE i uruchamiamy polecenia

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
DELETE FROM CTE WHERE DuplicateCount > 1;

(8684 rows affected)

Sprawdzamy ile wierszy zostało w tabeli:

select COUNT(*) as [REKORDY] from [Database1].[dbo].[duplikaty];

REKORDY
-----------
1316

(1 row affected)

Sprawdzamy czy nie ma dwóch takich samych wierszy (powinno zwrócić tyle samo co powyższe zapytanie):

select DISTINCT * from [Database1].[dbo].[duplikaty];

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region0   
0           nazwa0     region1   
0           nazwa0     region10  
0           nazwa0     region2   
0           nazwa0     region3
...
10          nazwa9     region6   
10          nazwa9     region7   
10          nazwa9     region8   
10          nazwa9     region9   

(1316 rows affected)

Czyszczenie tabeli zostało zakończone poprawnie. Oczywiście można to samo zrobić na kilka innych sposobów m.in. wykorzystując wspomniane wcześniej narzędzia Integration Services (SSIS).

Nie będę tu już opisywał jak stworzyć za pomocą Visual Studio taki projekt. Możecie to znaleźć na wcześniejszych wpisach na mojej stronie.
Do naszego projektu wrzucamy DataFlowTask.

W środku tworzymy OLEDBSource

Konfigurujemy połączenie do naszej bazy i tabeli ze zdublowanymi wierszami

W projekcie do OLEDBSource (który powinien już nie "świecić" się na czerwono) podłączamy element SORT (on się jeszcze świeci na czerwono bo go jeszcze nie skonfigurowaliśmy).

W konfiguracji SORT'a na dole ekranu zaznaczamy opcję "Remove rows with duplicate sort values"

Podłączamy element Delivered Column

A na ich połączeniu wstawiamy "Data Viewer" który pozwoli nam na podglądnięcie przesyłanych danych.

Uruchamiamy nasz projekt

Pojawia się tabelka z 1316 unikalnymi wierszami naszej tabeli

Taką samą ilość wierszy widzimy na schemacie przepływu w naszym projekcie.

Zaszufladkowano do kategorii Microsoft SQL, SSIS | Dodaj komentarz

Prosty ( i szybki) sposób na sprawdzenia połączenia z bazą MSSQL

Nie zawsze mamy zainstalowanego Management Studio by sprawdzić czy możemy się podłączyć do bazy. Jest na to prosty sposób.
Na komputerze z którego chcemy przetestować nasze połączenie z bazą tworzymy pusty plik tekstowy (może być na pulpicie :))

Następnie zmieniamy nu rozszerzenie pliku z TXT na UDL. System spyta się nas czy na pewno tego chcemy 😉

Plik UDL to nic innego niż plik konfiguracyjny połączenia do bazy za pomocą OLE DB, który wskazuje na zewnętrzną tabelę bazy danych.

Mamy gotowy plik w którego klikamy dwa razy myszką. Otwiera się nam okienko w które wpisujemy nazwę naszego serwer. Wybieramy też sposób autentykacji.

Jeżeli możemy wybrać z listy bazę danych to już świadczy o tym że połączenie się udało.

Mozemy też kliknąć na przycisk „Testuj połączenie”

Jak widać wszystko jest OK i połączenie pomiędzy naszym komputerem a bazą działa prawidłowo.

Jakbyśmy podejrzeli zawartość pliku UDL w notatniku zobaczymy, że to prosty ciąg definiujący połączenie do naszyj bazy.

 [oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Nauka_SSIS;Data Source=MICEK1968
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Import danych z MSSQL do pliku Excela

Czasami jest potrzeba zaimportowania danych z tabeli w bazie danych do pliku Excel. Można to zrobić na kilka sposobów. Wykorzystujemy do tego m.in. narzędzia Integration Services przedstawione w jednym z artykułów. Ja przedstawię dziś inny sposób polegający na wczytanie danych bezpośrednio do arkusza kalkulacyjnego. Połączenie będzie na stałe zdefiniowane co pozwoli na odczyt zawsze aktualnych danych przy każdorazowym otwarciu arkusza.

Otwieramy nowy plik Excela.

Po jego otwarciu z górnego menu przechodzimy do opcji DANE a następnie wybieramy „Z innych źródeł” a następnie „z programu SQLServer”.

Wpisujemy nazwę naszego serwera i wybieramy odpowiedni sposób uwierzytelnienia.

Na następnym ekranie wybieramy bazę i tabelę.

Zapisywanie pliku łączenia danych do pliku ODC.

Importowanie danych. Ustawiamy czy to ma być tabela, raport przestawny, wykres przestawny czy tylko mamy stworzyć plik połączenia do bazy. Wybieramy istniejący arkusz lub możemy wyeksportować dane do nowego arkusza.

Wchodząc we właściwości możemy poustawiać jeszcze parę rzeczy dotyczących naszego połączenia do bazy m.in. co ile ma być odświerzany nasz arkusz oraz czy mają być wczytane dane z bazy podczas otwierania arkusza.

Po zatwierdzeniu dane zostają wczytane do arkusza kalkulacyjnego.

Dane możemy „ręcznie” odświerzyć klikając w przycisk.

Lub otworzyć właściwości naszego połączenia.

I zaznaczyć automatyczne połączenie przy otwieraniu arkusza.

W zakładce Definicja mamy ścieżkę do plików konfiguracyjnych naszych połączeń . Dla programistów np w c# znajdziemy tu Conenction String do połączenia się z naszą bazą (okno „Parametry połączenia”)

Jesli zajrzymy do folderu w folderach naszego użytkownika znajdziemy wspomniane wcześniej pliki konfiguracyjne naszych połączeń do baz.

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

XACT_ABORT

Stwórzmy bazę TEST, a następnie tabelę TABLE_TEST składają się z dwóch kolumn : ID i nazwy oraz z kluczem publicznym na kolumnie ID.

CREATE DATABASE [TEST]

Commands completed successfully.

USE [TEST]
GO
CREATE TABLE [dbo].[TABLE_TEST](
[id] [int] NOT NULL,
[nazwa] [nchar](10) NULL,
CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Commands completed successfully.

Następnie spróbujmy wykonać polecenie:

BEGIN TRANSACTION
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'rower');
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 22
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).
The statement has been terminated.

Jako że dane w kolumnie ID muszą być unikalne, zwróciło błąd jednak pierwsza operacja INSERT została zapisana do tabeli.

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)

Jest tak ponieważ domyślnie transakcje nie jest anulowane w przypadku wystąpienia błędów w trakcie jej wykonywania, tylko jest kontynuowana. Aby temu zapobiec należy ustawić zmienną XACT_ABORT na wartość ON:

select 16384 & @@OPTIONS as '16384_@OPTIONS'

16384_@OPTIONS
--------------
0

(1 row affected)

Ustawiamy zmienną XACT_ABORT na wartość ON.

SET XACT_ABORT ON;

Commands completed successfully.

Teraz wyczyścimy naszą tabelę z danych

TRUNCATE TABLE TEST.TABLE_TEST

Commands completed successfully.

Ponownie wykonujemy nasze polecenie INSERT

BEGIN TRANSACTION
insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

Zwróciło nam błąd, ale dane nie zostały zapisane do tabeli

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------

(0 rows affected)

XACT_ABORT powoduje, że w przypadku wystąpienia błędu cała transakcja jest anulowana (nastąpi rollback).
Oczywiście działa to tylko w przypadku stosowanie opcji transakcji BEGIN/COMMIT.
Jeśli wykonamy INSERT pojedynczo pierwszy zostanie zapisany do tabeli

insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz