Andrew Kelly

What 3 events Brought Me Here

Andrew Kelly — Wed, 03 Feb 2010 05:17:41 GMT

I am a few weeks late getting to this but here we go. Paul Randal tagged a few people including me with this challenge to state what 3 events got us to where we are today. Here is my story: Event 1 I suppose the first event was when I decided to...(read more)

SQL 2008 R2 Enhancement

Andrew Kelly — Mon, 01 Feb 2010 23:01:44 GMT

The additions or changes in SQL Server 2008 R2 are mostly BI related but there is one I just became aware of that serves a more general purpose but at the same time is fundamental in how the storage engine works related to locking. I won’t...(read more)

Speaking at the Columbia SC Code Camp

Andrew Kelly — Sun, 24 Jan 2010 22:37:02 GMT

Just an FYI that I will be presenting at the Columbia South Carolina code camp this Saturday the 30th of January along with several other MVP’s and featured speakers. I will have two sessions: Maximizing Plan Re-use in SQL Server Storage and I/O...(read more)

My Greatest Weakness

Andrew Kelly — Tue, 15 Dec 2009 19:58:18 GMT

I was just called out ( see here ) to blog about this topic by a good friend Mike Walsh who’s twitter name is @mike_walsh and it has been a long time since I blogged so here goes. First off lets be realistic and recognize that we as humans all...(read more)

[OT] – Cool Spider

Andrew Kelly — Sun, 13 Sep 2009 16:34:13 GMT

I was out cutting the grass this morning and almost ran right into this web with this Banana spider in it. As I was taking some pictures a small grasshopper got stuck in the web and the spider immediately jumped into action wrapping it up and I assume...(read more)

Speaking in Columbia South Carolina

Andrew Kelly — Mon, 10 Aug 2009 19:20:26 GMT

I know this is late notice but I will speaking at the PASS chapter in Columbia South Carolina tomorrow night (August 11th) on Maximizing Plan Reuse. So if you are in the area please stop by and say hello. More details and directions found here: ...(read more)

Speaking at Best Practices in DC Next Month

Andrew Kelly — Fri, 10 Jul 2009 02:34:50 GMT

While I have been speaking about Best Practices for years this is the first time that I will speak at an actual Best Practices conference :). I have 3 sessions entitled: SQL Server Configuration Best Practices Storage and I/O Best Practices for...(read more)

Windows Live Writer – Good or Bad?

Andrew Kelly — Fri, 26 Jun 2009 21:27:00 GMT

<Updates at bottom of post> OK this will probably start a fire storm from Die- Hard Live Writer (LW) geeks but oh well :). I don’t blog as much as I would like and one reason is that I have had so much trouble getting posts properly formatted that...(read more)

Cleaning up Un-Named Defaults

Andrew Kelly — Fri, 26 Jun 2009 16:07:00 GMT

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write...(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)

SQLCMD Execution Order

Andrew Kelly — Fri, 05 Jun 2009 23:53:15 GMT

I find that many people don’t realize since SQL Server 2005 that you have the ability to execute SQLCMD commands or scripts directly in the Query Editor but it can come in handy at times. Even I don’t use it that as much as I should and I am aware of it:). One of the things I find it most useful for is during demos at presentations when I want to invoke an OS level command without having to use xp_cmdshell or opening a DOS window. For instance once you have enabled SQLCMD mode in the Query Editor you can issue this command to look at the contents of a folder:

!! DIR C:\*.*

That is all well and good but one thing I didn’t know until recently is that the SQLCMD commands always execute before any TSQL commands in the batch. I usually run the commands one line at a time so this was never an issue and as such I never paid much attention to the order of execution. If you run this simple example on your machine you can see the results in person.

PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

!! systeminfo

PRINT '3: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '4: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

The results will look similar to this:

Host Name:                 LAPTOP_IBM
OS Name:                   Microsoftr Windows VistaT Ultimate
OS Version:                6.0.6002 Service Pack 2 Build 6002
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation

…

Network Card(s):           3 NIC(s) Installed.
                           [01]: Intel(R) 82566MM Gigabit Network Connection
                                 Connection Name: Local Area Connection
                                 DHCP Enabled:    Yes
                                 DHCP Server:     192.168.1.1
                                 IP address(es)
                                 [01]: 192.168.1.100
                                 [02]: fe80::3ca9:45fd:f7e4:c5e2

