This is in continuation to my series on “Underappreciated features of SQL Server”, which is based on the editorial with the same name by Andy Warren on SQLServerCentral.com. The editorial is available here.

Locking & Blocking are not new to anyone who has worked on a production SQL Server based system with as little as 200 concurrent users. Locking is essential to maintain a database’s consistency. However, whenever a process holds a lock for more than a required amount of time, it starts interfering with the normal operation of other processes on the server. This manifests into long-running queries and application timeouts.

The conventional way of identifying blocking is by the use of TRACE flags 1204 and 1222. Microsoft SQL Server 2005 makes this a little bit easier by introducing a new feature called the Blocked Process Report which can help administrators monitor their servers for blocking whenever the blocking exceeds a given threshold.

Blocking Processes

Explaining blocking processes and deadlocks is beyond the scope of this article. If you need help in clearing the concept, please refer the MSDN resource here. Also, please do not proceed ahead without clarity on these concepts – it will do more harm than good.

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. A blocking situation is when the blocking is not permanent, but serve enough to impact the performance of the other tasks.

Using the AdventureWorks database, running the following batch in two separate query windows simultaneously will result in a blocking situation. You will notice that the “second” query waits for the first and then executes only after the “first” finishes. Give it a try.

BEGIN TRANSACTION BlockedProcessReport
    PRINT 'Entering Transaction at:' + CONVERT(VARCHAR(20),GETDATE(),114);
    
    UPDATE HumanResources.Employee SET CurrentFlag = 0
    --Introduce a 20 second delay
    WAITFOR DELAY '00:00:20'

    PRINT 'Leaving Transaction at: ' + CONVERT(VARCHAR(20),GETDATE(),114);
ROLLBACK TRANSACTION BlockedProcessReport

Here are the results from my execution:

--Window 01
Entering Transaction at:08:54:45:793

(290 row(s) affected)
Leaving Transaction at: 08:55:05:803

--Window 02
Entering Transaction at:08:54:47:517

(290 row(s) affected)
Leaving Transaction at: 08:55:25:903

Clearly, the process running in window #2 was blocked by the process running in window #1. The most common symptom that such an issue is occurring would be users calling up the support staff screaming about a poorly performing system. Let’s see how we can now identify blocked queries using the features new to Microsoft SQL Server 2005.

Preparing the environment

Before we go any further, let’s ensure that the blocking process thresholds are low enough for us to easily trigger the recognition of transactions as blocking transactions.

CAUTION!!! Please do not adjust these settings on your production and Quality Assurance environments without recommendation from a senior database administrator, or from Microsoft.

--Set the visibility of advanced options to ON
sp_configure 'show advanced options',1;
GO
--Use RECONFIGURE hint to prevent a restart of the SQL Server for changes to take effect
RECONFIGURE;
GO
--Fetch the value of the Blocked Process threshold
--By default, this value is 0, i.e. no Blocked Process reports are generated
sp_configure 'blocked process threshold';
GO
--Set the blocked process threshold value to 5 seconds
sp_configure 'blocked process threshold',5;
GO
--Use RECONFIGURE hint to prevent a restart of the SQL Server for changes to take effect
RECONFIGURE;
GO

Using the default health reports shipped with SQL Server Management Studio

For those administrators who do not want to go get their hands greasy just yet, Microsoft SQL Server comes with a couple of useful reports out-of-the-box. One such report is the Blocked Process report. You can access this from the Object Explorer by right-clicking on the instance name –> Reports –> Standard Reports –> Activity – All Blocking Transactions

When you run the report, under normal circumstances, it should not show any blocked processes. Now, re-run the batch of queries designed to simulate blocking in two separate query windows. While these are running, refresh the Blocked process report. This is how it will look like – simple, clear and concise:

This query can now be given to the development teams with proof that it is indeed, causing performance issues and needs attention.

Using the SQL Server Profiler – Blocked Process Event Class

Starting Microsoft SQL Server 2005, the Profiler introduces a new Event Class – Blocked Process Report. The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources. Let’s see how we can use this to detect blocking & deadlocks.

We will begin by setting up a SQL Server Profiler trace on our test instance. Use a (blank) trace template for simplicity. In the “Events Selection” tab, browse out to the “Errors and Warnings” section and choose “Blocked Process Report” as demonstrated below:

