October 19, 2011 at 11:27 am
I want to make sure I understand thread memory correctly, could someone correct me or confirm? Let's assume no parallelism is being used and the query is being executed on a single scheduler. Also, that any hash / sort operations fit into the default query memory grant and don't spill over to disk.
Client 1 -
Opens a connection and issues one batch:
select * from table1
select * from table2
select * from table3
This client has a single connection that issues a single request so one worker thread is handling the batch for execution on a single scheduler. From a simplistic memory perspective on a 64 bit system this will be 2 MB for the worker thread and a memory grant for the query if there are any hash / sort operations. By default this query memory grant would be 1 MB.
Client 2 -
Opens a connection and issues three batches:
select * from table1
GO
select * from table2
GO
select * from table3
GO
This client has a single connection that issues three separate requests so three worker threads are handling the batches for execution on a single scheduler. From a simplistic memory perspective on a 64 bit system this will be 2 MB for each worker thread (6 MB total) and a memory grant for the query if there are any hash / sort operations. By default this query memory grant would be 1 MB.
So, in general more requests = more memory required for worker threads.
October 25, 2011 at 6:55 am
You are correct in that on an x64 instance the stacksize for each thread stack is 2MB. On startup, sql server will allocate a set, small amount of worker threads and will only create more as the thread pool gets depleted.
As for "query/workspace memory" (your correct assumptions about additional memory due to hash/sort/etc operations due to the specific execution plan created) these allocations come out of the buffer pool. You can see the amount consumed by each operation when viewing an actual (not estimated) execution plan under "Misc" -> "Memory Grant". DOP (parallelism) will also be listed under "Misc" to confirm wether or not this was single-threaded for your example queries.
Does this help or did I maybe misunderstand your post? Would you like me to provide a few of the DMVs to refer to which are related to scheduling and memory grants?
Thanks
--tz
October 25, 2011 at 9:33 am
Yeah that helped, thank you. I can lookup the DMV's thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply