Some (new to me) SQL 2008 quirks

Let's just say that I inherited a bunch of really optimistic (naive?) code on the half a dozen or so SQL 2000 servers at my company.

History Lesson:
The first problem was everything was connected via linked server. In some sort of hokey attempt at redundancy / disaster recovery / load balancing the 'design' of the databases was such that a container could be moved to a different server and a hand full (ok, there are dozens) of linked servers could be modified and bam the entire thing just works.

Yeah.

Um.. right. That makes lots of assumptions on the security side (do the SID's match, are there the proper logins etc) not to mention the fact that performance over a linked server leaves a lot to be desired.

So we underwent a process of cramming 6 (+) database servers down to 3. We have one reporting server, one OLTP server and then one misc server. Well, ok, we have 3 misc servers but none of them are 'mission critical' so to speak.

Current Situation:
So when it is boiled down we have
1 OLTP server SQL 2008 Clustered
1 data warehouse server SQL 2000 (soon to be upgraded)
1 misc db server SQL 2008
1 SSRS Server SQL 2008

We consolidated all the OLTP stuff to the new OLTP server, then after we were satisfied it was good we underwent the clustering / SQL 2008 upgrade.

I gave the history lesson for a reason. Really I did.

If any system is going to work via linked servers over 6 different physical servers there must be a fairly small amount of data. AND if there is a small amount of data, well, then you can take liberties with how clean and efficient the code is (I mean, it isn't like SQL's Query Optimizer is going to work all that well over a linked server). Also, why index? I mean a few are good but no need to actually figure it out.

So that is the coding mentality of the majority of the code in most of our databases. Just get it done, as long as it runs.

Turns out SQL Server 2008 doesn't take kindly to poorly indexed tables and crappy TSQL code.

Chances are, if you wrote your own Stored Procs, designed your own databases, and have been charged with keeping it all up, your stuff is good... and upgrading to SQL 2008 is no big deal.

Boy oh boy, we have stumbled upon problem after problem. I have had to put my performance tuning hat on and dig into code that is so old and moldy that there are cob webs on top of cobwebs and carcass' of spiders a foot deep.

So observation 1: Stick to the basics
We had several instances of code operating on far too many records needlessly. Remember the where clause, it is your friend. Why update 28 Million rows when you can mess with 500,000?

Observation 2: The index suggestions in 2008 are OK, but not perfect
I really love the new (to me) feature of the execution plan suggesting indexes. Way cool. However, like all tools don't trust the output implicitly. Look at what it wants to do. Sometimes you have to force an index hint rather than build the index the server wants..... because a perfectly good index already exists.

Observation 3: Cursors still suck.
That goes without saying.... SQL is a set based language, code like it.

Observation 4: Index maint is even more important.
I had a pretty good defrag/reindex stored proc that I had for SQL 2000. It only touched indexes that it needed to based upon fragmentation, and didn't fill the logs (SQL's Maint plan did.. go fig)
So prior to the upgrade I tightened down on how much fragmentation I allowed. Down to 'round 1%... why? Because SQL 2000 was a bit over-optimistic on how clean the indexes are.

I downloaded and tweaked a good defrag script for 2008 (why re-invent the wheel?) and it runs great... however sometimes, when in the course of performance tuning, you run into indexes with low fragmentation that still perform like crap. Rebuild that sucker. It makes a big difference. Trust me.

Observation 5: Update statistics manually right after an upgrade
I figured the auto-update-stats would do the trick. It did... kinda.... Some of my most nagging and tough to git rid of problems disappeared when I updated stats.

AFTER I fixed the indexing and stats MOST of the code ran much faster. Some code had to be optimized because, well, it just wasn't well written... and as I said SQL 2008 isn't tolerant of crappy code.