SQL Saturday #34/New England Data Camp v2.0
November 30, 2009 at 1:54 pm (SNESSUG, SQLServerPedia Syndication) (New England Data Camp, SNESSUG, Southern New England SQL Server Users Group, sql saturday, sql saturday #34)
It’s official sports fans. Well, it’s been official since last week since Adam Machanic set up the web site. The New England SQL Server Users Group and the Southern New England SQL Server Users Group are again jointly hosting a full day of SQL Server goodness on January 30th. The call for sponsors and speakers is open. We already have several local luminaries lined up to present including Aaron Bertrand on Management Studio Tips & Tricks and Scott Abrants on Automating Database Deployments with Visual Studio.
Please register to spend a day with your peers, learning and networking. It’s being held at the Microsoft Waltham office, a great facility. We should have a full day with lots to do and learn.
SQL Server XQuery For Idiots
November 30, 2009 at 9:17 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (performance tuning, xml, xquery)
I’m still struggling with learning XQuery. My latest little revelation was small, but vital.
The difference between:
@inXML.nodes('/rss/channel/item')
and
@inXML.nodes('/rss[1]/channel[1]/item')
Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like “/rss/channel” so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data.
It’s a little thing, but it made an enormous difference. I’ve still got a long way to go in learning how to use XPath within XQuery.
UPDATED: I modified the title so that it’s clear I’m talking about SQL Server here.
Slow Blogging
November 23, 2009 at 7:25 am (Misc) (apologies, oops)
Sorry, I’ve been travelling for three weeks and, except for the posts around the PASS Summit, I’ve been blogging very intermittently. I’m back in town, all travel done. The posts will pick up from this point forward.
Record of a PSS Call
November 17, 2009 at 7:36 am (SQL Server 2008, SQLServerPedia Syndication) (microsoft, Premier Support, PSS, troubleshooting)
Not everyone has the opportunity to call Microsoft Premier Support. For those who have not yet had this experience, I’ll document my most recent, non-critical, call. Critical calls are a different critter entirely.
We were experiencing a very odd error on one server in our system. When a particular procedure was run with a particularly large set of data, it would produce an error, but only when called from the application. The exact same error with exactly the same data called from SSMS did not produce an error. We went through a very extensive set of tests and were unable to fix the problem on the server. After moving the production system that was experiencing the issue to a different server, we decided to contact PSS.
8:48 Am, Tuesday: I made the initial call and went through the process of validating my identity, our company, etc. I had to explain everything that we had done, what versions of the software involved everything was, etc. This took 10 minutes. Since I had called early in the day and this was non-critical call, although labeled as important since we are talking about a production system, I had to wait for the MS guys to come in.
9:47 AM, Tuesday: I received an email from PSS asking me for the SQL Server logs. I zipped up the most recent log with the error and sent it in to the guy. This took me about five minutes to get everything together and zip it up for him.
10:53AM, Tuesday: I received my first phone call on the process. I re-explained the issue to the support person. they suggested that we set up a server-side trace and get the query to error out when run from the app, and to run successfully from Management Studio and see what differences there might be, if any, in the basic trace. It took me about 30 minutes to set up the server-side trace, test it, generate the errors and run the query successfully and then zip it all up with sample code and sample data to ship off to MS.
Does it feel like this is going to take a long time? You’re right. It continues.
12:10PM, Tuesday: I get an email back with some initial thoughts. Unfortunately, it looks like we don’t have complete information. I respond in the email, providing more.
12:26PM, Tuesday: Another email back from MS confused about the trace. They had me use the default trace, which captures RPC:Completed and SQL:BatchStarting and SQL:BatchCompleted. They claimed that the app wasn’t run and must have errored before calling the database. But that’s because the app errored which means no RPC:Completed. We didn’t capture RPC:Starting. I pointed this out in a response. Nothing back yet.
1:32PM, Tuesday: I get my response back, am I sending a new trace? Nuts. I knew when I realized what they were looking for that I should have restarted the trace. There’s an hour shot.
2:35PM, Tuesday: I’ve finished rebuilding our trace, retesting and sending everything off for evaluation, again. It took longer for me this time because I was waiting on a developer who was away from his desk. PSS calls require everyone to be available, all the time. When we do critical calls we just jam everyone into the same room. It makes it easier.
6:39PM, Tuesday: The data wasn’t sufficient. It doesn’t show when the error occurs. PSS sends me a new trace template to try out. Unfortunately, I’m not at work any more and what’s more, the developers who’s support I need are long gone. I can’t do anything with this until morning.
DAY 2
8:08AM, Wednesday: I’m setting up the trace to see what they’re having me gather while I wait for the developers to show up. Odin’s all seeing eye! They’re capturing quite a few events here. Just about every single event in “Errors and Warnings.” OLEDB Errors. Auto Stats, Degree of Parallelism, Showplan All and Showplan XML Statistics Profile from “Performance.” Server Memory Change. PreConnect Completed and Preconnect:Starting from Sessions (I’ve never even seen these before). RPC:Completed, RPC:Starting, SP:Completed, SP:Recompile, SP:Starting, SP:StmtCompleted (I knew that one was coming),SP:StmtStarting from “Stored Procedures.” Finally SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtRecompile, SQLStmtStarting from “TSQL.” A full load. This should be fun.
I’m converting it to a script and adding a filter to only capture data from the database I’m having troubles with. I’m also converting to a script for another reason, which I’ll post a little blog post about seperate from this one.
11:11AM, Wednesday: The joys of developing software. The developer I was working with yesterday wasn’t in, so I got a different developer to help out. He didn’t know how to run the code the way the other guy ran it. So it was run in a different way. I captured everything and shipped it off to Microsoft. While waiting for their response, I read through the data gathered by the trace. No error. In fact, everything ran successfully. We did a bunch of tests and found that as long as we were running the big trace, we didn’t get the error. It was almost like the error was caused by having too many resources.
12:00PM, Wedensday: The PSS person comes back and says that the data contained no error. Yep. They don’t have a single suggestion. All the indications are, the faster, more powerful server, is causing the problem.
DAY 3
I get a response back from PSS. They’re asking if I’m suggesting that I’m supposed to run the trace all the time. This is the point where I decide to bail on PSS. I’m leaving the case in a non-closed state, but I’ve been talking to some other resources and have a troubleshooting scheme from those resources that we’re going to try out.
More when I know more.
Proof
November 16, 2009 at 11:20 pm (Misc) (DevConnections, Proof, SQL Connections)
SQL Rockstar tells me that unless there are photo’s, it didn’t happen. Brad McGehee published some pictures from DevConnections. See, I really do try to get people to buy my book… uh, I mean present technical sessions at conferences.
Note the laptop with the styling stickers from:
SQL PASS
SQL Batman (defunct)
SQL Serverpedia
SQL Server Central
Thrive
SQL Server Bible
SQL AgentMan
SQL Rockstar
One of these things is not like the others…
I have a Tag
November 16, 2009 at 9:40 pm (Misc) (confusion, microsoft tag, tag)
That’s a Microsoft Tag. I’m not quite sure what exactly to do with it, but here it is. Evidently you’re supposed to take pictures of it with your phone or something and it will lead you back here. I’ll have try making a sticker out of it or something.
Dev Connections Recap
November 16, 2009 at 9:37 pm (Misc, SQLServerPedia Syndication) (DevConnections, Las Vegas, SQL Server Connections)
Last week I attended the Dev Connections conference for the first time. It was held in the Mandalay Bay resort in Las Vegas. The conference is different because it doesn’t concentrate on any one technology, but combines a bunch of them; ASP, Sharepoint, SQL Server, Visual Studio & Architects. That makes it a bit more like Tech-Ed than the PASS Summit. I showed up on Tuesday night and was there through Friday. I attended a few sessions and presented three of my own on the SQL Server Central track of SQL Server Connection. Overall it was a very good experience. I enjoyed meeting a lot of different people that don’t go to the PASS Summit. My sessions went well (except for an odd interruption half way through my morning session on Wednesday. Less said there the better) and I got a lot of audience interaction (after bribing them with copies of my book). The sessions I attended were very good. My favorite two were Kimberly Tripp‘s session on indexing and Gert Drapers on how a Microsoft project used Visual Studio Team System Database Edition to develop and deploy apps. Kimberly’s session, extremely informative, was especially memorable because she hit a snag in a demo. Instead of just bailing, as I would have done, she tracked down the problem, fixed it AND used the initial problem to further illustrate the point she was trying to make. Experience really counts. Gert’s session was just flat out enjoyable. He’s a great presenter anyway, but getting to see how he used VSTSDB in real life scenarios was fascinating as someone who is using it in the real world.
The networking at the conference was not as intense and varied as that found at the PASS conference. I think the audiences are different and the focus of the organizers of the event is different, resulting in, surprise, a very different conference. I did get the opportunity to meet quite and talk with some very interesting people, especially during the speaker party (ask someone from Google how much one of their cloud servers rents for…anyway). The party’s were great, and, since it was Vegas, went well into the night (although, I bailed on Wednesday night since I had to present two sessions on Thursday). Thanks to the people at Tech Conferences Inc, Penton and SQL Sentry for having me. I appreciate it.
Vegas… I was sick of by the end of the day on Thursday and I still had to get through Friday. I didn’t enjoy it much. I didn’t lose money, in fact I won at the slots, but I found the whole thing to be too plastic and fake. I just couldn’t gin up enough artificial joy to really get going there. Sorry.
SQL Connections
November 10, 2009 at 7:34 am (SQLServerPedia Syndication) (Las Vegas, Presentation, SQL Connections)
I’m flying off to the SQL Server Magazine Connections part of DevConnections in Las Vegas later this afternoon. I’m presenting three sessions while I’m there, Wednesday & Thursday. I’m hanging around on Friday. Please track me down if you want to chat. Wednesday I’ll be putting on “Scouting Out Execution Plans” in the morning, 9:30-10:45AM. Thursday I’ve got two sessions, “MUQT: More Unnecessary Query Tuning” from 8:00-9:15AM (which should be a lot of fun at a developers conference), and “DMV’s for Performance Tuning” from 11:45-1:00PM. I’ve brought bribery material (signed books) to encourage interaction, so please stop by, ask questions, make comments, just don’t throw things (except for money).
I’ll blog a recap of each day from my point of view. I won’t be live-blogging the key notes because I’m not registered as press for this event, so I can be lazy.
Please let me know where the parties are at night so I can eat for free. I look forward to meeting with you.
Geek of the Week
November 6, 2009 at 2:05 am (Uncategorized) (geek, geek of the week)
I made geek of the week. I’m sorry, but I’m excited by that. I’m very proud to be a geek. Thanks guys.
PASS Summit 2009 Day 3
November 6, 2009 at 1:47 am (PASS, SQLServerPedia Syndication) (PASS, PASS Community Summit, pass summit 2009, sql pass)
The day started off with a mixed bag. First we had an honestly tearful farewell with Wayne Snyder saying goodbye to Kevin Kline, leaving the board for the first time since PASS was founded. This was followed by a painfully dull session with Dell all about their commitment to bread & butter DBA concerns. That was followed by Dr. DeWitt doing a deep dive into the history and the future of computing, showing and teaching in ways that only the very best can achieve. It was a fantastic performance, entertaining, enlightening, amazing… Just flat out incredible. It’s the kind of understanding that you wish you could get about most things, most of the time. Unfortunately, it came to an end.
Today I finally got to hit a lot of sessions. First I saw Andrew Kelly give a session on “Capturing and Analyzing File & Wait Stats.” It was great. Andrew Kelly is a good presenter and he knows this topic forwards and backwards. That makes it very easy to sit and learn from him. It’s the kind of useful information you can really take advantage of in your job. For lunch I went to a book signing to find out that both my books were sold out. A few people, including @sqlbelle, stopped by to get books signed. It was a real honor and privilege for them to do that. After that I went to two Buck Woody sessions, back-to-back. After the session yesterday I couldn’t have missed them. The first session was on “SQL Server Automation on Steroids.” The slide deck was laid out to look like a Zune. It was great stuff on fundamentals like how to configure SQL Agent, and drill downs on mechanisms for working with PowerShell, or POSH as Buck calls it. He showed several different scripts and I’m pretty jazzed to continue my pursuit of POSH skills after his session and Allen White’s earlier in the week. Yes, this sort of reinforcement of session on session with different people giving different views of the same tools used in varying ways is something you can only get at the PASS Summit. His second session was on “Performance Tuning with SQL Server 2008.” While I didn’t find it as technically useful as the previous two sessions I’d seen him do, it was every bit as entertaining and enlightening. He made my list of must see presenters. I finished out the day, and the PASS Summit, at Gail Shaw’s “Lies, damned lies and statistics.” Gail presented fantastic information in her clear, informative style. If you needed to know something about statistics, she laid them out for you in this session. Things were a bit subdued, this being the end of the Summit (not counting the post-conference) but Gail got the audience up and awake with some great demo’s and explanations of how statistics works inside SQL Server.
After hours it was off to the Friends of Red Gate party. I’m a friend of Red Gate because I sing the praises of their products, which are absolutely praiseworthy. But, I’ll tell you, I might be inspired to sing at least one praise more because of the meal we had. Nice food at a nice resteraunt with great, impassioned people, excited about what they do. It’s hard to enjoy things more.
So that’s the end of the Summit proper for me. I’ll be staying in Seattle through Friday because of a series of events that Microsoft is holding, but I won’t be blogging about them here. This has been one of the best PASS conferences I’ve been to, out of the five that I’ve attended.
Home of the Scary DBA 