Blog

Bill Baker: Keynote on Office PerformancePoint 2007

Bill’s a fantastic speaker, and this is an interesting product, but our organization’s BI isn’t mature enough to take on this product yet. The product has a lot of potential; in a nutshell, it combines the flexibility and agility of Excel with the data safety & consistency of SQL Server BI.

Sitting in seminars the last few days, though, has gotten me really excited about the direction of the Microsoft BI stack. When our company is ready to take it to the next level, Microsoft has some great tools to help us get there. First I gotta get Analysis Services in place.

Linchi Shea: SQL Server on SANs

I picked this seminar before I realized I’m already subscribed to Linchi’s blog. He does a great job in the blog of illustrating SAN performance issues with charts and tests, and this seminar was like his blog’s greatest hits. His recommendations included:

Measure the performance of each piece of the storage I/O path using MBps, IOps, and latency. That will make it easier to identify the bottlenecks. Use IOMeter or SQLIO.exe to quantify each I/O path with those metrics. Then follow up with repeated measurements over time to watch for shifting SAN performance.

Linchi’s slide on “Create a balanced storage I/O path (6)” clearly showed how I/O bottlenecks can occur at each point of the SAN infrastructure. 2Gb cards mean 256MB/sec throughput max.

Linchi likes synchronous storage-based replication simply because it’s easier to manage large scale replication jobs than it is to manage large scale database mirroring. Heavy I/O servers may not work out because of the added latency, but for most servers he likes it. Note that he’s using fiber to connect the datacenters, which keeps latency low.

A lot of the data applied to EMC, but did not apply as much to IBM, whose SANs carve out LUNs differently. I can’t say how much it applied to other SANs, though.

He recommended sticking with basic disks instead of dynamic disks, and noted that dynamic disks are not supported in a MS failover cluster. He hasn’t seen a performance differentiation, and that in-place LUN growth is not quite there yet.

He explained performance tuning as a continual, iterative process cycle: measure I/O, identify the bottleneck, improve or remove the bottleneck, and start the process again.

An EMC guy in the audience kept interrupting and clarifying things. Note to seminar attendees employed by a vendor: if you would like to talk, get your own vendor seminar. Note to other seminar attendees: when someone talks, look at their badge, because they’re not always innocent bystanders.

Phil Hildebrand: Hash Partitioning and Scaling the OLTP Database

Phil spent the first 45 minutes walking through the basics of how SQL 2005 automated partitioning works and how to implement it. This was a snoozer for me because I’ve got partitioning in production already, but I was clearly in the minority. The rest of the attendees asked lots of basic partitioning questions, and they asked the kinds of questions that showed they were grasping the concept.

One of them caught on quickly that in select statements, SQL 2005 only eliminates partitions when the partition key is used as a part of the WHERE clause. Otherwise, it always scans every partition. Phil sighed and agreed that this is a huge problem, and he hopes SQL 2008 does a better job of partition elimination. I wholeheartedly agree there. He pointed out that you still get a performance gain on insert/update/delete statements, since they only lock a single partition.

The best part for me was the last 15 minutes, where he walked through hash partitioning, and I got this huge “AH!” look on my face.

Imagine a Customer table with an identity field for CustomerID. You don’t want to pre-create partitions ahead of time for all of the possible customer ranges. Instead, you just want to reduce load by lumping customers into, say, 10 partitions.

The solution: add a persisted computed column that contains your own hash function like modulus 10 (CustomerID % 10). Every customer will fall into one of ten buckets (0-9), instantly slicing your table volume by 10. Wow. I wish I’d have thought of that! This reduces insert/update contention because you can load balance the data across 10 partition, preferably on 10 different LUNs.

The biggest drawback is the partition elimination problem here too: SQL will continue to scan all of the partitions unless your where clause specifically states the persisted computed column value. Makes me want to go play with 2008 just to see if the partition elimination is better.

Another drawback would be the difficulty of changing the partition scheme later. Changes would require modifying the persisted computed field, recalculating the data for all of the customers, and moving their records onto the new filegroups. Ouch. Regardless, it’s a neat idea with interesting applications.

And there you have it!

