Dynamic Management View: sys.dm_exec_sessions

Have you ever wondered who is connected to SQL server?  sys.dm_exec_sessions is an excellent DMV to be able to determine who is logged on and where they are logged on from.

Often times I’ll use the view to narrow down how many connections a certain application is using, or to narrow down what machines connections are coming from.  A very common usage of this view is when I query on sys.dm_exec_requests.  I’ll join by session_id on this DMV in order to determine where a query is coming from.  Based on the host name, I can usually find out who is working with the query.

Another common situation I’ve used this query is to kill outstanding sessions.  I’ll load the output into a table variable and go through the sessions one by one with the “kill” command to terminate them.  This is very useful when I need to bring down the server, or I know there is a rogue application causing mischief on the server.

When you first select from the view you may notice some odd entries with a null host_name, and several other null columns.  It’s completely normal and just some internal SQL tasks being reflected in the view.

— get the most connections by host name
SELECT host_name,Count(*)
FROM   sys.dm_exec_sessions
GROUP  BY host_name

Check out more on this DMV on the MSDN site: http://msdn.microsoft.com/en-us/library/ms176013.aspx


Dynamic Management Views: sys.dm_exec_cached_plans

The next view I’d like to spend some time talking about is the sys.dm_exec_cached_plans view. sys.dm_exec_cached_plans gives a row for each cached plan in the plan cache for SQL server. The plan cache is used to cache a mapping of a particular SQL command to it’s corresponding compiled query.  This is very valuable as it can be CPU intensive to compile queries and having a good rate of plan reuse is an easy way to mitigate the cost of compilation.

Since the plan cache is a cache plans do get evicted from it when there is memory pressure.  So, do not expect it to be a full view of the workload against a server, but it does give a very good insight into what is happening.

Like the sys.dm_exec_requests view, you can lookup the text and plan of the query.

There are a few ways I primarily use this view:

1. I use this view to look at the actual plans that SQL server is executing for a particular query.

2. I use the view to look at possible queries running against a particular table.  I’ll usually do a like restriction on the query text where I supply the table name.

SELECT [text].text,[plan].query_plan,*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS [text]
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS [plan]

There are a few things in particular to watch out for in the plan cache from this view:

Adhoc queries – there are two types of workloads that will cause you to see these queries in the plan cache.  The first being occasional one-off queries by someone running a statement against the database.  This is expected in most DB systems as there usually will be a DBA or someone from the company trying to run custom queries against a data store.  If this happens often in your business environment, it’s a good idea to try out the optimize for adhoc workload on your server.  Rather than caching an entire plan for the query, instead a stub will be cached.  If the query is executed again the stub will be replace with the actual compiled plan.  For systems where a lot of single use queries get run, this is a good option.

Another instance where you’ll see a lot of these types of queries is from misbehaving applications.  Application developers should write parameterized statements which separate out the parameters of a query from the actual SQL text.  When this is done, it prevents a cached plan from being included for every combination of parameters that are included.  When SQL is not parameterized, the parameters themselves are considered part of the text and will cause SQL Server to miss plans that otherwise could have been reused.

Forced parameterization is an option that cause SQL server to automatically separate out parameters from the SQL text.  This is largely used for those programs which don’t appropriately parameterize their queries.  However, this does not avoid the adhoc plan from being cached in SQL server.  An adhoc plan gets cached prior to parameterization followed by looking up of the new parameterized query after parameterization has been forced.

Like all the views I’ve talked about up to now, there are many ways and possibilities to use them.  I hope this gives you a good introduction to this view. You can find the documentation on sys.dm_exec_cached_plans here http://msdn.microsoft.com/en-us/library/ms187404.aspx


Dynamic Management Views: sys.dm_os_wait_stats

In my last post, I talked about the sys.dm_exec_requests dynamic management view.  That view is useful for finding currently executing queries on SQL server.  In this post I’ll talk about another very useful dynamic management view sys.dm_os_wait_stats.

The sys.dm_os_wait_stats view is primarily used to determine where tasks in SQL server have spent their time waiting.  It can be used determine general performance issues within a SQL server instance.

select wait_type
	from sys.dm_os_wait_stats
	order by wait_time_ms desc

Usually, I’ve used this DMV in a test environment to determine if there are any bottlenecks inhibiting performance.  I’ll usually order by wait_time_ms and take the top few wait types to determine if there is a problem based on the description in the MSDN article. Monitoring increases in wait times can also be useful to determine what’s inhibiting performance over certain time periods or intervals.

The unfortunate thing about this view is that it will give you a general inclination of a performance problem, but determining where specific performance problems originate from take more investigation. As such, it’s a good view to monitor to get an overall health of a SQL server instance.

Another thing to be aware of, there are certain wait types that do have a high wait time by design that don’t necessarily indicate a performance problem.

It is possible to reset the values in this view via a DBCC command. The only other time the stats will be reset is after a SQL server restart. This view is tracked server wide so remember, resetting this in an active system will affect others monitoring this view.

dbcc sqlperf ('sys.dm_os_wait_stats', clear)

For more information on specific wait types see the MSDN article on the DMV: sys.dm_os_wait_stats

Dynamic Management Views: sys.dm_exec_requests

I’ll be blogging about some very useful dynamic management views that can be used to debug very specific problems in SQL Server.

First, a background on what a dynamic management view is.

Dynamic management views were introduced in SQL Server 2005 to give information about the internals of SQL Server.  They include information from memory allocations, executing requests, thread wait times, etc…  Needless to say, they provide a wealth of information.  However with a wealth of information comes into question, how to use it all.

The first dynamic management view I’m going to talk about is the dm_exec_session_requests view. It has been incredibly useful for me.

This view gives a list of outstanding queries that are running against a server.  For example, let’s say your CPU usage on the server spikes to 100%.  This view will let you know what queries are running.

SELECT text,req.* FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS text

Notice the CROSS APPLY here.  This is very common to do with the execution related dynamic management views either to determine the request text, or the SQL plan for a particular request.  The sys.dm_exec_sql_text gives the sql text, likewise the sys.dm_exec_query_plan view can be used to give the query plan for a query.

I’ve used this view a few different ways.

First, while performance testing applications, I often group by the text from the dm_exec_sql_text cross apply to get an idea of how many times a specific query is being run at any given instant.  It gives me a real time look into what the system is doing.

Second, if there is some sort of problem query for example, one causing excessive CPU usage, I can use the cpu_time to pick this out.

Third, sometimes certain queries will execute a long time.  I’ll use the start_time column to narrow down what queries have been executing for the longest amount of time.

There are many other ways this view can be used. See the MSDN library reference here for additional information on this view: http://msdn.microsoft.com/en-us/library/ms177648.aspx

Queues and locking contention in SQL Server

One of the primary issues with implementing a high performance multi-threaded queue in SQL server is retrieving and processing tasks from a table with low rate of locking contention.

Try to remove an item out of a queue with some sort of ordering.  In this case we’ll use the example of priority. In order to retrieve the item from the queue, a SELECT with an order by and top clause must be specified followed by a potential update to indicate our item in the queue is now processing.

What if between the SELECT and UPDATE statement, some other process were to try get the same task?  A natural response to this would be, add a transaction, or add a check to the UPDATE statement to ensure the item hasn’t already been selected.  While both of these are potential solutions, they are not ideal and both introduce potential locking contention.  SQL Server has a query hint that allows for us to bypass this issue.  The query hint is READPAST.  The READPAST hint effectively ignores any rows which are currently locked.  Combining this with a lock of some sort within a transaction will allow a much more efficient way to retrieve the items from the queue.

1 BEGIN TRANSACTION 2 3  DECLARE @QueueID INT 4 5 SELECT TOP 1 @QueueID=@QueueID FROM [Queue] AS Q WITH (READPAST, UPDLOCK) 6 WHERE Processing = 0 7 ORDER BY Q.Priority 8 9 UPDATE [Queue] SET Processing = 1 WHERE QueueID = @QueueID 10 11 COMMIT TRANSACTION

An alternative would be to use SQL services broker to implement a queue.  However there are situations where SQL services broker just won’t provide the flexibility writing a custom queue will provide.