Run the trace. While the trace is running, re-run the batch of queries designed to simulate blocking in two separate query windows. Monitoring the profiler trace shows us the following:

Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. The event is generated once per reporting interval for each of the blocked tasks. Because our blocked process threshold is set for 5 seconds, a blocked process report has been generated by the blocked process at 5, 10 and 15 seconds of execution time respectively (At the 20 seconds mark, the first process had already proceeded ahead and ended the blockage, which is why it is not seen in the report).

Clicking on the individual blocked process report, we see an XML like the one below, which shows the blocked process as the one with SPID (54) and the blocking process to be one with SPID (53).

< blocked-process-report monitorLoop="20097">
 < blocked-process>
  < process id="process87be28" taskpriority="0" logused="0" waitresource="KEY: 5:72057594044153856 (6d003d0ff717)" waittime="17725" ownerId="120328" transactionname="BlockedProcessReport" lasttranstarted="2011-01-22T09:41:14.083" XDES="0x77247f0" lockMode="U" schedulerid="1" kpid="3128" status="suspended" spid="54" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-01-22T09:41:14.083" lastbatchcompleted="2011-01-22T09:40:58.680" clientapp="Microsoft SQL Server Management Studio - Query" hostname="VPCW2K3" hostpid="2348" loginname="VPCW2K3Administrator" isolationlevel="read committed (2)" xactid="120328" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   < executionStack>
    < frame line="4" stmtstart="16" sqlhandle="0x0200000036147f28ca6059811837a9a13bd54860b50d7590"/>
    < frame line="4" stmtstart="252" stmtend="432" sqlhandle="0x02000000ee8af21f3464249db7b445232f6c427f69424d78"/>
   < /executionStack>
   < inputbuf>
BEGIN TRANSACTION BlockedProcessReport
    PRINT &apos;Entering Transaction at:&apos; + CONVERT(VARCHAR(20),GETDATE(),114);
    
    UPDATE HumanResources.Employee SET CurrentFlag = 0
    --Introduce a 20 second delay
    WAITFOR DELAY &apos;00:00:20&apos;

    PRINT &apos;Leaving Transaction at: &apos; + CONVERT(VARCHAR(20),GETDATE(),114);
ROLLBACK TRANSACTION BlockedProcessReport   < /inputbuf>
  < /process>
 < /blocked-process>
 < blocking-process>
  < process status="suspended" waittime="19027" spid="53" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2011-01-22T09:41:12.773" lastbatchcompleted="2011-01-22T09:40:38.603" clientapp="Microsoft SQL Server Management Studio - Query" hostname="VPCW2K3" hostpid="2348" loginname="VPCW2K3Administrator" isolationlevel="read committed (2)" xactid="120327" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   < executionStack>
    < frame line="6" stmtstart="434" stmtend="496" sqlhandle="0x02000000ee8af21f3464249db7b445232f6c427f69424d78"/>
   < /executionStack>
   < inputbuf>
BEGIN TRANSACTION BlockedProcessReport
    PRINT &apos;Entering Transaction at:&apos; + CONVERT(VARCHAR(20),GETDATE(),114);
    
    UPDATE HumanResources.Employee SET CurrentFlag = 0
    --Introduce a 20 second delay
    WAITFOR DELAY &apos;00:00:20&apos;

    PRINT &apos;Leaving Transaction at: &apos; + CONVERT(VARCHAR(20),GETDATE(),114);
ROLLBACK TRANSACTION BlockedProcessReport   < /inputbuf>
  < /process>
 < /blocking-process>
< /blocked-process-report>

Cleanup the environment

As we come to a close on this demo, we will cleanup the environment by resetting the blocked process threshold value to 0.

CAUTION!!! Please do not adjust these settings on your production and Quality Assurance environments without recommendation from a senior database administrator, or from Microsoft.

--Reset the blocked process threshold value to 0
sp_configure 'blocked process threshold',0;
GO
--Use RECONFIGURE hint to prevent a restart of the SQL Server for changes to take effect
RECONFIGURE;
GO
--Fetch the value of the Blocked Process threshold
--By default, this value is 0, i.e. no Blocked Process reports are generated
sp_configure 'blocked process threshold';
GO
--Set the visibility of advanced options to OFF
sp_configure 'show advanced options',0;
GO
--Use RECONFIGURE hint to prevent a restart of the SQL Server for changes to take effect
RECONFIGURE;
GO