Now for the tough part: poring over all of the notes from the sessions I attended, figuring out what to implement first, and even worse, examining the slides from the sessions I couldn’t attend due to conflicts!  What a great experience.

Tom Casey: Keynote

Tom covered basic SQL Server marketing slides about the BI stack. Yawn. Good presenter, but couldn’t overcome the dry material. Then he handed it off to a Dell woman to announce that they’ll be selling packaged BI solutions with hardware, software, storage, etc all in one bundle. They’re targeting customers in the 1-4 terabyte range with pricing around $100k per terabyte.

I don’t understand the target customer for Dell’s packaged BI systems. I think of the decision tree like this: if a company is large enough to spend $100k per terabyte on a BI solution, aren’t they large enough to already have a preferred x86 hardware vendor, and probably a SAN? Why not just buy hardware from your existing preferred vendors? Surely you wouldn’t choose Dell because of their BI services expertise….

Russell Christopher suggests that companies will be attracted because Dell’s answered the question of what hardware configuration is required for a 1/2/4tb BI project. I’m not so sure – I haven’t seen two 1tb BI projects go the same way.

Anyway, regardless of my dim-witted analysis, this package would have been a hard sell coming out of anybody’s mouth, but it was an especially tough sell from this particular presenter. She was a nervous wreck: she kept constantly rehashing the same “I came from the dark side, not SQL Server” joke, and her slides didn’t match her speech. She would advance a slide only to say, “Oh, yeah, I already mentioned that.” Argh. Evidently Dell saves money on their marketing staff and passes that savings on to us.

Keynotes should be reserved for the very best speakers. Tom was fine, but it all went south when she picked up the mike. I bailed just before her presentation finished, and I heard bad things from other attendees as well.

Jerry Foster: Plan Cache Analysis in SQL Server 2005

In contrast to the Dell presenter, Jerry lit up the room as soon as he got started. This guy was born to teach. This was the first SQL seminar I’ve seen where people burst into spontaneous applause during one of his demos, even before he got to a conclusion.

Jerry and the crew at Plexus Online built a slick system to interpret the dynamic management views for the query plan cache. In a nutshell, his queries make it easy to see where load is coming from down to the batch and statement level, all without running a resource-expensive trace on the production servers.

About five minutes into the session, I knew I wouldn’t bother taking notes because I’d print out the slides and pore over them anyway. I downloaded his code the minute he put the URL up on the projector, and I’m going to spend a week going through it. He didn’t give out the source code for his front end, and I’m torn between building my own in classic ASP (miserable language, but I know it well enough to be dangerous) versus SSRS 2008 (which I don’t know at all, but might do well to learn.)

I’m not even going to try to touch base on everything Jerry discussed. Hard-core database engine DBAs owe it to themselves to go get his samples and pore over them.

I got chuckles out of some of the audience members’ questions, though. One of them started picking out differences between memory allocations on 32-bit versus 64-bit servers, trying to find out how much memory over 4gb his 32-bit servers could use for the plan cache. Hey, buddy, if you have to ask that question, then you need to upgrade to 64-bit. And if your database server isn’t capable of upgrading to 64-bit SQL, but you’re sitting in a seminar about caching, then you’ve got your priorities all wrong.

SQL Customer Advisory Team: Building Highly Available SQL Server Implementations

Going in, I thought this would be more technical, but it turned out to be a fairly high-level comparison of the newer HA technologies: database mirroring, peer-to-peer replication, log shipping and just a tiny bit on clustering. I didn’t learn much because I’d already researched the bejeezus out of these options, but their lessons-learned stuff bears some repeating here for people who haven’t done the homework.

Peer to peer replication and database mirroring have one good advantage over log shipping: the backup server (replication partner or mirror) can have a read-only copy of the database for query purposes. The CAT guys didn’t mention that the act of doing this means you have to pay licensing on the backup server; if you use it for purely disaster recovery reasons, you don’t have to license it.

Mirroring & log shipping should be done with Active Directory group security instead of SQL logins. Companies that frequently create SQL logins and modify their passwords will run into problems during disaster recovery, because the SQL logins aren’t synced between servers using mirroring or log shipping. If you strictly use AD groups for access permissions, then no user info is stored in the system databases, and you won’t have to worry about syncing the individual users.

