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.

After Activity Monitor, this is one of the other features that I was surprised to see in the list of underappreciated features. This feature has been a dream come true for anybody who manages more than one server at a time. The need to be able to query a bunch of servers in one go and the fact that this feature has been around since Microsoft SQL Server 2005 had made me think that this should have been pretty common in usage by now.

Anyway, let’s quickly see how to use this wonderful feature that Microsoft SQL Server Management Studio provides.

Running Multi-Server Queries

One of our esteemed SQL Server MVPs is Mr. Pinal Dave (blog). He has, on June 14, 2009 written a very short, concise and precise blog post on how to run Multi-Server Queries. You can find that particular post here.

Keep in mind that the most important usability change that you need to make in order to use this feature is that all servers participating in a multi-server query must be registered servers.

What Pinal’s blog post does not cover are some navigational aids and query execution options. I will attempt to cover them here.

Changes to the Query Editor window

The Query editor window in a multi-server query is similar to the regular single-instance query editor, but there is one minor difference. The SSMS query status bar informs us about the number of servers that a particular query will run against. On my test environment, let’s say I have two registered instances. When I attempt to run a multi-server query, this is what I see:

Query Execution Options:

In Pinal’s post, you would have already seen how the results would appear in your results pane by default. Results from both instances are available in a single grid, and the SQL Server instance name helps us identify which server contributed to the result. However, we can change the way these results are displayed. You can navigate out to Tools->Options->Query Results to see a window similar to the following. Please note that the values in the screenshot below are default values.

• Add login name to the results – Setting this to TRUE adds a column containing the login name which produces the result row
• Add server name to the results – Adds a column containing the server instance name for a particular result row when set to TRUE
• Merge Results – Shows results from different servers in the same grid when set to TRUE

Allow me to change the SQL Server registration of one of the servers to use SQL Server authentication. Also, I will set the “Merge Results” property to FALSE, and the “Add login name to the results” to TRUE. Below will be the output when I run the following query (borrowed from the example in Pinal’s blog):

SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
GO 

Important points to note

Limitation

As of now, it is not possible to select a subset of servers from within a server group (you can select a single instance, but not a subset). The recommendation would be to have a separate group if such a selection needs to be made often.

This is not a programmability enhancement!

Also, this feature is nothing more than an ability to execute the same query against multiple registered servers and obtain their results within a single window. It is not a programmability enhancement, and hence the following should not be expected:

1. Cross-server support for JOIN or ORDER BY clauses
2. Transaction support across multiple servers

Security considerations

Quoting MSDN:

“Because the connections maintained by a Central Management Server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.”

You may also want to read:

• Administering multiple servers using Central Management Servers: http://msdn.microsoft.com/en-us/library/bb895144(SQL.100).aspx

This post completes the first part of my series on “Underappreciated features of Microsoft SQL Server”. Do share your feedback on how the journey has been till now, and what else would you like me to write on.

We will resume our journey to explore T-SQL Enhancements in the next couple of weeks. In the meanwhile, I will share with you some other interesting incidents that happened last month.

Until we meet next time,

Be courteous. Drive responsibly.

This is a part of my ongoing series on the "Underappreciated Features of Microsoft SQL Server", inspired from Andy Warren’s editorial on SQL Server Central.com. You can read the editorial and follow the discussion here.

More often than not, there is a myth in the developer community that Microsoft SQL Server does not provide the debugging abilities that other IDEs provide. SQL Server 2000 provided basic debugging ability. Starting Microsoft SQL Server 2005, the product now comes with it’s own Management Studio, which is based upon Microsoft’s renowned IDE – the Visual Studio. Therefore, the most common debugging abilities come out of the box for SQL Server – enough to give application developers a run for their money! Let’s have a look at some of the basic debugging options that are available to us in SSMS.

Please note that throughout this example, we will be using Microsoft SQL Server 2008, and the AdventureWorks2008 sample database as our reference. Please allow for a larger loading period of this post because the Debugger is best explained visually.

NOTE: For T-SQL Debugging, connecting the Transact-SQL editor to the server with a login having the sysadmin fixed server role is not sufficient; the Windows login of the Visual Studio user must also be a member of the sysadmin fixed server role on that instance of SQL Server.

Creating the environment

We can debug almost everything in SQL Server – ad-hoc SQL statement batches, stored procedures and even triggers! To begin with, we will create the following stored procedure on the SQL Server 2008 instance. The purpose of using a stored procedure that updates the [Sales].[SalesOrderDetail] table is that the table has a trigger on it and hence it would be easy for us to see how to debug triggers in Microsoft SQL Server 2008.

