Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don’t mean the usual server process (SPID), but the system processes that append “s” onto the end of a SPID (eg .spid29s) as seen when looking at the SQL Server Logs.
The system processes are seen a lot when a server is first started.
Best Answer
So I actually went and tried this myself.
The "s" suffix in the log is only for display purposes to show that it was a system SPID. It's not actually part of the SPID.
In Profiler, there's an
IsSystemcolumn that you can filter on, but otherwise, the system SPIDs show up as per normal (i.e., "spid25s" is simply SPID 25), and you can filter as normal using theSPIDcolumn.I'm not sure what problem you're having with that, as I was able to capture activity on the system SPIDs during my test.
That said, since traces don't persist on instance restart, it might be difficult, or impossible to capture startup activity immediately as the server comes up. You could start a trace by executing a startup stored procedure, or create an Extended Events session that does persist on startup.
While I didn't go to either of those extents, I tried resuming a trace using Profiler while I clicked Yes on the Service Control dialog to start the database engine service. I didn't see any activity at all.
Just because a process (system or otherwise) has activity (disk I/O, CPU, etc.) associated with it doesn't necessarily mean the process is running a T-SQL statement. It's possible there just isn't anything to see and analyze through this channel.
If you're having issues with slow startup, it may be more fruitful to look at the SQL Server error logs to see if there are any long-running operations that stick out.