Query Store, Forced Plans, and New Plans

Azure, SQL Server 2016
I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let's say you have selected a plan that you want to force. You set it up. Now, let's say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that's going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store? I have no idea. Let's find out. The Setup To start with, a small stored procedure…
Read More

Customizing Your Azure Portal

Azure
Not all of us are going to use Azure in the same way. That's just common sense. Therefore, not all of us are going to want it to look the same. Here's a default view of the portal for a login I created within my own domain: You can contrast that with this login to my portal that I use for most of my testing and training (this isn't my company's production or development portal, which looks still more different): Clicking on either of these will open them so you can look at details. Not only are the color schemes different, but you'll note that the selection lists on the left are different as are the grids on the dashboard. All this can be customized for each login, and, within…
Read More

Microsoft’s Commitment to Azure

Azure
For several years, many of us who were involved with working in Azure from the early days, were concerned that Microsoft had bet the farm and could possibly lose it all. They may well have bet the farm, but more and more it's extremely clear that there is zero likelihood of them losing the farm. In fact, it's looking more and more like, while using farming analogies, they're turning into an agro-corp. Azure is taking off. You need to start working on adding Azure knowledge to your skill set. If you have access to an MSDN license, getting into Azure is easy because of the credits available. However, not everyone works for a company that provides MSDN or has purchased a license. In that case, getting into Azure, just for…
Read More

A View Is Not A Table

Azure, SQL Server, SQL Server 2016, T-SQL
Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I'm using the AdventureWorks2014 database for all these examples): SELECT * FROM Production.vProductAndDescription AS vpad;   SELECT vpad.Name, vpad.Description, vpmi.Instructions FROM Production.vProductAndDescription AS vpad JOIN Production.Product AS p ON p.ProductID = vpad.ProductID JOIN Production.vProductModelInstructions AS vpmi ON vpmi.ProductModelID = p.ProductModelID WHERE vpad.ProductID = 891 AND vpad.CultureID = 'fr'; The above query actually combines two views and a table. This is what is commonly referred to as a "code smell". A code smell is a coding practice that works, but that can lead to problems. In this case, we're talking about performance problems. The performance problems when using views to join to…
Read More

Do You Teach Azure Data Platform?

Azure, Professional Development
I offer instruction on the Azure Data Platform, and have for about six years, since shortly after it came out. I started using Azure SQL Database (although it had a different name then) Day 1. I know a few other people who don't work for Microsoft, but have been actively pursuing Azure SQL Database, SQL Server on Azure VMs, and pretty much all the Microsoft Data Platform. I'm not counting the BI people who have dived into PowerBI and related tech. The BI people, who are generally pretty smart, jumped on Azure with both feet. I'm talking about the data platform aspect of Azure. The people that I know who regularly teach classes are (in no particular order, sheesh, you people): Karen Lopez(b|t) Denny Cherry(b|t) Jes Borland (b|t) Thomas LaRock (b|t) Joe D'Antoni…
Read More

Happy Dance!

Azure
I'm all like: Because I saw this on an eval: I've been trying to ramp up to take advantage of my MSDN subscription and haven't known where to start. I don't have that excuse now. And then I was all like: Because: We are moving a lot of stuff to Azure. I had some experience using SQL Azure but felt blind when doing it. Grant made me feel better about my experience as it is very much like he explained. and: Azure is becoming a REAL THING. It's nice to get such a great primer of it. <calming down> I'm quite pleased to see that Azure sessions are getting such an improved reception. <SQUEEE>
Read More

Cross Database Query in Azure SQL Database

Azure
You can't query across databases in Azure SQL Database... or can you? Let's check. I've created two new databases on an existing server: I've created two tables on each respective database: CREATE TABLE dbo.DB1Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) ); CREATE TABLE dbo.DB2Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) ); Now, let's query the DB2 table from the DB1 database: SELECT * FROM DB2.dbo.DB2Table AS dt; And here's the lovely error message: Msg 40515, Level 15, State 1, Line 35 Reference to database and/or server name in 'DB2.dbo.DB2Table' is not supported in this version of SQL Server. So, like I said, you can't do three part name cross-database queries in Azure SQL Database... oh wait, that's not quite what I said…
Read More

Wait Statistics in Azure SQL Database

Azure, T-SQL
You need to be aware that you're going to see differences when you're working with Azure SQL Database when it comes to wait statistics. If you're running a v12 Azure SQL Database (and if you're not, go fix it), you can run a query against sys.dm_os_wait_stats. Here's the output for the TOP 10 waits ordered by wait time on one of my databases: Everything you're used to seeing, right? Well... not quite. This is Azure SQL Database. So, let's use sys.dm_db_wait_stats, a DMO that is only available within Azure. This lists waits by database. The TOP 10 here are: You'll notice that these results are wildly different from those above. What we're looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the…
Read More

Query Store and Recompile

Azure, SQL Server 2016, T-SQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS 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; If this procedure is called with the value of 'Mentor' you get…
Read More

Azure SQL Database Error

Azure
I was on SQL Cruise where I was scheduled to present a session on Azure SQL Database. I recorded all my demonstrations before we went to sea, but, I planned to attempt to try live demo's. Yeah, yeah. Well, anyway, I got a unique error when I attempted to connect: Forced connection closes from remote host That's a partial message from the whole error. I don't have a good screen capture. I wasn't able to find anything on it through Ging searches, but this week I was at Microsoft for a training course on Azure. I asked the room. The rough answer is (paraphrasing): The IP address I was attempting to connect from is not is not on the approved list Interesting. I didn't realize there were blackout zones. The really…
Read More