Использование трейса по умолчанию в SQL Server

Трассировки в SQL Server представляют собой мощное многоцелевое средство мониторинга. Поэтому, для любой системы, хорошей практикой является наличие постоянно запущенного серверного трейса. Именно по этому такая трассировка предусмотрена в SQL Server по умолчанию.

Ссылки:

Profiler and Trace Tips

Tracing and Profiling

Using SQL Server Default Trace

What can you tell me Default Trace

Stairway to Server-side Tracing - Level 4: How to Create and View Default and black box Trace Data

SQL Server Profiler - Saving and Exporting Traces

SQL Server Profiler Architecture

Default trace - A Beginner's Guide

Using the Default Trace

Наиболее используемые индексы и таблицы

Всегда полезно анализировать нагрузку на сервер баз данных. В частность важно знать какие таблицы используются наиболее часто. Это важно, например, при разнесении БД по нескольким файловым группам расположенным на разных дисках.

--get most used tables
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('MyDb')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC


--get most used indexes
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
i.NAME AS IndexName,
i.type_desc AS IndexType,
ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
WHERE database_id = DB_ID('MyDb')
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

источник




declare @dbid int
--To get Datbase ID
set @dbid = db_id()

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')
as 'index_columns' FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = 'NONCLUSTERED'
and objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc

источник

Мониторинг памяти в SQL Server

- Текущее состояние памяти можно увидеть коммандой DBCC MEMORYSTATUS расшифровка результатов тут

- Два запроса позволяющих определить данные из каких таблиц находятся в кеше

1).

;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC

2).

SELECT top 20 obj.[name]as "Table Name" ,obj.index_id ,si.name,convert(numeric(10,2),(count(*)*8)/1024.0) AS "cached size (mb)"
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
join sys.indexes si on si.index_id = obj.index_id and si.[object_id] = object_id(obj.name)
WHERE bd.database_id = db_id()
GROUP BY obj.name, obj.index_id,si.name
ORDER BY "cached size (mb)" DESC