Blocked Process Monitoring Tool

Jonathan Kehayias has developed a Blocked Process Monitoring Tool, which is available on CodePlex at: http://sqlblockedprocessmon.codeplex.com/

In Conclusion

As systems scale up to the cloud in the coming days; preventing blocking and deadlocks are going to be of utmost importance. It is required that the entire application development process takes into account the fact that ultimately, if the underlying database is struggling to get the data you need in time because of poor application design, no amount of high-end technology and hardware is going to resolve the problem at hand. I hope that the above has given development teams and DBAs a new and easy way to identify and troubleshoot performance bottle-necks in their systems.

While you do this, please keep in mind the following extract from SQL Server Books On Line: “The blocked process report is done on a "best effort" basis. There is no guarantee of any "real-time" or even close to real-time reporting.“

Until we meet next time,

Be courteous. Drive responsibly.

This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Continuing our journey through some of the features of Microsoft SQL Server Management Studio, we will today look at a feature that is very helpful in getting a quick overview of the SQL Server performance. The Activity monitor has been much talked about and is generally a starting point for most performance troubleshooting projects, and hence I was quite surprised when this one came in the “Underappreciated features” list.

In any case, here’s a quick overview (more like a crash course) on the various features of Activity Monitor. Please excuse the slightly higher load-time – the Activity Monitor is best explained visually.

The Activity Monitor

Accessing the Activity Monitor

The activity monitor can be accessed in multiple ways. However, the first one I would particularly recommend for DBAs whose most important activity upon connecting to a server is to view it’s ongoing activity or those who connect to a server to troubleshoot performance issues.

At Startup (Tools –> Options)	From the SSMS (On the toolbar)	From the Object Explorer (Right click on the server instance name)

Navigating through the Activity Monitor

Activity Monitor is a tabbed document window that has the following expandable and collapsible panes:

1. Overview
2. Active User Processes/Tasks
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries

Overview

The active user processes are represented in the Activity Monitor in terms of a percentage (%) value. This is obtained by monitoring the SQL Server process “sqlserv.exe” spread over all of the available CPUs, not for the entire server.

Active User Processes/Tasks

The information on this pane is similar to the sp_who2 and to a combination of the DMVs sys.dm_exec_sessions and sys.dm_exec_requests.

This pane shows information for active user connections to the instance. You can perform the following tasks on any particular SPID from this view:

1. Monitor in detail in the SQL Server Profiler
2. KILL the process

Here’s how to monitor a process in the SQL Server Profiler:

Let’s say I have two instances of SSMS running against a SQL Server. One is running a SPID of (54) and the other a SPID of (58). You can use the following query to get the SPID of the process you are running:

SELECT @@SPID

Simply right-click on the row with the required SPID and chose “Trace Process in SQL Profiler”. All operations done on SPID (54) can now be monitored via the Profiler.

Choosing a process to monitor	SQL Server Profiler

Here’s how to kill a process from the Activity Monitor:

The Activity Monitor is the best tool to start digging into performance issues that you might be experiencing with your server. In such a case, once you have identified and monitored the offending process, you might want to kill it. Instead of going to a new SSMS query window, you can kill the process from the Activity monitor itself.

Choosing a process to Kill	Attempting to fire a query in the SSMS query window using SPID (54)

Resource waits

This internally uses the DMV – sys.dm_os_wait_stats. Resource waits measure the amount of time a worker thread has to wait until it can gain access to the resources on the server that it needs, such as memory or CPU. A high resource wait time might indicate a resource bottleneck.

TIP: A useful feature that all panes in the Activity Monitor provide is filtering. Simply click on the drop-down boxes at that top of each column. You can also sort the view as per your requirement.

Data File I/O

As the name itself indicates, this pane shows information about the database files for the databases that belong to the instance. If you want to find out the most actively database, this is the place to look in.

Recent Expensive Queries

From Books-On-Line, this pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds. The information is derived from the union of sys.dm_exec_requests and sys.dm_exec_query_stats, and includes queries in process and queries that finished during the time period.

Once you have boiled down to a query being the root cause of a performance problem, this pane will help you identify the problem query/set of queries.

