Recently at the office an automated performance testing team came up to me and asked me a very interesting question – Based on the Windows Process Id, how can one determine the number sessions that have been initiated by a process? The question is interesting because their application is a client-server based application, capable of running on a terminal server. Multiple instances of the same application may be running from the same machine, and hence, simply tracing based on the host name would not be sufficient.

Depending upon the requirement, there are two methods to answer this question:

1. If a T-SQL based method is required for logging and other purposes, the DMV: sys.dm_exec_sessions can be used
2. If the intention is to troubleshoot or monitor a process (i.e. a one-time activity), the SQL Server Profiler can be used

Using the DMV: sys.dm_exec_sessions

The sys.dm_exec_sessions DMV returns one row per authenticated session on SQL Server. Therefore, the view will return us one record for each session that an application establishes with the SQL Server.

Because the sys.dm_exec_sessions DMV maps to the sys.processes system table (it’s a best practice not to query the system tables directly), the columns returned contain information about the client/host process including (but not limited to):

1. Host Name
2. User context used to establish the session
3. Program name, and
4. Host Process Id

The column of interest for us is the “host_process_id”. This columns holds the (Windows) process ID of the client program that initiated the session. Please note that this column will show a value of NULL for internal sessions (established by the SQL Server itself).

Using the SQL Server Profiler

The SQL Server profiler, being a dedicated debugging and performance analysis tool, has to have a mechanism to track the processes based on the windows process Id.

Editing a profiler trace allows us to select data columns of our choice (If you are new to Profiler, or need instructions on how to customize a Profiler/SQL Trace, please follow my series of tutorials on the subject at: http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&ts=46&bs=57). The column – “ClientProcessId” captures the (windows) process ID of the application calling SQL Server.

Filtering on the required ClientProcessId column will give us a way to monitor the required session.

Example

Let’s take an example – assume we want to monitor the sessions initiated by the SQL Server Management Studio on my machine.

To begin, get the Process ID (PID) of the required process. An application might use system functions to obtain this information If you wish to manually get this information, you can use the Task Manager (Press: Ctrl + Alt + Del, launch the Task Manager) Based on the screenshot below, you can see that the SSMS process on my test workstation is running under a PID = 2152

Armed with this information, let us go to SQL Server Management Studio, and use the following query to know the open sessions initiated by SQL Server: SELECT * FROM sys.dm_exec_sessions WHERE host_process_id = 2152 Running this query yields 2 records as shown below:

