Polecenia SQL Server przydatne zaawansowanemu użytkownikowi
Podczas pracy z SQL Server zaawansowanym użytkownikom mogą okazać się niezbędne następujące polecenia:
1. Wyszukiwanie treści w bazie:
select o.type, o.name from sys.sql_modules c join sysobjects o on c.object_id = o.id where [definition] like '%%'
Polecenie przydaje się podczas odnajdywania np. procedur i/lub funkcji, które zawierają określony ciąg znaków np. nazwę tabeli, wywołanie funkcji, odwołanie się do konkretnego zasobu.
Podczas bardziej zaawansowanego wyszukiwania polecam posłużyć się poleceniem:
EXEC sp_depends @objname = N'NazwaTabeli';
dzięki czemu odnajdziemy miejsca gdzie występuje faktyczne użycie zasobu, a nie np. jedynie zakomentowany fragment kodu.
2. Procent przebudowania indeksów w bazie:
;WITH cte AS ( SELECT object_id, index_id, partition_number, rows, ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn FROM sys.partitions ) SELECT object_name(cur.object_id) as TableName, cur.index_id, cur.partition_number, PrecentDone = CASE WHEN pre.rows = 0 THEN 0 ELSE ((cur.rows * 100.0) / pre.rows) END, pre.rows - cur.rows as MissingRows FROM cte as cur INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1) ORDER BY 4
3. Aktualnie wykonujący się blok kodu w danej sesji:
select QueryText = SUBSTRING(st.text, (re.statement_start_offset/2)+1, ((CASE re.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE re.statement_end_offset END - re.statement_start_offset)/2) + 1) from sys.dm_exec_requests re with(nolock) CROSS APPLY sys.dm_exec_sql_text(re.sql_handle) AS st where re.session_id = 1234
4. Wypisywanie aktualnie wykonywanej operacji bez oczekiwania na zakończenie się całej procedury:
declare @Date date ='2015-01-16'; declare @DateStr varchar(100); while(@Date <= cast(getdate() as date)) begin set @DateStr = cast(@Date as varchar); Raiserror(@DateStr,0,1) with nowait --tutaj operacje z jakimiś datami set @Date = dateadd(day,1,@date); end
5. Przykład wykorzystania ROW_NUMBER() do wyeliminowania duplikatów danych:
create table #fox_table (ID bigint) insert into #fox_table(ID) values (1); insert into #fox_table(ID) values (1); insert into #fox_table(ID) values (1); insert into #fox_table(ID) values (1); insert into #fox_table(ID) values (5); insert into #fox_table(ID) values (5); insert into #fox_table(ID) values (5); ;with duplicates as ( select ID, ROW_NUMBER() over (partition by ID order by ID) as RowNum from #fox_table with (nolock) ) delete from duplicates where RowNum > 1; select * from #fox_table drop table #fox_table
6. Wyświetlenie top 25 procedur wykonujących największe ilości:
a) Logical Writes
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
b) Physical Reads
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() AND qs.total_physical_reads &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 0 ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
c) Logical Reads
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
d) CPU
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
e) duration
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);