19Mar 2012 by Grant Fritchey 1 Comment

TDE and Compression

SQL Server
I knew that enabling TDE would cause the information stored within to, effectively, be randomized, which means it would seriously impact the ability for backup compression to work well. It even says this in Books Online: Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended. I took that completely at face value and always figured it meant I would only get 5-10% compression or something instead of the higher rates available through backup compression (or the much, much higher rates available through 3rd party compression). Imagine my surprise when I tested this on a 20gb database. Not only did I…
Read More
31Jan 2012 by Grant Fritchey No Comments

Tech on Tap v1.1 Wrap-up

SQL Server
The first ever Tech on Tap event was held on Saturday, January 27th, 2012 at the Stone Cellar Brewpub in Appleton, WI. If you weren’t there, well, you missed it. Notice that keyword in that earlier sentence, Brewpub. Yes, the Tech on Tap events (and there will be more) are specifically designed to mix Technology and Beer in a learning and networking event with a single focus. This first event was all about virtualization. The space where the event was held was the back bar of the Brewpub in a private room. The layout of the tables wasn’t perfect for presenting, but they were very conducive to networking. Other than that, the space was just excellent. The first presenter was Brian Lewis (blog) from Microsoft. He spoke about virtualization in…
Read More
30Jan 2012 by Grant Fritchey 12 Comments

Execution Plans, What Do I Look At?

SQL Server, T-SQL
The question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6: Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s…
Read More
11Jan 2012 by Grant Fritchey 10 Comments

Database Backups: Things You Need To Do Now

SQL Server, T-SQL
I want to say a few things about database backups that you need to know. Wait a minute, haven’t you written about backups before? Why, yes. Yes I have. Aaand… you’re doing it again because? Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out. Oh, good point. Carry on. Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate…
Read More
25Oct 2011 by Grant Fritchey 6 Comments

SQL Server XQuery Against Execution Plans

SQL Server, T-SQL
One of the greatest things about all the DMOs is how you can combine the information they present to you with execution plans. It allows you to see what a query is doing, even as it’s executing, because the plan is created first. I couldn’t possibly emphasize enough how important that’s going to be in your day-to-day troubleshooting. Even better is the fact that you’re going to be able to make use of XQuery to pull useful information out of the execution plans that are in cache. Or are you? Here’s a query to pull some information out of the procedure cache: SELECT  deps.type_desc, deps.last_execution_time, deps.execution_count, deps.total_logical_reads, dest.encrypted AS EncryptedText, dest.text, deqp.query_plan, deqp.encrypted AS EncryptedPlan FROM    sys.dm_exec_procedure_stats AS deps CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp WHERE  …
Read More
22Sep 2011 by Grant Fritchey 5 Comments

SQL Server Execution Plans

PASS, Professional Development, SQL Server
I write quite frequently about SQL Server Execution Plans. I started in that area just because that’s how you figure out what a query is doing and sooner or later, we all have to tune a query. I found I was doing it sooner and more frequently. When the opportunity came up to write a book , I jumped on it. Now I find myself presenting, rather frequently, on execution plans. One of the people I’ve learned from over the last several years is Gail Shaw (blog|twitter). I first saw Gail on stage at the PASS Summit, I think it was 2007. A co-worker of mine was picked, along with Gail, to go on stage for the Quiz Bowl. Gail was answering all the questions. If you go over to…
Read More
21Sep 2011 by Grant Fritchey 1 Comment

MicroTraining: Performance Tuning Checklist

SQL Server
I’ve been playing a lot with Google+ and the Hangouts there. I love them. I think they’re opening up a whole new way of interacting and building community. Today Andy Leonard (blog|twitter) and Ken Watson (blog|twitter) were trying out the new features in G+, including desktop sharing. We were talking about how great this was and suddenly the idea of short, sharp training for small groups of people that can interact came into being. Andy termed it MicroTraining, and an idea was born. So, Friday, 9/23/2011, at 11:00AM EDT, I’ll host a Google+ hangout. It will be public and accessible to anyone. I’ll make it one of the Air ones that gets recorded. That means that 10 people can attend & ask questions, but an unlimited number can watch. It’s…
Read More
20Sep 2011 by Grant Fritchey 17 Comments

Statistics in Execution Plans

SQL Server, T-SQL
I was presenting on execution plans when another question came up that I didn’t know the answer to immediately. Yes, I know you’ve seen that phrase before on this blog. I love presenting because you get exactly the kinds of questions that make you think and make you learn. I’m presenting, in part, to learn, just as much as I am to teach. It was the same with kenpo. The more I taught, the better I learned the art. Wait, this isn’t supposed to be a blog post about learning. This one is about statistics. The question was, does the execution plan have the statistics that were used by the optimizer to decide on the execution plan. And no, what was meant, was not does it show the estimated rows,…
Read More
15Sep 2011 by Grant Fritchey 6 Comments

Probe Residual on Hash Match

SQL Server, T-SQL
I have to say, I only recently noticed this on a tool tip: and this in the property sheet: The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn't remember having seen one before. Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter: SELECT  soh.PurchaseOrderNumber, soh.AccountNumber, p.Name, sod.OrderQty, sod.LineTotal, cc.CardNumber FROM    Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Production.Product AS p ON sod.ProductID = p.ProductID…
Read More
22Aug 2011 by Grant Fritchey 3 Comments

Expert? Ha!

SQL Server, T-SQL
How do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book. Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before. I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew…
Read More