W tym miejscu będę aktualizował zbiór artykułów dostępnych w necie, których znajomość pomogła mi znacznie w zrozumieniu/zapoznaniu się z wiedzą dotyczącą SQL Server. GENERAL Biblioteka wielu przydatnych linków do stron, blogów, whitepapers itp. [akt. #2 (12.10.2017)] Jeszcze jedna biblioteka, tym razem utrzymywana na githubie, pełna narzędzi, skryptów, kodu i słodyczy!Czytaj dalej / Read more
Kategoria: Przydatne skrypty
Dział z mniejszymi/większymi skryptami przydatnymi w codziennej pracy z SQL Server
SPID query plan
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
SELECT EQP.query_plan, ER.session_id, ER.request_id, ER.start_time, ER.status, ER.command, ER.sql_handle, ER.statement_start_offset, ER.statement_end_offset, ER.plan_handle, ER.database_id, ER.user_id, ER.connection_id, ER.blocking_session_id, ER.wait_type, ER.wait_time, ER.last_wait_type, ER.wait_resource, ER.open_transaction_count, ER.open_resultset_count, ER.transaction_id, ER.context_info, ER.percent_complete, ER.estimated_completion_time, ER.cpu_time, ER.total_elapsed_time, ER.scheduler_id, ER.task_address, ER.reads, ER.writes, ER.logical_reads, ER.text_size, ER.language, ER.date_format, ER.date_first, ER.quoted_identifier, ER.arithabort, ER.ansi_null_dflt_on, ER.ansi_defaults, ER.ansi_warnings, ER.ansi_padding, ER.ansi_nulls, ER.concat_null_yields_null, ER.transaction_isolation_level, ER.lock_timeout, ER.deadlock_priority, ER.row_count, ER.prev_error, ER.nest_level, ER.granted_query_memory, ER.executing_managed_code, ER.group_id, ER.query_hash, ER.query_plan_hash, ER.statement_sql_handle, ER.statement_context_id, ER.dop, ER.parallel_worker_count, ER.external_script_request_id, EQP.dbid, EQP.objectid, EQP.number, EQP.encrypted FROM sys.dm_exec_requests AS ER CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) AS EQP WHERE ER.session_id = <SPID>; |
Historia jobów (job history)
Źródło: https://www.sqlmatters.com/Articles/Checking%20the%20status%20of%20SQL%20Server%20Agent%20jobs.aspx
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
USE msdb; SELECT SJ.name AS [Job Name], CONVERT( VARCHAR, DATEADD( S, (SJH.run_time / 10000) * 60 * 60 /* hours */ + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */ + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */ CONVERT(DATETIME, RTRIM(SJH.run_date), 113) ), 20 ) AS [Time Run], CASE WHEN SJ.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END [Job Status], CASE WHEN SJH.run_status = 0 THEN 'Failed' WHEN SJH.run_status = 1 THEN 'Succeeded' WHEN SJH.run_status = 2 THEN 'Retry' WHEN SJH.run_status = 3 THEN 'Cancelled' ELSE 'Unknown' END [Job Outcome] FROM dbo.sysjobhistory SJH JOIN dbo.sysjobs SJ ON SJH.job_id = SJ.job_id WHERE SJH.step_id = 0 AND DATEADD( S, (SJH.run_time / 10000) * 60 * 60 /* hours */ + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */ + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */ CONVERT(DATETIME, RTRIM(SJH.run_date), 113) ) >= DATEADD(d, -1, GETDATE()) ORDER BY SJ.name, SJH.run_date, SJH.run_time; |
Lekko zmodyfikowana:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
USE msdb; SELECT TOP 5 SJ.name AS [Job Name], CONVERT(VARCHAR, DATEADD( S, (SJH.run_time / 10000) * 60 * 60 /* hours */ + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */ + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */ CONVERT(DATETIME, RTRIM(SJH.run_date), 113) ), 20 ) AS [Time Run], CASE WHEN SJ.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END [Job Status], CASE WHEN SJH.run_status = 0 THEN 'Failed' WHEN SJH.run_status = 1 THEN 'Succeeded' WHEN SJH.run_status = 2 THEN 'Retry' WHEN SJH.run_status = 3 THEN 'Cancelled' ELSE 'Unknown' END [Job Outcome], Duration = STUFF(STUFF(RIGHT('000000' + CAST(SJH.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') FROM dbo.sysjobhistory SJH JOIN dbo.sysjobs SJ ON SJH.job_id = SJ.job_id WHERE SJH.step_id = 0 AND DATEADD( S, (SJH.run_time / 10000) * 60 * 60 /* hours */ + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */ + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */ CONVERT(DATETIME, RTRIM(SJH.run_date), 113) ) >= DATEADD(d, -1, GETDATE()) AND SJ.name = 'job_name' ORDER BY SJ.name, [Time Run] DESC; |
SSAS info o bazach (wielkość bazy, partycji, statusu procesowania etc.)
Źródło: http://www.ssas-info.com/analysis-services-scripts/1197-powershell-script-to-list-info-about-ssas-databases lekko zmodyfikowany
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
param($ServerName = "localhost", $dbname = "db_name") ## Add the AMO namespace $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($server.name -eq $null) { Write-Output ("Server '{0}' not found" -f $ServerName) break } foreach ($d in $server.Databases ) { if ($d.Name -ne $dbname) { continue; } Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB" -f $d.Name, $d.State, ($d.EstimatedSize / 1024 / 1024).ToString("#,##0") ) foreach ($cube in $d.Cubes) { Write-Output ( " Cube: {0}" -f $Cube.Name ) foreach ($mg in $cube.MeasureGroups) { Write-Output ( " MG: {0}; Status: {1}; Size: {2}MB" -f $mg.Name.PadRight(25), $mg.State, ($mg.EstimatedSize / 1024 / 1024).tostring("#,##0")) foreach ($part in $mg.Partitions) { Write-Output ( " Partition: {0}; Status: {1}; Size: {2}MB" -f $part.Name.PadRight(35), $part.State, ($part.EstimatedSize / 1024 / 1024).ToString("#,##0") ) } # Partition } # Measure group foreach ($dim in $d.Dimensions) { Write-Output ( "Dimension: {0}" -f $dim.Name) } # Dimensions } # Cube } # Databases |
Rozrost tempdb – Extended Events + podgląd via TSQL
Skrypt dzięki uprzejmości Igora Yaremenko 🙂
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
-- create event session CREATE EVENT SESSION [Database_Growth_Watchdog] ON SERVER ADD EVENT sqlserver.database_file_size_change ( ACTION ( sqlserver.client_app_name , sqlserver.client_hostname , sqlserver.database_name , sqlserver.session_nt_username , sqlserver.sql_text , sqlserver.username ) WHERE ( [database_id] = ( 2 ))) ADD TARGET package0.event_file ( SET filename = N'D:\Temp\Database_Growth_Wathdog.xel', max_file_size = ( 10 )) WITH ( MAX_MEMORY = 4096KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 1 SECONDS , MAX_EVENT_SIZE = 0KB , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = OFF , STARTUP_STATE = ON ); GO -- parsing event session log DECLARE @TraceFileLocation NVARCHAR(255) = N'D:\Temp\Database_Growth_Wathdog*.xel'; WITH FileSizeChangedEvent AS ( SELECT object_name Event , CONVERT(XML, event_data) Data FROM sys.fn_xe_file_target_read_file(@TraceFileLocation, NULL, NULL, NULL) ) , FileSizeChangedEventParsed AS ( SELECT Data.value('(/event/@timestamp)[1]', 'DATETIME') EventTime , Data.query('/event/action[@name=''client_hostname'']/value').value('.', 'VARCHAR(MAX)') ClientHostname , Data.query('/event/action[@name=''username'']/value').value('.', 'VARCHAR(MAX)') ClientUsername , Data.query('/event/action[@name=''client_app_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppName , Data.query('/event/action[@name=''database_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppDBName , Data.query('/event/data[@name=''database_id'']/value').value('.', 'INT') SystemDatabaseId , Data.query('/event/data[@name=''file_name'']/value').value('.', 'VARCHAR(MAX)') SystemDatabaseFileName , Data.query('/event/data[@name=''file_type'']/text').value('.', 'VARCHAR(MAX)') SystemDatabaseFileType , Data.query('/event/data[@name=''is_automatic'']/text').value('.', 'VARCHAR(MAX)') SystemIsAutomaticGrowth , Data.query('/event/data[@name=''size_change_kb'']/value').value('.', 'BIGINT') GrowthInKB , Data.query('/event/data[@name=''total_size_kb'']/value').value('.', 'BIGINT') TotalSizeInKB , Data.query('/event/data[@name=''duration'']/value').value('.', 'BIGINT') SystemDuration , Data.query('/event/action[@name=''sql_text'']/value').value('.', 'VARCHAR(MAX)') SQLCommandText FROM FileSizeChangedEvent ) SELECT * FROM FileSizeChangedEventParsed; |
Konkatenacja napisów w SQL < 2017 - wersja z użyciem XML PATH bez podmiany znaków specjalnych np. & na & amp;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @M TABLE ( ParameterName NVARCHAR(20), ParameterValue NVARCHAR(MAX) ); INSERT INTO @M VALUES ('1', 'Napis1 &<'), ('1', 'Napis2 &'), ('1', 'Napis3'), ('2', 'aaa'), ('2', 'bbb'), ('3', 'a'); SELECT * FROM @M SELECT DISTINCT ParameterName, RemappedValues = STUFF( ( SELECT ';' + ParameterValue FROM @M AS M2 WHERE M2.ParameterName = M1.ParameterName FOR XML PATH(''), ROOT('ParameterString'), TYPE ).value('/ParameterString[1]', 'nvarchar(max)'), 1, 1, '' ) FROM @M AS M1; |
Partycje i ich granice
Thanks to https://sqlity.net/en/1031/partitions-boundaries-filgroups/
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SELECT f.NAME AS file_group_name, SCHEMA_NAME(t.schema_id) AS table_schema, t.name AS table_name, p.partition_number, ISNULL(CAST(left_prv.value AS VARCHAR(MAX))+ CASE WHEN pf.boundary_value_on_right = 0 THEN ' < ' ELSE ' <= ' END , '-INF < ') + 'X' + ISNULL(CASE WHEN pf.boundary_value_on_right = 0 THEN ' <= ' ELSE ' < ' END + CAST(right_prv.value AS NVARCHAR(MAX)), ' < INF') AS range_desc, pf.boundary_value_on_right, ps.name AS partition_schem_name, pf.name AS partition_function_name, left_prv.value AS left_boundary, right_prv.value AS right_boundary FROM sys.partitions p JOIN sys.tables t ON p.object_id = t.object_id JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units au ON p.hobt_id = au.container_id JOIN sys.filegroups f ON au.data_space_id = f.data_space_id LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values left_prv ON left_prv.function_id = ps.function_id AND left_prv.boundary_id + 1 = p.partition_number LEFT JOIN sys.partition_range_values right_prv ON right_prv.function_id = ps.function_id AND right_prv.boundary_id = p.partition_number |
Zaokrąglanie daty do najbliższych 30 minut
|
1 2 3 4 5 |
--w górę SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0,1)/48.0) FROM [tableName] --w dół SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0)/48.0) FROM [tableName] |
Monitorowanie free space na dyskach, z których korzystają wszystkie bazy
|
1 2 3 4 5 6 7 8 |
WITH CTE AS (SELECT DISTINCT Drive = s.volume_mount_point, [Free(MB)] = CAST(s.available_bytes / 1048576.0 AS DECIMAL(32, 2)) FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s ) SELECT * FROM CTE; |
Autogrowth events – skrypt na detale rozrostu MDF / NDF i LDF z default trace’a
Znalezione na stronie The SQL Guy by Norm Enger
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
USE [master]; GO BEGIN TRY IF ( SELECT CONVERT(INT, value_in_use) FROM sys.configurations WHERE name = 'default trace enabled' ) = 1 BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX('%\%', @curr_tracefilename); SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @base_tracefilename = LEFT(@curr_tracefilename, LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1, ServerName AS [SQL_Instance], --CONVERT(INT, EventClass) AS EventClass, DatabaseName AS [Database_Name], Filename AS [Logical_File_Name], (Duration / 1000) AS [Duration_MS], CONVERT(VARCHAR(50), StartTime, 100) AS [Start_Time], --EndTime, CAST((IntegerData * 8.0 / 1024) AS DECIMAL(19, 2)) AS [Change_In_Size_MB] FROM::fn_trace_gettable(@base_tracefilename, DEFAULT) WHERE EventClass >= 92 AND EventClass <= 95 --AND ServerName = @@SERVERNAME --AND DatabaseName = 'myDBName' ORDER BY DatabaseName, StartTime DESC; END; ELSE SELECT -1 AS l1, 0 AS EventClass, 0 DatabaseName, 0 AS Filename, 0 AS Duration, 0 AS StartTime, 0 AS EndTime, 0 AS ChangeInSize; END TRY BEGIN CATCH SELECT -100 AS l1, ERROR_NUMBER() AS EventClass, ERROR_SEVERITY() DatabaseName, ERROR_STATE() AS Filename, ERROR_MESSAGE() AS Duration, 1 AS StartTime, 1 AS EndTime, 1 AS ChangeInSize; END CATCH; |