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.

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.

As mentioned in my previous post, I am currently developing a series of brief articles on the many underappreciated features of Microsoft SQL Server as received from the community as a response to Andy Warren’s SQLServerCentral.com’s editorial (http://www.sqlservercentral.com/articles/Editorial/71788/), which was published on December 17, 2010.

We started this journey by looking at the Object Explorer Details window(Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server). I am sure that by now, most of you would have explored the Object Explorer details window and would have started to get used to it as part of your daily work life.

Searching in the Object Explorer Details Window

Today, we will be doing something a bit more fun and productivity enhancing. Imagine the following scenario.

Scenario:

You want to change the data-type of a column in your database, but are not sure about the impacted areas. You don’t have (or don’t have access to) any database documentation and don’t have the time to create any. What do you do?

Solution:

The Microsoft SQL Server 2008 Management Studio gives you a fast and easy way to do this very quickly and easily via the Object Explorer Details window. Here are the brief steps:

1. Navigate to the Object Explorer Details window by using the View menu or pressing the (F7) key when in the SQL Server Management Studio
2. Drill down to the database against which the search needs to be carried out
3. In the Search bar, enter the object name that you would like to search for (you can use wild-card characters)
4. Simply hit “Enter” to execute the search!

Simply right-clicking on a specific search result and choosing “Synchronize” navigates to the particular object in the Object Explorer!

Right click on a result	Click Synchronize

If no specific database is chosen, the search is executed across all databases in the entire SQL Server instance. This is very useful feature, however, can be very slow. If you happen to use this accidentally, there’s always the panic button labeled – “Stop”!

Salient Features/Benefits:

1. Copy results over to Excel – Select the set of records required (or use “Ctrl+A” for selecting on the results) and use “Ctrl+C”. Doing so puts the tab-separated results (with the headers) on the clipboard, and pasting these in Excel maintains all columns
2. The search is across all object types – Stored Procedures, Views, Indexes and Tables
3. The results grid can be sorted and columns can be chosen and rearranged as per requirements
4. No 3rd party tools required
5. No knowledge of SQL Server internals required

A drawback & Red Gate SQL Search

The search feature is good, but not complete when used out-of-the-box from the Microsoft SQL Server Management Studio. The one feature that does not come is a contains search. The search searches through the object names, but does not search within the objects. Let’s see an example:

CREATE PROCEDURE proc_tmpNakul
AS
BEGIN
    PRINT 'Employee'
END

Let’s try searching for “Employee” and for “Nakul”.

Searching for “Employee”	Searching for “Nakul”

 

Red Gate Software has developed a free tool for more sophisticated or in-depth searching. You can evaluate this tool, SQL Search by downloading it for free at:http://www.red-gate.com/products/sql-development/sql-search/

More Resources:

• Using Object Explorer Details and SQL Server Object Search – http://msdn.microsoft.com/en-us/library/cc646011.aspx

In conclusion:

There are a lot of other things that can be done with the Object Explorer details window. You can view dependencies, confirm whether or not an object is matching up to preset policies and a lot more. The Object Explorer Details described in Part I, along with the using Red-Gate SQL Search make for very powerful database navigation and search.

In the next post, I will be looking at how to filter objects within the Object Explorer itself.

Have a good day!

Be courteous. Drive responsibly.