05Oct 2015 by Grant Fritchey 1 Comment

Trace Flags in Azure SQL Database

Azure, SQL Server 2016, T-SQL
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,…
Read More
24Aug 2015 by Grant Fritchey 9 Comments

Targeted Plan Cache Removal

Azure, T-SQL
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…
Read More
29Jul 2015 by Grant Fritchey 1 Comment

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

Azure, Database Lifecycle Management, DevOps, PASS, SQL Server, T-SQL
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…
Read More
20Jul 2015 by Grant Fritchey 5 Comments

SQL Server Management Studio – Footloose and Fancy Free

Azure, SQL Server
That's right. There's been a divorce. SQL Server Management Studio (SSMS) has been divorced from the server product. In fact, Microsoft is inviting you to the new SSMS coming out party. I'm pretty excited about this. While I'm very comfortable in SSMS, to a large degree, it's like that old pair of jeans that you've worn for the last 10 years. They're comfortable too. Well, maybe a little tight when you pull them on out of the wash. One of the knees is gone. The legs are frayed so much it almost looks intentional. You just noticed a hole in the bottom. The zipper is acting up... Yeah, OK. These jeans have had it. So has SSMS. The plan from Microsoft is to upgrade SSMS independently from the boxed product. In fact, since one of…
Read More
26May 2015 by Grant Fritchey 11 Comments

Database Engine Tuning Advisor

Azure, SQL Server, T-SQL
I would love to see the Database Engine Tuning Advisor (DTA) pulled from the product. Completely. Heck, I feel bad that I included a chapter on it in my query tuning book (all updated for SQL Server 2014 by the way). Let me tell you why we need to pull this tool. First, I understand its purpose. It's supposed to be a fast and easy way to get some performance tuning done for people who just don't have the time or knowledge to go through the full process of gathering metrics, evaluating poor performers, understanding root causes and applying indexes to fix those causes. I also readily acknowledge that it actually is an amazing piece of software. If you don't agree with that, go read this white paper. With those acknowledgements…
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
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
06Apr 2015 by Grant Fritchey 8 Comments

Constraints and SELECT Statements

Azure, SQL Server, T-SQL
I've posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don't affect execution plans do they? Yes. Let's take this constraint as an example: ALTER TABLE Sales.SalesOrderDetail WITH CHECK ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK  ((UnitPrice>=(0.00))) That will ensure that no values less than zero can slip in there. We can even validate it: INSERT Sales.SalesOrderDetail (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate ) VALUES (60176, -- SalesOrderID - int N'XYZ123', -- CarrierTrackingNumber - nvarchar(25) 1, -- OrderQty - smallint 873, -- ProductID - int 1, -- SpecialOfferID - int -22, -- UnitPrice - money 0.0, -- UnitPriceDiscount - money NEWID(), -- rowguid - uniqueidentifier GETDATE() -- ModifiedDate - datetime ); Will give me…
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