adam machanic now blogs at http://sqlblog.com

More ADO.NET performance tips, plus PASS Summit, plus about this blog

ADO.NET performance tip article by John Papa, from the May issue of MSDN: http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx.

I'm also pleased to announce that like Kent, I've had a session accepted for the 2005 PASS Community Summit. I'll be doing my talk on Structured SQL Server Development (also presented at Code Camp III and Dev Teach 2005.) Should be good, and since it will be my third time delivering the talk I'll have almost all the kinks worked out!

Finally, I'd like to apologize for the fact that the level of quality of this blog has gone down so much recently. I've been ultra-busy with work, finishing my chapters for Pro SQL Server 2005, and preparing all of these talks for conferences! I've also been working on the next version of TSQLMacro. But I do keep a to-do list of technical topics for this blog, and it's now grown to around 25 items. I have some good stuff in there, so stay tuned! I hope to free up some time shortly.

New white paper posted: Processing XML Showplans

This is really cool -- this white paper, Processing XML Showplans Using SQLCLR in SQL Server 2005, shows how to analyze XML showplan data before sending the actual query, in order to catch expensive queries before they bog down the server.

I can see immediate use for this in reporting scenarios, in which ad hoc queries are allowed. Instead of submitting the query and destroying server performance (as well as testing the users' patience), the data access code can now be smart enough to reject the query and return an error to the user ("go re-write your query!" -- I'm sure that will make lots of users happy). Or, perhaps, more expensive queries could be put in a queue for later, serialized execution. Many options are available -- this technique will be quite useful.

Upon further reflection (and before hitting the "Post" button), I'm forced to ask: Why is this any better than using SET SHOWPLAN_ALL ON and evaluating the estimated cost it returns? What does using XML for this task give us that using a result set doesn't?

I don't have an answer to that question. Any ideas?

SQL Server 2005 April CTP + Important Announcements

In addition to releasing the April CTP of SQL Server 2005, Microsoft has made two important announcements this morning:

• One, the rest of the SQL Server 2005 beta program will follow the CTP model -- no more "beta" milestones. It's CTP from here on out.
• Two, SQL Server 2005 Express Edition is now cleared for a "Go Live" license. That means you can use it in production without violating the EULA. However, you still won't get support from MS.

Of course, I have a few reactions to these announcements...

First of all, the fact that MS is going with a CTP model for the rest of the release is very interesting. This shows, in my opinion, that MS both values the community's input on the direction of the product, and is willing to put the product through a much more rigorous test that only real users can throw at it. MS simply cannot test all possible scenarios, and users are apt to do many strange things in the course of a deployment. The CTP model will produce a much more robust product, by subjecting SQL Server to tests that no one in a lab could have ever thought of. I think that opening the beta like this is a big step in the right direction for MS -- and I think doing this kind of thing in the past would have resulted in a lot less hassles with regards to fixes, patches, etc.

The "Go Live" news for the SQL Server Express bits is also quite intriguing! SQL Server express has most of the capabilities of the "bigger" SQL Server editions -- so from my perspective this is really a validation that most of the product is feature-complete, relatively bug-free, and ready to go. I don't think I'll be able to take advantage quite yet, but I'm looking forward to hearing others' experiences using this thing in production environments.

Reminder: SQL Server 2005 Roadshow

SQL Server Magazine, Scalability Experts, DevelopMentor, Hitachi Consulting, PASS, Microsoft, BMC, HP, Imceda...

That's a lot of good names, and should get me lots of hits to my blog from people Googling for various stuff.

But more importantly, they're putting on a SQL Server 2005 Roadshow.

This definitely looks like it's going to be a quality event. And as a bonus, you get to heckle fellow SQLJunkies blogger, Kent Tegels, since he's doing the speaking for DevelopMentor.

Windows XP Crash: Lessons Learned

Yesterday morning I had to deal with a non-bootable Windows XP machine. Every time it turned on, it would get to the Windows XP spash screen, sit there for a while, then flash a BSOD and restart -- the BSOD flashed just long enough to see that the screen was blue, and maybe the words "dump" or "kernel" if you looked fast enough. But not enough to get any real data.

Nothing new had been installed on the machine, and it had booted fine the night before. Typical bit-rot situation. Very annoying.

This wasn't the first time I've ever had to deal with XP spontaneously deciding not to boot... I've had this happen on numerous occasions. And here's what usually happens: I throw in the XP CD ROM, boot it, and try to get it to launch auto-repair mode from the install screen. But nine times out of ten, that option doesn't show up. I'm not sure what makes that option show up or not, but apparently on my computers it just doesn't.

So at this point, I usually just shrug and re-install XP. I specify a new computer name and a new default user name so that none of the documents will be overwritten, and resign myself to a few days of re-installing all of the apps I use. One side benefit of this is that I now use very few apps!

But yesterday, that wasn't an option. It wasn't my computer, and documents on the computer needed to be completed by mid-afternoon for a major deadline. Ugh...

