Não possuem index:
SELECT
t.name AS Tabela
FROM
sys.tables t
WHERE
NOT EXISTS (
SELECT 1
FROM sys.indexes i
WHERE i.object_id = t.object_id
)
ORDER BY
t.name;
Index desatualizados
SELECT
OBJECT_NAME(s.[object_id]) AS Tabela,
i.name AS Indice,
last_user_update,
last_system_update
FROM
sys.dm_db_index_usage_stats s
JOIN
sys.indexes i
ON
i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE
database_id = DB_ID()
AND last_user_update < last_system_update
ORDER BY
Tabela;
Queries caras:
SELECT TOP 50
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS query_text,
qs.execution_count,
qs.total_worker_time/qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time/qs.execution_count AS avg_duration,
qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
qs.total_physical_reads/qs.execution_count AS avg_physical_reads,
qs.total_logical_writes/qs.execution_count AS avg_logical_writes,
qs.creation_time
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
qs.total_logical_reads DESC;
Comentários