Category: Azure

Jan 25 2016

Finding Your Query in Query Store

Query Store is pretty amazing. I’m loving working with it. I think it’s likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you’re going to want to do is track down your query.

The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query:

SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = 'Hamilton';

If we wanted to retrieve this from the Query Store AdventureWorks2014, we’d run a query like this:

SELECT * FROM sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text = 'SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = ''Hamilton''';

Of note, I had to drop the statement terminator from the text of the query, the semi-colon, in order to retrieve the correct query. That’ll be good to know in a moment. This retrieves the query information I requested, just fine.

For our next example, let’s simplify things a whole bunch:

SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;

If I then attempt to retrieve the information from Query Store like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363';

It actually doesn’t work. Note, I took off the statement terminator, just like before. In fact, the problem here can be identified if we look at the T-SQL from the execution plan from the second example:

SELECT * FROM [Production].[BillOfMaterials] [bom] WHERE [bom].[BillOfMaterialsID]=@1

This query has gone through simple parameterization. So, in order to retrieve the information from Query Store, we have a function, sys.fn_stmt_sql_handle_from_sql_stmt that we have to incorporate like this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt('SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;', qsq.query_parameterization_type) AS fsshfss

And note, I have the statement terminator back in place, but this function takes that into account. I joined to the query_store_query table in order to get the parameterization type value. With the function figuring out the statement handle based on the text I originally ran the query through, everything is hunky dory.

Let’s look at one more example:

EXEC dbo.spAddressByCity
    @City = N'London';

This is a stored procedure, so I can just do this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

However, you may have multi-statement stored procs, so you might want to query based on the text within the procedure like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City'

Bad news. That doesn’t work. If you looked at the qsqt.query_sql_text value in the previous Query Store query using the OBJECT_ID function, you’ll see that the query looks like this within the Query Store:

(@City nvarchar(30))SELECT  a.AddressID,          a.AddressLine1,          a.AddressLine2,          a.City,          sp.Name AS StateProvinceName,          a.PostalCode  FROM    Person.Address AS a  JOIN    Person.StateProvince AS sp          ON a.StateProvinceID = sp.StateProvinceID  WHERE   a.City = @City

But, if you look at the documentation for sys.fn_stmt_sql_handle_from_sql_stmt that I linked above, it only works with simple or forced parameterization (this, despite having options for none and user, 0 & 1 respectively in the documents). That means you may be resorted to the use of LIKE to retrieve particular statements:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City%';

At least, that’s where we’re at with the current, public, CTP.

Tracking down your query can be a little bit of work and hopefully these tips will make it a little easier.

Jan 18 2016

Removing All SQL Server Query Store Data

While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.

Jamey Johnston (t|b) came through… and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today.

Just so that you know, it’s actually really easy:

ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;

By the way, Query Store is absolutely amazing. Did you know that you can set up an extended event to fire when Query Store detects a query regression? Yep. The extended event query_store_aprc_regression will do just that. Query Store is coming with SQL Server 2016, but it’s already in production in Azure SQL Database.

Oh yes, we’re moving into some very interesting times for query tuning. Now if I can just get my reading comprehension to keep up…

Jan 12 2016

“Applies To…” in the MSDN Documentation

Quick little post. I just wanted to share how happy I am with the new “THIS TOPIC APPLIES TO” infographic. An example here:appliesto

I think it makes things much more clear when you’re attempting to figure out what’s up with some T-SQL syntax.

Well done Microsoft and thank you.

Side note, this only exists in documentation that has been updated recently. I first saw it in some documentation that was updated January 11, 2016. It’s not there in another piece of documentation I saw that was updated October 15, 2015. Here’s hoping it gets put everywhere. It works.

Dec 07 2015

Learning R: Foundations

Learning a programming language is largely an act of using that language to do stuff. Done.

However, the big thing about R is the mathematical and statistical analyses that can be easily run against your data sets. This means, part of learning this language is learning another, that of data science.

