Everyone is a DBA (XIII) index information collection script compilation

Where's the problem? I don't know, I have to find a DBA.

Where are the DBAs? Quit the job!! Wipe!!!

When programmers are nowhere to seek help, they have to try to save themselves and become "pseudo-DBAs".

Index

1. Find out which tables the Index needs to improve
2. To find out which tables in the specified database the Index needs to be improved
3. Determine if the SP needs optimization based on the cached query plan
4. Find that the Index is written far more than the reading table
5. View Statistics last update time for Index
6. See which Index is most frequently modified
7. View Index Fragmentation Index
8. Which Index has the most active read operation
9. Which Index has the most active write operations
10. View the number of Buffer used by Index
11. The index is ranked according to the IO Latch wait request

Find out which tables the Index needs to improve

SELECT CONVERT (DECIMAL (2), User_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) as [Index_advantage]    , MIG S.last_user_seek    , mid.[statement] as [Database.Schema.Table]    , Mid.equality_columns    , Mid.inequality_ Columns,    mid.included_columns    , Migs.unique_compiles    , Migs.user_seeks    , Migs.avg_total_user_cost    , Migs.avg_user_impactfrom sys.dm_db_missing_index_group_stats as MiGs with (NOLOCK) INNER JOIN sys.dm_db_missing_ Index_groups as MiG with (NOLOCK) on migs.group_handle = Mig.index_group_handleinner JOIN sys.dm_db_missing_index_ Details as mid with (NOLOCK) on mig.index_handle = Mid.index_handleorder by Index_advantage descoption (RECOMPILE);

The data that is queried here simply indicates that the data addressing time is a bit long, not necessarily the absence of an index.

To find out which tables in the specified database the Index needs to be improved

