10Jun 2014 by Grant Fritchey 3 Comments

Differences In Native Compiled Procedures Execution Plans

Uncategorized
All the wonderful functionality that in-memory tables and natively compiled procedures provide in SQL Server 2014 is pretty cool. But, changes to core of the engine results in changes in things that we may have developed a level of comfort with. In my post last week I pointed out that you can't see an actual execution plan for natively compiled procedures. There are more changes than just the type of execution plan available. There are also changes to the information available within the plans themselves. For example, I have a couple of stored procedures, one running in AdventureWorks2012 and one in an in-memory enabled database with a few copies of AdventureWorks tables: --natively compiled CREATE PROC dbo.AddressDetails @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL…
Read More
12Feb 2014 by Grant Fritchey 1 Comment

SQL Server 2014 and the New Cardinality Estimator

Uncategorized
Cardinality, basically the number of rows being processed by an operation with the optimizer, is a calculation predicated on the statistics available for the columns in question. The statistics used are generally either the values from the histogram or the density. Prior to SQL Server 2014, and going all the way back to SQL Server 7.0 (in the Dark Ages when we had to walk uphill to our cubicles through 15 feet of snow battling Oracle DBAs and Fenris the whole way), there's been one cardinality estimator (although you can modify the behavior somewhat with a traceflag in 2008R2 and 2012). Not any more. There's a possibility for really complex, edge-case queries, that you may run into a regression from this. You control whether or not you get the new…
Read More
10Jun 2013 by Grant Fritchey 1 Comment

Praise and a Suggestion for AlwaysOn

Uncategorized
One of my favorite additions to SQL Server 2012 is the Availability Groups, referred to as AlwaysOn. These things are awesome. It's basically shared nothing clustering at a database-by-database level. You can set up a series of secondary machines that will enable you to failover a database in the event of an outage. This is a huge advantage in high availability and disaster recovery scenarios. We're talking serious business continuity. Further, you can set up one of those secondary machines to allow for reads, meaning, you get a reporting database that allows you to offload read queries from a transactional machine. Another giant win. But wait, it gets better. Now, with the capabilities that are coming with Azure Virtual Machines and Azure Virtual Networks you can go even further. It's…
Read More
25Jun 2012 by Grant Fritchey 3 Comments

Does Encryption Affect Seeing Statements in Deadlock Graphs?

Uncategorized
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: CREATE PROCEDURE DL2e WITH ENCRYPTION AS BEGIN TRANSACTION UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 2 WHERE ProductID = 448 AND PurchaseOrderID = 1255; Then I’ll execute things in the following order. From one connection this query: UPDATE Purchasing.PurchaseOrderHeader SET Freight = Freight * 0.9 --9% discount on shipping WHERE PurchaseOrderID = 1255; From a second connection, my stored procedure: EXEC dbo.dl2e; Then, back on the first connection, this query: UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 4 WHERE ProductID = 448 AND PurchaseOrderID = 1255; That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: <deadlock> <victim-list> <victimProcess id="process472310928" />…
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
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
12Dec 2011 by Grant Fritchey 11 Comments

Changes to SQL Server 2012 Execution Plans

Uncategorized
I’ve been working with execution plans quite a lot in SQL Server 2012. There are a number of changes, most of them associated with new or different functionality. I had not noticed anything really fundamental until recently. I’ve become a huge proponent of always checking the properties of the SELECT statement. There’s so much useful information in there about what’s happened with the plan generation in the optimizer (not low level stuff, but the big picture items) that you should always be checking it first as a fundamental part of your plan examinations. Here’s an example from SQL Server 2008R2: You can see cool stuff like the size of the plan, the time it took to compile, the optimization level, the reason for early termination. Many of the properties on…
Read More
21Nov 2011 by Grant Fritchey 3 Comments

Installing SQL Server 2012 RC0

Uncategorized
Very short informational post. If you’re like me and you’ve been running the public Client Technology Preview (CTP3) of SQL Server Denali, uh, I mean 2012, then the news about the release of RC0 is pretty exciting stuff… until you realize that you’re going to have to tear down your virtual test machine, removing the CTP in order to prep for the install of RC0. I love playing with the toys, but I hate working on the toys, if you know what I mean. Great news. You can upgrade from CTP3 to RC0 in place. I know because I’ve done it. When you run the install and you get to the screen that asks what type of install you want, select Upgrade, as highlighted below: I know it doesn’t say…
Read More
17Oct 2011 by Grant Fritchey 1 Comment

Extended Events Data

Uncategorized
I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer magnitude of what you can do with extended events is just becoming clear to me. The interesting thing though is how much the basics are similar to trace. Similar mind you, not the same. For example, the best way to gather trace data is to output it to a file and then read the file into a table for later querying. It’s the same with extended events. There’s even a function that acts as a table: SELECT * FROM sys.fn_xe_file_target_read_file ('C:\APath\Query Performance Tuning*.xel', NULL, NULL, NULL); This can take advantage of roll-over files just like the old function used for traces. You can also provide offsets to read a sub-set of…
Read More
05Oct 2011 by Grant Fritchey 1 Comment

Extended Events and Performance Tuning Knowledge

Uncategorized
I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Read More