Andrew Kelly

New England SQL Users Group & Craig Freedman

2008-05-06T19:50:23Z

Adam Machanic has already blogged about Craig's visit to New England coming up on May 8th but I wanted to re-iterate some points for the benefit of the folks planning to attend. Due to the large crowd expected we really need you to RSVP if you plan to attend to ensure we have enough chairs and Pizza for everyone:). Red Gate Software is sponsoring the event and it would be great to get the head count as close as possible to maximize the event potential. They made it possible to get a great speaker like Craig as out guest. See the link below if you need more info on how to RSVP or just want more details.

http://sqlblog.com/blogs/adam_machanic/archive/2008/05/02/new-england-nesql-special-meeting-featuring-craig-freedman.aspx

Thanks and hope to see you there...

OT - Where is the gas cap?

2008-04-04T13:33:50Z

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:).

When a Function is indeed a Constant

2008-03-01T16:19:19Z

In my last blog post:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx

I mentioned that I ran across a situation in which GETDATE() used in a SELECT statement occasionally returned more than 1 value for the result set. That sparked quite a debate amongst SQL Server programmers who belonged to one of two camps in how they believed this really should behave. For instance if you run the following SQL statement:

SELECT GETDATE() AS [The Datetime] FROM sys.sysobjects

Should you expect all the rows to have the same DateTime value or would you expect a different value for each row? Now in this simple example it would be easy to say that the query was so fast that there was not enough elapsed time between rows to give a different DateTime value and you could very well be correct. But this next example calls a UDF that introduces some lag as a result of the WHILE loop and returns GETDATE(). If you run that example you will definately see differences in the return from the function. But what about the plain GETDATE()? Will it be the same value for all rows or similar to the UDF output? After all they are both spitting out GETDATE() right? Well try it and see.

USE tempdb
go
CREATE FUNCTION dbo.test$wait()

RETURNS DATETIME
AS
   BEGIN
       DECLARE @I INT SET @I = 1
       WHILE @I < 10000
         BEGIN
           SET @I = @I + 1
         END
       RETURN (GETDATE())
   END
GO
SELECT GETDATE() AS [Getdate],dbo.test$wait() AS [UDF]
    FROM master.sys.sysobjects

As I am sure most of you will see it turns out that GETDATE() or any other non-deterministic runtime constant scalar function will indeed act as a constant for the life of the query execution. What does that mean exactly? Well in a nutshell it means these functions will be evaluated once at the beginning of the query execution and that value will be used for all resulting rows. You will always get the same value and if you don't you have a bug. This behavior was confirmed today by one of the SQL Server developers who maintains this code and guarantees this behavior.

While this may not be news to many of you (I was always under the impression this was the intended behavior) but it is to many others. There has been a lot of code written over the years that expects functions like this to return a potentially different value for each row in a single Select statement. That assumption is wrong and I wanted to get the word out there in order to minimize any future coding errors in this regard. As you can see in my original blog post that there is at least 1 older revision of SQL Server in which this was not the case, but don't let that fool you into thinking that is the intended behavior. Keep this behavior in mind as you code along in the future.

When GETDATE() is not a constant

2008-02-27T15:53:00Z

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

Getting back to the basics with I/O

2008-01-13T15:25:13Z

One of the most common trends that I see related to performance & scalability with SQL Server is a poorly configured or implemented storage subsystem. There is a ton of information out there on this subject but in my opinion that is part of the problem. Too much data is not always a good thing and there is a lot of misinformation out there as well. I also see a lot of systems that were configured based on the advice someone gave them or they read about which may have been great for that other system but not necessarily for theirs.

So I think it is time we got back to the Basics and Best Practices when it comes to I/O in SQL Server. Again in my opinion and my experience from seeing systems all over the world this list should get people off on the right foot if they are not sure what they need or how they should approach a proper I/O configuration. The first article hits the nail right on the head and is a great place to start. The next two give a very good understanding of what actually goes on when SQL Server makes I/O requests and explains the terminology so that everyone can talk the same language. The 4th link is a relatively new white paper to most that should be sort of a bible and gone over long before you deploy or even buy the equipment for your next SQL Server. And finally there is a link that everyone should be aware of that gives you access to a whole host of white papers that should be read as needed.

SQL Server Storage TOP 10 Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

SQL Server 2000 I/O Basics

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

I/O part 2 for updates to SQl2000 SP4 and SQL2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