As I was writing this post, I only ran one query on my test server instance. Hence, I get only one row in the Recent Expensive Queries pane.

Here’s the most interesting & useful part. The pane allows you to view the entire query, and also view the associated graphical execution plan.

Options (Right click on the required query)
Viewing the Query Text
Viewing the Execution Plan

TIP

You might miss the fact that tool-tips are available for your assistance throughout the Activity Monitor, providing you with useful information on what you are seeing. Some tool-tips even tell you the name of the DMV that populates the particular information on the Activity Monitor.

Permissions

Here is a quick summary of the permissions that one needs in order to use the Activity Monitor. More details on how to selectively apply security to the Activity Monitor is available from Pinal’s blog here.

Word of caution

The Activity Monitor continuously polls the SQL Server via the DMVs for refreshing the data on-screen. Running the Activity Monitor for extended periods of time will cause performance issues on the server. Ironically, the very tool that helps you to troubleshoot performance issues can be responsible for creating these issues. Hence, use it in moderation.

Before you go, if you actively use Activity monitor or have used it in the past, I would be curious to know the scenario under which you used it (minus the business specific details of course). Please do leave your feedback, I appreciate it.

Until we meet next time,

Be courteous. Drive responsibly.

As you have known by now, I am currently running a series of posts inspired by Andy Warren’s editorial in SQLServerCentral.com on the "Underappreciated features of Microsoft SQL Server".

Continuing our journey through some of the features of Microsoft SQL Server Management Studio, we will today look at a feature that I am sure will increase developer productivity by leaps and bounds.

The Template Explorer

Per Microsoft SQL Server Books On Line, Template Explorer is a component in SQL Server Management Studio that presents templates to quickly construct code in the Code Explorer. The templates are grouped by the type of code being created, which in turn makes finding the right templates blazingly fast.

Navigating through the Template Explorer

To launch the Template Explorer launch the SQL Server Management Studio and simply use the Ctrl+Alt+T keyboard combination or go to View->Template Explorer.

Launching the Template Explorer

SQL Server provides the user the option to choose from any of the 3 families of templates:

1. SQL Server
2. Analysis Services
3. SQL Compact

NOTE: On SQL Server 2008, the first time the template explorer is opened, a copy of the templates is placed in the users Documents and Settings folder under Application DataMicrosoftMicrosoft SQL Server100ToolsShellTemplates.

SQL Server Templates

For the purposes of this example, we will attempt to create a new stored procedure using SQL Server templates. All one needs to do is to navigate out to “Stored Procedures” folder in the Template Explorer and double-click on “Create Procedure Basic Template”.

The first thing that strikes us is that they contain parameters to help us customize the code. Per BOL, template parameter definitions use this format <parameter_name, data_type, value>, where:

• parameter_name is the name of the parameter in the script
• data_type is the data type of the parameter
• value is the value that is to replace every occurrence of the parameter in the script

Replacing Parameters in SQL Server Templates

Replacing the parameters in a SQL Server Template to create an almost fully-functional script is a simple three step process, as under:

1. On the Query menu, click Specify Values for Template Parameters
2. In the Specify Values for Template Parameters dialog box, the Values column contains suggested value for the parameter. Accept the value or replace it with a new value as required, and then click OK to close the Replace Template Parameters dialog box and modify the script in the query editor
3. Modify the query to add whatever business logic necessary

Launching the “Specify Values for Template Parameters” dialog	Replacing Template Parameters

Here’s the script with the parameters replaced. The SELECT statement in the body of the stored procedure has been replaced with business logic:

-- =============================================
-- Create basic stored procedure template
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'[Sales]'
     AND SPECIFIC_NAME = N'[proc_AddNumbers]' 
)
   DROP PROCEDURE [Sales].[proc_AddNumbers]
GO

CREATE PROCEDURE [Sales].[proc_AddNumbers]
	@p1 int = 0, 
	@p2 int = 0
AS
	--Template Default
	--SELECT @p1, @p2
	SELECT @p1+@p2 AS 'Result'
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE [Sales].[proc_AddNumbers] 1, 2
GO

As you can see, our basic script is now ready for use in just 3 steps!

Creating custom templates

Most organizations have very specific coding standards and thus, if a mechanism could be devised to tweak the default templates to suit their needs, the feature would be extremely helpful to them. This can very well be done as per the following steps mentioned in SQL Server Books On Line:

