The error seen as some of the counters used to create these reports are not added to the SP Diagnostic Performance counters . Below are the steps that fixed the issue in my environment .
1. From the SharePoint Manage shell run the following commands to add missing SQL counters
Add-SPDiagnosticsPerformanceCounter –category SQLServer:Locks –counter "Average Wait Time (ms)" –allinstances -databaseserver
Add-SPDiagnosticsPerformanceCounter –category LogicalDisk –counter "Disk Reads/sec" –allinstance -databaseserver
Add-SPDiagnosticsPerformanceCounter –category LogicalDisk –counter "Disk Writes/sec" –allinstance –databaseserver
2. Run the following command to ensure the new counters added are visible in the list , You should get response as below
Get-SPDiagnosticsPerformanceCounter | where {$_.providername -eq "job-diagnostics-performance-counter-sql-provider"}| FL
InstanceNames : {_Total}
MonitorAllInstances : False
CategoryName : Processor
CounterNames : {% Processor Time}
LocalizedCategoryName : Processor
ProviderName : job-diagnostics-performance-counter-sql-provider
UpgradedPersistedProperties : {}
InstanceNames :
MonitorAllInstances : False
CategoryName : Memory
CounterNames : {Available Mbytes}
LocalizedCategoryName : Memory
ProviderName : job-diagnostics-performance-counter-sql-provider
UpgradedPersistedProperties : {}
InstanceNames : {*}
MonitorAllInstances : True
CategoryName : LogicalDisk
CounterNames : {Avg. Disk Queue Length, Avg. Disk sec/Transfer,
Disk Reads/sec, Disk Writes/sec}
LocalizedCategoryName : LogicalDisk
ProviderName : job-diagnostics-performance-counter-sql-provider
UpgradedPersistedProperties : {}
InstanceNames : {*}
MonitorAllInstances : True
CategoryName : SQLServer:Locks
CounterNames : {Average Wait Time (ms)}
LocalizedCategoryName : SQLServer:Locks
ProviderName : job-diagnostics-performance-counter-sql-provider
UpgradedPersistedProperties : {}
InstanceNames : {}
MonitorAllInstances : False
CategoryName : SQLServer:Latches
CounterNames : {Average Latch Wait Time (ms)}
LocalizedCategoryName : SQLServer:Latches
ProviderName : job-diagnostics-performance-counter-sql-provider
UpgradedPersistedProperties : {}
3. Restart the Timer service on the WFE used to run SPDIAG .
4. Launch the SQL Diagnostic Studio again , Click on New project & create the project with same name again . This removes the old project & all the provider configurations are re-initialized .
5. The “SQL overview Report” should now work without throwing any errors .
Thanks!!Regards,
Manas Biswas
Microsoft Online Community Support
Please remember to click 'Mark as Answer' on the post that helps you or click 'Unmark as Answer' if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