SQL Server I/O Pre-Deployment Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Overall SQL Server Best Practices

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Exists Vs. Count(*) - The battle never ends...

2007-12-16T01:27:26Z

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

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

Vista's Perfmon Reports

2007-12-15T02:48:06Z

A short while back I had to buy a new laptop and one of the choices I had to make was whether to run Vista or XP. While that is the subject for a whole different blog post the short answer is I decided to go with Vista Ultimate. I knew a lot of things changed between XP and Vista but it never dawned on me that Perfmon got such a makeover. I don’t mean the real time counter monitoring aspect or what we call the Performance or System Monitor, I am talking about the Counter Logs mode. This is the area in which you can set up counters to be collected behind the scenes and in Vista is now called “Data Collector Sets”. Even though all of the previous functionality is still there plus a whole lot more the look and feel is totally different. Now my intent is not to talk about all of these changes per say, just one aspect in particular. But I highly recommend if you haven’t played with Perfmon in Vista or Longhorn that you do so when you get a minute.

The section I want to highlight here are the new reporting features found under the Reports section of Perfmon. After you have created a new Data Collection Set you will see a corresponding entry in the Reports section as well. If you look at the properties of this you will see a dialog similar to the one below.

Again I am not going to go into all the aspects of the data manager but if you check the checkbox in the lower left hand corner you get the ability for Perfmon to automatically generate a series of XML and HTML reports each time the collection set is completed. I see a lot of people do a fair amount of manual labor to generate a report similar to what you now get for free. It will look similar to the report I show below:

Of course it will be specific to the counters you set up in the data collector set but you get the idea. Overall it will take some getting used to the new features and layouts in Perfmon but there is no getting around it. Sooner or later we will all end up on Vista or Longhorn anyway. The new look can be quite confusing at times but it brings a whole host of new features that just wouldn’t be possible the old way.

Good luck

Successful Backup Messages No More...

2007-10-29T17:09:00Z

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

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)

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

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)

Double Standard?

2007-09-27T20:22:00Z

While this is nothing new, a conversation I had with a client the other day got me thinking more than I wanted to about what I see as sort of a double – standard for lack of a better term at the moment. Now I fully realize this can turn into a full out war of opinions, I feel the need to blog about it. But please keep in mind the focus of the argument and understand that I am in no way bashing or hyping one side vs. the other. What I am referring to is the mindset that it is OK or even encouraged for an application developer to develop both the app objects and the database objects. But it is not OK for a DBA to also develop application code. The point the client had was that the developer of an application needs to be somewhat of an expert in C#, VB etc. but they don’t need to know that much about databases to do a adequate job because SQL Server is simple. So the general idea seems to be that a DBA doesn’t require as much skill to architect a database as a C# developer needs to develop an application. So most people would never think of letting a DBA develop both the application and the database but it’s perfectly fine the other way around. Now I have been both a developer and a DBA so I have seen both sides of the fence and if you are talking about an application that doesn’t require a large or complicated database schema I don’t see too much problem with that. But how many small apps stay that way? Or what about ones that were always intended to be large and complicated? Why do so many people think that there is less of a need for a truly qualified person to design the database side? I see way too many apps that suffer from poor database design and implementation, especially the larger they get. If you wait until the database is hundreds of GB’s or even TB’s in size before you make the proper design changes to accommodate that it is often too late to do the right things. I don’t think that is too hard of a concept to understand or even agree with. Sure there are people who can be experts as both an application developer and a database architect as in any two skilled trades. But let’s face it that is the minority not the majority. I simply don’t expect most DBA’s to be experts in both SQL Server and C#. But I also don’t expect most developers to be experts in C# and SQL Server either. So why the double standard? Why do so many companies today feel it is perfectly OK to have the C# developer also do the database design and coding? What is it about proper database design and architecting that appears to be so simple that they feel developers can do just as good a job on the database with dramatically less training and experience than they typically have for the application side?

DBCC OPENTRAN() behavior

2007-09-25T20:25:00Z

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:

    Oldest active transaction:
        SPID (server process ID): 52
        UID (user ID) : -1
       Name          : user_transaction
       LSN           : (22:248:502)
        Start time    : Sep 25 2007 5:28:59:700PM
        SID           : 0x010500000000000515000000864be9f541b8a3fc1f944d76e8030000
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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.

LINQ to the rescue