1. In Template Explorer, navigate to the node where you would like to store the new template
2. Right-click the node, point to New, and then click Template
3. Type the name for your new template and then press ENTER
4. Right-click the new template, and then click Edit. In the Connect to Database Engine dialog box, click Connect to open the new template in Query Editor
5. Create a script in Query Editor. Insert parameters in your script in the format <parameter_name, data_type, value>
6. On the toolbar, click Save to save your new template

NOTE: When defining parameters, the data type and value areas must be present, but can be blank.

Let’s follow these steps to customize the “Create Stored Procedure Basic Template” as under (in the below script, replace ‘< ‘ with ‘<’ and ‘ >’ with ‘>’. The site kept messing up the parameter identifiers.):

/*
********************************************************************************************************
Developed By          : < Developer_Name, , Developer_Name >
Functionality         : < Functionality, , Functionality >
Template              : Create Procedure Basic Template
Modifications         :
< Creation_Date, DATE, Creation_Date > - < Developer_Initials, , Developer_Initials > - Created
********************************************************************************************************
*/
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'< Schema_Name, sysname, Schema_Name >'
     AND SPECIFIC_NAME = N'< Procedure_Name, sysname, Procedure_Name >' 
)
   DROP PROCEDURE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name >
GO

CREATE PROCEDURE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name >
	< @param1, sysname, @p1 > < datatype_for_param1, , int > = < default_value_for_param1, , 0 >, 
	< @param2, sysname, @p2 > < datatype_for_param2, , int > = < default_value_for_param2, , 0 >
AS
	SELECT @p1, @p2
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE < Schema_Name, sysname, Schema_Name >.< Procedure_Name, sysname, Procedure_Name > < value_for_param1, , 1 >, < value_for_param2, , 2 >
GO

Following the above mentioned process to replace template parameters, we can then produce the following:

/*
********************************************************************************************************
Developed By          : Nakul Vachhrajani
Functionality         : Adds two integers
Template              : Create Procedure Basic Template
Modifications         :
January 09, 2011 - NAV - Created
********************************************************************************************************
*/
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'[Sales]'
     AND SPECIFIC_NAME = N'[proc_AddNumbers]' 
)
   DROP PROCEDURE [Sales].[proc_AddNumbers]
GO

CREATE PROCEDURE [Sales].[proc_AddNumbers]
	@p1 int = 0, 
	@p2 int = 0
AS
	--Template Default
	--SELECT @p1, @p2
	SELECT @p1+@p2 AS 'Result'
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE [Sales].[proc_AddNumbers] 1, 2
GO

In Conclusion

According to me, Template Explorer is one of the most powerful productivity enhancing tools after Intelli-sense. For those who are still using Microsoft SQL Server 2005, Template Explorer is also available there.

To all the DBAs reading this, if you haven’t already done so, please create your own templates as per your organization’s standards. Once done, please distribute these amongst developers and train them on how to use the Template Explorer.

I am sure developers will love to use this feature of Microsoft SQL Server. Do share the feedback that you receive from the developers.

In my next post, I will be looking at the Activity Monitor. Till then,

Be courteous. Drive responsibly.

Continuing my series on the underappreciated features of Microsoft SQL Server (based on the editorial with the same name by Andy Warren on SQLServerCentral.com), we will today look at a very simple, productivity enhancing feature of the Object explorer – filtering!

The problem

When working on a complex database schema, it often becomes confusing for the developer/administrator to locate a particular database object from a long list of available objects. Microsoft SQL Server offers two solutions to overcome this challenge.

1. Use the object search feature referenced in the post
2. Use object filtering within the Object Explorer

Using filtering within Object Explorer

Filtering within Object Explorer is a very simple and easy 3 step process. In fact, it is so simple that those who have not used this till date will start using it right away.

For the purposes of this demonstration, assume that we have a Microsoft SQL Server 2008 instance with the AdventureWorks2008 database loaded, and we need to only work on tables whose names contain the word “Person” in them.

