/*

Dzienniki serwera SQL

*/
EXEC sp_readerrorlog 6;

EXEC sp_readerrorlog 1, 1, '2005';

RAISERROR ('Unable to update %s.', 14, 1, 'Customer') WITH LOG;

EXEC sp_addmessage 50001, 16, 'Unable to update %s';

RAISERROR (50001, 14, 1, 'Customer') WITH LOG;

/*

Narzędzie SQLdiag 

*/
/* -- opis składni narzędzia sqldiag
sqldiag 
   { [/?] }
   |
   { [/I configuration_file]
    [/O output_folder_path]
    [/P support_folder_path]
    [/N output_folder_management_option]
       [/M machine1 [ machine2 machineN] | @machinelistfile]
    [/C file_compression_type]
       [/B [+]start_time]
    [/E [+]stop_time]
    [/A SQLdiag_application_name]
    [/T { tcp [ ,port ] | np | lpc | via } ]
    [/Q] [/G] [/R] [/U] [/L] [/X] }
   |
   { [START | STOP | STOP_ABORT] }
   |
   { [START | STOP | STOP_ABORT] /A SQLdiag_application_name }

*/


/*

Konfiguracja zarządcy zasobów

*/

/* -- opis składni włączenia/wyłączenia zarządcy zasobów
ALTER RESOURCE GOVERNOR 
{ DISABLE | RECONFIGURE }
|
  WITH ( CLASSIFIER_FUNCTION = { schema_name.function_name | NULL } )
|
 RESET STATISTICS
[ ; ]

*/

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


ALTER RESOURCE GOVERNOR DISABLE;

ALTER RESOURCE GOVERNOR RESET STATISTICS;

/*

Definiowanie puli zasobów

*/

/* -- opis składni tworzenia puli zasobów

CREATE RESOURCE POOL pool_name
[ WITH
   ( [ MIN_CPU_PERCENT = value ]
  [ [ , ] MAX_CPU_PERCENT = value ]
  [ [ , ] MIN_MEMORY_PERCENT = value ]
  [ [ , ] MAX_MEMORY_PERCENT = value ] )
]
[;]
*/

CREATE RESOURCE POOL pulaTestowa1;


ALTER RESOURCE POOL pulaTestowa1
WITH ( MAX_CPU_PERCENT = 50,MIN_CPU_PERCENT = 10);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


ALTER RESOURCE POOL 'default'
WITH ( MAX_CPU_PERCENT = 25);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

/*

Definiowanie grup zadań

*/


/* -- opis składni tworzenia grup

CREATE WORKLOAD GROUP group_name
[ WITH
 ( [ IMPORTANCE = { LOW | MEDIUM   | HIGH } ]
   [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
   [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
   [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
   [ [ , ] MAX_DOP = value ]
   [ [ , ] GROUP_MAX_REQUESTS = value ] )
 ]
[ USING { pool_name | "default"   } ]
[ ; ]

*/
CREATE WORKLOAD GROUP grupaTestowa
 USING 'default' ;

/*

Definiowanie funkcji klasyfikującej

*/
USE master
GO
CREATE FUNCTION dbo.UdfMojaFunkcjaKlasyfikujaca()
 RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @Group as sysname = 'default'
 IF UPPER(APP_NAME()) LIKE '%MANAGEMENT STUDIO%'
         SET @Group = 'Group_High'
 IF SUSER_SNAME() = 'ReportsUser'
         SET @Group = 'Group_Low'
 IF SUSER_SNAME() = 'sa'
         SET @Group = 'Group_Admin'
 RETURN @Group
END;


ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.UdfMojaFunkcjaKlasyfikujaca);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

/*

Monitorowanie pracy zarządcy zasobów

*/

SELECT 
         [object_name]n as [nazwa komponentu]
         , counter_name as [nazwa licznika] 
         ,instance_name as [instancja]
         ,cntr_value as [wartosc]
FROM sys.dm_os_performance_counters 
WHERE [object_name] IN ('SQLServer:Workload Group Stats','SQLServer:Resource Pool Stats');


/*

Architektura  rozszerzonych zdarzeń

*/

SELECT map_value 
FROM sys.dm_xe_map_values
WHERE name = 'keyword_map'
ORDER BY map_value;


SELECT map_key,map_value
FROM sys.dm_xe_map_values
WHERE [name]= 'wait_types';


/*

Stan sesji rozszerzonych zdarzeń

*/
/* -- opis składni tworzenia sesji rozszerzonych zdarzeń

CREATE EVENT SESSION event_session_name
ON SERVER
{
<event_definition> [ ,...n]
[ <event_target_definition> [ ,...n] ]
[ WITH ( <event_session_options> [ ,...n] ) ]
}
[;]

*/


/*

Dynamiczne widoki wspierające mechanizm rozszerzonych zdarzeń

*/

SELECT name
,object_type
,package_guid
,description
FROM sys.dm_xe_objects
WHERE object_type='event ';


SELECT PCK.name as [nazwa paczki] 
,OC.name as [nazwa kolumny]
,OC.column_id as [id kolumny]
,OC.type_name as [typ danych kolumny]
,OC.column_type as [typ kolumny]
,OC.column_value as [wartosc]
,OC.description as [opis kolumny]
FROM sys.dm_xe_object_columns OC
JOIN sys.dm_xe_packages PCK
ON OC.object_package_guid = PCK.guid
WHERE OC.[object_name] = 'lock_acquired';


SELECT name
, guid
, description
, module_guid
, module_address	
FROM sys.dm_xe_packages



/*

Przykład praktycznego zastosowania 

*/
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’Sesja_LocksAcquired’)
DROP EVENT SESSION Sesja_LocksAcquired ON SERVER;


DECLARE @DB int = DB_ID('AdventureWorks');
DECLARE @session varchar(max);
SET @session = 'CREATE EVENT SESSION Sesja_LocksAcquired ON SERVER
ADD EVENT sqlserver.lock_acquired(WHERE sqlserver.database_id = '+CAST(@DB as varchar(2))+ ')
ADD TARGET package0.asynchronous_bucketizer ( 
SET filtering_event_name="sqlserver.lock_acquired"
, source_type=0
, source="mode") ';
EXEC(@session);


SELECT P.name as [nazwa paczki]
,O.name as [nazwa zdarzenia]
,O.description as [opis zdarzenia]
FROM sys.dm_xe_objects O JOIN sys.dm_xe_packages P
ON O.package_guid=P.guid 
WHERE O.object_type='event'
ORDER BY P.name,O.name;


SELECT P.name as [nazwa paczki]
,O.name as [nazwa odbiorcy]
,O.description as [opis dbiorcy]
FROM sys.dm_xe_objects O JOIN sys.dm_xe_packages P
ON O.package_guid=P.guid 
WHERE O.object_type='target'
ORDER BY P.name,O.name;


SELECT name,column_type,column_value
FROM sys.dm_xe_object_columns
WHERE object_name ='lock_acquired';


SELECT name,column_type,column_value,capabilities_desc
FROM sys.dm_xe_object_columns
WHERE object_name ='asynchronous_bucketize';


ALTER EVENT SESSION Sesja_LocksAcquired ON SERVER 
STATE=START;

-- 1. Hierarchia pracowników oraz lista kontaktów
WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) 
AS (
	SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0
	FROM [HumanResources].[Employee] e 
	INNER JOIN [Person].[Contact] c 
	ON e.[ContactID] = c.[ContactID]
	WHERE [ManagerID] = 109 -- CEO
	UNION ALL
	SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1
	FROM [HumanResources].[Employee] e 
	INNER JOIN [EMP_cte]
	ON e.[ManagerID] = [EMP_cte].[EmployeeID]
	INNER JOIN [Person].[Contact] c 
	ON e.[ContactID] = c.[ContactID]
  )
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS ‘Imie’, c.[LastName] AS ‘Nazwisko’,
[EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName]
FROM [EMP_cte] 
INNER JOIN [HumanResources].[Employee] e WITH(UPDLOCK)
ON [EMP_cte].[ManagerID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c 
ON e.[ContactID] = c.[ContactID]
ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
GO

-- 2. Aktualizacja wiersza dla CEO – szefa firmy
UPDATE HumanResources.Employee
SET Title = 'Mrs'
WHERE EmployeeID='109';


SELECT 
map_value as lock_description
, [ilosc_blokad] 
FROM
 (
		SELECT C.value(' (./value)[1] ', 'int') as [id_blokady]
		,C.value('@count', 'int') as [ilosc_blokad]
		FROM (
				SELECT CAST(xest.target_data AS XML).query('//Slot') LockData
				FROM sys.dm_xe_session_targets xest
				JOIN sys.dm_xe_sessions xes 
				ON xes.address = xest.event_session_address
				JOIN sys.server_event_sessions ses ON xes.name = ses.name
				WHERE xest.target_name = 'asynchronous_bucketizer' 
				and xes.name = 'Sesja_LocksAcquired'
		) Locks
		CROSS APPLY LockData.nodes('/Slot') as T(c)
) LockedObjects 
INNER JOIN sys.dm_xe_map_values M
ON LockedObjects.[id_blokady] = M.map_key
WHERE M.name='lock_mode'
ORDER by [ilosc_blokad] DESC;


ALTER EVENT SESSION Sesja_LocksAcquired ON SERVER 
STATE=STOP;

INNER JOIN [HumanResources].[Employee] e WITH(UPDLOCK)
INNER JOIN [HumanResources].[Employee] e 