Syncing SQL agent jobs, alerts, SSIS packages and maintenance plans is also a headache when doing disaster recovery planning, because those aren’t synced automatically either.

When doing database mirroring, remember that databases fail over individually, not the whole server at once. If your application uses multiple databases, you don’t want to have the failovers occur automatically, because a single database might fail over without the others, and timing would be important.

Monitor resources when mirroring more than 10 databases on an instance. That 10 number is flexible, just a rough guesstimate. (That scared me because I mirror more than 10 already.) Due to the way the mirror server handles writes, it may incur significantly higher I/O than the principal server.

In the event of a disaster, break mirroring quickly if there’s a chance the log files may fill up before the principal server comes back online.

When planning database mirroring, carefully analyze the log backup volume over time. The maintenance process of rebuilding indexes will add a lot of log volume, and you want that to happen during a very low activity window so that the mirroring logs don’t get too far behind. They’ve seen index rebuilds cause asynchronous mirroring to get over 2gb behind in less than 10 minutes.

They talked through a rather esoteric DR setup: two servers in the primary database doing synchronous database mirroring between each other, and then a third server in the disaster recovery datacenter with log shipping. That struck me as ridiculous because I’d have three possible database server names, which would be a configuration nightmare on the client side. Anyway, to get that scenario working requires manual setup and scripting, because log shipping has to be set up on both mirrored servers, and it can’t be done with the GUI.

Jason Carlson: Rich Report Design with SSRS 2008

I don’t know Jack about SSRS, but I figured I’d better sit in on this seminar after a midday conference call suggested that we might be doing it in-house.

The SSRS report design process is pretty much all new from the ground up with SSRS 2008, which makes me glad I didn’t put time into learning SSRS 2005. (Yay, procrastination!) The new design tool will be completely integrated into Visual Studio after CTP5, with a second non-VS designer with a Vista/Office 12 feel inspired by PowerPoint. The non-VS designer will support server mode (instead of just working locally), whereas the VS designer will only work when paired with an SSRS server.

Microsoft acquired the rights to a lot of Dundas chart code a few months ago. Dundas circular and linear gauges are coming in CTP6, but maps may not make it to RTM.

The chart setup is much more drag & drop than it’s been in previous releases (they say, and the crowd oohed in approval). Coders can click right in the chart to change the legend, title, locations, etc., much like Excel. As you’re doing chart setup in pop-up dialogs, the charts update in the background instantly. As a user of Excel for over ten years, I wasn’t quite as impressed as the developer members in the audience, but that’s okay – it just means I picked the right time to start poking around in SSRS.

I left about halfway through this presentation because I got some bummer news via email about a project, and wanted to do some damage control.

Tomorrow’s going to be tough – I’m stymied as to which sessions to attend. There’s some pretty good stuff out there with scheduling conflicts. I’m staying overnight on Friday, so I’ll be able to stay through the last session: troubleshooting connectivity errors. Sounds boring, but we’re having those issues at work, so I’ll be tuned in.

On to my Day Three notes.

My notes on the sessions I’ve attended so far:

Ted Kummert: Keynote on SQL 2008

I’d already seen most of this PowerPoint deck during a South Florida SQL meeting at Microsoft, but the demos kicked butt.  Finally, we have Intellisense in Management Studio!  Woohoo!  There was other fun stuff, but I’ve already forgotten it.

One odd note – they slipped in a slide that said the easiest transition to SQL 2008 will be to move to SQL 2005 now.  The exact wording strongly hinted to SQL 2000 users that they need to get with the program, or that maybe we won’t be able to migrate directly to SQL 2008.  That could be a problem – I’d planned on skipping SQL 2005 for the majority of our third party apps like Project, Sharepoint, Mercury Quality Center, etc.  SQL 2008’s transparent database mirroring is a big selling point, and I’ll put the time into migrating programs to it just for that alone, but it’s a tougher sell on 2005’s not-so-transparent mirroring that requires application changes.

Kalen Delaney: The Hidden Costs of Concurrency