I’ll be posting about how I’m learning R, but I also should tell you how I’m picking up on Data Science. First and foremost, madman he may be, but one of the few sources of information that I simply trust is Buck Woody. He’s been running a series on Data Science. Here’s an excellent example on how to pick a particular algorithm. These are must reads.

Next, I’m starting a book called Data Science for Business: What you need to know about data mining and data analytic thinking. The title pretty much sums it up. It’s geared towards the business person who needs to understand this, not necessarily a computer nerd who is trying to learn it. However, if you’re anything like me, who went to film school for college (and dropped out of that), then some more introductory level knowledge is needed before we get into deep mathematics (as I’m constantly reminded when I get into discussions with the maths nerds here at Redgate Software).

This information is foundational to understanding what I’m attempting to do using R within SQL Server. Next I’ll detail some of the sources I’m using to get started within R itself.

Dec 03 2015

Changing Course On Learning

With all the new stuff on the Microsoft Data Platform, it’s really hard to keep up with it all. I had announced my plans to charge down the DocumentDB road to try to get the basics of that in my head along with learning some JSON so I could get what all the hoopla is about.

However, after a lot of thought and some extensive meetings at Redgate, I’m looking to shift my learning in a new direction.

First up. Arrrrrrrrr!

No, it’s not yet “Talk Like a Pirate Day.” I’m going to start learning the R language. It’s a language for statistical computing and is one of the many underpinnings for what’s going to be happening with a lot of the Machine Learning capabilities in the Data Platform. With Azure SQL Database, and soon, SQL Server 2016, this new language is going to be part of the query landscape. It’s going to cause performance issues and all kinds of wonderful opportunities. I need to know it.

I’m also looking to embrace and extend my knowledge into the Machine Learning area. I’m not sure exactly where that’s going to take me, but again, I’m pretty sure we’re going to see more and more of this within the systems we manage.

With so much of the data stack now available through Azure (Azure SQL Data Warehouse is a game changer and you should be looking at that right now, in your spare time) changing not only what we can do, but how we do it, it’s affecting directly SQL Server. It’s not enough to know and understand just the core engine (it never really was, but we could tell ourselves that). This doesn’t just affect queries and query tuning. It has impact into our Data Lifecycle Management, DevOps and development releases and methods. In short, all the stack is getting impacted by the expanding Data Platform and I intend to be on top of it.

Watch for the R posts coming up, and forgive me if I occasionally sound a little piratical (OK, a little MORE piratical). Also, don’t worry. You’re still going to see stuff on query tuning, execution plans and all the core engine stuff. Fact is, that doesn’t go away just because I’m looking at Azure SQL Database or Azure SQL Data Warehouse or attaching R to my T-SQL, because, under the covers, it’s still SQL Server.

Dec 01 2015

It’s Not Too Late

You know you want to go on the SQL Cruise. You can. You just have to convince the boss that it’s worth doing. It is. I’ve said it before and I’ll repeat it as necessary, SQL Cruise changes peoples lives. I’ve watched people go on the cruise with a job and come back with a career. People don’t just learn on the Cruise. They get energized. They get engaged with the data professional community.

How do I profit by promoting SQL Cruise?

I don’t.

Tim Ford is a friend and I’m supporting him. My company, Redgate Software, is a sponsor of the cruise, so I’m supporting them. I could just be doing the bare minimum in support of these parties. However, I’m not getting paid anything special by anyone for doing more. Instead, for me, personally, SQL Cruise is all about the ability to share.

Understand, I love my job. I spend a lot of time teaching about the Microsoft Data Platform, Azure SQL Database, SQL Server, deployments, development, automation, performance tuning and all of it from the perspective of Redgate Software. I get to share. A lot. However, I only get to share the way I do on SQL Cruise, on SQL Cruise. It’s intimate. It’s direct. It’s personal. I’m not standing in front of a group of 75 people with another 200-5000 waiting outside the door. It’s just 25-30 people, all the time. We get close. We talk. It’s the lowest level, most fundamental kind of interactions. I benefit from it personally, and I get the opportunity to share. This is why I love and support SQL Cruise.