Step 01: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Filter Settings
Step 02: Enter the object information as necessary. For our case, enter: Name = Person (If you only want to work within a schema, enter only the schema name.
Step 03: Simply click “OK” to apply the filters in the Object Explorer
Step 04: To remove filtering, all one needs to do is: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Remove Filter

Filtering is applicable to all object types within the Object Explorer. If one is to only work on a particular schema or a set of objects, it is very productive to have only those required objects filtered out. For SQL Server 2005 users, there are no bad news because this feature is available in Microsoft SQL Server 2005 as well.

In my next post, we will now explore how can we effectively use templates within SSMS (SQL Server Management Studio).

Be courteous. Drive responsibly.

SQL Server Management Studio (SSMS) is the one stop shop for almost UI-accessible features of the SQL Server, and an indispensable tool for administrators, deployment professionals and developers alike.

Anybody who has ever worked with Microsoft SQL Server ever since SQL Server 2005 came out would have used SSMS extensively. But, can you tell how many sessions to the SQL Server does a typical (default) SSMS session establish?

As they always say, there’s always a tradeoff. SSMS is a lot more than a query editor, and if you have followed a couple of my recent blog posts, you will find that I talk about the Object Explorer and Object Explorer details window within the SSMS (refer the starting point of the blog series here). Additional features means that we do have some tradeoff, which comes in the form of additional SQL Server connections.

About the DMV

Today, we will be using the Dynamic Management View (DMV) – sys.dm_exec_sessions, which is a very powerful DMV which returns one row per authenticated session on SQL Server. This DMV is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. This view is often used to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

Once you start using DMVs, you will find that quite often this becomes the starting point of all your monitoring queries.

A point to be noted

A session_id in the results of the DMV – sys.dm_exec_sessions is equivalent to a SPID in the prior versions of SQL Server. Starting SQL Server 2005, the global function @@SPID returns the session_id of the caller.

In earlier versions of SQL Server, the system SPIDs (such as checkpoint, lazywriter, ghost record cleanup, etc) always had a SPID value < 50. Starting SQL 2005, however, this is no longer the case, and a system process may have any value as it’s SPID, as can a user session.

The distinction between a system process and a user process can be made via the “is_user_process” column of the DMV – sys.dm_exec_sessions. Also, since connections are always user processes, this can also be determined via sys.dm_exec_connections.

The Demonstration

1. Open a New SQL Server Management studio instance. Do not connect to any SQL Server
2. Launch the Windows Task Manager and get the Process ID for the SSMS.EXE (If not available by default, go to View -> Select Columns -> Process ID)
3. Now, open a new SQL Server Management Studio instance and connect to a SQL Server (in my case, it was VPCW2K3-SQL2K8)
4. Run the following query against the SQL Server. Notice that as of now, no results are returned
   ~~~
   –In a separate SSMS query window, run the following query with the PID
   –fetched from Step 02
   SELECT *
   FROM sys.dm_exec_sessions sdes
   WHERE sdes.host_process_id = 612
   ~~~
5. Now, in the test SSMS window (opened in Step# 1), create a new Database engine query by going to File –> New –> Database Engine Query. This time, connect to the SQL Server referenced in Step 03
6. Rerun the query in Step #4. Notice that we now get one record in the result
7. Now, view the Object Explorer by going to View->Object Explorer. Connect to the same SQL Server as connection #5
8. Rerun the query in Step #4. Notice that we now get two records in the result
9. Finally, SQL Now, view the Object Explorer Details by going to View->Object Explorer Details
10. Rerun the query in Step #4. Notice that we now get three records in the result

The Moral of the Story

Almost every feature of the SQL Server Management Studio contributes to an additional session to the SQL Server targeted. Avoid the urge to use all features at the same time on a production

server, or during troubleshooting – not only will it confuse you, but also might slow down the SQL Server (if the server is under pressure of some sort).

Finally, if the SQL Server has a database which is in single-user mode, it will be available to only one of the multiple sessions that SSMS starts. The trouble is that which connection will come first is unknown. Hence, it is always a good practice to have nothing but the query editor open when working with either single-user databases or single query modes like the DAC (Dedicated Admin connection).

So, configure your environment wisely, and the power of SSMS will be with you.

More Information on the DMVs

If you need more information on DMVs, please refer the following E-book, which is available for free download from http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv:

Name: SQL Server DMV Starter Pack

Authors: Glenn Berry, Louis Davidson and Tim Ford

Publisher: Red-Gate Software

Be courteous. Drive responsibly.