To Find The Index by size
USE
DynamicsPerf
SELECT TOP 100 DATABASE_NAME,
TABLE_NAME,
SUM(CASE
WHEN ISV.INDEX_ID IN (0,1) THEN
PAGE_COUNT * 8 /
1024
END) AS SIZEMB_DATA,
SUM(CASE
WHEN ISV.INDEX_ID > 1 THEN PAGE_COUNT * 8 / 1024
END) AS SIZEMB_INDEXES,
COUNT(CASE
WHEN ISV.INDEX_ID > 1 THEN TABLE_NAME
END) AS
NO_OF_INDEXES,
MAX(CASE
WHEN ( DATA_COMPRESSION
> 0 )
AND ( ISV.INDEX_ID IN (0,1) ) THEN 'Y'
ELSE 'N'
END) AS DATA_COMPRESSED,
MAX(CASE
WHEN ( DATA_COMPRESSION
> 0 )
AND ( ISV.INDEX_ID > 1) THEN 'Y'
ELSE 'N'
END) AS INDEXES_COMPRESSED
FROM INDEX_STATS_CURR_VW ISV
--WHERE
DATABASE_NAME = 'Your AX Database Name'
--AND
SERVER_NAME = 'Database Server Name'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
ORDER BY 3 DESC
___________________________________________________________________________________
Index Activity
SELECT
DATABASE_NAME,
TABLE_NAME,
CASE
WHEN ( SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) =
0 ) THEN NULL
ELSE ( CAST(SUM(USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS DECIMAL) / CAST(SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) ) END AS ratioofreads,
CASE
WHEN ( SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) =
0 ) THEN NULL
ELSE ( CAST(SUM(USER_UPDATES) AS DECIMAL) / CAST(SUM(USER_UPDATES + USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS DECIMAL) ) END AS
ratioofwrites,
SUM(USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS
totalreadoperations,
SUM(USER_UPDATES) AS totalwriteoperations,
SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) AS totaloperations
FROM INDEX_STATS_CURR_VW /*sys.dm_db_index_usage_stats*/
--WHERE
DATABASE_NAME = 'XXXXXXXXXXXX'
-- AND
SERVER_NAME = 'XXXXXXXX'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
--ORDER
BY TotalOperations DESC
--ORDER
BY TotalReadOperations DESC
ORDER BY
TotalWriteOperations DESC
_____________________________________________________________________________________
INDEX_STATISTICS
SELECT
DATABASE_NAME,
SERVER_NAME,
TABLENAME,
INDEXNAME,
UPDATED
FROM INDEX_STAT_HEADER ISH
WHERE UPDATED IS NOT NULL
-- AND
SERVER_NAME = 'XXXXXXXXX'
-- AND
DATABASE_NAME = 'XXXXXXXX'
ORDER BY UPDATED DESC
___________________________________________________________________________________________
TOO_LARGE_INDEXES
;WITH TABLE_SIZE (SERVER_NAME,
DATABASE_NAME, TABLE_NAME, SIZE)
AS (SELECT SERVER_NAME,DATABASE_NAME,
TABLE_NAME,
PAGE_COUNT * 8 / 1024 AS size
FROM INDEX_STATS_CURR_VW
WHERE INDEX_ID IN (0,1) )
SELECT DISTINCT ISV.DATABASE_NAME,
ISV.TABLE_NAME,
TS.SIZE,
ISV.INDEX_NAME,
( ( PAGE_COUNT * 8 / 1024 ) / TS.SIZE ) * 100 AS [%_of_table],
ISV.INDEX_DESCRIPTION,
ISV.INDEX_KEYS,
ISV.INCLUDED_COLUMNS
FROM INDEX_STATS_CURR_VW ISV
INNER JOIN TABLE_SIZE TS
ON ISV.SERVER_NAME = TS.SERVER_NAME
AND ISV.DATABASE_NAME = TS.DATABASE_NAME
AND ISV.TABLE_NAME = TS.TABLE_NAME
AND TS.SIZE > 0
WHERE ISV.INDEX_ID > 1
AND
USER_UPDATES > 100 --
index must be getting update
AND
PAGE_COUNT > 100 --
small parameter tables are ok
AND ( ( PAGE_COUNT * 8 / 1024 ) / TS.SIZE ) * 100 > 25 --25%
ORDER BY TS.SIZE DESC
__________________________________________________________________________________________
HIGH_COST_INDEXES
SELECT
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT*8/1024 AS SIZE_MB,
CASE
WHEN ( SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) =
0 ) THEN NULL
ELSE ( CAST(SUM(USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS DECIMAL) / CAST(SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) ) END AS RatioOfReads,
CASE
WHEN ( SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) =
0 ) THEN NULL
ELSE ( CAST(SUM(USER_UPDATES) AS DECIMAL) / CAST(SUM(USER_UPDATES + USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS DECIMAL) ) END AS
RatioOfWrites,
SUM(USER_SEEKS +
USER_SCANS + USER_LOOKUPS) AS
TotalReadOperations,
SUM(USER_UPDATES) AS TotalWriteOperations,
SUM(USER_UPDATES +
USER_SEEKS + USER_SCANS
+
USER_LOOKUPS) AS TotalOperations
FROM INDEX_STATS_CURR_VW /*sys.dm_db_index_usage_stats*/
WHERE USER_UPDATES > (USER_SEEKS + USER_SCANS +
USER_LOOKUPS)
AND
INDEX_DESCRIPTION NOT LIKE
'%UNIQUE%' and
INDEX_DESCRIPTION NOT LIKE
'CLUSTERED%'
-- AND
SERVER_NAME = ‘XXXXXXXXX’
-- AND
DATABASE_NAME = ‘XXXXXXXX’
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
PAGE_COUNT
--ORDER
BY TotalOperations DESC
--ORDER
BY TotalReadOperations DESC
ORDER BY
TotalWriteOperations DESC
_____________________________________________________________________________________________
COMPRESSED_INDEXES
SELECT *
FROM INDEX_STATS_CURR_VW
WHERE DATA_COMPRESSION
> 0
ORDER BY
USER_UPDATES DESC
________________________________________________________________________________________
UNIQUE_INDEXES_NOT_DEFINED_UNIQUE
;WITH RECORDS_CTE (SERVER_NAME,
DATABASE_NAME, TABLENAME, INDEX_NAME, INDEX_KEYS, ROW_COUNT, READS)
AS
-- Define
the CTE query.
(SELECT DISTINCT
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
INDEX_KEYS,
ROW_COUNT,
USER_SEEKS + USER_SCANS +
USER_LOOKUPS AS reads
FROM INDEX_STATS_CURR_VW)
SELECT DISTINCT dv.SERVER_NAME,
dv.DATABASE_NAME,
dv.TABLENAME,
rows.INDEX_NAME,
DS.TYPE_DESC AS
index_desc,
rows.INDEX_KEYS,
rows.ROW_COUNT
FROM INDEX_DENSITY_VECTOR dv
INNER JOIN RECORDS_CTE rows
ON dv.TABLENAME = rows.TABLENAME
AND dv.DATABASE_NAME = rows.DATABASE_NAME
AND dv.SERVER_NAME = rows.SERVER_NAME
INNER JOIN DYNSYSINDEXES DS
ON DS.DATABASE_NAME = dv.DATABASE_NAME
AND DS.RUN_NAME LIKE dv.SERVER_NAME + '%'
AND DS.NAME = rows.INDEX_NAME
WHERE DS.IS_UNIQUE = 0
AND
ROW_COUNT / ( 1 / DENSITY ) =
1.000000000000000000
AND rows.READS > 1000 -- relatively
used tables
AND rows.ROW_COUNT > 10000 -- larger tables
only
ORDER BY ROW_COUNT DESC
__________________________________________________________________________________________
EXACT_DUPLICATE_INDEXES
SELECT
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_KEYS,
COUNT(*),
STUFF ((SELECT ', ' + 'INDEX_NAME = ' + ISV.INDEX_NAME
+ ' KEYS= ' + ISV.INDEX_KEYS
+ ' INCLUDED_COLUMNS= '
+ ISV.INCLUDED_COLUMNS
FROM INDEX_STATS_CURR_VW ISV
WHERE ISV.DATABASE_NAME
= A.DATABASE_NAME
AND ISV.SERVER_NAME = A.SERVER_NAME
AND ISV.TABLE_NAME = A.TABLE_NAME
AND ISV.INDEX_KEYS = A.INDEX_KEYS
FOR xml path('')), 1, 1, '''') AS indexes
FROM INDEX_STATS_CURR_VW A
WHERE INDEX_DESCRIPTION NOT
LIKE '%primary key%'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_KEYS
HAVING COUNT(INDEX_KEYS) > 1
ORDER BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME
__________________________________________________________________________________________________
SUBSET_DUPLICATE_INDEXES
SELECT DISTINCT O.SERVER_NAME,
O.DATABASE_NAME,
O.TABLE_NAME,
O.INDEX_NAME
AS subset_index,
O.INDEX_KEYS
AS subset_index_keys,
O.INDEX_DESCRIPTION
AS subset_index_description,
O.PAGE_COUNT * 8 / 1024 AS
subset_size_mb,
I.INDEX_NAME
AS superset_index,
I.INDEX_KEYS
AS superset_keys
FROM INDEX_STATS_CURR_VW O
LEFT JOIN INDEX_STATS_CURR_VW I
ON I.SERVER_NAME = O.SERVER_NAME
AND I.DATABASE_NAME = O.DATABASE_NAME
AND I.TABLE_NAME = O.TABLE_NAME
AND I.INDEX_KEYS <>
O.INDEX_KEYS
AND I.INDEX_KEYS LIKE O.INDEX_KEYS + ',%'
WHERE O.INDEX_DESCRIPTION
NOT LIKE '%UNIQUE%'
AND O.INDEX_DESCRIPTION NOT
LIKE 'CLUSTERED%'
AND I.INDEX_NAME IS NOT NULL
AND O.PAGE_COUNT > 0
ORDER BY O.SERVER_NAME,
O.DATABASE_NAME,
O.TABLE_NAME,
O.INDEX_KEYS
____________________________________________________________________________________________
INCLUDED_COLUMN_INDEXES
SELECT TOP 100 *
FROM INDEX_STATS_CURR_VW
WHERE INCLUDED_COLUMNS <>
'N/A'
AND
PAGE_COUNT > 0
ORDER BY len(INCLUDED_COLUMNS) DESC
_____________________________________________________________________________________________________
UNUSED_INDEXES
SELECT
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
MAX(PAGE_COUNT * 8 / 1024) AS
SIZE_MB,
SUM(USER_SEEKS_DELTA +
USER_SCANS_DELTA
+
USER_LOOKUPS_DELTA) AS
READ_COUNT,
MIN(DATEDIFF(DD, DATE, GETDATE())) AS DAYS
FROM INDEX_HISTORY
WHERE FLAG = 'M'
AND
INDEX_DESCRIPTION NOT LIKE
'%UNIQUE%'
AND
INDEX_DESCRIPTION NOT LIKE
'CLUSTERED%'
AND
INDEX_DESCRIPTION NOT LIKE
'%FILTERED%'
GROUP BY
SERVER_NAME,
DATABASE_NAME,
TABLE_NAME,
INDEX_NAME
HAVING SUM(USER_SEEKS_DELTA
+ USER_SCANS_DELTA
+
USER_LOOKUPS_DELTA) =
0
AND MAX(PAGE_COUNT * 8 / 1024) > 0
ORDER BY 5 DESC
--Indexes
not used this month but used last month
SELECT TMH.*
FROM INDEX_HISTORY TMH
INNER JOIN INDEX_HISTORY LMH
ON TMH.SERVER_NAME = LMH.SERVER_NAME
AND TMH.DATABASE_NAME = LMH.DATABASE_NAME
AND TMH.TABLE_NAME = LMH.TABLE_NAME
AND TMH.INDEX_NAME = LMH.INDEX_NAME
AND TMH.DATE = Dateadd(MONTH, 1, LMH.DATE)
WHERE TMH.FLAG = 'M'
AND LMH.FLAG = 'M'
AND TMH.INDEX_DESCRIPTION NOT
LIKE '%UNIQUE%'
AND TMH.INDEX_DESCRIPTION NOT
LIKE 'CLUSTERED%'
AND ( TMH.USER_SEEKS_DELTA
+ TMH.USER_LOOKUPS_DELTA
+ TMH.USER_SCANS_DELTA ) = 0 --REH Not used this
month
AND ( LMH.USER_SEEKS_DELTA
+ LMH.USER_LOOKUPS_DELTA
+ LMH.USER_SCANS_DELTA ) > 0 --REH was used last
month
______________________________________________________________________________________
TABLES_WITHOUT_CLUSTERED_INDEX
SELECT
CLUS.SERVER_NAME,
CLUS.DATABASE_NAME,
CLUS.TABLE_NAME,
CLUS.INDEX_NAME AS heap_table,
CLUS.INDEX_KEYS AS clustered_keys,
NONCLUS.INDEX_NAME AS nonclustered_index,
NONCLUS.INDEX_KEYS,
(
NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT
) AS
nonclustered_vs_clustered_range_count,
CLUS.USER_SEEKS AS clustered_user_seeks,
CLUS.USER_SCANS AS clustered_user_scans,
CLUS.SINGLETON_LOOKUP_COUNT AS clustered_single_lookups,
CLUS.RANGE_SCAN_COUNT AS clustered_range_scan,
NONCLUS.USER_SEEKS AS nonclustered_user_seeks,
NONCLUS.USER_SCANS AS nonclustered_user_scans,
NONCLUS.SINGLETON_LOOKUP_COUNT AS
nonclustered_single_lookups,
NONCLUS.RANGE_SCAN_COUNT AS nonclustered_range_scans,
NONCLUS.USER_UPDATES AS nonclustered_user_updates
FROM INDEX_STATS_CURR_VW CLUS
INNER JOIN INDEX_STATS_CURR_VW NONCLUS
ON
CLUS.TABLE_NAME =
NONCLUS.TABLE_NAME
AND
CLUS.DATABASE_NAME =
NONCLUS.DATABASE_NAME
AND
CLUS.SERVER_NAME =
NONCLUS.SERVER_NAME
AND
CLUS.INDEX_NAME <>
NONCLUS.INDEX_NAME
WHERE CLUS.INDEX_DESCRIPTION
LIKE 'HEAP%'
AND ( ( NONCLUS.RANGE_SCAN_COUNT
> CLUS.RANGE_SCAN_COUNT
)
OR ( NONCLUS.SINGLETON_LOOKUP_COUNT >
CLUS.SINGLETON_LOOKUP_COUNT ) )
AND
CLUS.PAGE_COUNT >
0
ORDER BY CLUS.USER_LOOKUPS DESC,
CLUS.TABLE_NAME,
(
NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT
) DESC
_____________________________________________________________________________________________
ADJUST_CLUSTERED_INDEXES
SELECT
CLUS.SERVER_NAME,
CLUS.DATABASE_NAME,
CLUS.TABLE_NAME,
CLUS.INDEX_NAME AS CLUSTERED_INDEX,
CLUS.INDEX_KEYS AS CLUSTERED_KEYS,
NONCLUS.INDEX_NAME AS NONCLUSTERED_INDEX,
NONCLUS.INDEX_KEYS,
(
NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT
) AS
NONCLUSTERED_VS_CLUSTERED_RANGE_COUNT,
CLUS.USER_SEEKS AS CLUSTERED_USER_SEEKS,
CLUS.USER_SCANS AS CLUSTERED_USER_SCANS,
CLUS.SINGLETON_LOOKUP_COUNT AS CLUSTERED_SINGLE_LOOKUPS,
CLUS.RANGE_SCAN_COUNT AS CLUSTERED_RANGE_SCAN,
NONCLUS.USER_SEEKS AS NONCLUSTERED_USER_SEEKS,
NONCLUS.USER_SCANS AS NONCLUSTERED_USER_SCANS,
NONCLUS.SINGLETON_LOOKUP_COUNT AS
NONCLUSTERED_SINGLE_LOOKUPS,
NONCLUS.RANGE_SCAN_COUNT AS NONCLUSTERED_RANGE_SCANS,
NONCLUS.USER_UPDATES
AS NONCLUSTERED_USER_UPDATES
FROM INDEX_STATS_CURR_VW CLUS
INNER JOIN INDEX_STATS_CURR_VW NONCLUS
ON
CLUS.TABLE_NAME =
NONCLUS.TABLE_NAME
AND
CLUS.DATABASE_NAME =
NONCLUS.DATABASE_NAME
AND
CLUS.SERVER_NAME =
NONCLUS.SERVER_NAME
AND
CLUS.INDEX_NAME <>
NONCLUS.INDEX_NAME
WHERE CLUS.INDEX_DESCRIPTION
LIKE 'CLUSTERED%'
AND ( ( NONCLUS.RANGE_SCAN_COUNT
> CLUS.RANGE_SCAN_COUNT
)
OR ( NONCLUS.SINGLETON_LOOKUP_COUNT >
CLUS.SINGLETON_LOOKUP_COUNT ) )
ORDER BY CLUS.USER_LOOKUPS DESC,
CLUS.TABLE_NAME,
(
NONCLUS.RANGE_SCAN_COUNT - CLUS.RANGE_SCAN_COUNT
) DESC
______________________________________________________________________________________________________
ANALYZE_INDEX_KEY_ORDER
SELECT ISV.SERVER_NAME, ISV.DATABASE_NAME,
ISV.TABLE_NAME, ISV.INDEX_NAME,
ISV.INDEX_KEYS AS
CURRENT_INDEX_ORDER,
Stuff ((SELECT ', ' + KEYCOLUMN
FROM INDEX_KEY_ORDER_VW IKO
WHERE ISV.SERVER_NAME
= IKO.SERVER_NAME
AND ISV.DATABASE_NAME = IKO.DATABASE_NAME
AND ISV.TABLE_NAME = IKO.TABLENAME
AND ISV.INDEX_NAME = IKO.INDEXNAME
ORDER BY TABLENAME,
INDEXNAME,
TOTAL_ROWS DESC
FOR xml path('')), 1, 1, '') AS
POTENTIAL_INDEX_ORDER
FROM INDEX_STATS_CURR_VW ISV
WHERE ISV.ROW_COUNT
> 1000
AND ISV.USER_SEEKS > 1000
--AND
ISV.TABLE_NAME = 'AX Table Name'
-- AND
ISV.DATABASE_NAME = 'AX Database Name'
--AND
ISV.SERVER_NAME = 'AX Database Server Name'
____________________________________________________________________________________________
INDEXES_BEING_SCANNED
SELECT TOP 100 *
FROM INDEX_STATS_CURR_VW
WHERE USER_SCANS >
0
AND
INDEX_DESCRIPTION LIKE 'NONCLUSTERED%'
ORDER BY USER_SCANS
DESC
____________________________________________________________________________________________________
SEARCH_QUERY_PLANS_FOR_INDEX_USAGE
;WITH FT_CTE2 (QUERY_PLAN_HASH,
QUERY_PLAN)
AS
(SELECT QUERY_PLAN_HASH,
QUERY_PLAN
FROM QUERY_PLANS
WHERE CONTAINS (C_QUERY_PLAN, '"INVENTDIM" AND
"INDEX SCAN"') -- find all statements scanning a specific table
--WHERE CONTAINS (C_QUERY_PLAN,
'"I_6143RECID"') — find all SQL statements that contain a specific
index
)
SELECT TOP 100 *
FROM QUERY_STATS_CURR_VW QS -- Queries from last data collection only
--FROM QUERY_STATS_VW QS -- Review queries for all
data collections
INNER JOIN FT_CTE2 FT2
ON QS.QUERY_PLAN_HASH =
FT2.QUERY_PLAN_HASH
WHERE 1 = 1
-- AND
LAST_EXECUTION_TIME > 'XXXXXXX' --
find all queries that have executed after a specific time
ORDER BY
TOTAL_ELAPSED_TIME DESC
