XML to Multiple Queries
One of our development teams created a set of queries that are receiving some rather large XML parameters for processing. Unfortunately, the developers didn’t think things through entirely. They passed the same, large, XML string in to the server five times in a row. Needless to say, the performance was substandard since the XML parser had to be instantiated five times. My initial suggestion was to use a wrapper procedure to perform a single load of the XML data and then call the other 5 procedures. I further suggested to load the XML into a temporary table and use that within the other procs. Unfortunately this is all taking place within SQL Server 2000. When my initial set of recommendations was done, we had solved one problem and introduced another. We were getting serious recompiles. If we could move this to SQL Server 2008, there are a number of possible solutions. However, my co-worker, Det (name changed to protect the guilty), came up with a solution that just hadn’t occurred to me. He dropped the temporary table and instead simply passed the XML handle generated in the wrapper procedure to each of the calling procedures. It works like a charm and supplied a huge performance improvement. I wish I had thought of it.
The wrapper proc now looks a bit like this:
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlCollection,
‘<ClientAndIndustriesCollection
xmlns:cl=http://Our.local.def
xmlns:i=http://www.w3.org/2001/XMLSchema-instance
xmlns:a=”http://thds.fmglobal.com/v1.0.0/ClientDataContract”/>’
EXEC myProc1 @hDoc
EXEC myProc2 @hDoc
Sign Me Up!
I am joining the battle. It’s the Battle Against Lawless Database Design (BALD-D or baldy). Because, after all, enough is enough.
I encourage you too to join the battle. Cross your arms, join the battle cry! Enough is enough!
PASS Summit 2008: Day 3
Two speakers at the keynote. The first, some guy from Dell, whose name I didn’t catch, presented a few slides on something. The second presenter was Dr. DeWitt. It was one of those moments where I realized just how stupid I was. This man was scary smart. Not only that, he was witty. He went over parallel databases, shared nothing servers, and related technologies. He drilled down and defined the problems around developing a database that does parallel data storage. It was amazing. I can’t properly convey it (I’m flat out, not smart enough), but you should download that video and check it out. One of my favorite quotes from him was “Query Optimizers are fragile.” Don’t get him wrong. He praised the concept of optimizers, but he was pretty clear that they’re rather delicate, which is very true.
I next went to a Microsoft Database Developer Evaluation and breakout session. I’m not supposed to talk about things that were done there in detail, but suffice to say, it was fascinating to see, a little bit, how Microsoft thinks and some of the directions they were headed in. I’d like to do more of that. But, it was another one of those moments where I was probably the dumbest guy in the room. While I love going to PASS, it’s awfully humbling, several times a day.
Next session was Entity Framework Futures. It was good, but a bit dry and I’m very tired. I tried to keep good notes, but I just couldn’t. However, I liked the way the tool worked with relational databases, not object databases.
My vote for best session of the summit was next. Gail Shaw, MVP and one of the top posters over at SQL Server Central, gave a presentation she called “The Dirty Dozen.” It was a dozen query patterns that cause lots of problems. She should know since she answers more posts on SSC than just about anyone (Jeff Moden aside). I enjoyed it all and learned a bit. However, getting publicly spanked by Gail (verbally, verbally) was a bit of a shock. She called me on SELECT (*) vs. SELECT ([column name]), and rightly so. But it was all better in the end. This blog got a plug. Woo Hoo! She told a joke, repeated from Itzik Ben-Gan, that a good question is one that the presenter doesn’t know but an excellent question is one that’s on the next slide. There was a lot of discussion of what she presented and she had several other MVP’s in the audience with whom she interacted. It was great. Once again, I was one of the dumber people in the room (it was a big room, so claiming to be the dumbest might be presumptive). The discussion around collation’s affect on query times is pretty interesting. I’m going to have to do some research and experimentation on that one.
Final session of the day I went to Kevin Kline’s session on troubleshooting SQL Server. This is good stuff. I especially like that he put out real numbers.
This was a terrific PASS. In some ways, the best one I’ve been to (although, I think that’s partly because I presented). I got reunite with a bunch of great people that I had met at previous PASS Summits. I met people like R Barry Young, or Gail Shaw, and others, who I had only every interacted with through emails & newsgroups or posts here on the blog. I also met several Microsoft developers and other MVP’s and authors, all people that make or design or define the tools I work with. I’m tired as hell, but I can’t wait to get back to work and I can’t wait to come to PASS next year.
Multi-Statement Table Value Function Alternative
I was talking with Andrew Novick at the PASS Summit. We ended up talking about multi-statement table valued functions. I was talking about how much the performance of these things is weak (to be kind). He agreed, but suggested an alertnative that might be worth further exploration, if you really think you need multi-statement UDFs. Andrew said that in his testing, using CLR offered a great alernative to using the UDF. I’m still pretty convinced that any type of programming you’re doing on the SQL Server end that requires a UDF or CLR is probably either just TSQL gone wrong (see Jeff Moden and the RBAR concept) or it’s something that doesn’t belong on the SQL Server but instead should be done on the application layer somewhere. Still, it is an option.
PASS Board
The winners were the other guy, Lynda Rab and Andy Warren. Congratulations to all three.
I lobbied hard for one of the three winners, Andy, so my special congratulations go out to him. On the back of my laptop, as I type this, is the logo (probably due for a law-suit soon) of the SQLBatman. He lost his second PASS election in a row. However, he came through like a trooper and says he’s going to run again next year (go Susan Lucci, uh, I mean Tom).
PASS Summit Day 2
I only half listened to the key notes. The party’s the preceding night may have had something to do with it.
First session was with Kalen Delaney on Plan Guides. She didn’t really do plan guides though. Instead she talked about guiding plans. She is such a great presenter. I like her use of the language and precise definitions. Lubor Kollar (sp?) was in the room and made a point of standing up & addressing some of Kalen’s info. My best take away was that plan guides (she covered those too) do not reduce compile time and can in fact increase compile time. Second best was the use of plan guides as a mechanism for testing since you can apply the guide, enable & disable, without rewriting the proc. Sessions like this are a big part of making the conference worth while.
Next session was Tom LaRock’s on Ops Manager. He picked on me from the podium several times, but since we were collaborating a bit (I slacked off on that and feel bad about it), I guess it’s fair. I loved hearing him say he starts his day looking at Ops Manager. I’m trying to get our system DBA’s to do the same thing. Interesting enough, he doesn’t use it for gathering performance metrics. That’s one of our main uses. His scripts around verifying backups are worth downloading.
I went down to a Microsoft session on tips and tricks from within Microsoft. There were a bunch of different presenters covering different topics. The frist half was very ineresting. The last half slacked off a bit for me (I just don’t have a need for mirroring, so spending time learning about implimentation tricks just doesn’t help me). Extended events for monitoring sounded pretty good. I’m going to have to check up on it. There was another session covering that in detail. I’ll track down the recording. Centeral Management Server was pretty cool too.
I bailed early on that and went to see Andy Leonard’s Unit Testing. He’s a great presenter. He covered the topic very well. The information he presented was great. I especially liked how he showed how to deal with scripts that would generate an error if run, which prevented you from using them as a test (referential integrity checks was the example). Check that one out.
The last session I went to was Joe Webb’s. Unfortunately his machine died right before the session started. I left. He apparently got a few things back together & presented. I’ll go back and watch the video.
In between and around the sessions, and of course, afterwards, I got the chance to talk to a lot of great people. I really like the networking possibilities of this place. If you’re attending PASS and you’re not talking to everyone you possibly can, you’re losing a huge part of the value of the place. I’ve been talking to Brad McGeehee (nicest guy, and scary smart), Michael Coles, Gail Shaw (ask her to tell you her scary Itanium stories, they’re great), Kathi Kellenburger (the recipient of the PASS PASSion award this year, many kudos), Steve Jones, Andy Warren (new PASS Board member, congrats, I’ll post something seperate on that one), Brent Ozar, TJBelt, Tim Ford, Jamie Laflen (one of the developers of Data Dude, it was so great to get the chance to bounce my ideas for his tool off him), more… I mean all these fantastic people who know so much more than I do and are so smart, and talented and helpful. You have to take the opportunity that PASS presents to talk to these guys.
[Update]
I originally typed Bob Ward instead of Joe Webb for the presenter who had the malfunction. I can only attribute the mistake to my own incompentence and being tired. Apologies. I updated the text.
PASS Summit Day 1
What a day. I’m not going to do justice to the keynote. First off, the room was frigging huge (not Tech-Ed huge, but enormous for PASS). It was pretty full too. The keynote wasn’t terribly exciting, but it was terribly interesting. Watching 150tb databases process queries in less than 30 seconds is not something you see every day. Of more interest to me was the info on the new deployment mechanisms across database “fabrics” coming up with the new version of SQL Server (yes, it’s at least two years away, but it’s only two years away). It pretty clearly incorporates some of VSDB, but it’s also a bit different. It includes data for example. This will be something for me to keep an eye on.
My first session was with Allen White. He gave an introductory session on PowerShell. It was pretty good. My last session was also with Allen White. Here he went quite a lot farther (further?) with the same information. It was much better once he hit the higher level example scripts. In between I hit the vendor floor, which is MUCH smaller than in previous years despite the fact that attendence is up so much. Clearly, the economy is affecting everything and everyone. My user group may be hurting on new SWAG this year.
I presented at PASS for the first time this year. I have to say, I had a blast. A little too much of a blast. I went so fast, I blew through my slide deck in 45 minutes. I then spent 1/2 an hour just talking deployments, VSDB, and process, answering questions & comments from the audience. I think it went over reasonably well. I sure hope it did. I had enough fun, I’d love to come back next year. We’ll see how the evals go. I’ll post some pictures when I get home. I brought the camera, but I forgot the cord to connect it to the computer.
PASS Summit: Tuesday Night Parties
I visited at three. I went to the Opening Night Reception hosted by the SIGs of course. I kept score in the quiz bowl (I wasn’t informed math would be involved). Nice enough party. It was a big room, but we mostly filled it up. Went over to the SQL Server Central party next. Steve still puts on a nice shindig. I tried finding other SQL Server Central posters, but didn’t track any others down except for GilaMonster (Gail Shaw). I put all my money on the craps table, 30:1, a single roll of the dice. I guess there is a reason those odds are so high. I had an invite to the Solid Quality Mentors party, so I went over there. It was a little overly crowded (I almost knocked over one poor woman who shot daggers out of her eyes at me for the rest of the night). I got a chance to talk with a few people who I’d never met before and spent a little time with Itzik (is my nose still brown?). Nice guy as well as scary smart. Allen White, Adam Machanic, and Martin Schmidt (tall, good gosh he must be nearly 7 feet).
This morning, off to the Summit. Presenting at 3PM.
Pass Volunteer Training 2008 – Part 2
Next year is going to be in Seattle again… Argggh! Another six hour flight. More jet lag. YUCK!
Money review. Yawn. But you’d be horrified how much all this stuff costs at the convention center.
BTW, I’m pretty excited to be presenting. I know someone else who pointed at their ribbons and said “The only ones that count are MVP and Speaker.” I feel the same way about my Speaker ribbon (I don’t have the other one). More so because, of 70 or so available slots, more than 500 abstracts were submitted. If you want to thank any particular set of volunteers, I’d go for the program committee. They bust butt. Of course, if your abstract didn’t get picked, you might not be too happy with those guys, but look at what they had to do. The good news is more slots will be open next year.
The last half of the day was mainly break-out sessions. I had a chat with Chuck about the Editorial Committee and then I spent hours with the SIG Committee where we are trying to figure out how best to generate content, publish it, and get people involved with the community through the SIG.
It’s been a great day. The PASS Board did a good job of putting together a lot of information.
PASS Volunteer Training 2008 – Part 1
While it’s called training, the PASS volunteer “day” is much more a report on the current status of the organization and planning for the next year.
Today we had good start. Pat Wright went over the org chart, something new to PASS volunteers this year. Kevin Kline went over the history of the organization, including a few stories (ask him about escorting an attendee off site because of way too public a display of self-love). Lots of interesting information has come out. PASS is run under international rules as a non-profit which requires it to maintain ~20% of it’s operating expenses in the bank. Regional conferences are under consideration (although, as was pointed out, internationally, they’re already underway). The C&C management company performs the day-to-day operations above & beyond what volunteers do. It’s very clear that they’re doing a good job. They’re popular in ways that the previous company wasn’t.
The board of directors each gave a quick overview of the past year and some future goals. Erin Welker reviewed the SIGs. The big deal this year was the web site migration.
Greg Lowe went over what’s up with the chapters. There are 132 now with more than 70 outside the US.
Christop Stotz gave a great little summation on what’s up in Europe.
Lynda Rab covered the program for the Summit. They’re making a lot of changes to the speaker program. I hope I can get in next year (this is still a lot of fun, that story may change tomorrow).
Pat Wright reviewed the volunteer program. His only goal was to have a fun volunteer party. Mission accomplished, Pat. But seriously, they’re trying to help organize the volunteers in a better way to make volunteering easier & more rewarding (as a side note, get involved, it’s more fun than watching from the sidelines).
Special Projects was presented by Rick Heiges (he was noisy). They’re creating a chapter in Second Life. They’re introducing PASS Tips, tips about SQL Server. Sounds a bit like what Jumpstart TV is doing.
Chuck Heinzelman reviewed the Online Content for PASS. The organization is discontinuing the SQL Standard magazine. The main reason, and this was fascinating, was because printing costs are directly tied to energy costs. That’s pretty wild. They are going to expand the online content stream. They are going for high quality content, which means they’re not competing with SQL Server Central (not that SSC is low quality, at all, but the stuff published there is just a bit raw sometimes, magazine content is much, much, much more edited and reviewed). I seem to have won an award from Chuck for that content I’ve provided him over the last year. Cool. Thanks Chuck.
Bill Graziano spent some time on Marketing. They’re going to expand that part of the program to take on volunteers. Nice job on the new logo and the new color scheme, Bill. They might find a way to help market chapters. That would be huge.
Technology at PASS was reported on by Rushabh Mehta. They’ve launched the new PASSPort program (I can’t link to it yet, it’s supposed to be hidden). The conference is going to be updated constantly through the web page. They’re also posting updates on Twitter.
Home of the Scary DBA 