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