02Apr 2012 by Grant Fritchey 4 Comments

SQL Server Log Backups

T-SQL
While presenting a session on Common Backup Problems both at SQL Saturday in Orange County and at SQL Connections in Las Vegas, questions came up regarding whether, when, and how your databases should be configured in FULL recovery mode and what that means for your recovery plans. To boil the questions down to as simple a single statement as possible: Should your database be in FULL recovery mode at all times which requires you to run log backups on a regular schedule? The same question came up while Brad McGehee(blog|twitter) was presenting a session on using the settings on your SQL Server instance and your database to ensure you got proper performance. We both answered the question the same way, but our emphasis was slightly different. Brad’s answer was that,…
Read More
26Mar 2012 by Grant Fritchey 15 Comments

Execution Plan for a User Defined Function

SQL Server, T-SQL
When you execute a multi-statement user-defined function you may see an execution plan that looks something like this: It appears as if the cost of the UDF is free. This is especially true if you use the UDF in a query with other objects, such as joining it to actual tables. Since the optimizer always assumes a multi-statement UDF has a single row for statistics estimates, it' frequently displays a low cost. But you know that there’s more going on there, right? It’s a multi-statement UDF because it’s doing a lot of work, but that is not reflected in the execution plan.. or is it? What if we went after the cache? Let’s run this little query: SELECT deqp.query_plan, dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) /…
Read More
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
12Mar 2012 by Grant Fritchey 14 Comments

SQL Server 2012 AlwaysOn & A Thorough Setup

Uncategorized
It’s surprisingly easy to set up the new AlwaysOn features. I’ve done it on VMs running on my laptop, from scratch, three times in the last few weeks. It’s easy because there are a set of validations that your run for the cluster and for the AlwaysOn setup that ensure you’re going to get a successful install… or do they? I hit a situation where it didn’t work correctly, so I thought I’d share it in case others ran into it. The setup is straight forward. I have network, contoso (yes, I’m using Microsoft training & documentation, it’s a beta, but you should see it available soon), with a domain controller and five servers all in a failover cluster. They passed the cluster test, so all five are hooked in.…
Read More
08Mar 2012 by Grant Fritchey 1 Comment

Let’s Talk

PASS, Professional Development
I’m travelling to several upcoming events and I’d love to sit down and talk with you. Yes, I get it, most of these events you’re going to see the sessions, but you really should plan on networking too. Here’s your chance to get started on that: Southern New England SQL Server Users Group, East Greenwich,RI – 3/14/2012: TSQL Deployment and Continuous Integration Best Practices SQL Saturday 120, Orange County - 3/24/2012: I’m presenting two times here. They’re both presentations that I’m working up for Connections. Common Backup Problems and How To Deal With Them TSQL Deployment and Continuous Integration Best Practices SQL Connections:Las Vegas 3/26 – 3/29/2012: I get it. You don’t go to Vegas to network. That doesn’t mean you can’t still do that too. I’m presenting three sessions.…
Read More
07Feb 2012 by Grant Fritchey 6 Comments

Avoiding Bad Query Performance

T-SQL
There’s a very old saying, “When you find yourself in a hole, stop digging.” And my evidence today is: That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard. If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows. Please, stop digging.
Read More
06Feb 2012 by Grant Fritchey No Comments

Meme Monday: Deadlines

Professional Development
Tom LaRock (blog|twitter) has assigned an interesting topic for Meme Monday this month, working with deadlines. Some people hate deadlines. Some people love deadlines. But when you have one, there’s a good chance you need to really meet that deadline or there could be repercussions. I have a tip that I’ve found useful in the past. When I have a deadline for delivery of X, I evaluate that requirement and determine what, if anything, is dependent upon other people. I’ve found this to be the biggest issue because my deadline is seldom their deadline. So if there are parts of my deadline where I’m dependent on others, that’s my first task: Go have a chat. Here are a few questions: Is X a deadline for them? If so, when do…
Read More
01Feb 2012 by Grant Fritchey No Comments

Learning, Networking & Wine

Misc
Let’s say, you want to spend a couple of days learning about SQL Server. You also want to spend some time networking with others. And, it just so happens, you’re a bit of a wine connoisseur. I have the event with you in mind. It’s the first ever, SQL Excursion. It’s due to take place May 17-19 in Napa Valley. The training is from two of the biggest names in the industry, Denny Cherry and Tom Larock. I learn from these guys all the time and now it’s your turn. But this isn’t going to be 60 minutes in a presentation followed by 10 minutes in the hallway at a SQL Saturday. Not, this is going to be intense sessions with these guys on topics from performance tuning to troubleshooting…
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 13 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