Kalen explained the differences between optimistic & pessimistic concurrency: in a nutshell, pessimistic concurrency’s problem is that it blocks other queries that try to read what you’re updating. Both optimistic and pessimistic concurrency modes will hold locks on what they’re trying to update, but pessimistic will cause blocks to select statements as well.

The solution, switching from the default pessimistic concurrency to optimistic concurrency, means that snapshot isolation must be enabled. That causes SQL Server to store versions of updated/inserted rows in TempDB, so every update/insert incurs a lot more I/O. Reads are slower too, because if a process needs to read an older version of a record, that version will be fetched out of TempDB.

The sudden increased use of TempDB means that a database administrator’s job becomes much more difficult: they have to carefully watch the row versioning storage amounts in TempDB and size their files appropriately. Run out of space in TempDB for versions, and everything will grind to a halt. Perfmon counters are available under SQLServer:Transactions for the version store load, like checking to see how much space is being used for the version store as opposed to user TempDB objects.

She made the case for using Read Committed Snapshot Isolation instead of what she calls “full” Snapshot Isolation because it requires less overhead.

I’d love to enable RCSI on my data warehouse, but I’d need to put some time and testing into validating the TempDB speed & size first. Not a low-hanging fruit.

Kalen did a great job of conveying difficult concepts with simple demos, and I would recommend her sessions and her books to highly technical DBAs. This was one of those sessions that I really wanted to come to PASS to see, because this stuff is pretty esoteric.

SQL CAT: Optimizing OLTP: Lessons Learned from MySpace.com

This one was a bit of a surprise, because Hala Al-Adwan from MySpace showed up to talk about the challenges she’s had with SQL 2005. She’s worked closely with the Microsoft SQL Customer Advisory Team on managing her 275+ SQL Servers. That’s right, 275 HP DL585 4-way dual-cores with 32-64gb of ram. They’re adding 3 SQL Servers a day. Humina humina. And you think you have replication problems.

They use SQL2005 Standard Edition x64, and the drawback of Standard Edition x64 is that it won’t lock pages in memory. Enterprise does, and Standard 32-bit does, but Standard x64 doesn’t.

MySpace ran into connection denials because the same bits of code that manage IO also manage connections. This was an eye-opener for me, because I’ve heard rumblings from my data warehouse users that they’ve recently had connection denials during random times of the day. As soon as I get back to the office, I’ll be doing some in-depth performance monitoring.

As part of that problem, the SQL Customer Advisory Team recommended that transaction log writes need to happen in 2 milliseconds or less. It’s good to monitor wait queues, but for t-log drives, low wait queues isn’t enough.

They highly recommended having a clustered index on every single table regardless of size. They gave great charts and statistics about the impact of adding a clustered index: it improved speed under all circumstances, even inserts. I took a camera phone picture of that slide just to send to my BI manager because he still questions that one.

They’ve seen a problem with hotspots when inserting into tables with an identity field, but only with insert rates of >4500 inserts/second and >100 concurrent users. The fix is in the August cumulative update for SQL 2005, which I’ll be promptly applying on my dev & QA servers when I get back, because I’m pretty sure we’ve seen that issue with our ETL nightly loads. We run a lot of loads concurrently into the same tables.

The one shocker: the query optimizer will only do index seeks down to the level of the index where the user uses an inequality clause. That’s a mouthful, so lemme say it another way. Imagine a table with columns Col1, Col2, Col3 and Col4, all integers. It has an index on those columns, in order. A user writes this query:

SELECT * FROM MyTable WHERE Col1 = 100 AND Col2 > 350 AND Col3 = 135 AND Col4 = 151

The query optimizer will not look at all four fields on the index – only Col1 and Col2. Col2 has an inequality clause (less than, greater than, other than, etc) and that’s where the optimizer draws the line. Whoa. Gotta look into that.

Eric Hanson: Data Warehousing with SQL 2008

Eric specializes in the database engine, so he focused on the engine improvements for warehousing. I was most interested in the star join query plan improvements, and those alone would be worth the upgrade process. We use a lot of star join queries, and the query plans he showed looked exactly like the kinds of problems we’re having: inability to section out partitions, inability to filter down large subsets of data, etc.

