Query più “costose”
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;
Strutture indici
SELECT Tab.name Table_Name
,IX.name Index_Name
,IX.type_desc Index_Type
,Col.name Index_Column_Name
,IXC.is_included_column Is_Included_Column
,IX.fill_factor
,IX.is_disabled
,IX.is_primary_key
,IX.is_unique
FROM sys.indexes IX
INNER JOIN sys.index_columns IXC ON IX.object_id = IXC.object_id AND IX.index_id = IXC.index_id
INNER JOIN sys.columns Col ON IX.object_id = Col.object_id AND IXC.column_id = Col.column_id
INNER JOIN sys.tables Tab ON IX.object_id = Tab.object_id;
Informazioni sulle tabelle
SELECT FullName = s.name + '.' + t.name
,SchemaName = s.name
,TableName = t.name
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;
SELECT *
FROM (SELECT TableName = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
,[RowCount] = SUM(sp.[rows])
,Megabytes = (8 * SUM(CASE WHEN sau.type != 1 THEN sau.used_pages
WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions sp
ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
JOIN sys.allocation_units sau
ON sau.container_id = sp.partition_id
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME ) A
ORDER BY TableName;
Mostra constraints
select table_view,
object_type,
constraint_type,
constraint_name,
details
from (select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
isnull(c.[name], i.[name]) as constraint_name,
substring(column_names, 1, len(column_names)-1) as [details]
from sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
union all
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'Table',
'Foreign key',
fk.name as fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
from sys.foreign_keys fk
inner join sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
inner join sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] as constraint_name,
con.[definition]
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
from sys.default_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id) a
order by table_view, constraint_type, constraint_name;
Se SQL Server funziona lentamente, uno dei primi passi e' capire cosa sta funzionando in questo momento. Uno dei modi migliori per farlo è con sp_whoisactive. Questo differisce da sys.sysprocesses perché solo mostra i processi che eseguono una query in questo momento, non gli SPID inattivi.
Si puo' scaricare sp_whoisactive direttamente da GitHub https://github.com/amachanic/sp_whoisactive/releases.
EXEC sp_whoisactive;
La prima colonna è la durata della query, quindi puoi evitare di guardare le query che hanno funzionato solo per una frazione di secondo.
A volte ricevi un avviso di prestazioni lente, ma si cancella prima che tu possa eseguire sp_whoisactive. Fortunatamente, possiamo ottenere la cache del piano delle query più costose.
WITH qs AS (
SELECT TOP 10
total_worker_time/execution_count AvgCPU
, total_elapsed_time/execution_count AvgDuration
, (total_logical_reads + total_physical_reads)/execution_count AvgReads
, execution_count
, sql_handle
, plan_handle
, statement_start_offset
, statement_end_offset
FROM sys.dm_exec_query_stats
WHERE execution_count > 5
AND min_logical_reads > 100
AND min_worker_time > 100
ORDER BY (total_logical_reads + total_physical_reads)/execution_count DESC)
SELECT
AvgCPU
, AvgDuration
, AvgReads
, execution_count
,SUBSTRING(st.TEXT, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) StatementText
,query_plan ExecutionPlan
FROM
qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY
AvgDuration DESC;
A volte il problema è il lock. Nel dubbio, si possono usare queste due query:
SELECT * FROM sys.sysprocesses
WHERE blocked > 0
OR SPID IN (SELECT Blocked FROM sys.sysprocesses);
La colonna “bloccato” mostra 0 quando un SPID non viene bloccato e mostra il blocco SPID quando viene bloccato. Questa query mostra tutto processi bloccati o che stanno bloccando qualcuno.
A volte si verifica un problema di blocco quando qualcuno apre una transazione esplicita e dimentica di chiuderlo. L'esecuzione di questo comando ti dirà l'apertura più vecchia transazione. È ragionevole che questo restituisca un valore dall'ultimo pochi secondi, ma se mostra una data / ora da diversi minuti o ore fa, quello indica un problema
DBCC OPENTRAN();
SQL Server tiene traccia delle attese sperimentate da ogni processo in esecuzione sul server. Ogni tipo di attesa inizia a 0 ms ogni volta che il servizio viene avviato e conta da Là. Per avere un'idea di ciò che sta accadendo in questo momento, devi confrontare i numeri da un punto nel tempo a un altro. Questo script farà proprio questo confrontando il DMV con un'istantanea di se stesso da 15 secondi prima.
SELECT
wait_type
, waiting_tasks_count
, signal_wait_time_ms
, wait_time_ms
, SysDateTime() AS StartTime
INTO
#WaitStatsBefore
FROM
sys.dm_os_wait_stats
WHERE
wait_type NOT IN ('SLEEP_TASK','BROKER_EVENTHANDLER','XE_DISPATCHER_WAIT','BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_BUFFER_FLUSH','LAZYWRITER_SLEEP','XE_TIMER_EVENT','XE_DISPATCHER_WAIT','FT_IFTS_SCHEDULER_IDLE_WAIT','LOGMGR_QUEUE','CHECKPOINT_QUEUE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'BROKER_EVENTHANDLER', 'SLEEP_TASK', 'WAITFOR', 'DBMIRROR_DBM_MUTEX', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'DISPATCHER_QUEUE_SEMAPHORE','BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT', 'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'ONDEMAND_TASK_QUEUE', 'FT_IFTSHC_MUTEX', 'CLR_MANUAL_EVENT', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','CLR_SEMAPHORE','DBMIRROR_WORKER_QUEUE','SP_SERVER_DIAGNOSTICS_SLEEP','HADR_CLUSAPI_CALL','HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE','HADR_TIMER_TASK','HADR_WORK_QUEUE','REDO_THREAD_PENDING_WORK','UCS_SESSION_REGISTRATION','BROKER_TRANSMITTER','SLEEP_SYSTEMTASK','QDS_SHUTDOWN_QUEUE');--These are a series of irrelevant wait stats.
WAITFOR DELAY '00:00:15'; --15 seconds
SELECT
a.wait_type
, a.signal_wait_time_ms - b.signal_wait_time_ms AS CPUDiff
, (a.wait_time_ms - b.wait_time_ms) - (a.signal_wait_time_ms - b.signal_wait_time_ms) AS ResourceDiff
, a.waiting_tasks_count - b.waiting_tasks_count AS waiting_tasks_diff
, CAST(CAST(a.wait_time_ms - b.wait_time_ms AS FLOAT) / (a.waiting_tasks_count - b.waiting_tasks_count) AS DECIMAL(10,1)) AS AverageDurationMS
, a.max_wait_time_ms max_wait_all_timeMS
, DATEDIFF(ms,StartTime, SysDateTime()) AS DurationSeconds
FROM
sys.dm_os_wait_stats a
INNER JOIN
#WaitStatsBefore b ON a.wait_type = b.wait_type
WHERE
a.signal_wait_time_ms <> b.signal_wait_time_ms
OR
a.wait_time_ms <> b.wait_time_ms
ORDER BY 3 DESC;
SQL Server tiene traccia dei ritardi riscontrati durante l'interazione con i dati e il registro File. Come le statistiche di attesa, questi contatori iniziano da 0 ogni volta che il servizio è iniziato. Il prossimo script creerà una tabella temporanea per memorizzare un'istantanea di i numeri, attendi 15 secondi, quindi vedi cosa è cambiato.
SELECT
b.name
, a.database_id
, a.[FILE_ID]
, a.num_of_reads
, a.num_of_bytes_read
, a.io_stall_read_ms
, a.num_of_writes
, a.num_of_bytes_written
, a.io_stall_write_ms
, a.io_stall
, GetDate() AS StartTime
INTO
#IOStatsBefore
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) a
INNER JOIN
sys.databases b ON a.database_id = b.database_id;
WAITFOR DELAY '00:00:15'
SELECT
a.name DatabaseName
, a.[FILE_ID]
, (b.io_stall_read_ms - a.io_stall_read_ms)/ CAST(1000 as DECIMAL(10,1)) io_stall_read_Diff
, (b.io_stall_write_ms - a.io_stall_write_ms)/ CAST(1000 as DECIMAL(10,1)) io_stall_write_Diff
, (b.io_stall - a.io_stall)/ CAST(1000 as DECIMAL(10,1)) io_stall_Diff
, DATEDIFF(s,StartTime, GETDATE()) AS DurationSeconds
FROM
#IOStatsBefore a
INNER JOIN
sys.dm_io_virtual_file_stats(NULL, NULL) b ON a.database_id = b.database_id AND a.[file_id] = b.[file_id]
ORDER BY
a.name
, a.[FILE_ID];
SQL Server tiene traccia nativamente della cronologia di utilizzo della CPU di un'istanza una volta per minuto per gli ultimi 250 minuti. Puoi ottenere quelle letture usando la seguente query. Se le prestazioni sono disattivate, confrontare gli ultimi 30 minuti con i 220 precedenti per vedere se l'utilizzo della CPU è aumentato mentre gli utenti si sono lamentati delle prestazioni problemi.
WITH XMLRecords AS (
SELECT
DATEADD (ms, r.[timestamp] - sys.ms_ticks,SYSDATETIME()) AS record_time
, CAST(r.record AS XML) record
FROM
sys.dm_os_ring_buffers r
CROSS JOIN
sys.dm_os_sys_info sys
WHERE
ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'
AND
record LIKE '%<SystemHealth>%')
SELECT
100-record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemUtilization
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
, record_time
FROM XMLRecords;
A volte un disco completo può causare un problema di prestazioni. Utilizzare questa query per analizzare rapidamente tutti i volumi logici contenenti almeno un dato o registro di SQL Server File.
SELECT DISTINCT
vs.volume_mount_point Drive
, vs.logical_volume_name
, vs.total_bytes/1024/1024/1024 CapacityGB
, vs.available_bytes/1024/1024/1024 FreeGB
, CAST(vs.available_bytes * 100. / vs.total_bytes AS DECIMAL(4,1)) FreePct
FROM
sys.master_files mf
CROSS APPLY
sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;