I know this is a SQL blog but I need to take my mind out of the technical mode for a few minutes to gain some sanity and hey, this is a blog after all:).  I was thinking about something my wife reminded me of while we were driving a rental car in Myrtle Beach last week. Being a consultant I rent a lot of cars or all makes & models as I am sure a lot of you do. But how many times have you pulled into a gas station in a rental car and the gas tank filler nozzle was on the wrong side? I am sure more than we want to admit. Well as it turns out every modern car (at least in the US) these days has a little arrow next to the gas gauge on the dashboard that points to the right or left indicating which side the gas cap is on. Now I bet most of you have been driving your own car for years and never even noticed this arrow. Why should you, you know which side it is on. Anyway I just wanted to share this little tidbit of almost useless information (until you actually need it that is). Happy car renting:).

A short while ago I was collecting wait stat information at a client and ran across a very peculiar situation that I would like to share. Let me start by saying that for years I have coded with the understanding that when you include a system function in the SELECT list of a TSQL statement the function was evaluated once at the beginning and that same value was used for each row returned. I am talking about a statement such as this:

                    SELECT GETDATE(), CompanyName FROM Customers

The output expected looks like this:

2008-02-27 10:22:34.270    Alfreds Futterkiste
2008-02-27 10:22:34.270    Ana Trujillo Emparedados y helados
2008-02-27 10:22:34.270    Antonio Moreno Taquería
2008-02-27 10:22:34.270    Around the Horn
2008-02-27 10:22:34.270    Berglunds snabbköp
2008-02-27 10:22:34.270    Blauer See Delikatessen
2008-02-27 10:22:34.270    Blondesddsl père et fils
2008-02-27 10:22:34.270    Bólido Comidas preparadas

...

Please note that I am not talking about a User Defined Function or once that takes a column as an input to determine the result. In this case I am specifically referring to GETDATE().  As you can see all the datetime values are exactly the same as expected.

     But what I experienced the other day was not as expected and quite concerning. What I got was for a single SELECT I received several different values for the GETDATE() column in the result set. This did not happen every time but happened enough times over a few days that I certainly took note of it. Now let me give a little more background because it was not just a SELECT. It was actually an INSERT INTO with the SELECT from a DMV. Not that any of this should matter anyway but for consistency sake let me give you the actual code (with a slight enhancement for demo purposes).  I added an extra column called R_ID that is used to store the unique value of each loop and I placed the Insert in a WHILE loop so it can be exercised.  In real life the Insert was only executed several times each day.  The code below can be used to see if your system is experiencing this behavior or not. Depending on the version and service pack you may have a different number of Waits but in my case with Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) I get 201 rows for each pass. I suspect the version has everything to do with this behavior. The system at the time was running an older version of SQL Server 2005 which was:  9.00.2047.00.  If anyone finds that their server returns different values of GETDATE() for any iteration of the select I would really be interested in what version of SQL Server you are running.  There is a LOT of code out there that relies on the value acting like a constant and having the same value in each row of a single SELECT statement. I suspect this is a bug in that particular version but who knows...  

Please note that the sole purpose of the WHILE loop is just to give you a better chance of seeing the issue if it appears. We are looking for a difference in the datetime values for each instance of the SELECT only and not from loop to loop.

SET NOCOUNT ON

IF OBJECT_ID(N'[dbo].[wait_stats]',N'U') IS NULL
    CREATE TABLE [dbo].[wait_stats]
        ([R_ID] INT not null,
        [wait_type] nvarchar(60) not null,
        [waiting_tasks_count] bigint not null,
        [wait_time_ms] bigint not null,
        [max_wait_time_ms] bigint not null,
        [signal_wait_time_ms] bigint not null,
        [capture_time] datetime not null default getdate())

DECLARE @x INT
SET @x = 1

WHILE @x < 100
BEGIN

    INSERT INTO [dbo].[wait_stats] ([R_ID], [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time])   
        SELECT @x, [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], GETDATE()
            FROM sys.dm_os_wait_stats

    SET @x = @x + 1
END

--  Find the ones that have odd counts. If this returns any rows you had a difference in time for a single itteration.

SELECT [R_ID], COUNT(*) AS [Totals], [capture_time]
    FROM [dbo].[wait_stats]
GROUP BY [R_ID], [capture_time] HAVING COUNT(*) <> 201

 **Updates**

I have some new and very important information about this subject and chose to put it in a new blog post that can be found here:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

    I am still amazed at how many of the database applications written today still disregard some basic rules of thumb when it comes to accessing the data. One in particular is the use of COUNT(*) to check to see if there are any rows that match some criteria. The technique of using EXISTS over COUNT(*) has been widely publicized and is in pretty much every best practices document I have come across. So why are database developers still using COUNT(*) instead of EXISTS all over the place?  If it is because people just don't believe or recognize the benefits of it? Or is the concept of EXISTS difficult for new programmers to grasp?  I am not really sure since I have heard both views. So to cover both bases I will show a little demo of why EXISTS is almost always a better way to code when you simply need to see if there is at least 1 row that matches some condition in the WHERE clause. Of course if you really need to know exactly how many match that condition then COUNT(*) is appropriate so hopefully this won't confuse anyone in that regard.

