NAV Team: Table Information including Index information (Usage, Blocks and Reads) - Again

[Blog bot]

This is only the same query which is already here:

Table Information including Index information (Usage, Blocks and Reads)

But I noticed that the query somehow got mal-formed to a point where it could not run. So here is a cleaned-up version.

In summary: This query shows a list of all tables and indexes in a SQL database to help identifying in which tables the most blocks happen, and to give a starting point for index tuning. For further details, refer to the original blog post.









--use NAVDatabase

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] [bigint] NOT NULL,
[F_Obj_ID] [bigint] NOT NULL,
[F_Schema_Name] [nvarchar] (128) NULL,
[F_Table_Name] [nvarchar] (128) NOT NULL,
[F_Row_Count] [bigint] NULL,
[F_Reserved] [bigint] NULL,
[F_Data] [bigint] NULL,
[F_Index_Size] [bigint] NULL,
[F_UnUsed] [bigint] NULL,
[F_Index_Name] [nvarchar] (128) NULL,
[F_Index_ID] [bigint] NOT NULL,
[F_Column_Name] [nvarchar] (128) NULL,
[F_User_Updates] [bigint] NULL,
[F_User_Reads] [bigint] NULL,
[F_Locks] [bigint] NULL,
[F_Blocks] [bigint] NULL,
[F_Block_Wait_Time] [bigint] NULL,
[F_Last_Used] [datetime] NULL,
[F_Index_Type] [nvarchar] (128) NOT NULL,
[F_Index_Column_ID] [bigint] 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,

-- Index blocks
IStats.row_lock_count + IStats.page_lock_count,
IStats.row_lock_wait_count + IStats.page_lock_wait_count,
IStats.row_lock_wait_in_ms + IStats.page_lock_wait_in_ms,

-- Dates
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 and US.database_id = db_id())
LEFT OUTER JOIN sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_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 results
SELECT
[F_Table_Name] TableName,
[F_Row_Count] No_Of_Records,
[F_Data] Data_Size,
[F_Index_Size] Index_Size,
[F_Index_Name] Index_Name,
[F_User_Updates] Index_Updates,
[F_User_Reads] Index_Reads,
CASE WHEN
F_User_Reads = 0 THEN F_User_Updates
ELSE
F_User_Updates / F_User_Reads
END AS Updates_Per_Read,
[F_Locks] Locks,
[F_Blocks] Blocks,
[F_Block_Wait_Time] Block_Wait_Time,
[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]
--order by F_User_Updates desc
--order by Blocks desc
--order by Block_Wait_Time desc
--order by Updates_Per_Read desc
ORDER BY F_Table_Name













Источник: http://feedproxy.google.com/~r/Micro...ads-again.aspx