We help IT Professionals succeed at work.

index related performance counter  for SQL server DB

285 Views
Last Modified: 2018-03-21
hi,

usually we have some perfmon counter for RAM:

Index searches /sec  >1000 will have warning. >2000 will have critical message.
SQL Compilations/Sec >100 will have warning ,>300 will have critical message
Page Faults/sec,  >800 will have warning.  >1000 will have critical message.

what is the most efficiency perfmon counter to indicate index problem ?
Comment
Watch Question

Database Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

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

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
" 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

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
so the 3 x counter above can't use to check index problem?  just the index search/sec is the key counter?
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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/
marrowyungSenior Technical architecture (Data)

Author

Commented:
" 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?
marrowyungSenior Technical architecture (Data)

Author

Commented:
no more option, her you go sir.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.