why cannot use ClientProcessID to trace data in SQL Server Profile.

  • A C/S system is running on a SQL Server database, and I get its ClientProcessID under task manager, when I use ClientProcessID to trace running sql script, but SQL profile can't capture any data, if I remove ClientProcessID filter, it can capture data. I can make sure the value of ClientProcessID correct. Could you please tell me why? thanks so much!

  • It would be easier to give a complete answer with the Profiler events and filter. However, on a guess, the process id changed? They're not always the same. If a connection drops & reconnects, you can have a different ID. Or, you're trying to capture say, rpc_completed, but the procedure hasn't completed, so you won't see anything. Also, since we're talking 2012, Extended Events are a radically more efficient way to capture behaviors.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks Grant Fritchey!  The connection process ID wasn't changed, when I login system (C/S system), I check its process ID under windows task manager, and I didn't log out this C/S system, and checked the process ID is same when I found SQL Server profiler didn't capture any data. I captured all the event names ended with completed, when I removed the process ID filter , it showed captured data in SQL profiler right now, I didn't know the reason, thank you!

     

  • Grant Fritchey wrote:

    It would be easier to give a complete answer with the Profiler events and filter. However, on a guess, the process id changed? They're not always the same. If a connection drops & reconnects, you can have a different ID. Or, you're trying to capture say, rpc_completed, but the procedure hasn't completed, so you won't see anything. Also, since we're talking 2012, Extended Events are a radically more efficient way to capture behaviors.

    Dear Grant Fritchey, thanks for your kind help!

    After double checked, there is a application server ( that is the middleware ) for this system, when I run the function of c/s client, it calls the application server to execute the function and return the result to c/s client, so the SQL Script should be performed the middleware system, so SQL Server profile couldn't capture the sql scripts when I filtered using clientprocessID, I  guess this is the reason, thanks!

  • Makes sense.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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