11May 2015 by Grant Fritchey 13 Comments

Is Performance Better With LEFT JOIN or RIGHT JOIN?

T-SQL
I tend to write my queries using LEFT JOIN. Why? Because logically I see it in my head like this: Give me all the rows from this table and only those rows that match from the other table. But, wouldn't this logic work just as well: Give me only the rows in this table that match the rows from this other table where I'm selecting all of them. I know. If I worked on it some more I could make that a better sentence, but I'm pretty sure the logic is still sound. Only matching rows from one data set, all the rows from another data set. In short, RIGHT JOIN. I read recently that we ought to be making everything into a LEFT JOIN because it performs better. I suspect…
Read More
05May 2015 by Grant Fritchey 15 Comments

Effects of Persisted Columns on Performance

T-SQL
I live for questions. And my favorite questions are the ones where I'm not completely sure of the answer. Those are the questions that make me stop presenting in order to take a note so I can try to answer the question later, usually in a blog post. Guess where we are today? I was asked at SQL Bits in London about the direct impact of the PERSISTED operator on calculated columns, both inserts and selects. I didn't have a specific answer, so I wrote it down for later (and asked the, self-described, persisting Dane, to email me to remind me. He did, so I put together a few tests to try to answer his question. First, I created three tables: CREATE TABLE dbo.PersistTest ( PersistTestID INT IDENTITY(1,1) NOT NULL PRIMARY…
Read More
01May 2015 by Grant Fritchey 6 Comments

Speaker of the Month: May 2015

Professional Development
I finally got out to some community events last month and got to see a bunch of great speakers. Not that you don't see great speakers at paid events, you do. It's just that they're usually professional speakers and I want to encourage others when I can. The Speaker of the Month for May 2015 is John Sterrett (b|t) and his presentation Automate Your Daily Checklist With PBM and CMS at SQL Saturday Boston. The very first thing that impressed me about this presentation was how John entered the room and immediately started interacting with the attendees. You see a lot of speakers hiding behind the lectern, fiddling with cables, nervously sipping water. Not John. He was chatting as he set up, immediately getting control of the room. It's a great…
Read More
27Apr 2015 by Grant Fritchey 20 Comments

Benefits for Some, All or Only a Few

PASS, Professional Development
As a member of the PASS Board of Directors I attended the PASS Business Analytics Conference (BAC) recently. You can read more about it here and here (as well as here). Let me start with an important note: I am voicing my opinion here as an individual, not an official stance of the PASS organization. There is controversy around the BAC because of a whole bunch of things, but one question in particular bothered me. It was suggested that the people attending the BAC were just consuming the worth or value that other people who paid for the Summit generated. At first, I just dismissed this concept. It stuck in the back of my mind though. Suddenly I realized why. Yes, the BAC was partly paid for by Summit. The…
Read More
23Apr 2015 by Grant Fritchey 14 Comments

PASS Board Update: April 2015

PASS
It's been a pretty interesting month on the board. First, we did have a little problem. I'm sure one or two of you may have noticed that the SQL Saturday web site was down for a moment. Well, three days. Joking aside, this was a serious problem, but my involvement was largely peripheral since I'm in charge of Chapters. I tried to help out a little, offering what support I could and, if nothing else, supplying an ear, willing to listen. Quite a few people took advantage of that. I communicated all their information up the chain to HQ and the other board members. Nothing has been held back. Next, we've started the budgeting process. That's fascinating. As well as a giant pain in the... well, anyway. Thankfully the people…
Read More
21Apr 2015 by Grant Fritchey 5 Comments

Query Hash Formula Changes Between Versions

SQL Server
The question came up on SQL Server Central that someone wanted to track queries on their 2008R2 instance and on their 2014 instance in order to validate performance after an upgrade. The plan was to use query hash values. I was curious, so I set up a quick test. First, I spun up two VMs in Azure, both A1, both Enterprise, but one for 2008R2 and one for 2014. Then, I grabbed a generic query that runs against DMVs: SELECT TOP 10 SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset - deqs.statement_start_offset END) / 2 + 1) AS querystatement, deqp.query_plan, deqs.query_hash, deqs.execution_count, deqs.last_elapsed_time, deqs.last_logical_reads, deqs.last_logical_writes, deqs.last_worker_time, deqs.max_elapsed_time, deqs.max_logical_reads, deqs.max_logical_writes, deqs.max_worker_time, deqs.total_elapsed_time, deqs.total_logical_reads, deqs.total_logical_writes, deqs.total_worker_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY…
Read More
16Apr 2015 by Grant Fritchey No Comments

Azure SQL Database v12 and SQL Magazine

Azure
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…
Read More
13Apr 2015 by Grant Fritchey 2 Comments

Azure SQL Database Firewall Settings

Azure
The new portal for managing Azure is pretty. I'm not sure I'm in love with it, but it's pretty. However, one thing that I have to access regularly is the firewall settings for my Azure SQL Database. I do demos from all over the place. I never know what my IP address is going to be. Rather than expose everything, I just set up whatever IP address I'm on and then remove it later. The old portal made this easy. The new one... not so much. So, let's get this down real quick. Assuming you connect to the new portal and go straight to your database, you'll see this image showing you the database and the server it's on: You won't see anything else that suggests FIREWALL. But, you can…
Read More
10Apr 2015 by Grant Fritchey 5 Comments

I Am Grateful For

Professional Development
I decided in January that I would write regularly about people that I'm grateful for. Now it's April. Oops. The concepts are simple and science based. Positive emotions help your clarity of thought. Further, your moods and actions extend out through your network to the third layer. With that in mind, I want to think more clearly and the most direct path to positive thoughts and emotions being gratitude, I'm attempting to focus and publicize my gratitude by publicly sharing it through these blogs (in short, I'm trying to tune my brain like I would tune a query). I am grateful for Tim Ford (b|t). Tim is a great guy. Further, Tim can be patient with thick headed dorks (raising my hand). For example, among all the other stuff Tim does…
Read More
07Apr 2015 by Grant Fritchey No Comments

Error: Unknown Property ismemoryoptimized

Azure
If you're starting the process of moving your databases in Azure SQL Databast to v12, you need to do one thing. Make sure you have SQL Server 2014 CU5 or better installed on your machine with Management Studio (current list of updates). Otherwise, like me, you'll be looking at the above error. Just a quick blog post to help out. I saw this error, did a search, and didn't hit a single entry telling me what to do. I started the install of CU6 (I needed to catch up on cumulative updates anyway). While that was happening, I went to Twitter and posted to #sqlhelp to see if anyone else had hit this. I got a response from Amit Banarjee pointing me to this MSDB blog on the topic, reinforcing…
Read More