30May 2011 by Grant Fritchey 7 Comments

Optimizer Timeouts with XQuery

SQL Server
I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out? This sounds like a job for XQuery! There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code. WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),  QueryPlans AS  (  SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,         RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,         --dest.text,         SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,                   (deqs.statement_end_offset - deqs.statement_start_offset)                   / 2 + 1) AS StatementText,         deqp.query_plan,         deqp.dbid,         deqs.execution_count,         deqs.total_elapsed_time,         deqs.total_logical_reads,         deqs.total_logical_writes FROM    sys.dm_exec_query_stats AS deqs         CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)…
Read More
25May 2011 by Grant Fritchey 26 Comments

Oh ****!

SQL Server
Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that…. OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been…
Read More
23May 2011 by Grant Fritchey 6 Comments

SQL Azure Query Tuning

Azure
SQL Azure is still SQL Server at the end of the day. This means it is entirely possible to write queries against SQL Azure that really… what’s a good word… stink. So what do you do? It’s all in the cloud. You couldn’t possibly tune the queries, right? Wrong. Many of the same tools that you have available to you, such as execution plans and dynamic management objects, are still available in SQL Azure. Let’s talk DMOs for a second. First off, don’t make the mistake I did of trying to run these outside the context of a specific database on SQL Azure. You’ll get extremely inconsistent results, trust me on this. Anyway, I did a quick run-down on some of the most used DMOs for performance tuning, the sys.dm_exec_*…
Read More
18May 2011 by Grant Fritchey 1 Comment

Summit Preference Voting

PASS
This year the PASS organization is asking us to vote on the PASS Summit 2011 sessions, not as a means of picking them, but rather as a way to tell where there interests that may not be fulfilled. However, we all know it’s down, to a degree, to a popularity contest. I’ve already posted my sessions on this blog and asked you to vote. I’m not even going to link to it. If you’re interested, you will, if not, why bother you. What I am going to suggest is a few sessions by others that you might not have picked. First up, Jes Schultz Borland (blog|twitter) put on this excellent session at SQL Saturday in Chicago called Make Your Voice Heard! She’s put it in at the Summit for a…
Read More
16May 2011 by Grant Fritchey 1 Comment

SQL Rally

PASS
The first ever SQL Rally was held just last week. It was an excellent time. The event was organized and run by PASS. Which means, in  effect, it was a like a mini-Summit. I’m struggling a bit to come up with the words to describe what the event was like. It was so much more than a long SQL Saturday, but describing it, as I just did, as a mini-Summit mischaracterizes it as well. I guess it must be it’s own critter. Let me just tell you what went on, maybe that will help. The event was held at a very nice hotel, the Marriott World in Orlando, Florida. The Rally proper was two days long, but there was a set of pre-conference seminars held the previous day, making the…
Read More
10May 2011 by Grant Fritchey No Comments

PASS Summit 2011 Abstracts

PASS
I’ve put in several abstracts for the 2011 Summit. This year we’re voting for preferred sessions. If you’re interested in any of the ones I’ve listed below, please consider giving me a vote.I was very kindly invited to submit for a spotlight session (for which I am very grateful and humbled, again) so I put two in for that. I also put in for two regular sessions. This year, for the first time I put in not one, but two abstracts for all day pre/post-conference sessions. One of them was put together as a partnership between Gail Shaw (blog|twitter) and myself. I’m excited by that one. I love speaking and I really hope I make the cut. In the interest of sharing, these are the abstracts I’ve submitted: Spotlight: DMOs…
Read More
09May 2011 by Grant Fritchey 2 Comments

SQL Azure Migration Wizard

Azure
There’s a project over on code plex to come up with a mechanism for validating databases and generating the necessary scripts to allow you to move those databases, and data, over to SQL Azure. It’s called the SQL Azure Migration Wizard. There’s no real install yet since it’s just a beta. You can download the executable and run it. It’s pretty straight forward stuff. It’s primarily focused on validating that there is a database, script or series of calls (from a trace event file) that will be properly compatible with SQL Azure, but it can also generate a deployment script from the database. Here’s the opening screen: Not much to it. I’ll walk you through the Analyze and Migrate path which covers most of the functionality of the app. Clearly,…
Read More
04May 2011 by Grant Fritchey No Comments

SQL Rally

PASS
The Rally is next week. It’s not too late to register. This is going to be a very solid event with excellent opportunities for learning and networking. If you’re on the fence about going, don’t be. You should attend. I even have a few (somewhere near 10) slots open in my pre-conference seminar, Query Performance Tuning: Start to Finish. I’ve been working on hard on this presentation and showing pieces of it to various user groups around the country. The reception so far has been very good. If you want a seven hour brain dump on the ins and outs of performance tuning your queries, I strongly recommend it. Since there are still openings, you can get in, but I’d act quickly. Not because they’re all likely to fill, but…
Read More
02May 2011 by Grant Fritchey 3 Comments

I’ve got 99 Problems, but a disk ain’t one

nHibernate, Object Relational Mapping, SQL Server, T-SQL, Tools
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…
Read More
02May 2011 by Grant Fritchey 4 Comments

SQL Azure and the DAC Pac

Azure
When last we left our intrepid hero he had successfully deployed to SQL Azure using the Data-Tier Application Package, a DAC pac. It was easy and I had a database in place in nothing flat. There really weren’t any issues worth mentioning. I wasn’t crazy about the fact that unless I had Visual Studio 2010 I couldn’t edit the Data-Tier apps or get them into source control, but there you. So, assuming this is a real production application, I’ve just realized that I need to get a new procedure into my database. If I just got and run the script to create the procedure then I’ll be breaking the link between my database and the DAC pac (and yes, I still enjoy saying that knowing that blood pressure is rising…
Read More