16May 2012 by Grant Fritchey 18 Comments

Don’t You Know Who I Am?

PASS, Professional Development
I’m happy to say that for most of you out there, the answer to this question is “no.” That’s as it should be. I’m not anyone all that special. I present technical sessions at various events from local user group meetings to SQL Saturday’s to international events like the PASS Summit and 24 Hours of PASS. Why? Not because the attendees know my name, that’s for sure. It’s because of a combination of at least two of these three things: The organizers might know my name or may know of my books or the fact that I’m working for a vendor that sponsors their event I’ve submitted sessions that seem to be of interest to the people who might be attending the event being organized I’ve got a track record…
Read More
07May 2012 by Grant Fritchey 1 Comment

Which SELECT * Is Better?

SQL Server, T-SQL
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT    * FROM    Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Read More
30Apr 2012 by Grant Fritchey No Comments

Changing DB_CHAIN Can Clear the Plan Cache

T-SQL
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. ALTER DATABASE Testing SET DB_CHAINING OFF; GO CREATE PROCEDURE x AS SELECT * FROM test.dbo.A AS a2; GO CREATE PROCEDURE y AS SELECT * FROM dbo.Table_1 AS t; GO EXEC dbo.x; EXEC dbo.y; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'CREATE PROCEDURE x%' OR dest.text LIKE 'CREATE PROCEDURE y%'; ALTER DATABASE Testing SET DB_CHAINING ON; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs…
Read More
26Apr 2012 by Grant Fritchey 5 Comments

AlwaysOn Failover Bug

Uncategorized
I just found a little bug in AlwaysOn. It’s actually not that big a deal, but it’s interesting. In a nutshell, if you have a table with an IDENTITY seed less than 1000, when you do a failover the table is reseeded to 1001. The steps to reproduce are posted on this Connect item. If you have feedback on this, please post it directly to the Connect item since I don’t think Microsoft reads my blog much.
Read More
23Apr 2012 by Grant Fritchey 47 Comments

Comments on Acceptable Behavior

Misc
Here’s the situation. We’re absolutely falling down as men and professionals. Ladies, feel free to read on and comment, but I’m talking to the guys. This blog post has been percolating in my mind for quite some time, but a few new stories have brought it to the fore. The most recent was when I heard that, in front of multiple other male MVPs, a female peer was propositioned using language that I wouldn’t condone when speaking to an animal, let alone a peer. And that’s the point, these women are peers. Yes, they happen to be female, but more importantly, they’re our peers. And you know who I’m most upset with? No, not the individual who acted like a jerk. There’s always a few of those around. No, I’m…
Read More
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