I booted off the CD ROM, and as usual the repair option didn't bother coming up. So I started Googling for some solutions... And found a few web pages with advice on how to get that option to come up.

Turns out, you need to use the Recovery Console for that. I'd booted into it a few times in the past, but never bothered learning how to use it...

Lesson 1: To boot into the recovery console, you need the administrator password. Oops. I hadn't written it down when I installed XP on this machine. I have about 10 different passwords I generally use, but you're only allowed 3 tries per boot. And each boot cycle takes a LOT longer than it needs to -- who knew there were so many different disk drivers required to start up Windows? (Anyone who's recently booted off the CD ROM knows exactly what I'm talking about) -- On the fourth try, 30 minutes or so into this exercise, I finally figured out what the password was.

Into the recovery console I went, and after trying several different "tricks" from various web pages, and rebooting a bunch of times, the option still didn't appear. So I kept searching. Finally, when I was about ready to just re-install and try to very quickly get the needed documents back in shape for the deadline, I found this excellent, utterly-lifesaving article by Charlie White. Following his sage advice I went back into the Recovery Console where I backed up the registry, restored a recovery version, booted into XP (and strangely, after rebooting I had to use a different administrator password! I'm not sure why), recovered to a point from a few days ago, and rebooted the machine back into XP, good as it had been before the crash.

Lesson 2: If you search the Web, you will probably find someone who knows more about the subject than you do, and who can save you a lot of time. Re-installing was the way I knew to fix the problem; this is due solely to the fact that I've never bothered actually searching for a better solution before!

Great stuff. But why did it crash to begin with? I was unable to find any log entries or other diagnostic data, but I figured I should run a disk scan to check for issues (CHKDSK /R). And that made the problem instantly apparent. The scan reported a single bad cluster in the SOFTWARE registry file.

Lesson 3: If your hard disk starts making clicking noises, that means that bad things are about to happen! Turns out, this disk had been clicking for about a week before the crash.

... And were there any data backups? Of course not, this is a home computer! I still don't know what to do about that; another computer decided to crash this morning, so it's becoming painfully apparent that my house is cursed -- and I need a backup solution.

Lesson 4: As if I haven't learned this one about a million times before... Backup is key! But I need a real-time solution of some sort. I believe Microsoft is working on some sort of "data integrity server" (?) -- I'm not sure if that will be suitable for home networks or only targetted at enterprise users, though.

Finally, I'd like to whine about the registry a bit. After this disaster, it's clear to me that a monolithic solution like the registry is just begging for problems. A single faulty cluster brought down the entire machine! I'm hoping we'll see some kind of solution for these types of issues in Longhorn.

Anyway, I'm now typing on the fixed computer, waiting for the other computer to finish its scan (two faulty clusters already found)... I hope everyone else is having a more productive weekend!

SQL Server 2000 I/O Basics

Via Karen Watterson's SQL Blog:

Yet another great Technet article that I missed when it was published!

This one appeared in January, and is called SQL Server 2000 I/O Basics. It's a very informative look at how SQL Server maintains those wonderful ACID properties we know and love, in order to maintain data integrity. If terms such as Write-Ahead Logging, Scatter-Gather, Checkpoint, and Lazy Writer are somewhat nebulous to you, you'll want to read this article.

SqlDataReader performance tips

I just posted a few SqlDataReader performance tips in response to a newsgroup post; I think they're some pretty good tips, so I'll repeat them here.

These tips were gleaned from using both Lutz Roeder's Reflector and Compuware's DevPartner Profiler Community Edition.

Both of these packages are free, excellent additions to the toolbox of anyone who wants to write high-performance .NET code. The Reflector will show you what all of those library routines are really doing under the covers (for instance, did you know that the DataView's FindRows() method does a linear search?) And the Profiler will show you how long each line of code takes to execute -- accurately, down the microsecond. Very useful for figuring out where your bottlenecks are!

Anyway, on to the tips... Pretty basic stuff, but good to keep in mind:

A) Use an indexer with the ordinal position:

Instead of:

while (reader.read())
    object x = reader.GetValue("ColX");

or

while (reader.read())
    object x = reader["ColX"];

do:

int ColX = reader.GetOrdinal("ColX");
while (reader.read())
    object x = reader[ColX];

Under the covers, the string-based indexer and GetValue both call GetOrdinal -- so if you're looping through a large rowset GetOrdinal might be called every single time. Instead, only call it once.

B) Avoid the Get<datatype> (e.g. GetInt32) methods at all costs, and use static casts instead of converts:

Bad:

int x = reader.GetInt32(ColX);

Better:

int x = Convert.ToInt32(reader[ColX]);

Best:

int x = (int)reader[ColX];

For maximal performance return the SQL Server equivalent of whatever datatype you'll be casting to (so that you can avoid the Convert methods.) And avoid NULLs so that you don't have to check for DBNull on the client.

C) Finally, just like in classic ADO, when using a SqlDataReader, "open late and close early" is the way to go. Open your connection at the last possible moment and close it as soon as you're done reading the data -- this will maximize connection pool availability.

SQL 2005 Hands-On Labs

