locked
execution mode of sqlserver RRS feed

  • Question

  • hello
    I wonder if sqlserver works the same way as iis, let's say I have a system in aspnet with sqlserver and for each http request can be a thread of execution and ram consumption in sqlserver ...

    if so if I can use my page aspnet have multiple threads of execution in sqlserver, and
    I can know how much RAM consumed by each thread .....
    Wednesday, September 25, 2013 3:21 AM

Answers

  • Perfect the explanation:

    thanks ..

    • Marked as answer by neonash Saturday, September 28, 2013 8:27 PM
    Saturday, September 28, 2013 8:27 PM

All replies

  • Try

    SP_WHO2

    Also refer CPUTime ( in milisecond ) and DiskIO (in byte).

    Refer this link, http://support.microsoft.com/kb/117559


    Regards, RSingh


    Wednesday, September 25, 2013 3:48 AM
  • Each thread will create a separate session in SQL Server and you can track the execution statistics of each of them separately. Try running the following query:

    SELECT 
    	r.Session_id,
    	object_name(st.objectid) as ObjectName, 
    	r.Status,
    	r.granted_query_memory
    	FROM
    	sys.dm_exec_requests r
    CROSS APPLY 
    	sys.dm_exec_sql_text(sql_handle) AS st

    The column "granted_query_memory" shows the "Number of pages allocated to the execution of a query on the request.". There is a bunch of other information you can grab from sys.dm_exec_requests. See the documentation here.


    My Blog


    Wednesday, September 25, 2013 5:21 AM
  • Hi,

    When an application authenticates to SQL Server it establishes a connection in the context of a session, which is identified by a session_id (in older versions of SQL Server this was called a SPID). You can view a list of all authenticated sessions by querying the sys.dm_exec_sessions DMV.

    When an execution request is made within a session, SQL Server divides the work into one or more tasks and then associates a worker thread to each task for its duration. Each thread can be in one of three states (that you need to care about):

    • Running — A processor can only execute one thing at a time and the thread currently executing on a processor will have a state of running.
    • Suspended — SQL Server has a co-operative scheduler (see below) so running threads will yield the processor and become suspended while they wait for a resource. This is what we call a wait in SQL Server.
    • Runnable — When a thread has finished waiting, it becomes runnable which means that it’s ready to execute again. This is known as a signal wait.

    If no worker threads are available and max worker threads has not been reached, then SQL Server will allocate a new worker thread. If the max worker threads count has been reached, then the task will wait with a wait type of THREADPOOL until a thread becomes available. Waits and wait types are covered later in this section.

    The default max workers count is based on the CPU  architecture and the number of logical processors.

    Please refer to the article below for more information.

    SQL Server 2012 : SQL Server Architecture - SQL SERVER’S EXECUTION MODEL AND THE SQLOS
    http://mscerts.programming4.us/sql_server/sql%20server%202012%20%20%20sql%20server%20architecture%20-%20sql%20server%E2%80%99s%20execution%20model%20and%20the%20sqlos.aspx

    Thanks

    Candy Zhou

    • Edited by Candy_Zhou Friday, September 27, 2013 6:26 AM edit
    Friday, September 27, 2013 6:26 AM
  • Perfect the explanation:

    thanks ..

    • Marked as answer by neonash Saturday, September 28, 2013 8:27 PM
    Saturday, September 28, 2013 8:27 PM