Andrew Kelly : Documentation

Finding IP Addresses With CMS

Andrew Kelly — Mon, 25 Jul 2011 20:14:29 GMT

I was just tasked with putting together a list of all the IP Addresses of the SQL Servers that one of my clients deals with on a daily basis. In this case there was over 30 of them and while I could ping each one individually I found an easier...(read more)

The latest edition of the SolidQ Journal is now available

Andrew Kelly — Mon, 11 Jul 2011 23:57:44 GMT

The SolidQ Journal is published monthly by SolidQ and packed with lots of good information related to SQL Server and related topics. If you haven’t already signed up to get these each month then have a look here . It’s a free resource that...(read more)

SQL Server 2008 R2 Analysis Services Operations Guide

Andrew Kelly — Wed, 01 Jun 2011 22:03:00 GMT

Some members of the SQL CAT Team just released an Operations Guide for SQL 2008 R2 Analysis Services that can be found here . While I am not a SSAS person by any means this looks pretty cool and worth while for anyone who does work with it so have a look....(read more)

New Book on the Query Optimizer

Andrew Kelly — Sat, 09 Apr 2011 22:30:04 GMT

A friend of mine Benjamin Nevarez has written a book on the inner workings of the Query Optimizer and it was just released. I read a preview and was definitely impressed so I expect the full version to be just as good. More details can be found...(read more)

System Center Advisor Collection Details

Andrew Kelly — Thu, 07 Apr 2011 03:39:26 GMT

The System Center Advisor team (formally Project Atlanta) now lists some pre-release documentation on exactly what information they collect and how you can view it before it gets uploaded. So if you are wondering what they collect you can have...(read more)

Project Atlanta Has a New Name

Andrew Kelly — Tue, 22 Mar 2011 17:57:23 GMT

Brad Anderson just completed a keynote in Las Vegas at the Microsoft Management Summit 2011 and announced the new name for what was originally called Project Atlanta. Dot da da dah… And the new name is: System Center Advisor . You can...(read more)

The January 2011 Edition of the SolidQ Journal is Out

Andrew Kelly — Mon, 17 Jan 2011 18:21:45 GMT

The latest edition of the SolidQ Journal is now available online at http://www.solidq.com/sqj/Pages/Home.aspx . Enjoy, Andrew J. Kelly...(read more)

Latest SolidQ Journal Plus Giveaways

Andrew Kelly — Fri, 17 Dec 2010 19:34:33 GMT

You can find the latest edition of the SolidQ Journal here that is always good reading but if you register over the next 3 weeks you may be eligible for a prize including: One $500 Amazon gift card and 5 $150 gift cards; books from Itzik...(read more)

White Paper on Parallel Processing

Andrew Kelly — Fri, 10 Dec 2010 15:24:35 GMT

I just ran across what I think is a newly published white paper on parallel processing in SQL Server 2008 R2. The date is October 2010 but this is the first time I have seen it so I am not 100% sure how new it really is. Maybe you have seen...(read more)

SQL Server 2008 R2 System Views Map Poster

Andrew Kelly — Thu, 09 Dec 2010 01:21:23 GMT

The latest SQL Server 2008 R2 systems views map poster is available here: 2008 R2 Poster It’s available in a pdf or xps format so have fun with it. Andy...(read more)

Updates for Some 2008 Tools

Andrew Kelly — Sun, 20 Jun 2010 03:03:00 GMT

There have been a few releases over the last few days for some SQL Server 2008 tools that you may be interested in. The first one is the 2008 R2 Best Practices Analyzer found here: http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/06/19/sql-server-2008-r2-best-practices-analyzer-is-now-available.aspx...(read more)

Trace Flag Groupings

Andrew Kelly — Sun, 21 Jun 2009 15:56:52 GMT

I just saw a news group post by Paul Randal in which he explained how trace flags were grouped into certain categories and thought others may find this interesting as well. Please note that Paul stated that these are from memory so they may not be 100%...(read more)

What time do you have?

Andrew Kelly — Sat, 20 Dec 2008 13:12:43 GMT

The way in which SQL Server has treated Time has been a very misunderstood subject for as long as I can remember. The largest part of that was the fact that SQL Server was only accurate to 3.33ms. I would like to point you to an excellent article on this and other details surrounding Datetime in SQL Server by Tibor Karaszi found here: http://www.karaszi.com/SQLServer/info_datetime.asp Well now with the release of SP3 for SQL Server 2005 (and of course SQL 2008 as well) SQL Server no longer uses the inaccurate techniques associated with the RDTSC component of Windows. Since there is a lot to explain about how this all works I will again point you to another link which goes into much greater detail. http://blogs.msdn.com/psssql/archive/2008/12/16/how-it-works-sql-server-no-longer-uses-rdtsc-for-timings-in-sql-2008-and-sql-2005-service-pack-3-sp3.aspx I for one say it is about time:).

Debug button in SSMS 2008

Andrew Kelly — Wed, 17 Sep 2008 20:44:59 GMT

How many people are tired of accidentally hitting the toolbar button with the green triangle on it when trying to run a query in the Query Editor only to get into the debug mode? If you are like me then I bet a lot of you:). It only seems natural to hit the Green button to run something right? Especially since it is right next to the execute button to begin with. Maybe if you are more of a developer and use Visual Studio a lot its not so bad. Well I got tired of it real quick so I simply removed it from the toolbar altogether and no longer have to think about it. For those few times you really want to debug the script you can still access it from the menu or by hitting Alt - F5. These are such simple solutions but we tend to forget about them so this is just a reminder that you can control your SSMS life after all:).

When a Function is indeed a Constant

Andrew Kelly — Sat, 01 Mar 2008 16:19:19 GMT

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.