<div>
hi,<br />
<br />
tks too, but this post is more on index problem. any other counter designed to indicate index problem like fragmentation.
</div>


<div>
Right but isn't that the answer to your question &quot;what is the most efficiency perfmon counter to indicate index problem ?&quot;<br />
<br />
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:<br />
<br />
SELECT ss.name AS schname,OBJECT_NAME(i.OBJE<wbr />CT_ID) AS tabname, i.name AS IndexName, indexstats.avg_fragmentati<wbr />on_in_perc<wbr />ent as frag,ddps.row_count <br />
FROM sys.dm_db_index_physical_s<wbr />tats(DB_ID<wbr />(), NULL, NULL, NULL, NULL) indexstats<br />
&nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.indexes i <br />
&nbsp; INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID<br />
&nbsp; INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID &nbsp; &nbsp;<br />
&nbsp; INNER JOIN sys.tables st ON o.OBJECT_ID = st.object_id<br />
&nbsp; INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id <br />
&nbsp; AND i.index_id = ddps.index_id <br />
&nbsp; AND o.is_ms_shipped = 0 AND ddps.row_count &gt;&nbsp;0<br />
&nbsp; AND i.name NOT LIKE 'sys%'<br />
&nbsp; &nbsp; &nbsp; &nbsp;ON i.object_id = indexstats.object_id<br />
&nbsp; &nbsp; &nbsp; &nbsp;AND i.index_id = indexstats.index_id<br />
&nbsp; &nbsp; &nbsp; &nbsp;WHERE indexstats.avg_fragmentati<wbr />on_in_perc<wbr />ent &gt;&nbsp;10--You can specify the percent as you want<br />
&nbsp; &nbsp; &nbsp; &nbsp;ORDER BY indexstats.avg_fragmentati<wbr />on_in_perc<wbr />ent DESC
</div>


<div>
ok. this is the only metric you will do when checking index problem, right? so this is not sth perfmon can tell then.<br />
<br />
so this script is not that intelligence to show we should do reorg. or rebuild, right?<br />
<br />
depends on the 5 and 30 rules from MS again ?
</div>


<div>
lcohan,<br />
<br />
do you have the full script of what you shown above?<br />
<br />
I will use ola solution but need a script to find out any index has fragmentation level higher than no. of percentage.
</div>


<div>
&quot; need a script to find out any index has fragmentation level higher than no. of percentage.&quot; - well isn't it exactly what I posted above? <br />
...<br />
WHERE indexstats.avg_fragmentati<wbr />on_in_perc<wbr />ent &gt;&nbsp;10--You can specify the percent as you want<br />
...<br />

<pre><code class="code-10 nolinks" id="code-20-42093886-1"><span>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 </span>
<span>FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats</span>
<span>       INNER JOIN sys.indexes i </span>
<span>  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID</span>
<span>  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID    </span>
<span>  INNER JOIN sys.tables st ON o.OBJECT_ID = st.object_id</span>
<span>  INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id </span>
<span>  AND i.index_id = ddps.index_id </span>
<span>  AND o.is_ms_shipped = 0 AND ddps.row_count &gt; 0</span>
<span>  AND i.name NOT LIKE 'sys%'</span>
<span>       ON i.object_id = indexstats.object_id</span>
<span>       AND i.index_id = indexstats.index_id</span>
<span>       WHERE indexstats.avg_fragmentation_in_percent &gt; 10--You can specify the percent as you want</span>
<span>       ORDER BY indexstats.avg_fragmentation_in_percent DESC</span>
</code></pre>
</div>


<div>
so the 3 x counter above can't use to check index problem? &nbsp;just the index search/sec is the key counter?
</div>


<div>
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:<br />
<br />
<a href="https://logicalread.com/monitoring-sql-server-index-fragmentation-w02/#.WPeKFU3D8ic" rel="ugc">https://logicalread.com/monitoring-sql-server-index-fragmentation-w02/#.WPeKFU3D8ic</a><br />
<a href="http://www.sql-server-performance.com/2008/monitoring-index-fragmentation/" rel="ugc">http://www.sql-server-performance.com/2008/monitoring-index-fragmentation/</a><br />
<br />
&nbsp;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:<br />
<br />
<a href="https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/" rel="ugc">https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/</a>
</div>


<div>
&quot; performance<br />
ADVERTISEMENT<br />
<br />
&nbsp;monitor can help identifying a possible index related issue&quot;<br />
<br />
you mean perfmon has counter to measure index problem other than index fragmentation ? what is that if so?
</div>


<div>
no more option, her you go sir.
</div>


<div>
<div class="content wysiwyg-content">
hi,<br />
<br />
usually we have some perfmon counter for RAM:<br />
<br />
Index searches /sec &nbsp;&gt;1000 will have warning. &gt;2000 will have critical message.<br />
SQL Compilations/Sec &gt;100 will have warning ,&gt;300 will have critical message<br />
Page Faults/sec, &nbsp;&gt;800 will have warning. &nbsp;&gt;1000 will have critical message.<br />
<br />
what is the most efficiency perfmon counter to indicate index problem ?
</div>
</div>


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 ?

index related performance counter  for SQL server DB

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.