SQL-запрос для отображения таблиц, их индексов и использования индексов
Написанный ниже SQL-запрос выводит список таблиц, отсортированных по размеру. Для каждой таблице показаны индексы. Для каждого индекса указано время его последнего запуска. Запрос написан с целью собрать разрозненные куски информации в одном месте для обзора, и помочь в настройке индексов.
Столбцы слева показывают данные для таблицы (кол-во записей, размер таблицы, размер индексов), для демонстрации влияния наличия индексов на таблицу.
Столбцы справа показывают данные по каждому индексы, включая Updates (costs) и Reads (benefits), а также время последнего запуска (с момента запуска/перезапуска SQL Server’а).
Прочие примечания:
- Запрос работает для версии SQL Server 2005 и выше.
- Показатели в запросе сбрасываются каждый раз при перезапуске сервера.
- Запрос может выполняться несколько минут.
- Запрос предлагается вам на условиях «as is» – т.е. никто ни за что не отвечает (как всегда).
Ждем ваших комментариев, отзывов, критики и предложений!
IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL
DROP TABLE z_IUQ2_Temp_Index_Keys;
-- Generate list of indexes with key list
CREATE TABLE z_IUQ2_Temp_Index_Keys(
[l1] [INT] NOT NULL,
[F_Obj_ID] [INT] NOT NULL,
[F_Schema_Name] [nvarchar] (128) NULL,
[F_Table_Name] [nvarchar] (128) NOT NULL,
[F_Row_Count] [INT] NULL,
[F_Reserved] [INT] NULL,
[F_Data] [INT] NULL,
[F_Index_Size] [INT] NULL,
[F_UnUsed] [INT] NULL,
[F_Index_Name] [nvarchar] (128) NOT NULL,
[F_Index_ID] [INT] NOT NULL,
[F_Column_Name] [nvarchar] (128) NOT NULL,
[F_User_Updates] [INT] NULL,
[F_User_Reads] [INT] NULL,
[F_Last_Used] [datetime] NULL,
[F_Index_Type] [nvarchar] (128) NOT NULL,
[F_Index_Column_ID] [INT] NOT NULL,
[F_Last_Seek] [datetime] NULL,
[F_Last_Scan] [datetime] NULL,
[F_Last_Lookup] [datetime] NULL,
[Index_Key_List] [nvarchar] (MAX) NULL
)
GO
CREATE NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Obj_ID] ASC)
GO
CREATE NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Index_ID] ASC)
GO
CREATE NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Row_Count] ASC)
GO
INSERT INTO z_IUQ2_Temp_Index_Keys
SELECT (ROW_NUMBER() OVER(ORDER BY a3.name, a2.name))%2 AS l1,
a1.object_id, ---
a3.name AS [schemaname],
a2.name AS [tablename],
a1.ROWS AS ROW_COUNT,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.DATA * 8 AS DATA,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.DATA THEN (a1.used + ISNULL(a4.used,0)) - a1.DATA ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
-- Index Description
SI.name,
SI.Index_ID,
index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),
-- Index Stats
US.user_updates,
US.user_seeks + US.user_scans + US.user_lookups User_Reads,
CASE
WHEN (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) AND (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) THEN US.last_user_seek
WHEN (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) AND (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) THEN US.last_user_scan
ELSE US.last_user_lookup
END AS Last_Used_For_Reads,
SI.type_desc,
SIC.index_column_id,
US.last_user_seek,
US.last_user_scan,
US.last_user_lookup,
''
FROM
(SELECT ps.object_id,
SUM(CASE WHEN (ps.index_id < 2) THEN ROW_COUNT ELSE 0 END) AS [ROWS],
SUM(ps.reserved_page_count) AS reserved,
SUM(CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END) AS DATA,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(SELECT it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
INNER JOIN sys.indexes SI ON (SI.object_id = a1."object_id") ---
INNER JOIN sys.index_columns SIC ON (SIC.object_id = SI.object_id AND SIC.index_id = SI.index_id)
LEFT OUTER JOIN sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id AND US.index_id = SI.index_id)
WHERE a2.TYPE <> N'S' AND a2.TYPE <> N'IT'
ORDER BY ROW_COUNT DESC
GO
-- Populate key string
DECLARE IndexCursor cursor FOR
SELECT F_Obj_ID, F_Index_ID FROM z_IUQ2_Temp_Index_Keys
FOR UPDATE OF Index_Key_List
DECLARE @objID INT
DECLARE @IndID INT
DECLARE @KeyString VARCHAR(MAX)
SET @KeyString = NULL
OPEN IndexCursor
SET NOCOUNT ON
fetch NEXT FROM IndexCursor INTO @ObjID, @IndID
while @@fetch_status = 0 BEGIN
SET @KeyString = ''
SELECT @KeyString = COALESCE(@KeyString,'') + F_Column_Name + ', '
FROM z_IUQ2_Temp_Index_Keys
WHERE F_Obj_ID = @ObjID AND F_Index_ID = @IndID
ORDER BY F_Index_ID, F_Index_Column_ID
SET @KeyString = LEFT(@KeyString,LEN(@KeyString) -2)
UPDATE z_IUQ2_Temp_Index_Keys
SET Index_Key_List = @KeyString
WHERE CURRENT OF IndexCursor
fetch NEXT FROM IndexCursor INTO @ObjID, @IndID
END;
close IndexCursor
deallocate IndexCursor
GO
-- clean up table to one line per index
DELETE FROM z_IUQ2_Temp_Index_Keys
WHERE [F_Index_Column_ID] > 1
GO
SELECT [F_Table_Name] TableName,
[F_Row_Count] No_Of_Records,
[F_Data] Data_Size,
[F_Index_Size] Index_Size,
[F_UnUsed] UnUsed_Space,
[F_Index_Name] Index_Name,
[F_User_Updates] Index_Updates,
[F_User_Reads] Index_Reads,
[F_Last_Used] Index_Last_Used,
[F_Index_Type] Index_Type,
[Index_Key_List] Index_Fields
FROM z_IUQ2_Temp_Index_Keys
ORDER BY F_Row_Count DESC, F_Table_Name, [F_Index_ID]
Оригинал заметки здесь: http://blogs.msdn.com/b/nav/archive/2009/06/04/sql-query-to-show-tables-their-indexes-and-index-usage.aspx

Автор: Андрей Стрельников
В области Navision - с 2003 года. Профессиональные интересы: NAV, MS SQL, .NET, BPMN, IT-менеджмент. Предметная область: логистика, финансы, склады, 3PL.
Количество статей, опубликованных автором: 86.
а это нормально, что индексы с разным количеством полей занимают одинаковое место на диске, согласно вашему запросу?