I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don't need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan. Trace Flag 7412 Here's how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan…
Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram. We've always been able to query the statistics using DBCC SHOW_STATISTICS. However, the output, three different result sets with three different structures, made automating access to statistics information a pain. Now, we have more capability through dm_db_stats_histogram. dm_db_stats_histogram To access the information in dm_db_stats_histogram, you just have to pass in the object_id and the statistics_id values for the statistics you're interested in like this: SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('HumanResources.Employee'), 1) AS ddsh; It's very straight forward to use. The results look like this: Handy right? Now you can query the histogram directly. Yeah, I hear a few of you saying... and this helps me... how? Here's an example. This query…
In Azure SQL Database for quite some time and now available in SQL Server 2017, Microsoft has put a lot of the knowledge they've gleaned from running more databases that any of the rest of us ever will to work with Automatic Tuning. Automatic Tuning The core of automatic tuning at this point in time (because I'm sure it's going to evolve) is the ability of the query engine to spot when a query has generated a new plan and that new plan is causing performance to degrade. This is known as a regression in the plan. It comes from bad parameter sniffing, changes in statistics, cumulative updates, or the big notorious one, the cardinality estimator introduced in SQL Server 2014 (it's been almost four years, I'm not calling it…
I spend many of my evenings researching and writing. Sometimes it's writing new books. Sometimes it's fixing and rewriting old books. Occasionally it's a blog post like this one. Lately, it's been a series of articles for SQL Magazine that are all about the new functionality available in Azure SQL Database v12 that was released in February for most data centers in Azure. It's a whole new ball game. Check out my introductory article for v12 and the one on DMVs in Azure. I have more coming up on CLR in Azure, getting started, PowerShell, DBCC, T-SQL enhancements, Premium Tier and more. I'll also get into "old" functionality like backup and restore. I'll also explore new functionality, Azure is nothing if not dynamic, as becomes widely available. I know a…
Learning new things can be daunting. First, you have to come up with the spare time. Then you have track down resources. For computers, computing and programming, this is both extremely easy and extremely difficult. That difficulty is especially true when it comes to gathering resources for learning things that, while you learn, are potentially going to cost you money. It's a difficult decision to make to risk cash on exploring a new technology. Here's the good news, for several reasons, you don't need to sweat this to get going with Windows Azure SQL Database (WASD). A couple of years ago Jamie Thompson (b|t) set up an account on Azure, all on his own, that allowed people to connect up to it and play with a copy of the AdventureWorks…
The very concept of the Windows Azure SQL Database (WASD) is predicated on the up-time created by having three active copies of your database. Should there be a hardware or software failure that would cause the primary replica to go down, your database gets moved to one of the secondary replicas which has been maintaining a copy of the data from the primary replica. In theory, the worst thing that happens is that you have to retry a query. In fact, building that sort of resilience into your code is a pretty fundamental aspect of working with WASD. I was asked the question, how do you tell if your database has been moved between replicas. I didn't have a complete answer, so I set out to find one. The first, more obvious…
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…
I have lists. Lots of lists. I even have them in different locations sometimes. Some of them are carefully written down in my notebook, others are typed into OneNote and I've been experimenting with Remember the Milk and Trello (Trello is winning). These lists include ideas for presentations, blogs, articles. Notes from sessions I've attended or meetings. Lots and lots of plans and ideas and all that sort of stuff. I try to keep it organized, but sometimes it runs away from me. However, I find writing things down helps me to keep things organized. Between very carefully scheduling out my calendar and all these notes, I only occasionally completely drop the ball. One ball I dropped was coming up with some goals, some plans, for 2012. I just plowed…
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…
I keep thinking I've got a handle on the way execution plans are dealt with in SQL Server. I had a pretty simplistic view of things, there's the estimated plan which comes out of the optimizer and there's the actual plan which comes out of the data engine. The one shows the best guess for what will happen based on the query and the underlying statistics. The other shows what actually happened based on the query and the real data, load on the server, available memory, you get the idea. This model is easy to explain and understand. Too bad it's not quite the way things work. If you query the dynamic management function sys.dm_exec_query_plan, you can see a query plan. Once you drill down on the XML, or browse…