October 23, 2020 at 1:21 am
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!
October 23, 2020 at 1:33 pm
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
October 24, 2020 at 6:33 am
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!
October 26, 2020 at 12:36 am
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!
October 26, 2020 at 12:18 pm
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