16May 2016 by Grant Fritchey 8 Comments

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
12May 2016 by Grant Fritchey 9 Comments

Use The Correct Data Type

SQL Server, SQL Server 2016, T-SQL
Blog post #5 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Saying that you should use the correct data type seems like something that should be very straight forward. Unfortunately it's very easy for things to get confusing. Let's take a simple example from AdventureWorks. If I run this query: SELECT a.ModifiedDate FROM Person.Address AS a WHERE a.AddressID = 42; The output looks like this: 2009-01-20 00:00:00.000 Normal right? You see the year, the month and the day followed by the time in hours, minutes, and seconds as a decimal. Ah, but there is an issue. This query is supposed to be for the reporting system, and the business only cares about the date that the values in the Person.Address table have been modified, so they don't want…
Read More
03May 2016 by Grant Fritchey 5 Comments

Implicit Conversion and Performance

SQL Server, SQL Server 2016, T-SQL
Letting SQL Server change data types automatically can seriously impact performance in a negative way. Because a calculation has to be run on each column, you can't get an index seek. Instead, you're forced to use a scan. I can demonstrate this pretty simply. Here's a script that sets up a test table with three columns and three indexes and tosses a couple of rows in: CREATE TABLE dbo.ConvertTest ( BigIntColumn BIGINT NOT NULL, IntColumn INT NOT NULL, DateColumn VARCHAR(30) ); CREATE INDEX BigIntIndex ON dbo.ConvertTest (BigIntColumn); CREATE INDEX IntIndex ON dbo.ConvertTest (IntColumn); CREATE INDEX DateIndex ON dbo.ConvertTest (DateColumn); WITH Nums AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) AS n FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ) INSERT INTO dbo.ConvertTest (BigIntColumn, IntColumn, DateColumn ) SELECT Nums.n, Nums.n,…
Read More
02May 2016 by Grant Fritchey 2 Comments

How to Convince the Boss to Send You to PASS Summit

PASS, Professional Development
August two years ago I originally posted, Make the PASS Summit Work for Your Employer. After conversations at several SQL Saturdays over the last couple of months, I decided to refresh and update that original content and post it again. I keep hearing how the job market has changed. That companies just don't want to pay for training any more. However, I don't recall any of my employers in the past ever actively wanting, desiring, begging me, please, oh, please, can't you go out to a little training? In fact, for the most part, I pretty much always had to beg the boss to send me out to training. I had to sell it. I don't think that's a new development. Let's review the selling points to help you convince the boss.…
Read More
27Apr 2016 by Grant Fritchey No Comments

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
21Apr 2016 by Grant Fritchey 1 Comment

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
20Apr 2016 by Grant Fritchey 22 Comments

Payment and the Board

PASS
If you read the March PASS Board meeting minutes, you saw that there was a discussion around board members and payments related to SQLSaturday precons. The question is simple, for a PASS branded event, should a member of the PASS board receive payment? There are a couple of relevant facts. In the by-laws it says: “…provided that nothing herein contained shall be construed to preclude any Director from serving PASS in an educational or speaking capacity and receiving compensation upon approval by a majority vote from the Board of Directors.” That's very clear. If a member of the Board wishes to receive payment from PASS to put on a precon, they have to get approval of the Board. Next fact, PASS branded events, such as SQLSaturday, are not, because of…
Read More
12Apr 2016 by Grant Fritchey 8 Comments

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
05Apr 2016 by Grant Fritchey 3 Comments

Views and Simplification

SQL Server, SQL Server 2016, T-SQL
I've been getting lots of questions on views lately. Must be something in the water. Because SQL Server allows you to treat a view as if it was a table, lots of people pretty much assume that it is a table since they get to treat it that way. The thing is, a view is not a table. It's a query. Let's explore this just a little bit. Here's a relatively straight forward view: CREATE VIEW dbo.PersonInfo AS SELECT a.AddressLine1, a.City, a.PostalCode, a.SpatialLocation, p.FirstName, p.LastName, be.BusinessEntityID, bea.AddressID, bea.AddressTypeID FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; GO I can query this view like this: SELECT * FROM dbo.PersonInfo AS pni…
Read More
01Apr 2016 by Grant Fritchey No Comments

Speaker of the Month: April 2016

Professional Development
THIS IS NOT AN APRIL FOOL POST! Seriously. My Speaker of the Month for April 2016 is Keith Tate (b|t) and his session at SQL Saturday Chicago called Profiler is Dead, Long Live Extended Events. I actually suspected very strongly from the start of the session that it was going to be good. The reason for this, Keith was having issues with his machine, but he started the session anyway. It was an excellent beginning. Then, he started to talk about Extended Events and use his slide deck to emphasize the points he was making, and it was wonderful. For example, as he talked about the way the number of events has grown in each version of SQL Server since 2008, he used larger and larger fonts with the bigger…
Read More