SELECT DISTINCT CONVERT (DECIMAL (2), User_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) as [index_advantage]< C0/>,migs.last_user_seek    , mid.[statement] as [Database.Schema.Table]    , Mid.equality_columns    , Mid.inequality_columns,    mid.included_columns    , Migs.unique_compiles    , Migs.user_seeks    , Migs.avg_ Total_user_cost    , Migs.avg_user_impact    , object_name (mid.[object_id]) as [table NAME]    , p.rows as [table Rows]from Sys.dm_db_missing_index_group_stats as MiGs with (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups as MiG with (NOLOCK) On migs.group_handle = Mig.index_group_handleinner JOIN sys.dm_db_missing_index_details as Mid with (NOLOCK) on Mig.index _handle = Mid.index_handleinner JOIN sys.partitions as P with (NOLOCK) on p.[object_id] = Mid.[object_id]where Mid.databas e_id = db_id () ORDER by Index_advantage descoption (RECOMPILE);

Determine if the SP needs optimization based on the cached query plan

SELECT TOP object_name (Objectid) as [ObjectName]    , Query_plan    , Cp.objtype    , Cp.usecountsfrom sys.dm_ Exec_cached_plans as CP with (NOLOCK) Cross APPLY sys.dm_exec_query_plan (cp.plan_handle) as Qpwhere CAST (Query_plan as NVA Rchar (MAX)) like N '%missingindex% ' and    dbid = db_id () ORDER by Cp.usecounts descoption (RECOMPILE);

Find that the Index is written far more than the reading table

SELECT object_name (s.[object_id]) as [Table NAME]    , I.[name] as [Index name]    , i.index_id    , i.is_disabled    , i.is_hypothetical    , I.has_filter    , I.fill_factor    , user_updates as [Total writes]    , User_seeks + User_scans + user_lookups as [total Reads]    , User_updates-(user_seeks + User_scans + user_lookups) as [Difference]fro M Sys.dm_db_index_usage_stats as S with (NOLOCK) INNER joins sys.indexes as I with (NOLOCK) on s.[object_id] = i.[object_id]    and i.index_id = S.index_idwhere objectproperty (s.[object_id], ' isusertable ') = 1    and s.database_id = db_id () and    user_updates > (user_seeks + User_scans + user_lookups) and    i.index_id > 1 ORDER by [difference] desc    , [Total writes] desc    , [total Reads] ascoption (RECOMPILE);

Because the index is written much more than read, it seems that index does not help much, but it needs to be judged by the business need to drop the index.

View Statistics last update time for Index

SELECT schema_name (o.[schema_id]) + N '. ' + O.[name] as [object NAME]    , o.type_desc as [object Type]    , i.[name] as [i Ndex Name]    , Stats_date (i.[object_id], i.index_id) as [Statistics DATE]    , s.auto_created    , S.no_recompute    , s.user_created    , St.row_count    , St.used_page_countfrom sys.objects as O with (NOLOCK) INNER JOIN Sys.indexes as I with (NOLOCK) in o.[object_id] = I.[object_id]inner JOIN sys.stats as S with (NOLOCK) on i.[object_id] = S.[OBJECT_ID] and    i.index_id = S.stats_idinner JOIN sys.dm_db_partition_stats as St with (NOLOCK) on o.[object_id] = s T.[OBJECT_ID]    and i.[index_id] = St.[index_id]where O.[type] In (        ' U '        , ' V '        ) and    St.row_ Count > 0ORDER by Stats_date (i.[object_id], i.index_id) descoption (RECOMPILE);

Resources:

• Statistics
• UPDATE STATISTICS (Transact-SQL)
• SP_UPDATESTATS (Transact-SQL)
• Rebuilding Indexes vs. Updating Statistics
• Does a re-index update statistics?
• SQL Server Index and Statistics maintenance

See which Index is most frequently modified

SQL Server R2

SELECT TableName = object_name (s.[object_id])    , SchemaName = schema_name (o.[schema_id])    , IndexName = I.[name]    , User_updates    , I.is_primary_keyfrom sys.dm_db_index_usage_stats sjoin sys.objects O on s.[object_id] = O.[ Object_id]join sys.indexes i on s.[object_id] = i.[object_id] and    s.index_id = I.index_idwhere objectproperty (s.[ OBJECT_ID], ' ismsshipped ') = 0 and    user_seeks = 0 and    User_scans = 0 and    user_lookups = 0 and I.name is    N OT NULL--Ignore HEAP indexes. ORDER by User_updates DESC

The user_updates counter indicates the level of maintenance on the index caused by INSERT, UPDATE, or delete oper Ations on the underlying table or view.

SQL Server 2012

SELECT O.[name] As [Object name],    o.[object_id]    , O.type_desc    , S.[name] as [Statistics name]    , S.stats_ ID    , S.no_recompute    , s.auto_created    , Sp.modification_counter    , Sp.rows    , sp.rows_sampled    , Sp.last_updatedfrom sys.objects as O with (NOLOCK) INNER JOIN sys.stats as S with (NOLOCK) on s.object_id = O.object _idcross APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) as Spwhere O.type_desc not in (        N ' system_table '        , N ' internal_table '        )    and Sp.modification_counter > 0ORDER by Sp.modification_counter DESC    , O.[name]option (RECOMPILE);

View Index Fragmentation Index

SELECT db_name (ps.database_id) as [database name]    , object_name (ps.[object_id]) as [OBJECT name]    , I.[name] as [ Index Name]    , ps.index_id    , Ps.index_type_desc    , Ps.avg_fragmentation_in_percent    , Ps.fragment_ Count    , Ps.page_count    , I.fill_factor    , I.has_filter    , I.filter_definitionfrom sys.dm_db_index_ Physical_stats (db_id (), NULL, NULL, NULL, N ' LIMITED ') as Psinner JOIN sys.indexes as I with (NOLOCK) on ps.[object_id] = i . [OBJECT_ID]    and ps.index_id = I.index_idwhere ps.database_id = db_id () and    ps.page_count > 2500ORDER by Ps.avg_fragmentation_ In_percent descoption (RECOMPILE);

Resources:

• Stop worrying about SQL Server fragmentation
• Importance of index maintenance
• Reorganize and Rebuild Indexes
• Fragmentation and Index Maintenance Tips
• Index fragmentation– "If it isn ' t broken, don ' t fix it"

Which Index has the most active read operation

SELECT object_name (s.[object_id]) as [ObjectName]    , I.[name] as [IndexName]    , i.index_id    , User_seeks + user _scans + user_lookups as [Reads]    , s.user_updates as [writes]    , I.type_desc as [Indextype]    , i.fill_factor as [ FillFactor]    , I.has_filter    , I.filter_definition    , S.last_user_scan    , S.last_user_lookup    , S.last_user_seekfrom 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]where objectproperty (s.[object_id], ' isusertable ') = 1 and    i.index_id = s.index_id    and s.database_id = db_id () ORDER by User_seeks + User_scans + user_lookups descoption (RECOMPILE);