Lets use the Adventureworks database and turn statistics IO on so we can see the number of reads associated with each query. We will then compare COUNT(*) with EXISTS so there is no mistake on how much work is being done in relation to each other.

USE Adventureworks
GO

SET STATISTICS IO ON

GO

First lets look at a situation in which there is an index to satisfy the WHERE clause and there are only 2 matching rows:

IF (SELECT COUNT(*) FROM sales.salesorderdetail
WHERE ProductID = 870 ) > 0

    Print 'Yes'

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ProductID = 870)
    Print 'Yes'

Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As we can see from the Logical reads there was only a difference of 1 between the two queries since the amount of matching rows was so small. So in this case there was not a significant difference although it was still 1/3 more expensive to use COUNT(*) instead of EXISTS.

Now lets do the same but with 4688 matching rows:

IF (SELECT COUNT(*) FROM sales.salesorderdetail
WHERE ProductID = 897 ) > 0

    Print 'Yes'

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ProductID = 897)
    Print 'Yes'

Table 'SalesOrderDetail'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now the cost for the COUNT(*) is over 5 times the EXISTS. This still may not seem like a lot to you. But if you were calling this queries thousands of times a second this would be a big deal. And remember this index is pretty small overall still.

OK now lets try this on a column with no index in that same table. There are 357 rows that match but there are a total of 121,317 in the table.

IF (SELECT COUNT(*) FROM sales.salesorderdetail
WHERE ModifiedDate = '20010701 00:00:00.000' ) > 0

    Print 'Yes'

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ModifiedDate = '20010701 00:00:00.000')
    Print 'Yes'

Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 331, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is now almost 250 times more expensive to do a COUNT(*) vs. an EXISTS. Both queries scanned the table but the EXISTS was able to at least do a partial scan do to the fact it can stop after it finds the very first matching row. Where as the COUNT(*) must read each and every row in the entire table to determine if they match the criteria and how many there are. That is the key folks. The ability to stop working after the first row that meets the criteria of the WHERE clause is what makes EXISTS so efficient.  The optimizer knows of this behavior and can factor that in as well. Now keep in mind that these tables are relatively small compared to most databases in the real world. So the figures of the COUNT(*) queries would be multiplied many times on larger tables. You could easily get hundred's of thousands of reads or more on tables with millions of rows but the EXISTS will still only have just a few reads on any queries that can use an index to satisfy the WHERE clause.

Hopefully this will help to persuade those last holdouts who insist on using COUNT(*) everywhere even when EXISTS is the clear choice.  One last note, make sure to turn off the statistics IO when done.

SET STATISTICS IO OFF

How many times have you asked for a way to turn off the behavior in SQL Server that logs successful backup messages to the SQL Server Logs and to the Windows Application Event Logs? If you are like most DBA’s probably a lot. I have been asking for this feature for ages. I have sent email to SQLWish, filed on LadyBug and most recently on Connect with never any positive feedback. Well the other day I happened to be on campus in Redmond and was talking to Kevin Farlee (Thanks Kevin) who is a PM on the Storage Engine team for SQL Server.  I asked again if we could have this feature and he said he would get back to me. Well he did and the answer is the ability was already there in the form of a trace flag.  I could not believe this functionality was there all along and no one knew about it. Well at least I didn’t and I am pretty sure most others as well. So how do you use this functionality?  Pretty easy actually and here is an example.

First we will backup the Northwind database (you can substitute your own) to disk. We will then use another feature of SQL Server that I think is also under utilized, sp_readerrorlog to place the contents into a temp table so we can query against it. We then can see the backup message that was logged. 

 BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

GO

