28Mar 2016 by Grant Fritchey 8 Comments

Query Store and Optimize For Ad Hoc

SQL Server 2016
I love presenting sessions because you get so many interesting questions. For example, what happens with Optimize for Ad Hoc when Query Store is enabled? Great question. I didn't have the answer, so, on to testing. For those who don't know, Optimize for Ad Hoc is a mechanism for dealing with lots and lots of ad hoc queries. When this is enabled, instead of storing an execution plan the first time a query is called, a plan stub, basically the identifying mechanisms, for the plan is stored in cache. This reduces the amount of space wasted in your cache. The second time the query is called, the plan is then stored in cache. I'm going to set up Optimize for Ad Hoc and Query Store and, to clean the slate,…
Read More
07Mar 2016 by Grant Fritchey 2 Comments

SQL Server Backups Are A Business Decision

SQL Server, SQL Server 2016
Blog post #3 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel Read more about Tim's challenge here. It's very easy to think of SQL Server backups as a technical problem. You have so much stuff going on, BACKUP DATABASE commands, recovery models, BACKUP LOG commands, Differential backups. Getting them all into the correct order and automating the processes sure seems like a technical problem. It isn't. It's all about the business. If you're taking on the duties of a DBA whether you're an accidental DBA, a reluctant DBA or you were voluntold into the DBA position, you need to plan to sit down with responsible parties from the business and get an understanding with them regarding RPO and RTO. RPO is a TLA for Recovery Point Objective. The easiest way to…
Read More
12Jan 2016 by Grant Fritchey No Comments

“Applies To…” in the MSDN Documentation

Azure, SQL Server 2016, T-SQL
Quick little post. I just wanted to share how happy I am with the new "THIS TOPIC APPLIES TO" infographic. An example here: 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.
Read More
20Oct 2015 by Grant Fritchey No Comments

Statistics for the New Data Pro

PASS, SQL Server, SQL Server 2016, T-SQL
Next week at the PASS Summit I'll be presenting a session called Statistics for the New Data Pro. You can read the abstract at the link. I just want to emphasize that this is a beginner level session. I think way too many people who are just starting out with SQL Server don't understand the role that statistics play in determining how your queries are going to behave. What's more, too many people don't know how to get and read statistics to understand how it is that the optimizer thinks you have X number of rows in your database that match a given value. I'm going to make darned sure that the people who attend this session come out with a full understanding of how to read the statistics. This includes…
Read More
13May 2014 by Grant Fritchey 8 Comments

Add an Instance to SQL Server Azure Virtual Machine

Azure, SQL Server
How do you add an instance to your local SQL Server installation? You run the executable that you probably downloaded from MSDN or maybe from a CD. Works the same on an Azure VM right? Sure... but wait. Do I have to go and download the software to my VM instance? Let's assume that you're running one of the VMs from the Gallery, then, the answer is "No." Just navigate to C:\SQLServer_12.0_Full. There you'll find the full installation setup for SQL Server. And you're off and running... Until you realize that you don't have the Product Key for this thing. What happens when you get to this screen: You can look around all you want and you won't see a product key anywhere. At least no where that I could…
Read More
19Dec 2013 by Grant Fritchey 2 Comments

How to Set Up Managed Backups in SQL Server 2014

Azure
Earlier this week I introduced the concept of Managed Backups (and caused less of a turmoil than I thought I would). Now I want to show you how it works. It's really simple and quite well documented. Before you get to the, insanely simple, task of actually enabling Managed Backup, you will need to go through the prerequisites. First, and this should be obvious, but I'll state it, just in case, you need to set up an Azure storage account. That's so insanely straight forward that I'm not going to say more. Then, you have to set up encryption on your system. I used these commands to prep it: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$qlserver2012queryperformancetuning'; CREATE CERTIFICATE CloudDojoCert WITH SUBJECT = 'Backup Encryption Certificate'; Again, shouldn't have to…
Read More
01Jul 2013 by Grant Fritchey 15 Comments

Getting Started With SQL Server 2014 the Easy Way

Azure, PASS
You know you want to at least take a look at the new Client Technology Preview (CTP) of SQL Server 2014. I don't blame you either. I want to spend hours swimming through it too. But, you're thinking to yourself, "Heck, I'd have to download the silly thing, provision a new VM, walk through the install... Nah. Too much work." I don't blame you. I found myself on the road the day the software was released, so I was going to attempt to do all that work on a hotel wireless system. In short, I was going to have to wait, no options. Or were there? Actually, there is a much easier option. Azure Virtual Machines. And no, it's not that I can simply get a Windows Azure VM ready to go…
Read More
10Jun 2013 by Grant Fritchey 1 Comment

Praise and a Suggestion for AlwaysOn

Uncategorized
One of my favorite additions to SQL Server 2012 is the Availability Groups, referred to as AlwaysOn. These things are awesome. It's basically shared nothing clustering at a database-by-database level. You can set up a series of secondary machines that will enable you to failover a database in the event of an outage. This is a huge advantage in high availability and disaster recovery scenarios. We're talking serious business continuity. Further, you can set up one of those secondary machines to allow for reads, meaning, you get a reporting database that allows you to offload read queries from a transactional machine. Another giant win. But wait, it gets better. Now, with the capabilities that are coming with Azure Virtual Machines and Azure Virtual Networks you can go even further. It's…
Read More
31May 2012 by Grant Fritchey 21 Comments

Never, Ever Use Clustered Indexes

SQL Server
This whole concept of the clustered index as a foundational structure within SQL Server is just plain nuts. Sure, I get the concept that if a table has a clustered index, then that index actually becomes the table. When you create a clustered index on a table, the data is now stored at the leaf level of the Balanced Tree (b-tree) page distribution for that index, and I understand that retrieving the data using a seek on that index is going be extremely fast because no additional reads are necessary. Unlike what would happen with a non-clustered index on a heap table. Yes, I get that if I store my data in a heap, the only way to access the data is through the Index Allocation Mapping (IAM)  pages that…
Read More
13Dec 2010 by Grant Fritchey 133 Comments

Life/Work Balance

SQL Server, Tools
Technology, especially information technology, is the greatest thing to ever happen to mankind, freeing us from toil and drudgery. Technology, especially information technology, is a pernicious evil taking over our lives forcing us to work harder and longer. Depending on the time of day, the day of the week, my mood, my wife’s mood, or the direction the wind is blowing, either of these statements could be true. The fact is, I love technology and I do have to wrestle with keeping it from taking over my life, but only because I have so much fun with the toys that technology brings. You want to know how much I love toys, ask me about my Droid sometime. Pull up a chair. We’re going to be here a while. The trick…
Read More