When diagnosing the server I/O, Spotlight indicates that a non SQL Server I/O consumption is likely the issue. For example, it indicates that number one process indicated is svchost.exe. Is this service really the cause of the I/O pressure? How does Spotlight determine the "likely" label when diagnosing the I/O pressure and where does Spotlight get the list of processes info?
The Diagnose I/O collection calculates how likely non SQL Server I/O is by collecting the Operating System process data and looking at how much I/O is being done by processes that are not SQL Server compared to how much SQL Server is using. It will say this is "likely" if something other than SQL Server is the largest I/O - in other words, if SQL Server is not the largest I/O consumer.
Given this collection is designed for diagnosing I/O problems, the statement it is making effectively becomes:
"SQL Server is experiencing I/O related issues and SQL Server is not causing the majority of the IO on the server."
In the example that svchost.exe is the number one cause of I/O pressure, Spotlight filters out any process not performing IO (i.e. where the IO column would have a 0). Hence Spotlight determines that SQL Server is definitely not causing IO issues at the time that collection ran as it has been filtered out.
The actual Windows Process list is gathered via the win32_processes WMI collection - it's the same data displayed in Windows Task Manager. It’s also displayed on Spotlight's Windows Processes drilldown.
Also Spotlight can go deeper by right-clicking on the process and go into "View process details."