(Remember that the SSMS can open multiple connections to the SQL Server: http://beyondrelational.com/modules/2/blogs/77/posts/11275/the-multiple-connections-of-ssms.aspx)

Now, let us launch the SQL Server profiler. Choose a template of your choice to create the SQL trace. (If you are new to Profiler, or need instructions on how to customize a Profiler/SQL Trace, please follow my series of tutorials on the subject at: http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&ts=46&bs=57) As part of the customization, choose to display all columns for editing and choose to display the “ClientProcessID” in the trace output.

Click on “Column Filters” and filter on the required host process Id (i.e. PID = 2152). Once done, click “OK” to start the trace.

Navigate to the SSMS, create a new window and run any test query. For example, USE AdventureWorks2012 GO SELECT * FROM HumanResources.Employee GO Look at the output of the sys.dm_exec_sessions (query above) and the Profiler. Note that both show the presence of a new user session. The Profiler also shows the associated activity.

References:

• sys.dm_exec_sessions – http://msdn.microsoft.com/en-us/library/ms176013.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Most of our deployment queries (including the build & deployment output) from the Visual Studio 2010 database solution (SQL Server Data Tools (SSDT) for SQL 2012 Denali) generate their output in SQL queries designed to be executed under the CMD mode. Many of our pre-designed debugging scripts are also written to be executed in the SQLCMD mode.

(*If you are new to SQLCMD mode, you may want to explore my post: http://beyondrelational.com/modules/2/blogs/77/posts/11317/sqlcmd-mode-in-ssms-final-part-in-the-series-underappreciated-features-of-microsoft-sql-server.aspx)

To simply the day and to save a couple of steps of going to the Query menu and choosing the SQLCMD mode, the SSMS on the each team members’ workstation has the “Open new queries in SQLCMD mode” switch set by default. To the best of my knowledge, this feature is available from SSMS for SQL 2008 and above (I have not checked SSMS for SQL Server 2005). Once set, any new query editor window that opens (after restarting SSMS) will open in the SQLCMD mode.

To set this switch, one needs to follow the following simple steps:

1. Launch SSMS
   
2. Go to Tools –> Options
   
3. 
   
4. Navigate out to the options for “Query Execution”
   
5. Check the switch “Open new queries in SQLCMD mode”
   
6. 
   
7. Click OK
   
8. Exit out of SSMS

If you use SQLCMD mode a lot, I recommend that you keep this switch checked in your environment too.

Until we meet next time,

Be courteous. Drive responsibly.

It is a universal expectation that depending upon the nature of the product, organizations support at least one or two prior releases of their product. Microsoft SQL Server allows users to be able to use older databases with newer, latest releases of the server system through a property called as compatibility level.

Compatibility levels provide partial backward compatibility with earlier versions of SQL Server. Compatibility levels affects the behavior of a specific database, not the entire server – that way, most databases can continue to leverage the newly introduced features of SQL Server, while only those databases that have not been made compatible can use the relevant compatibility level setting for functioning.

Here’s a quick table showing the compatibility levels supported by SQL Server 2012 (code named “Denali”):

The default compatibility level for SQL Server 2012 is 110. All databases created in SQL Server 2012 have this compatibility level, unless the model database has a lower compatibility level (because all databases, including the tempdb are copies of the model database).

Please note that support for compatibility level 80 has been discontinued from SQL Server 2012. You can refer my post: http://beyondrelational.com/modules/2/blogs/77/Posts/14429/0156-sql-server-2012-deprecated-features-valid-compatibility-levels-compatibilitylevel-80-support-ms.aspx for more details.

Now, when upgrading a database from a prior version of SQL Server to the latest version, one of the things that need to be changed is the compatibility level. Today, I would like to draw your attention to the fact that changing of the compatibility level will cause all cached plans to be recompiled when the related queries/batches are executed again.

For a quick demonstration, run through the following set of queries step-by-step.

-- 0. Create the DB
CREATE DATABASE CompatLevelTest
GO

-- 1. Set the compatibility level
ALTER DATABASE CompatLevelTest SET COMPATIBILITY_LEVEL = 90
GO

-- 2. Clean the buffers
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- 3. Create the stored procedure
USE CompatLevelTest
GO
CREATE PROCEDURE proc_AddNumbers (@num1 INT, @num2 INT)
AS 
BEGIN
    SELECT (@num1 + @num2) AS Summation
END
GO

--4. Execute the procedure
USE CompatLevelTest
GO
EXEC proc_AddNumbers 2, 5
GO

-- 5. Check the plan_generation_num
USE CompatLevelTest
GO
--Check the plan generation 
SELECT execution_count, sql_handle, plan_handle, *
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text like '%proc_AddNumbers%'
  AND database_id = DB_ID('CompatLevelTest')

-- 6. Change the compatibility level
ALTER DATABASE CompatLevelTest SET COMPATIBILITY_LEVEL = 100
GO

--7. Execute the procedure
USE CompatLevelTest
GO
EXEC proc_AddNumbers 2, 5
GO

-- 8. Check the plan_generation_num
USE CompatLevelTest
GO
--Check the plan generation 
SELECT execution_count, sql_handle, plan_handle, *
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text like '%proc_AddNumbers%'
  AND database_id = DB_ID('CompatLevelTest')
  
  
--Repeat steps 7 & 8 again to verify the outputs

-- 9. Cleanup!
USE master
GO
DROP DATABASE CompatLevelTest
GO

The points of interest are the outputs of the DMV: sys.dm_exec_procedure_stats. This is same as the sys.dm_exec_query_stats DMV, except that this returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. If a plan is being re-used the value in the execution_count column will go up.

Here is the abridged result of the above test.

The above table shows us that when the procedure was executed for the 2nd time, i.e. after changing the compatibility level, the procedure was recompiled, because the execution_count value remains 1.

Why did I write about it?

The reason this observation attracted me is that many ISVs fail to convey this to the end customers. They would upgrade the database and change compatibility level of their database (the customer would already be using a higher version of SQL Server), and then complaints start pouring in about unusually slow response times from the server, when in their view, the response time should have been faster than the prior release. This is quite obvious because the SQL Server has to redo all the cached plans. If the end users are educated to expect a performance degradation, then a lot of customer support calls can be reduced.

References:

• sys.dm_exec_procedure_stats: http://msdn.microsoft.com/en-us/library/cc280701.aspx

Until we meet next time,

Be courteous. Drive responsibly.

I am sure this post would ring a bell with most development teams. Development servers are generally constrained for space. Whenever a development team requests more hardware, be it in terms of a new machine or even additional storage space, I am sure that most IT departments would have told them “Why do you need all these servers? You already have n number of servers, which are more than the number of people on the team!”

Ultimately, this results in lack of disk space to store both a database and it’s backup on the same drive. In fact, we had this issue some time ago, wherein we had to restore a reasonably large database 50GB+. Both the backup and the database simply could not reside on the same server due to disk space issues.

So, we decided to place the backup file on a network share, map it to a drive on the server and restore the database from there. However, that just wouldn’t work. Attempting to restore through the SSMS results in the following error:

But, as they say – “Where there is a will, there is a way.” The core issue here is that mapped network drives are not supported by SQL Server. UNC paths however, are a different story.

SQL Server fully supports backups & restores over UNC paths. There are 2 methods that you can use:

1. Directly type the UNC path where the backup needs to be taken/restored from
2. Create a backup device pointing to the UNC path and then backup to/restore from this device

After looking at how easy and convenient it is to use UNC paths with SQL Server, our entire team has started using them. Here are a some scripts that you may be interested in:

1. Backup Databases across the network: http://beyondrelational.com/modules/30/scripts/485/scripts/15042/backup-database-across-the-network.aspx
2. Restore Databases across the network: http://beyondrelational.com/modules/30/scripts/485/scripts/15043/restore-databases-from-a-backup-file-across-a-network.aspx

There are a couple of security considerations that you need to take care about. They are available in the following MS KB article: http://support.microsoft.com/kb/207187

These scripts are available in the Scripts module on BeyondRelational.com (http://beyondrelational.com/modules/30/default.aspx?s=stream&tab=scripts). If you haven’t been to the Scripts module page, do pay it a visit – you will find a lot of useful scripts that you can customize for your use.

Until we meet next time,

Be courteous. Drive responsibly.