Which Index has the most active write operations

SELECT object_name (s.[object_id]) as [ObjectName]    , I.[name] as [IndexName]    , i.index_id    , s.user_updates As [writes]    , User_seeks + User_scans + user_lookups as [Reads]    , i.type_desc as [Indextype]    , i.fill_factor as [FillFactor]    , I.has_filter    , I.filter_definition    , S.last_system_update    , S.last_user_updatefrom Sys.dm_db_index_usage_stats as S with (NOLOCK) INNER joins sys.indexes as I with (NOLOCK) on s.[object_id] = I.[OBJECT_ID]WH ERE objectproperty (s.[object_id], ' isusertable ') = 1 and    i.index_id = s.index_id and    s.database_id = db_id () ORDER by S.user_updates Descoption (RECOMPILE);

View the number of Buffer used by Index

SELECT TOP Obj.[name] as TableName, I.[name] as IndexName, I.[type_desc] as Indextype, COUNT (*) as Buffered_pa Ge_count, Count (*) * 8192/(1024x768 * 1024x768) as BUFFER_MB, Obj.index_idfrom sys.dm_os_buffer_descriptors as Bdinner JOI N (SELECT object_name (object_id) as name, index_id, allocation_unit_id, object_id from Sys.all            Ocation_units as au INNER JOIN sys.partitions as P on au.container_id = p.hobt_id and (Au.type = 1        OR Au.type = 3) UNION all SELECT object_name (object_id) as name, index_id , allocation_unit_id, object_id from sys.allocation_units as au INNER JOIN sys.partitions as P on Au.contain er_id = p.hobt_id and Au.type = 2) as obj on bd.allocation_unit_id = Obj.allocation_unit_idleft JOIN sys.indexe s i on i.object_id = obj.object_id and i.index_id = Obj.index_idwhere database_id = db_id () GROUP by Obj.name, obj.in DEX_ID, I.[name],I.[type_desc]order by Buffered_page_count Desc 

The index is ranked according to the IO Latch wait request

SELECT Object_schema_name (ios.object_id) + '. ' + object_name (ios.object_id) as table_name    , I.[name] as index_name< C3/>,page_io_latch_wait_count    , Page_io_latch_wait_in_ms    , CAST (1. * PAGE_IO_LATCH_WAIT_IN_MS/NULLIF (Page_ Io_latch_wait_count, 0) as DECIMAL (2) as Page_io_avg_lock_wait_ms    , Page_latch_wait_count    , Page_latch_ Wait_in_ms    , CAST (1. * PAGE_LATCH_WAIT_IN_MS/NULLIF (page_latch_wait_count, 0) as DECIMAL (2)) as Page_avg_lock_ Wait_msfrom sys.dm_db_index_operational_stats (db_id (), NULL, NULL, NULL) Iosinner JOIN sys.indexes i on i.object_id = iOS. object_id    and i.index_id = Ios.index_idwhere objectproperty (ios.object_id, ' isusertable ') = 1ORDER by 3 DESC

Everyone is a DBA the index of the article series:

Serial number	Name
1	Everyone is a DBA (I) SQL Server Architecture
2	Everyone is DBA (II) SQL Server Metadata
3	Everyone is a DBA (III) SQL Server Scheduler
4	Everyone is a DBA (IV) SQL Server memory Management
5	Everyone is a DBA (V) SQL Server database files
6	Everyone is a DBA (VI) SQL Server transaction log
7	Everyone is DBA (VII) B-Tree and + + Tree
8	Everyone is a DBA (VIII) SQL Server page storage structure
9	Everyone is DBA (IX) Server information collection script compilation
10	Everyone is a DBA (X) Resource Information Collection script compilation
11	Everyone is DBA (XI) I/O information collection script compilation
12	Everyone is DBA (XII) Query information collection script compilation
13	Everyone is a DBA (XIII) index information collection script compilation
14	Everyone is a DBA (XIV) Stored Procedure information collection script compilation
15	Everyone is DBA (XV) lock information Collection script compilation

Everyone is a DBA (XIII) index information collection script compilation