22Aug 2012 by Grant Fritchey 1 Comment

24 Hours of PASS, Fall 2012

PASS, SQL Server, T-SQL, Tools
It's time to get your learn on again. The schedule for the Fall 24 Hours of PASS is up and ready for registration. This is the Summit preview session, so many (most, all) of the speakers are showing off some of what you can learn at their sessions at the PASS Summit 2012 itself. It looks like a pretty exciting bunch of topics given by some of the best professionals in the industry. I'll be presenting Three Ways to Identify Slow Running Queries on September 20th, 1400 GMT. This is just a sub-set of the information that I'll be presenting during my all day pre-conference seminar, Query Performance Tuning: Start to Finish. The full seminar I talk about how to measure the performance of your systems, identify which queries are…
Read More
02Jul 2012 by Grant Fritchey 12 Comments

Querying Information from the Plan Cache, Simplified

SQL Server, T-SQL
One of the great things about the Dynamic Management Objects (DMOs) that expose the information in plan cache is that, by their very nature, they can be queried. The plans exposed are in XML format, so you can run XQuery against them to pull out interesting information. For example, what if you wanted to see all the plans in cache that had a Timeout as the reason for early termination from the optimizer? It’d be great way to see which of your plans were less than reliable. You could so like this: WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason, RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel, --dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) / 2 + 1) AS StatementText, deqp.query_plan, deqp.dbid, deqs.execution_count, deqs.total_elapsed_time, deqs.total_logical_reads, deqs.total_logical_writes FROM…
Read More
26Jun 2012 by Grant Fritchey 29 Comments

Problems with my new book

Professional Development, SQL Server
First off, I apologize. As if writing a book wasn't hard enough, now we get new problems because of on-demand printing. Here's the story. Book. Nine months of writing. Excellent technical editing. Great copy editing. Book complete about six weeks ago. Yay! Now things get fun... Here's how it works. Everyone these days uses digital copies of the book and prints on demand. So Apress is printing some copies of the book, but not all. They send a file out to places like Amazon. Amazon uses that file to print some copies of the book, as needed, on-demand. Everyone is, in theory, printing from the same digital file, creating exactly the same book. Or are they? What happens if, oh, let's just say that a file was corrupted somehow prior to…
Read More
05Jun 2012 by Grant Fritchey 5 Comments

Guest Blog

SQL Server
I was given the opportunity to put together a guest blog post for the MVP blog. I did a little something on determining whether or not you have high memory use through the use of a DMO. Check it out.
Read More
31May 2012 by Grant Fritchey 21 Comments

Never, Ever Use Clustered Indexes

SQL Server
This whole concept of the clustered index as a foundational structure within SQL Server is just plain nuts. Sure, I get the concept that if a table has a clustered index, then that index actually becomes the table. When you create a clustered index on a table, the data is now stored at the leaf level of the Balanced Tree (b-tree) page distribution for that index, and I understand that retrieving the data using a seek on that index is going be extremely fast because no additional reads are necessary. Unlike what would happen with a non-clustered index on a heap table. Yes, I get that if I store my data in a heap, the only way to access the data is through the Index Allocation Mapping (IAM)  pages that…
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
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
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