In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it's pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can't think of at the moment? To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most…
I was very privileged to get the opportunity to write a chapter in a book with some of my friends and SQL Family, Pro SQL Server 2012 Practices . Just as each of us took a chapter to write, each of us going to take a chapter to review. But, being the greedy sort, I'm going to review two. First up, Chapter 12, "Windows Azure SQL Database for DBAs", by Herve Roggero (b|t) Personally, I love Azure. And I love Azure SQL Databases. But, I get the fear and trepidation they might cause. I also get the urge to write about them, but I never really felt like I should approach them from a book. Everything changes so much, so quickly in Azure and books just take a while to…
If you've been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You'll get back results, timing, everything you need to observe behavior within the system. What's that? You never heard of this? Let's figure it out real quick. Here's my portal to my Azure SQL Databases: [caption id="attachment_2296" align="alignnone" width="150"] Azure SQL Database Portal showing usage overview[/caption] Yes, all sorts of things you can do from here, but we're focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below: [caption id="attachment_2298" align="alignnone" width="150"] Part of the Azure SQL Management Portal showing some of what…
Welcome to Day 2 of the PASS Summit! It's been a very exciting event so far. Today I'm presenting two sessions, one on tuning queries by fixing bad parameter sniffing and one on reading execution plans. Please stop by, or watch the one on execution plans on TV as PASS is livestreaming events all day long on SQL TV (which is what I used to call Profiler). The intro video, which can be good or goofy was really good this year. They had people from all over the world talking in their native language, making the point that the PASS organization is a global community. It really is. Doug McDowell is giving us the finance and governance information for the PASS organization. I find this boring and vital at the…
It may seem obvious, but I've heard more than one person suggest to me that statistics on a clustered index just don't matter. That if the clustered index can satisfy a given query, it's going to get selected. That just didn't make any sense to me, but I haven't seen anyone set up a test that shows how it might work one way or the other. Here you go. First, I'm going to create a table and load it up with data. I'm intentionally using strings because I don't want to confuse the ease of management of integers within indexes. I also went for one column that would have a very attractive set of statistics and one that would have a very ugly set. Also, because we're only dealing with…
A couple of weeks ago I posted about how different execution plans look when using the Azure Management Portal. I promised to look at a more complex plan and here we are. The query looks like this: SELECT * FROM dbo.Agent AS a JOIN dbo.Resource AS r ON a.AgentId = r.AgentId JOIN dbo.MovieStageResource AS msr ON r.ResourceId = msr.ResourceId JOIN dbo.MovieStage AS ms ON msr.Movieid = ms.MovieID AND msr.MovieStageDefinitionId = ms.MovieStageDefinitionId JOIN dbo.Movie AS m ON ms.MovieID = m.MovieId WHERE a.AgentId = 42; I didn't go nuts in creating a crazy complex query. I just wanted to see a standard set of different operators to see how things changed. As before, I'm working off a controlled database that has identical structures and data in both the Azure database and my…
I read an excellent article by Camille Fournier about the importance of recognizing that being right is not the only factor that needs to be taken into consideration when making a decision. You could even change it from "being" right to "doing" right. Although, I mean it in a technical sense, not a moral one. If you haven't read it already, go ahead, I'll wait... I agree with her. I've been that guy... more than once.... okay, okay, a bunch of times. You know that guy. The one who just couldn't see past the point that we were doing something wrong, something stupid, something that would bite us in the butt for the next three or four years. Oh yeah, that guy. The popular one (not at all). The one…
One of the great things about the Dynamic Management Objects (DMOs) that expose the information in plan cache is that, by their very nature, they can be queried. The plans exposed are in XML format, so you can run XQuery against them to pull out interesting information. For example, what if you wanted to see all the plans in cache that had a Timeout as the reason for early termination from the optimizer? It’d be great way to see which of your plans were less than reliable. You could so like this: 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…
And we’re off. We opened with a video of people saying “Connect, Share, Learn†and “This, is Community†Rob Farley & Buck Woody came out with a song about long running queries. [8:20]Wayne Snyder has been working with the PASS organization since 1999. He spoke at the first PASS Summit and he’s been on the board forever. He has finally hit the point as immediate-past president where he has to leave the board. We’ve got a great little thank you for him from all sorts of people. Wayne is a magnificent guy, seriously. If you see him, thank him for his service. [8:28]We have a new executive committee, Bill Graziano as President, WHOOP, Douglas McDowell is Executive Vice-President and Thomas LaRock is VP of Marketing and finally, Rushabh Mehta is…
Bill Graziano has come out on stage, looking marvelous, in a traditional kilt and stockings. Thanks Bill. For those who don’t know, Day 2 at the Summit every year is Kilt Day. [8:19]Outstanding volunteers being recognized are Tim Radney and Jack Corbett. These are some outstanding people who work their bottoms off for the PASS Community. If you meet them, thank them. The 2011 PASSion Award goes to Lori Edwards. She’s simply amazing. Congratulations Lori and thank you for all the work you’ve done! [8:23]Time to eat our vegetables. We’re looking at the financials. It’s a slightly painful process, but important to understand where the money goes since this is a non-profit organization managed by volunteers. You should understand where the money comes from and where it goes. [8:25]Quentin Clark…