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 > 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);