Comments (1)
Hi. I am using an standard functionality: sys.dm_db_missing_index_group_stats and show only indexes which applied by (i.UserImpact * i.TotalReads) / MAX(i.UserImpact * i.TotalReads) OVER()
condition
Try to use this code:
IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
DROP TABLE #Indexes
SELECT ObjectID = d.[object_id]
, UserImpact = gs.[avg_user_impact]
, TotalReads = gs.[user_seeks] + gs.[user_scans]
, TotalSeeks = gs.[user_seeks]
, TotalScans = gs.[user_scans]
, LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek])
, IndexColumns =
CASE
WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL
THEN d.[equality_columns] + ', ' + d.[inequality_columns]
WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL
THEN d.[equality_columns]
ELSE d.[inequality_columns]
END
, IncludedColumns = d.[included_columns]
INTO #Indexes
FROM sys.dm_db_missing_index_groups g WITH(NOLOCK)
JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle]
JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle]
WHERE d.[database_id] = DB_ID()
SELECT i.ObjectID
, ObjectName = o.[name]
, SchemaName = s.[name]
, Fragmentation = CAST(100. * (i.UserImpact * i.TotalReads) / MAX(i.UserImpact * i.TotalReads) OVER() AS FLOAT)
, i.TotalReads
, i.TotalSeeks
, i.TotalScans
, i.LastUsage
, i.IndexColumns
, i.IncludedColumns
FROM #Indexes i
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
JOIN sys.schemas s WITH(NOLOCK) ON o.[schema_id] = s.[schema_id]
from sqlindexmanager.
Related Issues (20)
- Nuget package HOT 1
- Feature Request : show all indexes HOT 4
- No option to use existing index options on rebuild etc? HOT 7
- DevExpress License HOT 1
- Feature Request
- Feature Request: Index Progress Indicator HOT 1
- Feature request: refresh only filtered object HOT 2
- Tooltips to describe icons HOT 3
- Tooltips Flags
- Version 65 runs without problems Version 66 hangs when starting HOT 6
- Could not find database HOT 22
- Ошибка при анализе БД HOT 4
- Не могу работать с другими базами, если одна из них битая HOT 7
- Error thrown and reported as requested HOT 3
- 'ONLINE' is not a recognized ALTER INDEX REBUILD PARTITION option. HOT 11
- Feature request: Index history
- Bug: Filtering by Table Name HOT 1
- Could not find table or object ID ... Check system catalog HOT 2
- When using on a server with a VERY large number of databases the initial population of the databases times out
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlindexmanager.