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.
I’ve seen some very creative ways to see how much free disk space SQL Servers have. Most make use of xp_cmdshell which some very complex dos commands or vbscripts.
However there is a much easier way. Microsoft has included the system extended stored procedure xp_fixeddrives. It returns the all the fixed drives and the amount of free space in megs.
That “s” is new in SQL Server 2005. It means that the SPID is a system process. For example my database is having a problem with tempdb running out of space for no reason. When I look in the log I see that SPID 118s is the offending SPID. When I watch SPID 118 using sp_who3 I see that the output looks a little strange. I see a lot of CPU and disk load and that the thread and been logged in for ever using the sa account but with no hostname, host process, or mac address listed. This is because this is the service broker process which is what runs the activated procedures. Starting in SQL Server 2005 system processes no longer need to run with SPIDs less than 50. Because of this, Microsoft has decided to make live easier when trying to identify the system processes by putting the s after the SPID number in the logs.
This same information appears to hold true for SQL Sever 2008 (so far at least).
When you have data in your table that you need to remove the DELETE statement is the way to do that. Using the DELETE statement without any WHERE clause will remove all the data from your table. The WHERE clause works the same way as the WHERE clause for SELECT and UPDATE statements.
DELETE FROM Employee
WHERE EmployeeId = 4
You can also JOIN to a table when you delete data from a table which allows you to use the second table within the filter. This requires a slightly different syntax than your normal DELETE syntax.
FROM Employee e
JOIN Department ON Employee.DepartmentId = Department.DepartmentId
AND Department.DepartmentName = 'Sales'
As you can see you need to set an alias for the table you are deleting from, and put that alias between the DELETE and FROM words.
P.S. Sorry this didn’t come up sooner, I could have sworn that I wrote it and published it back when I started the series.
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.
While I think that the one click database encryption that Microsoft has built into SQL Server 2008 is a good idea, but I’m not sure how useful it actually is. It’s touted as giving you data encryption of the entire database without any code change.
What this actually means is that if someone stops the SQL Server and copies the data files to another server and attaches the files to that SQL Server the files won’t be of any use. This however isn’t the common way that data is stolen from a SQL Server. A much more common method is someone logs into the database using a perfectly legit account and exports the table or tables which contain the useful information to Excel, Access, csv, etc. This method of database encryption does nothing to protect against this as if you query the table directly you are displayed the data in clear text.
All this database encryption does (as it appears to me) is that it protects the data at rest (ie. when the SQL Server service is stopped) and does nothing else to protect the data. If all you are looking for is a compliance check off saying that you data is encrypted for SOX, HIPAA, the California Online Privacy Protection Act of 2003, etc then this will probably do the trick. If you want actual data encryption where the data is not viewable when you query the table unless you know the key to unlock the data this isn’t it.
If you do want that quick and easy encryption for a compliance check off be sure to check your data storage requirements and IO requirements. Encrypted data is typically larger than unencrypted data and you may need to increase your allocated storage or IO requirements in order to have the encryption completed. Also remember that the higher level of encryption you use the more CPU time is required to decrypt the data.
Feel free to share your opinions below. I’m interested to here what others have to say on the topic.
The NJ SQL Server User Group has been kind enough to invite me to come speak to them about SQL Server Service Broker.
I’ll be speaking up there on August, 19, 2008.
I’ll be giving a similar presentation to the one that I gave at the SoCal Code Camp in February 2008. I’ll be revising the slide deck a little bit based on how that session went. If you are in the area I’d be thrilled to have you come by, listen to me blather on about Service Broker for a bit, and chat. The Address, directions and times are posted on the NJ SQL Server User Group web site.
See you in New Jersey.
The sp_change_users_login procedure has a specific purpose. It’s used to identify and correct users within a database which do not have a corresponding logins.
You can specify the value of Report for the @Action input parameter to see any users which do not have a corresponding login. This only needs to be done when you are restoring a database from one server to another, and the logins on each server were created with seperate SIDs. An example of when this would happen is when you are restoring a database from Production to QA.
After identifying the users which are not synced to a login, use the update_one value for the @Action parameter and with the username of the user as the value for the @UserNamePattern input parameter, and the login name as the value of the @LoginName input parameter. If the login doesn’t exist you can pass the password to the procedure for the @Password input parameter and the login will be created for you and mapped to the user.
Usually Logins and Users are words which are interchangeable with each other. However in Microsoft SQL Server they are very different things. Because everyone assumes that they are the same thing, it can get a little confusing.
Logins are created at the database server instance level, while uses are created at the database level. In other words logins are used to allow a user to connect to the SQL service (also called an instance). You can have a login defined without having access to any databases on the server. In this case you would have a login, but no users defined. The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future). This mapping is what allows the person connecting to the instance to use resources within the database.
If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare. Using the login in each database idea, lets create a login in each database called user1. We set the password for user1 the same on all the databases on the server. We then backup the database, change the password for that user on all the databases, then restore the database. We now have an out of sync password for a single database on the server.
Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name. You would think that the login would have access to the database. However you would be wrong. This is because the SID of the login and the user are different. You have to use the sp_change_users_login procedure to sync the user with the login.
Functions are create little blocks of code. They are fantastic for converting data from one format to another, or for looking up other values based on a lookup. However this comes at a price. That price is CPU power. Doing all these additional lookups can cause extra strain on the database server. It’s often better for the database server to simply join to the table which you are doing the lookup against. However you can guarantee the same lookup is done every time when a function is used.
There are two kinds of functions. One is a scalar function and one is a table function. A scalar function is used as a column of a select statement, or a value in an update statement. A table function is used in place of a table, and can be called directly or as a member of a JOIN. While functions are fairly simple in concept, they are in my opinion the most complex objects to create.
A scalar function accepts one or more input parameters and returns a single value of a predetermined data type. The CREATE FUNCTION command is used to create both scalar and table functions. To create a scalar function we use code along these lines.
CREATE FUNCTION dbo.FindObjectId
DECLARE @ObjectId INT
SELECT @ObjectId = ObjectId
WHERE name = @ObjectName
We use this function to return the object id of an object in the database (yes I’m aware that there are system functions to do this, but it’s an easy function to use as an example).
As we can see a single value is returned in a record set. We can also return more than one value in the record set by using the function against a table.
Creating a table function using much the same syntax. In this case we will return all the object names which have an object type which matches our input parameter.
CREATE FUNCTION dbo.ShowObjects
RETURNS @TableNames TABLE (name nvarchar(512))
INSERT INTO @TableNames
WHERE xtype = @ObjectType
To use this function we use a basic select statement.
We can also use it as a JOIN member.
SELECT sysobjects.name, a.name
JOIN dbo.ShowObjects('U') a ON sysobjects.name = a.name
If you want to get very complex you can use what’s called an inline table function or single statement table function. This is basically a regular table function, but you can only put a single select command within the table function. As you can see from the code below the results will be the same, but the single line table function is a bit shorter. Because we are not declaring a table variable and loading the data into that table variable, then reading from that table variable a single statement table function should have a slightly cheaper execution plan compared to the same SELECT statement within a multi-statement table function.
CREATE FUNCTION dbo.ShowObjects
RETURN (SELECT name
WHERE xtype = @ObjectType)