--Step 01: Create the following Stored Procedure
USE AdventureWorks2008;
GO
IF OBJECT_ID ( 'Sales.proc_UpdateSalesOrderDetailUnitPrice', 'P' ) IS NOT NULL 
    DROP PROCEDURE [Sales].[proc_UpdateSalesOrderDetailUnitPrice];
GO

CREATE PROCEDURE [Sales].[proc_UpdateSalesOrderDetailUnitPrice]
    @tSalesOrderId       INT,
    @tSalesOrderDetailId INT,
    @tUnitPrice          MONEY
AS
BEGIN
    --Wrap the UPDATE in a transaction so that we do not make permanent changes to the DB
    BEGIN TRANSACTION MyDebuggingTest
        UPDATE [Sales].[SalesOrderDetail]
        SET UnitPrice = @tUnitPrice
        WHERE SalesOrderID = @tSalesOrderId AND
              SalesOrderDetailID = @tSalesOrderDetailId
    ROLLBACK TRANSACTION MyDebuggingTest
END
GO

Here’s the query that we will be using to invoke the stored procedure in our debugging session.

USE AdventureWorks2008
GO

DECLARE @tSalesOrderId INT = 43659,
        @tSalesOrderDetailId INT = 2,
        @tUnitPrice MONEY = 2010.255

EXEC [Sales].[proc_UpdateSalesOrderDetailUnitPrice] @tSalesOrderId, @tSalesOrderDetailId, @tUnitPrice;

Performing the Debug session

We can initiate the Debugging session by clicking the “green arrow” button on the SSMS toolbar or by going to Debug –> Start Debugging or by using the Alt+F5 key combination.

Note that SQL Server shows reminds you that a Debug Session is in progress by showing the “Debugging Query…” message in the query status bar. You also get the Local Variable watcher and the Call Stack/Breakpoint editor and Output Windows.

Initiate Debugging session (Use F10 to advance to the next statement)
Use F11 to “step-into” the stored procedure Notice that both “Locals” and “Call stack” windows are updated
Use F11 on the UPDATE statement to step into the Trigger! Also notice that the “Call Stack” shows both the Server Instance name and the Database name where the object being debugged resides
Once inside a trigger, define a breakpoint by pressing F9, use “Continue” (Alt+F5) to proceed with the execution until the breakpoint is hit Is it just me or the breakpoint window is not what we expected to be?
You also get the standard debugging options like “Run to cursor”/”Set next statement”, etc

What is not supported?

The SQL Server Debugger is powerful, but still in it’s infancy. To the best of my knowledge, the following cannot be done in SQL Server 2008 debugger:

1. Edit & Continue – the objects that you step into are temporary scripts created by SQL Server – making changes to these will not have any effect
2. Conditional Breakpoints – its a take it or leave it situation with breakpoints and SQL Server – you cannot have conditional breakpoints
3. Pre-set breakpoints – Because SQL Server opens up a temporary copy of the object scripts, one cannot pre-set breakpoints before the object is “stepped into”

Interesting information in the breakpoint window

Talking about the limitations of Transact-SQL Debugging, did you notice anything strange in the screenshot where we hit the breakpoint (the second-last screenshot in the table above)?

You had to go back and take another look, didn’t you? No worries – I had to do that too!

The content in the breakpoint window is not what we expected it to be. Instead of showing the object name where the breakpoint is, SSMS is showing us a number. What can this number be? It’s the object_id of the trigger we are currently in! Here’s the confirmation:

SELECT * FROM sys.objects WHERE object_id = 1563152614

Result:

 

But why does SSMS give us the object_id instead of the object name? That’s because it is SQL Server’s way of keeping track of the breakpoint for future debugging sessions within the solution/project. If you are not running a debug session under a solution/project, closing SSMS will reset all breakpoints.

But don’t take my word for it. Rerun this debug session and see for yourself the importance of using the object_id. Once done, close SSMS. Reopen SSMS and run through this debug session once again – the breakpoint will be gone!

Important Resources

Here are some important MSDN resources to get you started on T-SQL Debugging

• Setting up Transact-SQL Debugging – http://msdn.microsoft.com/en-us/library/s4sszxst.aspx
• Transact-SQL Debugging Setup Errors and Troubleshooting – http://msdn.microsoft.com/en-us/library/s7ahaxtd.aspx

Powered with these resources, I hope that you will be able to unleash the power of T-SQL Debugging and use it for your making a smarter workplace. If you find any cool tricks to use with SQL Debugging, please share them here!

Until we meet next time,

Be courteous. Drive responsibly.

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.

Operation	Permission
Viewing the Activity Monitor	VIEW SERVER STATE
Killing a process	User must be a member of the sysadmin or processadmin fixed server role

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.