When I saw that Matt Velic's is hosting T-SQL Tuesday, and he has invited the SQL commmunity to participate, I was very glad to hear it.  Good for him! This is a great contribution and way to raise his profile.  I've met Matt on few occassions at various SQL Saturday events.  Since then, he's been doing a great job and has become quite the prolific SQL Blogger. 

I thought, ok, I have some time, so I'll probably give it a whirl.  How could I apply, my knowledge of APPLY? Then, I just noticed Steve Jones "reminder"  that T-SQL Tuesday is coming and we "have a week to get a post ready".  That post was Monday, April 11th - I just read this this morning, and we're talking about TODAY! - April 12, 2011.  So, I am litterally whipping up this post ad-hoc as I'm writing this.  I was recently working on something, and lo and behold, it uses the APPLY operator!  Woo-hoo.

So, it may not be my most detailed blog post, but I will share, for the sake of T-SQL Tuesday, my simple script and what I've been working on here.

I wanted a simple way to derive on a multi-processor system, what queries are actually runnning on a particular CPU. 

To understand how SQL Server manages the execution of user requests SQL Server uses schedulers. Each scheduler maps to a CPU. One CPU, per scheduler. Therefore, if the number of CPUs on the system is 8, there would be 8 OS schedulers.  This mapping is to logical CPU and not to physical CPU cores or sockets.  

You can see the number of existing OS Schedulers on your SQL Server by querying the sys.dm_os_schedulers DMV, such as:

SELECT COUNT(*) AS NO_OF_OS_SCHEDULERS FROM sys.dm_os_schedulers

where status='VISIBLE ONLINE'

For all intents and purposes then, the scheduler and scheduler id is interchangable with the cpu and cpu_id.  If I use a few of the available DMVs, then I can derive exactly which queries are running on which CPU.  This can be extremely interesting and helpful to know, if you begin to see signs of CPU pressure.  It would then be possible to pinpoint the point of contention to the exact CPU or scheduler and what query is causing the cpu to be bound.

If we query the sys.dm_os_tasks DMV, I can see all sorts of information on what tasks are currently running.  This DMV returns one row for each task that is active in the instance of SQL Server.  In addition to the session_id, you can view the task_state, which shows you whether the task is PENDING, RUNNABLE, RUNNING, or SUSPENDED.  Assuming the task has a worker thread assigned (no longer PENDING), when the worker thread is waiting to run on the CPU, we say that it is RUNNABLE.  The thread is therefore in the RUNNABLE queue.  The longer it must wait to run on the CPU, the more pressure is on the CPU.  The time a thread spends waiting in the runnable queue, is known as the signal wait time.  Signal wait time is the difference between time the waiting thread was signaled and when it started running. High signal wait times indicate a cpu bottleneck.

In addition to the above, you also have the scheduler_id, that is associated with a particular session_id (or spid).  Other info such as pending_IO_Count and Context_Switches is available.  Also of interest to us, for purposes of this blog, is the task_address.  The task address is the Memory address allocated to the task that is associated with this request.  Because each DMV has some information we need the others don't have, we will need to join as well as use the APPLY operator to get everything we want in the results.

In order to get the actual query that is executing on the CPU, we need to get the plan_handle, which is NOT available in sys.dm_os_tasks.  Therefore, we will need to join the sys.dm_os_tasks view to the sys.dm_exec_requests view.  Both of these have the actual task_address information and can be joined on this column.  The sys.dm_exec_requests is another handy DMV, even by itself, that returns information about each request that is executing within SQL Server.  There is a lot of useful statistics and info here, such as wait_time, total_elapsed_time, cpu_time, wait_type, status, etc.  You can find if there is any blocking or open transactions as well.  There is a lot more, so you can click on the technet link above for the DMV.  One piece of information that is also not in sys.dm_os_tasks, but available in sys.dm_exec_requests is the database_id.  Thus, we can find out what database the request is executing against.

Finally, to complete the query we need to use or APPLY the sys.dm_exec_sql_text function that will return the text of the SQL batch that is identified by the specified sql_handle. This invaluable and handy DMF, replaces the system function fn_get_sql., and pretty much makes the old DBCC INPUTBUFFER command obsolete.

Now that we understand, and have all the ingredients we need to create our script, I will use "select *" to show you all the columns in the output for demo purposes.  Of course, you are expected to filter out and use only the columns that are necessary for viewing.

Select * from sys.dm_os_tasks as t

inner join sys.dm_exec_requests as r
on t.task_address=r.task_address
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) as s
 --where task_state='RUNNABLE' --To filter out sessions that are waiting on CPU

The above TSQL will identify the exact query which is running on a particular CPU, and agaisnt which database.  You can even see the task_state and status of the command, the object id, along with the wait stats, cpu_time, number of physical & logical reads and writes - and more!  If you want to see only sessions that are waiting for CPU, you can filter the query by using "where task_state='RUNNABLE'

Hopefully, this will be useful in your performance tuning efforts, especially when you need to pinpoint which CPU is having the most contention, and what query is there is causing it.  And, just one other example of using the APPLY operator.

Thanks Matt Velic for hosting!