344 lines
10 KiB
PL/PgSQL
344 lines
10 KiB
PL/PgSQL
/*
|
|
Server: ssunb008vm01.sunstore.ch
|
|
Format: GCM
|
|
Business: TPPHAR
|
|
type: DEVE
|
|
Version: 23.1.10016.00066
|
|
|
|
01.11.2022, TSC
|
|
*/
|
|
USE Arizona;
|
|
BEGIN TRANSACTION;
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
IF OBJECT_ID('tempdb..#chkRes') IS NOT NULL BEGIN;
|
|
DROP TABLE #chkRes;
|
|
END
|
|
DECLARE @ref_schema VARCHAR(50) = 'dbo';
|
|
DECLARE @ref_table VARCHAR(100) = 'entry';
|
|
|
|
CREATE TABLE #chkRes (
|
|
tbl_name NVARCHAR(128) NOT NULL
|
|
,[schema_name] VARCHAR(50) NOT NULL
|
|
,[type] CHAR(2) NULL
|
|
,index_name NVARCHAR(128) NULL
|
|
,[index_id] INT NOT NULL
|
|
,[Reads] BIGINT NOT NULL
|
|
,[Writes] BIGINT NOT NULL
|
|
,[delta] BIGINT NOT NULL
|
|
,[index_type] NVARCHAR(60) NULL
|
|
,[FillFactor] TINYINT NULL
|
|
,[has_filter] BIT NOT NULL
|
|
,[filter_definition] NVARCHAR(MAX) NULL
|
|
,[last_user_scan] DATETIME NULL
|
|
,[last_user_lookup] DATETIME NULL
|
|
,[last_user_seek] DATETIME NULL
|
|
,[IndexColumns] NVARCHAR(MAX) NULL
|
|
,[IncludedColumns] NVARCHAR(MAX) NULL
|
|
,[primary_table] NVARCHAR(500) NULL
|
|
,[pk_column_name] NVARCHAR(128) NULL
|
|
,[fk_constraint_name] NVARCHAR(128) NULL
|
|
,comp_drop_cmd AS 'DROP INDEX ' + index_name + ' ON ' + [schema_name] + '.' + tbl_name + ';'
|
|
);
|
|
|
|
;WITH ColInfo
|
|
AS (
|
|
SELECT
|
|
o.name AS TblName
|
|
,s.name + '.' + o.name AS SchemaTbl
|
|
,i.name AS IndexName
|
|
,i.is_primary_key AS IsPrimaryKey
|
|
,i.is_unique_constraint AS IsUniqueConstraint
|
|
,i.is_unique AS IsUnique
|
|
,c.name AS ColName
|
|
,c.is_computed AS IsComputedCol
|
|
,ic.is_included_column AS IsIncludedCol
|
|
,ic.key_ordinal
|
|
,i.filter_definition AS FilterDefinition
|
|
FROM sys.objects o
|
|
INNER JOIN sys.schemas s
|
|
ON o.schema_id = s.schema_id
|
|
INNER JOIN sys.columns c
|
|
ON o.object_id = c.object_id
|
|
INNER JOIN sys.indexes i
|
|
ON c.object_id = i.object_id
|
|
INNER JOIN sys.index_columns ic
|
|
ON i.index_id = ic.index_id
|
|
AND o.object_id = ic.object_id
|
|
AND c.column_id = ic.column_id
|
|
)
|
|
|
|
|
|
--- Index Read/Write stats (all tables in current DB) ordered by Reads (Query 62) (Overall Index Usage - Reads)
|
|
INSERT INTO #chkRes (
|
|
tbl_name
|
|
,[schema_name]
|
|
,[type]
|
|
,index_name
|
|
,index_id
|
|
,Reads
|
|
,Writes
|
|
,delta
|
|
,index_type
|
|
,[FillFactor]
|
|
,has_filter
|
|
,filter_definition
|
|
,last_user_scan
|
|
,last_user_lookup
|
|
,last_user_seek
|
|
,IndexColumns
|
|
,IncludedColumns
|
|
,primary_table
|
|
,pk_column_name
|
|
,fk_constraint_name
|
|
)
|
|
SELECT
|
|
o.name AS [tbl_Name]
|
|
,SCHEMA_NAME(o.schema_id) AS [schema_name]
|
|
,o.type
|
|
,i.name AS [index_name]
|
|
,i.index_id
|
|
,user_seeks + user_scans + user_lookups AS [Reads]
|
|
,s.user_updates AS [Writes]
|
|
,CASE
|
|
WHEN s.user_updates > user_seeks + user_scans + user_lookups THEN
|
|
s.user_updates - (user_seeks + user_scans + user_lookups)
|
|
ELSE (user_seeks + user_scans + user_lookups) - s.user_updates
|
|
END AS delta
|
|
,i.type_desc AS [index_type]
|
|
,i.fill_factor AS [fill_factor]
|
|
,i.has_filter
|
|
,i.filter_definition
|
|
,s.last_user_scan
|
|
,s.last_user_lookup
|
|
,s.last_user_seek
|
|
,idx_cols.IndexColumns
|
|
,idx_cols.IncludedColumns
|
|
,fk.primary_table
|
|
,fk.pk_column_name
|
|
,fk.fk_constraint_name
|
|
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
|
|
INNER JOIN sys.indexes AS i WITH (NOLOCK)
|
|
ON s.object_id = i.object_id
|
|
INNER JOIN sys.objects o WITH (NOLOCK)
|
|
ON s.object_id = o.object_id
|
|
OUTER APPLY (
|
|
SELECT DISTINCT
|
|
x.TblName
|
|
,x.IndexName
|
|
,x.IsPrimaryKey
|
|
,x.IsUniqueConstraint
|
|
,x.IsUnique
|
|
--,size.IndexSizeKB
|
|
,c.IndexColumns
|
|
,ci.IncludedColumns
|
|
,cc.ComputedColumns
|
|
,x.FilterDefinition
|
|
FROM ColInfo x
|
|
CROSS APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS IndexColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsIncludedCol = 0
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) c
|
|
OUTER APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS IncludedColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsIncludedCol = 1
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) ci
|
|
OUTER APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS ComputedColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsComputedCol = 1
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) cc
|
|
WHERE x.TblName = o.name
|
|
AND x.IndexName = i.name
|
|
) idx_cols
|
|
LEFT OUTER JOIN (
|
|
SELECT
|
|
SCHEMA_NAME(tab.schema_id) + '.' + tab.name AS [table]
|
|
,col.column_id
|
|
,col.name AS column_name
|
|
,CASE
|
|
WHEN fk.object_id IS NOT NULL THEN '>-'
|
|
ELSE NULL
|
|
END AS rel
|
|
,SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.name AS primary_table
|
|
,pk_col.name AS pk_column_name
|
|
,fk_cols.constraint_column_id AS NO
|
|
,fk.name AS fk_constraint_name
|
|
,tab.name AS tbl_name
|
|
FROM sys.tables tab
|
|
INNER JOIN sys.columns col
|
|
ON col.object_id = tab.object_id
|
|
LEFT OUTER JOIN sys.foreign_key_columns fk_cols
|
|
ON fk_cols.parent_object_id = tab.object_id
|
|
AND fk_cols.parent_column_id = col.column_id
|
|
LEFT OUTER JOIN sys.foreign_keys fk
|
|
ON fk.object_id = fk_cols.constraint_object_id
|
|
LEFT OUTER JOIN sys.tables pk_tab
|
|
ON pk_tab.object_id = fk_cols.referenced_object_id
|
|
LEFT OUTER JOIN sys.columns pk_col
|
|
ON pk_col.column_id = fk_cols.referenced_column_id
|
|
AND pk_col.object_id = fk_cols.referenced_object_id
|
|
WHERE fk.object_id IS NOT NULL
|
|
) fk
|
|
ON fk.tbl_name = o.name
|
|
AND idx_cols.IndexColumns LIKE '%' + fk.column_name + '%'
|
|
WHERE o.type = 'U' -- user table
|
|
AND i.index_id = s.index_id
|
|
AND s.database_id = DB_ID()
|
|
AND o.name = @ref_table
|
|
AND SCHEMA_NAME(o.schema_id) = @ref_schema
|
|
ORDER BY delta DESC
|
|
--ORDER BY user_seeks + user_scans + user_lookups DESC -- Order by reads
|
|
--ORDER BY s.user_updates DESC OPTION -- Order by writes
|
|
OPTION (RECOMPILE);
|
|
|
|
|
|
|
|
/* list back data */
|
|
SELECT cr.tbl_name
|
|
,cr.[schema_name]
|
|
,cr.[type]
|
|
,cr.index_name
|
|
,cr.index_id
|
|
,cr.Reads
|
|
,cr.Writes
|
|
,cr.delta
|
|
,cr.index_type
|
|
,cr.[FillFactor]
|
|
,cr.has_filter
|
|
,cr.filter_definition
|
|
,cr.last_user_scan
|
|
,cr.last_user_lookup
|
|
,cr.last_user_seek
|
|
,cr.IndexColumns
|
|
,cr.IncludedColumns
|
|
,cr.primary_table
|
|
,cr.pk_column_name
|
|
,cr.fk_constraint_name
|
|
FROM #chkRes cr
|
|
ORDER BY IndexColumns
|
|
;
|
|
|
|
ROLLBACK TRANSACTION
|
|
|
|
RETURN
|
|
|
|
|
|
--/* Check if index with FK are in the list, alert if it's the case */
|
|
--IF EXISTS(
|
|
-- SELECT 1
|
|
-- FROM #chkRes cr
|
|
-- WHERE cr.Reads = 0 --not used
|
|
-- AND cr.Writes > 100 --but maintained
|
|
-- AND cr.fk_constraint_name IS NOT NULL --is part of a FK
|
|
--) BEGIN
|
|
-- SELECT
|
|
-- 'This index is tied to a column part of a FK, check if dropping is to be done.' AS msg
|
|
-- ,cr.[schema_name]
|
|
-- ,cr.tbl_name
|
|
-- ,cr.index_name
|
|
-- ,cr.Reads
|
|
-- ,cr.Writes
|
|
-- ,cr.primary_table
|
|
-- ,cr.pk_column_name
|
|
-- ,cr.fk_constraint_name
|
|
-- FROM #chkRes cr
|
|
-- WHERE cr.Reads = 0 --not used
|
|
-- AND cr.Writes > 100 --but maintained
|
|
-- AND cr.fk_constraint_name IS NOT NULL --is part of a FK
|
|
-- AND cr.index_type = 'NONCLUSTERED'
|
|
--END
|
|
|
|
|
|
--/* declare variables */
|
|
--DECLARE
|
|
-- @table_name VARCHAR(MAX)
|
|
-- ,@schema_name VARCHAR(20)
|
|
-- ,@index_name VARCHAR(500)
|
|
-- ,@drop_cmd VARCHAR(MAX)
|
|
-- ;
|
|
|
|
--DECLARE dropIdx CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
|
|
-- SELECT
|
|
-- cr.tbl_name
|
|
-- ,cr.[schema_name]
|
|
-- ,cr.index_name
|
|
-- ,cr.comp_drop_cmd
|
|
-- FROM #chkRes cr
|
|
-- WHERE cr.Reads = 0 --not used on select
|
|
-- AND cr.Writes > 100 --but maintained on insert / update
|
|
-- AND cr.fk_constraint_name IS NULL --not part of a FK
|
|
-- AND cr.index_type = 'NONCLUSTERED' --ignore clustered & heaps
|
|
-- ;
|
|
|
|
--OPEN dropIdx;
|
|
|
|
--FETCH NEXT FROM dropIdx
|
|
--INTO
|
|
-- @table_name
|
|
-- ,@schema_name
|
|
-- ,@index_name
|
|
-- ,@drop_cmd
|
|
-- ;
|
|
|
|
--WHILE @@FETCH_STATUS = 0 BEGIN
|
|
-- PRINT @drop_cmd;--'Dropping index ' + @index_name + ' on ' + @schema_name + '.' + @table_name;
|
|
-- --EXEC(@drop_cmd);
|
|
-- FETCH NEXT FROM dropIdx
|
|
-- INTO
|
|
-- @table_name
|
|
-- ,@schema_name
|
|
-- ,@index_name
|
|
-- ,@drop_cmd
|
|
-- ;
|
|
--END
|
|
|
|
--CLOSE dropIdx;
|
|
--DEALLOCATE dropIdx;
|
|
|
|
|
|
--SELECT cr.tbl_name
|
|
-- ,cr.[schema_name]
|
|
-- ,cr.index_name
|
|
-- ,cr.Reads
|
|
-- ,cr.Writes
|
|
-- ,cr.delta
|
|
-- ,cr.index_type
|
|
-- ,cr.last_user_scan
|
|
-- ,cr.last_user_lookup
|
|
-- ,cr.last_user_seek
|
|
-- ,cr.IndexColumns
|
|
-- ,cr.IncludedColumns
|
|
-- ,cr.primary_table
|
|
-- ,cr.pk_column_name
|
|
-- ,cr.fk_constraint_name
|
|
--FROM #chkRes cr
|
|
--ORDER BY cr.delta DESC
|
|
--;
|
|
|
|
--ROLLBACK TRANSACTION;
|