Match c++ / ado thead in app to sql spid

Question

Match c++ / ado thead in app to sql spid

jason craig-411431

SSC Rookie

Points: 38
More actions
November 9, 2011 at 6:53 am

#246500

Is there a dmv to query or some other method that I can use to tie my app's thread ids back to their corresponding spid?
I can narrow down things by adding the hostname and hostprocess id to my where clause but is there something I can capture that will get down to the thread level from the db side?
From the app side I'll have it execute a select @@spid but would like an option on the db side as well.
Many thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125596 More actions · Answer 1

jason craig-411431 (11/9/2011)
Is there a dmv to query or some other method that I can use to tie my app's thread ids back to their corresponding spid?
I can narrow down things by adding the hostname and hostprocess id to my where clause but is there something I can capture that will get down to the thread level from the db side?
From the app side I'll have it execute a select @@spid but would like an option on the db side as well.
Many thanks

I don't believe that SQL Server has any knowledge of your application, other than what information is provided in the login credentials. If your application is using connection pooling, then the same SPID will be used by multiple sessions or threads of your application, and I don't believe SQL Server even knows how many app sessions are using that SPID; pooling is managed by externally by ADO.NET or something.

The following article describes how to get the kernel processing id for a SQL Server SPID.

http://support.microsoft.com/kb/117559

However, I think what you're asking for is same kind of ID but for the application session that's using a specific SPID.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

jason craig-411431 SSC Rookie Points: 38 More actions · Answer 2

Thanks for the thought, but no that is the sql server thread running the query.

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 3

You can run an ETW trace on the client to capture detailed tracing from all of the sql drivers ( SQLNCLI, SQLNCLI10, ODBC, MDAC, .net SqlClient, .net DataSet/Table/Row/Adapter's, etc) including capturing thread stack dumps, connection pool information, etc.

See "Data Access Tracing in Sql Server 2008" http://msdn.microsoft.com/en-us/library/cc765421(v=sql.100).aspx

Also see "Integrating Microsoft SQL Server Event Tracing with OS-Level Events and Database Client Events" http://channel9.msdn.com/Events/TechEd/NorthAmerica/2010/DAT402