Via Julia Lerman:

You can now log into a Microsoft website and demo SQL Server 2005 without installing it! You get an instruction sheet (it's a lab, after all) and 90 minutes in which to do your tasks -- or just mess around, if you're like me!

Check it out if you don't have the disk space, aren't in the beta program, or aren't an MSDN Universal subscriber. It's definitely a good way to get into the product!

Kimberly Tripp on SQL CLR: "Microsoft has put in the work to give the DBA control over this technology."

Widely acknowledged SQL Server expert Kimberly Tripp has weighed in on SQL Server 2005's CLR integration in her latest blog post.

Tripp presents a fairly balanced reaction to the new technology, and stresses a message I've been trying to hit home recently: Given the massive number of changes coming in SQL Server 2005, DBAs must learn all aspects of SQL Server, at least enough to veto bad ideas from the development side. DBAs cannot rest on these changes. There is too much; and developers, unlike DBAs, get excited and want to implement now, not wait until a technology is proven. DBAs will need to know how to properly argue when the time comes.

Tripp also points out that set-based T-SQL is still the clear winner in most cases. T-SQL has been expanded with lots of new features that will help get rid of cursors and loops: Recursive CTEs, row numbering, and the APPLY operator are some of the more important ones on the list. Before you consider migration to SQL CLR, look at these new features. They may solve whatever problem you're having better and in a more performant fashion than CLR methods will.

SQL Server 2005 April Webcasts

Via my MVP lead, Ben Miller:

Discover how Microsoft SQL Server 2005 offers database developers the optimal combination of a tightly integrated development and data management platform. The rich and flexible programming environment in SQL Server 2005 allows you to leverage your existing skills and utilize familiar tools to build robust, secure, scalable applications.

Register today to learn how the integration of the .NET Framework in SQL Server 2005 provides several major benefits, such as an enhanced programming model, enhanced safety and security, user defined types and aggregates, and a common development environment that integrates database development into the Microsoft Visual Studio 2005 development environment. In this series, we cover:

• .NET Framework Integration: Learn how you can now take full advantage of the Microsoft .NET Framework class library and modern programming languages to implement functionality within the server.
• Transact-SQL and Managed Code: Find out how to decide between using traditional Transact-SQL or a programming language that is compatible with the .NET Framework, such as Visual Basic .NET or C#. Understand where each method provides benefits and how to design for this in the beginning.
• Web Services: See how to develop XML Web services in the database tier, making SQL Server an HTTP listener.
• XML: SQL Server 2005 contains deep native support for XML. Learn how this can significantly help as you develop applications that make use of XML.
• Data Access: Discover how ADO.NET 2.0 not only supports all of the new SQL Server 2005 features but also enables productivity and performance gains for all developers.

Register for the SQL Server 2005 webcast series to learn more.

Bonus:Attend a webcast in this MSDN series and complete an evaluation to receive the most current version of SQL Server 2005 Beta software on CD. Attend at least three MSDN webcasts in this SQL Server 2005 webcast series and submit evaluations and you will receive a SQL Server 2005 T-shirt*. And by attending a live webcast in this series and submitting an evaluation, you will qualify to win a Portable Media Center (official rules) pre-loaded with our best webcasts!

A couple of great ADO.NET 2.0 resources

ADO.NET 2.0 has some really interesting new features for interoperating with SQL Server 2005! I think my favorite is the new BulkCopy feature, which I'm very interested in trying out for some of the larger applications I sometimes work on, that need to deal with hundreds of thousands of rows of data at a time.

Two great resources I've found:

Julia Lerman has written a very good article in April's MSDN Magazine, entitled "Flex Your Data: Teach Old Data New Tricks with the Fully Loaded Advances in ADO.NET 2.0"

Alex Homer (who I cannot find a blog for) has published a chapter of a book called ADO.NET and System.Xml v. 2.0--The Beta Version (2nd Edition) on SearchSQLServer. This chapter is all about ADO.NET and SQL Server 2005. And in my opinion, it's a really great read.

Someone thinks I'm an "expert"...

...And no, this isn't an April Fools joke.

It took nothing less than clearing out my bank accounts to bribe the editors, but SearchSQLServer has added me to their Ask The Experts panel. I'll be answering all of the burning questions readers have about SQL Server 2005.

Seriously, though, I'm quite pleased to be a part of this new website. It looks like TechTarget is working hard to create some very high-quality portals for database information -- and it's interesting that they're breaking down into specific areas even though they've had their successful SearchDatabase site up for quite some time. Some impressive people have served on the Ask The Experts panels in the past, so I'm really proud to join their ranks (I would link to the archive list, but it appears that TechTarget has taken it down.)

I just want to give one note of caution. Anyone who asks me, "when will SQL Server 2005 be released," will be hunted down and forced to listen to Joe Celko rant for hours on SQLCLR, ISO-11179, and armageddon. So don't ask me that! I don't know and even if I did... I wouldn't tell you.

By the way, did you hear that Microsoft is releasing SQL Server on Linux???