And yeah, there’s classroom time. I’m really looking forward to learning from David Klee, Tim Ford, Jason Hall and Jes Borland. These are some of the most amazing people you’re ever going to meet and learn from.

I’m putting on a couple of classes too:

The Query Store and Query Tuning in SQL vNext

For the most part, query tuning in version of SQL Server is pretty much like query tuning in the next. SQL Server 2016 introduces a number of new functions and methods that directly impact how you’re going to do query tuning in the future. The most important change is the introduction of the Query Store. This session will explore how the Query Store works and how it’s going to change how you tune and troubleshoot performance. With the information in this session, not only will you understand how the Query Store works, but you’ll know everything you need to apply it to your own SQL Server 2016 tuning efforts as well as your Azure SQL Databases.

Azure SQL Database for the Earthed DBA

Everyone knows that Azure SQL Database only supports a small subset of SQL Server functionality, small databases, and has really bad performance. Except, everyone is wrong. In fact, Azure SQL Server Database is ready to support many, if not most, databases within your enterprise. This session reintroduces Azure SQL Database and shows the high degree of functionality and improved performance that is now available. You’ll leave this session with a more thorough understanding of the strengths and weaknesses of Azure SQL Database so that you can make a more informed choice over when or if you should use it within your environment.

Go and have that chat with your boss. You won’t regret it. I promise, this experience will change your life.

And there’s rum.

Oct 14 2015

Getting Started With DocumentDB

I’ve put this off for too long. It’s time to get my feet wet with some new tech.

Step 1 is easy. Go to the Azure portal and start the process for creating a DocumentDB:

NewDocumentDB

While that’s running, let’s see what’s on the interwebs about getting started in DocumentDB…

Nice. I know I’m going to have write a little code to exercise this thing. Here’s a great run-through on exactly how to do it. Actually, the first hit when I searched on “Getting Started With DocumentDB.” Microsoft has a start page on DocumentDB, but it was clearly put together by someone from marketing. Scroll down to the bottom. There are a couple of interesting links including SQL Query Within DocumentDB. Now we’re talking. Here’s a Curah! or Docs.com (which is it? do I care?) on DocumentDB with some of the above links and a few others. I spot a theme on this one. Here’s a more thorough how-to on querying DocumentDB.

Plenty to read, lots to do. And look, I have a DocumentDB database ready and waiting:

DocumentDBSettings

I’ll report back as I get things going.

Oct 05 2015

Trace Flags in Azure SQL Database

One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I’d say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I’ll leave to all the systems experts to advise you on.

What about Azure SQL Database?

I doubt you’ll be shocked, but if I try this:

DBCC TRACEON (2371,-1);

I get the following error:

Msg 2571, Level 14, State 3, Line 1
User ‘xxx’ does not have permission to run DBCC TRACEON.

 

This error makes sense right? We’re talking about a Platform as a Service (PaaS). You’re only managing the database, not the server, so you don’t have access to control underlying server behavior.

How about if we want to just modify the behavior of a query? You can use the query hint QUERYTRACEON to adjust behavior. For example, the new statistics cardinality estimation engine in 2014 and better is just marvelous. It’s in use in Azure SQL Database. However, there are edge cases where the old way can work better for certain queries. If you want to go to the old cardinality estimation engine in SQL Server 2014/2016, you use traceflag 9481 in a query hint like this:

SELECT  *
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE   sod.OrderQty > 30
AND sod.ProductID = 867
OPTION (QUERYTRACEON 9481);

Bad news. The error message is the same.

Working within Azure SQL Database, trace flags are not a part of your tool set.

Aug 24 2015

Targeted Plan Cache Removal

4926596880_321ac69592_m

A lot of times you’ll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they’ll fix it by running the following code:

DBCC FREEPROCCACHE();