He mentioned a new MERGE syntax for T-SQL that will do “upserts” – automatic insert/update/deletes, perfect for nightly ETL work.

He also covered a benefit of using indexed views for aggregates. Data warehouses use aggregate tables either by populating them manually as a part of the nightly ETL process, or by using indexed views. Indexed views have an advantage I hadn’t thought of: they will automatically be used in query plans whenever the engine detects that they’d speed up a query. That’s not the case with the aggregate tables we build manually, because the engine has no idea that those tables relate to our fact tables.

SQL 2008 will include database compression and backup compression, two things I’d heard previously but I’ll mention here in case anybody hasn’t heard it. An attendee asked Eric if this would phase out third party compression utilities like Quest Litespeed and Idera SQLsafe, and Eric danced around it well. He said SQL 2008’s compression will be a first version, and the third party utilities may be more mature with more features, so they may still be worth the money. Good answer.

Bill Ramos: Performance Monitoring Using Management Studio

SQL Server 2008 also steps on the toes of third-party performance monitoring products by collecting the same types of runtime statistics DBAs pay extra for now. Bill demoed a very-very-green version of the code, and it has a long way to go but it shows potential.

I was surprised that Microsoft would let this part of the product start up at this point in the SQL lifecycle. Microsoft just got done sticking a fork in Microsoft Operations Manager 2005, and the whole tagline around System Center 2007 is that admins should be monitoring whole applications, not just single servers. For example, I don’t just want to know that my SQL Server is having problems – I want to know what applications are affected, and how that affects the uptime of my entire application.

Performance Studio, on the other hand, was very server-centric and application-ignorant.

Plus, why implement this whole thing in the SQL stack? Why not include this inside of System Center? I don’t get it.

This was the one seminar I actually left early. I just didn’t see a long-term future for this piece of the product, at least not in its current incarnation. I was a little disappointed because I’d expected something else – not quite sure what.

At that point, I went back to the hotel room for dinner (Quizno’s, blah) and an early bedtime.  Lots of great stuff on the agenda tomorrow.

On to my Day Two Notes

I splurged and forked over $180 for a first class upgrade for my flight from Miami to Denver. It’s been years since I’ve flown, and it was scheduled to be a four hour flight, so it seemed like a good – albeit decadent – option. Turned out to be a lucky gamble: the plane was stuck on the runway for an hour while the crew worked on a communications problem, and I relaxed in my big leather seat. The only drawback: the worn-out recliner insisted on gradually, slowly, imperceptibly leaning itself back. Every ten minutes, I had to reset it back up to the upright position. Maybe it’s trying to tell me something, but I’m not an easy sleeper on flights, even in the expensive seats.By the time we were at cruising altitude and the flight attendant came around with the champagne and warm nuts, I’d forgotten that the five hours of comfort worked out to about the same cost as a new iPod Nano.

Speaking of Apple products, this was also the first time that I’d turned off my iPhone since I bought it. Nice. That thing is just rock-solid stable, especially compared to my old Cingular 8125 (HTC Wizard) that required constant reboots.

Once in Denver in my rental Nissan Xterra (free upgrade, woohoo!), I remembered two things about the West: height and distance. Even in the dark, it’s obvious how much vast, undeveloped open space looms around Denver. Being in Miami for a couple of years made me forget what that looks like.

For updates and eye candy, keep an eye on my Flickr photos.

I’ll be attending the SQLpass Summit 2007 in Denver in a couple of weeks.  It’s an annual conference for Microsoft SQL Server database administrators with seminars like “Managing Large Data Warehouses” and “Plan Cache Analysis in SQL Server 2005.”  I’m excited because this is the first national SQL conference I’ve been able to attend.

I’m also excited just to get away from the daily grind at work for a few days.  The last two and a half months were brutal, and things have finally slowed down to a normal pace again.  Not everything is back to normal – we’ve still got a couple of SANs that I’m afraid to touch – but for the most part, things are up and running.

I’ve got a new junior DBA starting the same week I’m heading out of town for SQLpass, too.  I needed the help, although I gotta say that the new guy probably isn’t going to get much SQL for the next few weeks.  After doing only SAN and VMware management for months, I’m eager to get back to SQL again.