http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/12/03/sqlos-basics-query-and-cpu/

Printed 2014/11/26 08:24AM

SQLOS Basics - Query and CPU

By Ramkumar (LivingForSQLServer), 2012/12/03

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_id database_id session_id command request_id start_time task_address text
3EDE893F-8929-4417-B006-6A6B8D63BE51 13 53 SELECT 0 34:03.9 0x09A781C0 SELECT * 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_address parent_task_address task_state request_id database_id session_id start_time command connection_id task_address text
0x09A781C0 NULL RUNNING 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x0020B8E8 0x09A781C0 SUSPENDED 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x001FFC78 0x09A781C0 SUSPENDED 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * 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_address last_wait_type state task_address parent_task_address task_state request_id database_id session_id start_time command connection_id task_address text
0x09A320D8 PREEMPTIVE_OS_WAITFORSINGLEOBJECT RUNNING 0x09A781C0 NULL RUNNING 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x0C1860D8 CXPACKET SUSPENDED 0x0020B8E8 0x09A781C0 SUSPENDED 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * FROM tEmployee A   CROSS JOIN tEmployee B    OPTION (RECOMPILE, QUERYTRACEON 8649)
0x09AA80D8 CXPACKET SUSPENDED 0x001FFC78 0x09A781C0 SUSPENDED 0 13 53 12/4/12 2:34 AM SELECT 3EDE893F-8929-4417-B006-6A6B8D63BE51 0x09A781C0 SELECT * 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_address priority processor_group started_by_sqlservr worker_address last_wait_type state task_address parent_task_address task_state
0x7FFDAE18 0 0 1 0x09AA80D8 CXPACKET SUSPENDED 0x001FFC78 0x09A781C0 SUSPENDED
0x7FF8AE18 0 0 1 0x0C1860D8 CXPACKET SUSPENDED 0x0020B8E8 0x09A781C0 SUSPENDED
0x7FF8FE18 0 0 1 0x09A320D8 SOS_SCHEDULER_YIELD RUNNABLE 0x09A781C0 NULL RUNNABLE

-- 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_address runnable_tasks_count cpu_id status thread_address priority processor_group started_by_sqlservr worker_address last_wait_type state task_address
0x00B62040 0 1 VISIBLE ONLINE 0x7FFDAE18 0 0 1 0x09AA80D8 CXPACKET SUSPENDED 0x001FFC78
0x002E0040 0 0 VISIBLE ONLINE 0x7FF8AE18 0 0 1 0x0C1860D8 CXPACKET SUSPENDED 0x0020B8E8
0x002E0040 0 0 VISIBLE ONLINE 0x7FF8FE18 0 0 1 0x09A320D8 PREEMPTIVE_OS_WAITFORSINGLEOBJECT RUNNING 0x09A781C0

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


 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.