index related performance counter for SQL server DB

hi,

tks too, but this post is more on index problem. any other counter designed to indicate index problem like fragmentation.

Right but isn't that the answer to your question "what is the most efficiency perfmon counter to indicate index problem ?"

in order to detect index fragmentation and decide if a REBUILD or REORG (which doesnt do much in my opion) is needed you will have to run some SQL code against that database like below:

SELECT ss.name AS schname,OBJECT_NAME(i.OBJECT_ID) AS tabname, i.name AS IndexName, indexstats.avg_fragmentation_in_percent as frag,ddps.row_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
       INNER JOIN sys.indexes i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID    
  INNER JOIN sys.tables st ON o.OBJECT_ID = st.object_id
  INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id
  AND i.index_id = ddps.index_id
  AND o.is_ms_shipped = 0 AND ddps.row_count > 0
  AND i.name NOT LIKE 'sys%'
       ON i.object_id = indexstats.object_id
       AND i.index_id = indexstats.index_id
       WHERE indexstats.avg_fragmentation_in_percent > 10--You can specify the percent as you want
       ORDER BY indexstats.avg_fragmentation_in_percent DESC

ok. this is the only metric you will do when checking index problem, right? so this is not sth perfmon can tell then.

so this script is not that intelligence to show we should do reorg. or rebuild, right?

depends on the 5 and 30 rules from MS again ?

lcohan,

do you have the full script of what you shown above?

I will use ola solution but need a script to find out any index has fragmentation level higher than no. of percentage.

" need a script to find out any index has fragmentation level higher than no. of percentage." - well isn't it exactly what I posted above?
...
WHERE indexstats.avg_fragmentation_in_percent > 10--You can specify the percent as you want
...

SELECT ss.name AS schname,OBJECT_NAME(i.OBJECT_ID) AS tabname, i.name AS IndexName, indexstats.avg_fragmentation_in_percent as frag,ddps.row_count 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
       INNER JOIN sys.indexes i 
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID    
  INNER JOIN sys.tables st ON o.OBJECT_ID = st.object_id
  INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id 
  AND i.index_id = ddps.index_id 
  AND o.is_ms_shipped = 0 AND ddps.row_count > 0
  AND i.name NOT LIKE 'sys%'
       ON i.object_id = indexstats.object_id
       AND i.index_id = indexstats.index_id
       WHERE indexstats.avg_fragmentation_in_percent > 10--You can specify the percent as you want
       ORDER BY indexstats.avg_fragmentation_in_percent DESC

Select allOpen in new window

tks.

so the 3 x counter above can't use to check index problem?  just the index search/sec is the key counter?

The counters from performance monitor can help identifying a possible index related issue in SQL but not the actual fragmentation you are after. There are various articles about how to monitor fragmentation like:

https://logicalread.com/monitoring-sql-server-index-fragmentation-w02/#.WPeKFU3D8ic
http://www.sql-server-performance.com/2008/monitoring-index-fragmentation/

 and some products that do it however they are based on SQL queries not perf mon counter to my knowledge. Also this article below offers good insight about index fragmentation:

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

" performance
ADVERTISEMENT

 monitor can help identifying a possible index related issue"

you mean perfmon has counter to measure index problem other than index fragmentation ? what is that if so?

no more option, her you go sir.