1: 2009-06-05 19:42:37.403
2: 2009-06-05 19:42:39.400
3: 2009-06-05 19:42:39.400
4: 2009-06-05 19:42:41.400

Note that the PRINT statements are all after the output of the systeminfo command even though it fell in between the PRINTS and the WAITFOR’s. I added the WIATFOR’s just so you can see there was a gap in between the first and last two PRINT’s yet no gap where the SQLCMD should have run. If you are still not convinced you can also rn this demo as well:

PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:10' ;
!! time

PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

You will have to hit the stop button to kill the batch since the time command is waiting for input. But it does show that the 1st PRINT statement or the WAITFOR never ran as the only output when you cancel the batch is the actual time output from the time command. While this may not be news for some of you I bet it is to most and I figured it was a nice little tidbit to share.

Have fun,

Andy

So the Real story Is….

Andrew Kelly — Sat, 09 May 2009 21:02:41 GMT

As many of you heard ( see Kevin’s post http://sqlblog.com/blogs/kevin_kline/archive/2009/05/06/microsoft-marketing-throws-sql-server-under-the-bus.aspx ) MS marketing released a comment last week that indicated SQL Server couldn’t handle the load for the Win 7 downloads. But it appears that reality sets in and we get a much different story according to the SQL CAT team. Have a look here:

http://sqlcat.com/faq/archive/2009/05/08/windows2008-r2-beta-download-runs-smoothly-now.aspx

It’s truly amazing how fast people are to blame SQL Server for lack of performance when they don’t have a handle on their environment in the first place :).

[OT] To Tweet or Not to Tweet…

Andrew Kelly — Sun, 03 May 2009 14:35:00 GMT

Last night I acted like a Twit and joined Twitter (@GunneyK) and now I am not sure if that was a good idea or not :). For one I have no clue what it’s really all about, I am not even sure I know how to even reply properly:). And once I figure it all out will I even have time to keep up with it. The lack of time is the main reason I never got into FaceBook even though I have lots of friends and relatives there. Same here with the blogs. But I have to wonder how do all of you keep up with it all these days. Does anyone get any sleep anymore…

Sometimes you just have to break it up

Andrew Kelly — Tue, 14 Apr 2009 23:19:02 GMT

I will be the 1st to admit that I am no Itzik Ben-Gan when it comes to writing complex SQL statements and I wonder at times how people come up with some of the Selects that I see. Sometimes they are so complex or convoluted that I simply have a hard time figuring out the intent or the logic behind them. But as a performance consultant I pretty much find myself looking at these on a regular basis when they end up scanning very large tables or indexes. I find that many developers have a hard time splitting up the query into what I think are more manageable pieces, especially if there is a temp table involved. Lets face it temp tables are evil aren’t they :). Well that’s a whole another discussion which we won’t get into now but sometimes they can be be your friend. For instance today I took a rather unwieldy query that was being used in a stored procedure in which the optimizer simply couldn’t come up with an efficient query the way it was written. And one of the issues was that they needed a total count for paging purposes so they were running the same query twice, once for the count and once to get the TOP xx rows. In a nutshell I changed the query to use a UNION which allowed the optimizer to focus better on the SARG’s for each part of the query and placed the resultant rows into a temp table. This gave me the ability to get a count and retrieve the rows using TOP xx without running it twice. As you can see from the picture the costs were dramatic. The reads went from over 3 Million to just under 5 thousand and the duration went from over 23 seconds to 150 milliseconds. So bottom line is don’t be afraid to make the query more maintainable and break it up at times. You might be surprised what you can accomplish.

Speaking in Raleigh NC

Andrew Kelly — Sat, 11 Apr 2009 18:14:29 GMT

Just an FYI for anyone in the Raleigh NC area that I will be speaking at this location for the first time on April 21st. This is a fairly new SQL Server User Group and PASS chapter so I am sure they won’t mind any free marketing they can get :). I am sure there is a ton of SQL Server users in the area so come out and support the local user group and take in some SQL knowledge at the same time. The topic is “Getting the most out of SQL Trace” and you can find details here:

http://www.tripass.org/dnn/

We hope to see you there.