SQL Server 2005

Checking the cached execution plans

As we all know SQL Server, will for good or bad, cache execution plans.  Up until now it’s been very tough to see the cached execution plans.  You pretty much had to grab the query or procedure (with the parameters) and run it with Query Analyzer and have it return the execution plan.  The only other option was to use SQL Profiler to capture the query along with the execution plan which it used.

 Starting in SQL Server 2005 you now have the ability to query the system catalog and see what query plans are stored in the cache, as well as the queries which trigger that plan to be used, as well as how many times that cached plan has been used.

The first DMV to look at is sys.dm_exec_cached_plans.  This DMV gives you the size of the plan, the handle of the plan (which is binary), and the number of times the plan has been used.

The next DMV to look at is sys.dm_exec_requests.  This DMV gives you the currently running processes, wait information, all the session settings for the processes, etc.  It also includes the handle for all the plans which are currently being run.  If you join this sys.dm_exec_requests to sys.dm_exec_cached_plans you can see exactly which processes are using which plans.  Because this shows you the spid (the session_id column) and the plan_handle you can see what users are running which commands.

The next object is a system table function called sys.dm_exec_query_plan.  This function accepts a single input parameter of a plan handle.  There are several columns in the output, the most important of which is the query_plan.  This is the actual xml plan which is stored in the SQL Server.

The last object we’ll be looking at is the sys.dm_exec_plan_attributes table function.  This function also accepts a single input parameter of a plan handle.  This function returns the various session settings which were in place when the plan was created.  This is important information to have when working with query plans, as changing a single ANSI connection setting will force SQL to create a new execution plan.

Don’t forget how to view the XML execution plan in the UI.

Denny

New Article: Configure SQL Server Service Broker for sending stored procedure data

I’ve just published a new tip on SearchSQLServer.com called Configure SQL Server Service Broker for sending stored procedure data.  In it I’m going over SQL Service Broker and how to configure all the objects.

As a followup to this I’ll be going over all these objects as part of the Back To Basics series over the next several weeks.

 Denny

Back To Basics: The UPDATE Statement

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = ‘Value’
WHERE AnotherColumn = ‘AnotherValue’

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: The INSERT Statement

While the SELECT statement is probably the most important command, the INSERT comes in handy.  The INSERT statement is used to do exactly what it sounds like, it inserts data into a table.

 There are two ways to insert data into a table.  The first is to pass in each of the values, and the second is to insert the data from a select statement.

For both commands we’ll be using a new table with this definition.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertTable')
DROP TABLE InsertTable
GO
CREATE TABLE InsertTable
(id INT,
name sysname)

First lets look at passing in the values. With this syntax we specify the names of the columns, and then specify each of the values.

INSERT INTO InsertTable
(id, name)
VALUES
(0, ‘test’)

Second we’ll look at the SELECT statement. There are two ways we can do this as well. The first is to load a single set of values with the select statement. When doing this you can optionally specify the column names or not.

INSERT INTO InsertTable
SELECT 0, ‘test’

The second option with the SELECT statement is to use a SELECT statement from a table. All of the functionally of the SELECT statement is available when using the SELECT statement as part of the INSERT statement.

INSERT INTO InsertTable
SELECT id, name
FROM sysobjects

We can also do this with some of the more advanced functions of the SELECT statement.

INSERT INTO InsertTable
(name, id)
SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the INSERT statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: Using Common Table Expressions

CTEs (Common Table Expressions) are one of the very cool features introduced in SQL Server 2005.  In there simplest most common form, think of them as a temporary single use view who’s context is only within the command which follows them directly.  The syntax of a CTE is very basic.

WITH CTE_Name (ColumnName, ColumnName) AS
(SELECT *
FROM Table)
SELECT *
FROM CTE_Name

The list of column names as part of the CTE defination is optional.  If all the columns are named this portion is not needed.  Here is an example from the AdventureWorks database.

WITH EmployeeData AS
(
SELECT e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle] ,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName] ,a.[PostalCode]
,cr.[Name] AS [CountryRegionName] ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] eINNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode])
SELECT *
FROM EmployeeData
WHERE CountryRegionName = ‘United States’


When done correctly CTEs can be used to link back to themselves to join child data up the chain so you can access the parent record. This is called a recursive common table expression and is done with a UNION ALL between two queries within the CTE like so.

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevelFROM HumanResources.EmployeeWHERE ManagerID IS NULL

UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1FROM HumanResources.Employee e
INNER JOIN DirectReports dON e.ManagerID = d.EmployeeID)

SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
The first part of the UNION ALL command shows us the top level employees who have no manager. The second query is used to link back to the managers to show the employee information including how many levels down the chain the record is.

Extreme care must be used when using recursive common table expressions as doing this incorrectly can put the SQL Server into a never ending loop while SQL is trying to recurse up the never ending tree.

Denny

Back To Basics: The SELECT Statement

There are four basic commands in databases.  They are SELECT, INSERT, UPDATE and DELETE.  Probably the most important of these is the SELECT command.  The SELECT command is how the data in the database is retrieved and displayed.