IF OBJECT_ID('[tempdb].[dbo].[#ErrorLogs]',N'U') IS NOT NULL

    DROP TABLE [dbo].[#ErrorLogs]

CREATE TABLE [dbo].[#ErrorLogs]

    ([LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ;

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

SELECT * FROM [dbo].[#ErrorLogs]

    WHERE [Text] LIKE 'Database Back%Northwind%' ;

Next we will turn on Trace Flag # 3226 and try it again. This flag can be set via TSQL or via a startup parameter.

DBCC TRACEON (3226)

GO

BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

GO

TRUNCATE TABLE [dbo].[#ErrorLogs]

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

SELECT * FROM [dbo].[#ErrorLogs]

    WHERE [Text] LIKE 'Database Back%Northwind%' ;

Notice that now the most recent backup is not in the log nor will it be in the Event log. No longer will we have to weed thru all those successful messages just to see if there was a failure.  Yes all failures will still be reported as before.  And if you want to get back to the default behavior just turn the flag back off as such:

-- To turn the behavior off

DBCC TRACEOFF (3226)

I recently was bitten by some not so obvious behavior with DBCC OPENTRAN() that I would like to share.  Basically this command is supposed to show you the oldest open transaction within the specified database or the current one if none is specified. If you run the example below you can see the expected behavior.

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO 

 This should show you something similar to this:

But now add a PK constraint into the mix and run it again. 

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
ALTER TABLE [dbo].[T1] ADD CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([Col1] ASC) ;

GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO

You now get this:

   No active open transactions.
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 Wait a minute, what's going on here. Are you telling me that just by adding a PK I changed the transaction? Well yes & no. If you look closely at the UPDATE statement it is updating the value with the same value that it already had. If you substitute the UPDATE with the one below you will get the expected output from DBCC OPENTRAN().

    UPDATE [dbo].[T1] SET [Col2] = 'xyz' WHERE [Col1] = 101 ;
 

In reality the transaction is not logged even if we create a clustered index vs. the PK constraint. A Non-Clustered index has the same effect as no index. So what does this all mean?  Based on some assumptions that I and some fellow MVP's had regarding this along with some further testing I came to two conclusions.

1.  As expected the Clustered Index or PK allows SQL Server to pinpoint the row and optimize the work done. But it must also be deeper rooted than that when it comes to how it actually logs this information in the transaction log.  

2.  The Engine is smart enough to realize that no changes have actually been made and does not log anything into the transaction log when the column in the WHERE clause has a Clustered index or PK constraint.

Since DBCC OPENTRAN() looks into the Transaction Log for these open transactions we can assume that there are certain optimizations built into the engine that minimize logging under the right conditions. So if you ever wonder why OPENTRAN isn't returning what you expect you should look to see if this may be the reason.

I had known for a while that as of Windows 2000 there was a way to create what I called a Drive Shortcut but I never could find the documentation on how to actually do it. It turns out this was included in the resource kit which is probably why i never stumbled across it. But I still get asked on a regular basis if it is possible to map a specific folder location to a logical drive letter similar to the way you map a networked share. The idea being that it would look like any other drive except it would actually point to a much longer or obscure physical mapping. Well I just saw a post from Mark Russinovich that talks about his utility called Junction that allows you to do this very easily without the resource kit. Windows Vista has a neat little feature in the Windows Explorer that allows you to map folder locations as a favorite but from what I can see you still need something like this to make it visable from other places outside Windows Explorer as well.

 http://www.microsoft.com/technet/sysinternals/FileAndDisk/Junction.mspx

As a long time consultant I have certainly had my share of travel woes. And although this latest experience is not the worst experience I have had I think it rated an entry in my blog. I (along with 1700 other MVP's) spent the last few days out in Redmond at the 2007 MVP summit. It was a great time and a fantastic opportunity to get a heads up on the upcoming version of SQL Server called Katmai. The last night a few of my fellow MVP's and I spent the night with a few unnamed members from the SQL product team visiting the different nightlife establishments in Seattle until about 3:00AM. But unfortunately that is where the fun ended. It started the next morning when I had to get up with a terrible hangover and head off to the airport. The 4 plus hour plane ride to Detroit was basically uneventful but also very uncomfortable due to the fact the head rest did not match my body height. This made the hangover even harder to deal with. Then once I arrived in Detroit I was informed that due to the storm on the east coast I could not make it back to Manchester NH until at least 6:30PM the next night. By this time all the local hotels were booked solid. I found a Fairfield Inn on the web that was about 15 miles away and booked a room. The airline told me they were not booking in hotels that did not have a shuttle so I figured it would be fine and I would take a cab after I got a bite to eat at the airport. They told me if I wanted my luggage it would be about 4 hours wait so I figured I could do without it for a night and after dinner I went to find a cab. After 40 minutes in line waiting for a cab I head off for the hotel. When it arrives at the hotel the driver dropped us off on the backside and since there were no cars in the parking lot I thought this was a good plan and I am all set. Then I turned the corner and spotted the buses leaving and the line of people out the door into the parking lot. The airlines just sent over about 80 people that all needed to be registered at the hotel and they were now ahead of me even though I did have a reservation already. So the waiting in line began with the first part the most difficult due to the fact I had no jacket and it was about 30 degrees and windy. After about 45 minutes I made it in to the lobby and started to thaw out. But it was another hour and a half before I actually got my room key. That night went pretty uneventful from there and now I sit and wait to get another cab back so I can fly out tonight. As you can see this was an unpleasant ordeal but not so much even trivial compared to other events going on in the world today. But it did make for really bummer of an ending to what was otherwise a fantastic summit. And I am sure I can look forward to some relaxing snow shoveling when I get home as well.