Andrew Kelly

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.

How Dirty are your Reads?

Andrew Kelly — Fri, 10 Apr 2009 23:08:11 GMT

As a SQL Server consultant I get to see a lot of different sets of code written by all sorts of database developers from all over the world. And I have to say that one thing that is clear is most developers make abundant use of the NOLOCK query hint or the READ UNCOMMITTED isolation level. Now don’t get me wrong, I am NOT trying to say this is bad and people should not do that. There is a place and time for all the features and hints SQL Server has to offer and these can be quite handy indeed. The question is “Are these methods overused or even abused” by the average shop these days? I had a recent conversation with someone close to the SQL Server dev team who stated that some of the members of the dev team would be surprised to hear that. Personally I am surprised to hear that they would be surprised to hear that :). So I ask the rest of the known universe out there what you think. Are dirty reads abused more than they should be? Technically if they weren’t doing anything wrong at all it wouldn’t be abuse now would it? But I think you get the point :).

But before you answer that question let me talk a little about what the dangers of dirty reads using either NOLOCK hints or setting the isolation level to READ UNCOMMITTED are. None of this information is new and lots of people have already written about the pitfalls many times. Itzik Ben-Gan for one, publically speaks on this subject often. But I find developers on a regular basis that fail to realize all of the implications. Lets touch on the more common ones here:

Dirty Reads:

This sounds like an obvious one but many people simply don’t understand that using NOLOCK as a hint doesn’t just prevent you from blocking other users, it allows you to potentially read dirty data. You never know when or if you have read dirty data so unless the data is read-only or static so you may be making decisions based on incorrect values. After all this is the main purpose of locking in the first place right.

Read Errors:

What happens if you are in the process of reading a row or page that someone deletes or reading a page that is in the process of splitting? You can and most likely will get an error. This is a fatal error and most applications are not set to properly handle these types of errors.

Reading Rows Twice:

If you are in the process of doing an IAM scan and read a page that gets split after you just read it you can actually read up to half the rows from that page again since the split page will go to the end of the IAM chain.

Missing Rows:

This is the opposite of the scenario above. You can actually miss rows if a page is moved back in the IAM chain before you read it.

So the bottom line is that if your application depends on accuracy and data integrity then you probably shouldn’t be using this isolation level for that part of the applications queries. Yes as I mentioned earlier there are certainly good uses for this and it can speed up certain queries and reduce the number of locks generated. But as with any technology there are always two sides to consider as nothing is for free. Are you writing an app that is using NOLOCK and calculating my checking account balance? I certainly hope not. Unless you want to credit me with some extra cash in the process :). But if your app doesn’t take into account all of the things we just discussed I say you are abusing it. If it costs you accuracy / integrity or stability when it wasn’t intended then yes you must seek help as you are an abuser :). So what do you think, is the world full of abusers?

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

RML Utilities Have Been Updated

Andrew Kelly — Thu, 13 Nov 2008 23:08:11 GMT

The RML utilities from PSS have just been updated to include support for SQL Server 2008 along with various enhancement and bug fixes. If you don't know what the RML utilities are I suggest you have a look at this link and take a few minutes to download them and play around some.

http://blogs.msdn.com/psssql/archive/2008/11/12/cumulative-update-1-to-the-rml-utilities-for-microsoft-sql-server-released.aspx

PASS Conference Stories - Tagging No one :)

Andrew Kelly — Fri, 03 Oct 2008 23:26:18 GMT

Well this isn't really a funny story given the like the others given the destruction that occurred but here goes. A number of years back PASS was at the Gaylord Palms in Orlando and we had a hurricane come thru a few days before the conference started. I had planned to come in a few days early anyway to play a few rounds of golf. Well the hurricane changed those plans somewhat as it roared right past Orlando. Anyone who was already at the hotel was pretty much locked inside for the brunt of it. I thought this was kind of ironic since the entire central part of the atrium area was solid glass panels 50 foot overhead. The wind and the rain was shaking and rattling the panels so much I thought they were all going to fall on everyone's heads. Many of them were leaking water during the peak of the storm and I remember they had guards at the doors to keep people in. All I could think of was that I felt like a prisoner and the prison was about to collapse :) . Well it turned out there was minor damage to the hotel itself but I am sure the guy parked outside my room was not too happy when he saw the palm tree that had fallen on his car. I did get to witness that from my window:).

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

New England SQL Users Group & Craig Freedman

Andrew Kelly — Tue, 06 May 2008 19:50:23 GMT

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?

Andrew Kelly — Fri, 04 Apr 2008 13:33:50 GMT

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