Blog Post

SQLOS Basics - Query and CPU

In this article We are going to understand the basics of SQLOS - CPU scheduling.

In high level:

1. When an user connects to SQL Server instance, unique connection id and session id is assigned to the user.
DMV: sys.dm_exec_connections

2. Queries being executed by the user sessions (requests in other words) are available in below DMVs
DMV: sys.dm_exec_requests and sys.dm_exec_sql_text(plan_handle)

3. Once the execution plan of a query is generated, it is divided into one or more tasks. Number of tasks depends on query parallelism.
DMV: sys.dm_os_tasks

4. Each task is assigned to a worker. A worker is where the work actually gets done.
Maximum number of workers (assigned to SQL Server) depends on the number of CPUs and hardware architecture (32 bit or 64 bit)
Further read:
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/14/max-worker-threads-and-when-you-should-change-it.aspx

DMV: sys.dm_os_workers

5. Each worker is associated with a thread.
DMV: sys.dm_os_threads

6. Scheduler schedules CPU time for a task/worker.
When SQL Server service starts, it creates one scheduler for each logical CPU. (few more schedulers for internal purpose).
During this period, Scheduler may keep a task in RUNNING or RUNNABLE or SUSPENDED state for various reasons.
DMV: sys.dm_os_schedulers

7. Once the task is completed, all consumed resources are freed.

 

Lets confirm this flow with below experiment:

-- Create a table and insert some records in a test database.

DROP DATABASE SQLOS_SCHEDULING
GO

CREATE DATABASE SQLOS_SCHEDULING
GO

USE SQLOS_SCHEDULING
GO

CREATE TABLE tEmployee(intRoll int, strName varchar(50))
GO

SET NOCOUNT ON
INSERT INTO tEmployee VALUES(1001,'AAAA')
GO 10000

-- Get  SPID of this session. To be used later.

SELECT @@SPID

-- Run below *poor* query (parallelism is forced with 8649 traceflag).

SELECT * FROM tEmployee A
CROSS JOIN tEmployee B 
OPTION (RECOMPILE, QUERYTRACEON 8649)
GO 100 -- To run this query many times. Cancel this query once once you run and understand below queries.

Above query will take few seconds to minutes to fetch the data.

Open new user session and run below queries one by one.

Query 1: User Connection and Query as Request.

SELECT
 REQ.connection_id,
 REQ.database_id,
 REQ.session_id,
 REQ.command,
 REQ.request_id,
 REQ.start_time,
 REQ.task_address,
 QUERY.text
FROM SYS.dm_exec_requests req
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53 -- Replace 53 with SPID of the session where tEmployee query is running

 

connection_iddatabase_idsession_idcommandrequest_idstart_timetask_addresstext
3EDE893F-8929-4417-B006-6A6B8D63BE511353SELECT034:03.90x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)

 

 

-- Query 2: User quey is divided as 3 Tasks (Parallelism forced)

SELECT
 task.task_address,
 task.parent_task_address,
 task.task_state,
 REQ.request_id,
 REQ.database_id,
 REQ.session_id,
 REQ.start_time,
 REQ.command,
 REQ.connection_id,
 REQ.task_address,
 QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task on req.task_address = task.task_address or req.task_address = task.parent_task_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53

 

task_addressparent_task_addresstask_staterequest_iddatabase_idsession_idstart_timecommandconnection_idtask_addresstext
0x09A781C0NULLRUNNING0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x0020B8E80x09A781C0SUSPENDED0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x001FFC780x09A781C0SUSPENDED0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)

 

 

-- Query 3: Each task is assigned to worker

SELECT
 worker.worker_address,
 worker.last_wait_type,
 worker.state,
 task.task_address,
 task.parent_task_address,
 task.task_state,
 REQ.request_id,
 REQ.database_id,
 REQ.session_id,
 REQ.start_time,
 REQ.command,
 REQ.connection_id,
 REQ.task_address,
 QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task
 on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53