BOOM!

Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing… Your fault for going nuclear.

Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here’s a little piece of code to help out:

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.SomeProcedureName');

IF @PlanHandle IS NOT NULL
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO

Take a look at the documentation for FREEPROCCACHE. You can target specific plans using a plan_handle, a sql_handle, or even a resource governor pool by passing pool_name. I take advantage of that with this query to pull the plan_handle from sys.dm_exec_procedure_stats. You could use T-SQL text too, you’d just have to add in sys.dm_exec_query_text to one of the other DMOs that has the plan_handle or sql_handle such as sys.dm_exec_requests or sys.dm_exec_query_stats. However you choose to do it, you can use a targeted approach to remove plans from cache.

Let’s take the nukes off the table.


Want to really get into talking about the plan cache, query tuning, parameter sniffing and the rest? I have two upcoming all day pre-conference seminars. There’s still room at IT/Dev Connections in Las Vegas on September 14th. Click here to register. Also, at Connections, I’m hoping to be able to read your execution plans, so bring the really scary ones. Later that same week, I’ll be at SQL Saturday San Diego and will do a pre-con there on query tuning. Click here to get your seat.

 

Jul 29 2015

Hey Kids! Let’s Put on a Show at the Old Barn

Alternate Title: I’m traveling a bunch. Let’s get together and talk.

A bunch of trips and presentations coming up, so I thought I’d share. First, I’ll be SQL Saturday Omaha for my first time ever presenting in Nebraska. I’m excited to add this state to my list (which is almost over 40 now). If you’re not doing anything August 15th, let’s have a chat. Next, fingers crossed, I’ll get selected to fly back to my home state, Oklahoma, to go to SQL Saturday OKC. These guys put on a great event and hey, it’s Oklahoma so how can it be bad. I hope they announce soon. I need to schedule my flights. This one is on August 29th.

September also has several events. First, I’ll be at SQL Saturday Las Vegas on the 12th of September. My first time at this event so I’m looking forward to it. That’s followed immediately by a pre-con and a couple of sessions at ITDev Connections. I love inter-disciplinary conferences because it gives you a chance to branch out and learn more knowledge across the stack. It’s extremely useful if you’re getting into DevOps (which you should be). My pre-con is on query tuning and execution plans, using one to help the other. This is a great event with a lot of excellent speakers. Go here to register. But I’m not done. Down in San Diego, at their SQL Saturday on the 19th, I’ll also be doing a pre-con on query performance tuning on the 18th. I’ll see if I can’t squeeze some 2016 stuff in there too. Click here now to register for the precon.

But we’re not done. In October, I’m going to hop the pond for one my absolute favorite events, Red Gate’s very own SQL in the City: London. It’s a great event with a great collection of speakers. I won’t lie, we’re going to focus on Database Lifecycle Management (DLM), but there’ll be sessions on query tuning and all sorts of other stuff as well. It’s focused on the Red Gate tools and, let’s face it, it’s a Red Gate style event, so it’s fun and entertaining. If you really do want to drill down on DLM, there’s also the all day seminars that Red Gate is hosting that week. We’ve put a lot of work into these and I think they’re some pretty amazing hands on classes that will get you started automating your own database deployments. Also, while I’m over there, I think I may find my way to a SQL Relay event, so eyes peeled.

Later in October, well, it’s the PASS Summit. ‘Nough said. I’m doing a session focused on the beginner that’s all around statistics. They’re so important to everything you do with queries, that it’s a good idea to spend some time understanding them before you get into all the craziness of query tuning. If you’re attending Summit, please stop by my session. Click here to register for the PASS Summit. Fingers cross, I might be at SQL Saturday Portland if I get accepted.

One more that I don’t mean to leave off, but I think it deserves it’s own paragraph. Right before the PASS Summit, we’re bringing SQL in the City back to Seattle. That’s right, another shot at seeing the great stuff we’re going to bringing to London, plus a few different things (we mix up the speakers and sessions to help keep it all fresh).