(All these code samples can be run on all versions of Microsoft SQL Server from 7.0 up.)

Like a regular sentence there are a few basic parts of the SELECT statement.  First there is a list of columns that you want to see.  Then there is the FROM portion of the statement.  This is the table or tables which you will be getting the data from.  Then is the WHERE portion of the statement which is the data filter.

When you put all this together the SELECT statement will look something like this.

SELECT id, name
FROM sysobjects
WHERE type = ‘U’

If you were going to read this as a normal sentence it would read something like:
I want to see the "id" and "name" columns, from the "sysobjects" table, where the "type" column has a value of "U".

Now if we need to look at data from two different tables which is combined together we add a JOIN statement between the FROM and WHERE portions of the SELECT statement.

SELECT sysobjects.name, syscolumns.name
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’

You can see from the above statement that we are getting the table names and column names for all the user tables. When you look at the JOIN command you see that we are matching up the id column from both of the tables. Now in this example the column name is the same in both tables. This isn’t always the case. If you don’t know how the tables relate to each other, you can usually look at the foreign key constraints to see how the tables relate to each other.

If you want to get counts of data you can use the GROUP BY clause along with the COUNT() function. In this next statement we will count the number of columns in each table.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name

When ever you use a mathematical function such as MIN(), MAX(), AVG(), COUNT(), etc you have to add the GROUP BY clause to the SELECT statement so that SQL Server knows how to roll up the data.

Now as a last part of the SELECT statement to look at today, we are going to filter our GROUP BY query further, by showing all the tables that have over 5 columns. To do this we use the HAVING clause. Without the HAVING clause we would have to do a very complex query as a sub-query in the WHERE clause which would be very inefficient.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name
HAVING count(*) > 5

The HAVING phrase is very simple in it’s syntax. You put the math function that you want to use (in this case COUNT(*)) then <, >, <> or = and what ever you want to compare it to.

I hope that you find this post useful. I’ve barely scratched the surface of the SELECT statement, and what it can do. I encourage everyone to open up Books OnLine and read through the information on the SELECT statement. It includes more examples, and some of the other options which are available to you.

Denny

T/SQL Back To Basics

I’ve seen a lot of posts online over the last few weeks with people asking about basic query syntax.  So over the next few posts I’m going to show some basic T/SQL queries as well as some more “advanced” features and syntaxes.

Hopefully you’ll find these syntaxes useful.

All the code syntaxes will cover SQL 2000 and SQL 2005.  Where code only works in one version or another it will be noted.

Denny

New Article: Create an upgrade plan for your move to SQL Server 2005

I’ve published a new tip over on SearchSQLServer.com entitled “Create an upgrade plan for your move to SQL Server 2005“.

While it’s based on upgrading to SQL Server 2005 it can be used to upgrade to SQL Server 2008 as well.

Denny

So I’ve heard about endpoints, but what are they and how do I use them?

In a nutshell Endpoints are ways that people, or applications can connect to the SQL Server.  There are several different kinds of end points which can be created; four to be specific.  Two are system specific, the SERVICE_BROKER and DATABASE_MIRRORING endpoints can only be used for the SQL Service Broker and Database Mirroring respectively. The other two are for general use.  They are the SOAP and TSQL endpoints.

Without knowing it you use an endpoint to connect to the SQL Server each time you connect.  There are actually 5 endpoints created by default on each instance of SQL Server.  You can check then out by querying the sys.endpoints DMV.  When you connect to the SQL Server using TCP (port 1433 by default) you are using the TSQL Default TCP endpoint.  By default all users have the rights to connect to this endpoint.  You can create other TCP endpoints on different ports for specific users to connect to.  This would be handy if you have several applications coming into the SQL Server from a single server, and you wanted to be able to separate there traffic through the firewall so that the network admins could watch the traffic in the event of an issue.  You could create a TCP endpoint for each application, and assign only that application IP rights to use that endpoint.  You then have the application specify the port number that it will be connecting through in the connection string.

The SOAP endpoints are used in a similar way, but instead of allowing regular TSQL connections they allow SOAP calls to be made directly against the database.  (I’m not that up to speed on SOAP so that’s about all I’ve got on that topic.)

 Endpoints are created with the CREATE ENDPOINT command with various switches depending on what kind of endpoint you are creating and how much security you require on the endpoint.

The endpoint that I’ve used the most would have to be the service broker endpoint.  It’s used to allow SQL Server service broker on one SQL Server to talk to the Service Broker of another SQL Server.

One thing to remember about endpoints is that they are used for inbound connections only.  Outbound connections do not require or use an endpoint.

Denny

Webcast turnout was fantastic

Thanks to all who attended this morning webcast ‘Under The Hood of SQL Server - Checking Out Internals’.  This mornings webcast was a complete success.  The number of people attending was fantastic, and some excellent questions were asked.  If I remember correctly there were about 140 people in attendance.  For those who weren’t able to attend the webcast this morning, it was recorded and as soon as it’s published I’ll post the link to the video.

If you have a question specific to the webcast please email my good friend Andy Grant with your questions and he will route them to myself or Jason Hall.

Don’t forget to check out the tech brief which we were talking about.

Denny