worker_addresslast_wait_typestatetask_addressparent_task_addresstask_staterequest_iddatabase_idsession_idstart_timecommandconnection_idtask_addresstext
0x09A320D8PREEMPTIVE_OS_WAITFORSINGLEOBJECTRUNNING0x09A781C0NULLRUNNING0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x0C1860D8CXPACKETSUSPENDED0x0020B8E80x09A781C0SUSPENDED0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x09AA80D8CXPACKETSUSPENDED0x001FFC780x09A781C0SUSPENDED0135312/4/12 2:34 AMSELECT3EDE893F-8929-4417-B006-6A6B8D63BE510x09A781C0SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)

 

 Query 4: User request as Tasks. Task assigned to worker. Each worker is associated with a thread

-- User Query as Request becomes Task(s)
-- Task is given to available Worker
-- Threads associated with Workers

SELECT
 thread.thread_address,
 thread.priority,
 thread.processor_group,
 thread.started_by_sqlservr,
 worker.worker_address,
 worker.last_wait_type,
 worker.state,
 task.task_address,
 task.parent_task_address,
 task.task_state,
 REQ.request_id,
 REQ.database_id,
 REQ.session_id,
 REQ.start_time,
 REQ.command,
 REQ.connection_id,
 REQ.task_address,
 QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task
 on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address
INNER JOIN sys.dm_os_threads thread on worker.thread_address = thread.thread_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53

Output (First few columns):

thread_addresspriorityprocessor_groupstarted_by_sqlservrworker_addresslast_wait_typestatetask_addressparent_task_addresstask_state
0x7FFDAE180010x09AA80D8CXPACKETSUSPENDED0x001FFC780x09A781C0SUSPENDED
0x7FF8AE180010x0C1860D8CXPACKETSUSPENDED0x0020B8E80x09A781C0SUSPENDED
0x7FF8FE180010x09A320D8SOS_SCHEDULER_YIELDRUNNABLE0x09A781C0NULLRUNNABLE

-- Query 5: CPU time is scheduled for task by Scheduler

-- User Query as Request becomes Task(s)
-- Task is given to available Worker
-- Threads associated with Workers
-- Schedulers associated with CPU schedules CPU time for Workers
SELECT
 sch.scheduler_address,
 sch.runnable_tasks_count,
 sch.cpu_id,
 sch.status,
 thread.thread_address,
 thread.priority,
 thread.processor_group,
 thread.started_by_sqlservr,
 worker.worker_address,
 worker.last_wait_type,
 worker.state,
 task.task_address,
 task.parent_task_address,
 task.task_state,
 REQ.request_id,
 REQ.database_id,
 REQ.session_id,
 REQ.start_time,
 REQ.command,
 REQ.connection_id,
 REQ.task_address,
 QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task
 on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address
INNER JOIN sys.dm_os_threads thread on worker.thread_address = thread.thread_address
INNER JOIN sys.dm_os_schedulers sch on sch.scheduler_address = worker.scheduler_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53

Output (First few columns)

scheduler_addressrunnable_tasks_countcpu_idstatusthread_addresspriorityprocessor_groupstarted_by_sqlservrworker_addresslast_wait_typestatetask_address
0x00B6204001VISIBLE ONLINE0x7FFDAE180010x09AA80D8CXPACKETSUSPENDED0x001FFC78
0x002E004000VISIBLE ONLINE0x7FF8AE180010x0C1860D8CXPACKETSUSPENDED0x0020B8E8
0x002E004000VISIBLE ONLINE0x7FF8FE180010x09A320D8PREEMPTIVE_OS_WAITFORSINGLEOBJECTRUNNING0x09A781C0

Reference:

Wrox press Professional SQL Server 2008 Internals and Troubleshooting book

http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

http://translate.google.co.in/translate?hl=en&sl=zh-CN&u=http://blogs.msdn.com/b/apgcdsd/archive/2011/11/24/sql-server-sqlos.aspx&prev=/search%3Fq%3Dsqlos%2Bsql%2Bserver%26start%3D30%26hl%3Den%26sa%3DN%26tbo%3Dd%26biw%3D1280%26bih%3D619&sa=X&ei=hhK3UNDXH5CqrAfhx4DIDg&ved=0CDgQ7gEwATge


 

Rate

Share

Share

Rate