2007-09-06T15:22:00Z

I heard someone state that LINQ (see http://www.sqlmag.com/Article/ArticleID/48759/sql_server_48759.html for details on LINQ) was going to rescue developers from having to know TSQL. Well if that's true then who will rescue us from LINQ? LINQ like so many other technologies that have come about makes certain things easier by abstracting the underlying objects or code and providing a "simpler" interface. That sounds great but as we have learned over and over again this comes at a price. The price here will ultimately be performance in the database. LINQ will allow you to write an English like statement that will be translated into TSQL and sent to the database as essentially an adhoc sql statement. So again instead of steering developers towards writing efficient and reusable stored procedures we give them the exact opposite. We will end up with code that has little chance of being optimized properly and few chances of getting plan reuse. As a consultant specializing in scalability and performance I can tell you that the number one culprit out there in this regard is exactly these types of applications. Ones in which the developers are coerced or even forced into using adhoc code due to time constraints or technologies such as LINQ. Now don't get me wrong I am not blaming these issues on developers, I used to be one:). They just happen to be the ones creating the code for the database these days. Most DBA's if they had their way would prefer to use stored procedures first. Only time will tell but I expect my job as a performance consultant to heat up in the future once everyone starts using LINQ. You see most large apps started out small and performance isn't as much of an issue then. But they tend to grow very large these days and adhoc sql and poorly optimized queries just don't cut it in the long run. I hope to be proven wrong but history leads me to believe otherwise. Any way happy coding...

Junctions in Windows

2007-08-21T12:43:00Z

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

Customizing BooksOnLine Help Collection

2007-07-13T17:18:00Z

I got into a situation the other day where I looked up an entry in BOL (BooksOnLine) and it pulled up the command for SQL CE edition instead of SQL Server 2005. Now in this case the command was "Update Statistics" and it just so happens the CE version has an ON clause where as the regular SQL Server editions don't. Since the CE version was the one that popped up first I just assumed I was looking at the correct command. But after fiddling with it for quite some time with syntax errors I was very disappointed to find I had been looking at the CE version all along. What idiot developer decided to make a command in CE that did the same thing, and was named the same as the regular edition yet have slightly different syntax is beyond me. But I digress.

Any way this is certainly not the first time something like this has happened with help files for SQL Server or Visual Studio. But hopefully I can now make it the last. I was reminded from a fellow MVP that you can choose which technologies get searched in BOL from the Search Screen by clicking on the little button with the down arrow next to the word "Technology" on the search screen. If you uncheck CE for instance it will no longer be considered in the Searches. But that still didn't fix the Index portion of BOL. I then learned from a former MVP (now Microsoft employee) by the nickname of UC that you can do something similar with the index as well. It is actually called the "Help Collection". You can read up about more details’ of Help Collections in BOL at this location: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlgtst9/html/ef798cc8-87cf-4d60-a7bf-9e061bdd0052.htm. But near the bottom in the "Removing Help Collections" section there is a link to a utility that can allow you to set what gets used when you use the regular part or Index of BOL. This is listed as the "SQL Server 2005 Combined Help Collection Manager" and brings you here: ms-help://MS.SQLCC.v9/sqlcc9/html/b06d0f98-ef00-4b03-9f5d-b5c184b8df92.htm. So if this has ever happened to you or you simply want to fine tune what areas get searched you know where to go.

The Joy's of traveling

2007-03-17T14:49:00Z

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.

What are you waiting for?

2006-11-24T18:29:00Z

Another question I asked at my pre-con seminar last week at PASS in Seattle troubled me somewhat. This question was “How many people currently collect Wait Stats.” Just a few people raised their hands out of about 105 in total. While you do have to keep in mind that this session was for people wanting to know more about performance monitoring it was still a very low percentage. Collecting Wait Stats is such a simple task and has virtually no overhead. Reporting on it is easy as well. So easy in fact that together with the wealth of information Wait Stats can give you there is no excuse for not collecting these figures on a regular basis. So if you are not I have to say shame on youJ. I won’t go into details about how to collect or analyze these since the SQL Server Customer Advisory Team has a new web site full of information on subjects just like this and a lot more. If you haven’t checked out this site yet you should make it a priority. This is going to be one of the most popular SQL Server sites in a very short time when it comes to performance and scalability tips and documentation. Wait Stats were just a teaser to get you interested so don’t stop there, enjoy all the great documentation.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx