June 13, 2006 at 6:49 am
I have an MSDE installation that is consistently over the 8 query limit even though it is doing almost no work. SP_WHO 'ACTIVE' returns:
1 0 background sa 0 NULL LAZY WRITER
2 0 background sa 0 master SIGNAL HANDLER
3 0 sleeping sa 0 NULL LOG WRITER
4 0 background sa 0 NULL LOCK MONITOR
5 0 background sa 0 master TASK MANAGER
6 0 background sa 0 master TASK MANAGER
7 0 sleeping sa 0 NULL CHECKPOINT SLEEP
8 0 background sa 0 master TASK MANAGER
9 0 background sa 0 master TASK MANAGER
10 0 background sa 0 master TASK MANAGER
11 0 background sa 0 master TASK MANAGER
12 0 background sa 0 master TASK MANAGER
What exactly is TASK MANAGER and is there a way to cut down on how many open/active SPID's it uses? I've looked in BOL and "TASK MANAGER" is not addressed.
TIA. RH
June 13, 2006 at 7:35 am
While I would still love to hear exactly what the TASK MANAGER is here under the CMD column, it turns out to not have anything to do with my issue. I ran profiler against the DB and found that the 27 other SPID's that are ommitted from SP_WHO 'ACTIVE' are actually very active.
Biztalk is sitting on this MSDE instance and it apparently is continually checknig statuses, messages, etc.
So, now my question becomes, why if Profiler can see track 1057 lines of RPC:Completed and SQL:BatchCompleted from these 27 SPID's does EXEC SP_WHO 'ACTIVE' ommit them as inactive? I can litterally hold me finger on the F5 key in QA and not a single SPID blips as active.
June 13, 2006 at 7:37 am
Also, those 1057 batches tracked by Profiler occurred in just 40 seconds. RH
June 13, 2006 at 8:23 am
Some activity happens too fast for sp_who or sp_who2 to catch.
-SQLBill
June 13, 2006 at 8:28 am
To follow up on my post......I can submit a command that updates every single row in the table.
UPDATE mytable
SET mycolumn = 'new value'
Let's say that takes 15 minutes to complete. My connection will not be active for that 15 minutes. It only needs to be active for the second that it takes to send that command. Then it will be inactive until SQL Server returns the (n Rows Affected). Which again will only keep the connection active for less than a second. But I have a connection for the whole 15 minutes.
-SQLBill
June 13, 2006 at 8:30 am
Gotcha. Thanks. RH
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply