Jun 14 2016

Because

gaypride

Because there’s nothing else I can do at the moment.

 

Jun 13 2016

Choosing the Right SQL Server Edition

Post #6 of #entrylevel #iwanttohelp in support of Tim Ford’s (b|t) beginner’s initiative.

If you’re just getting started with SQL Server, the choices you have in front of you are legion. Which drive do you install your instance on? Which drives hold the databases? How many files do you need for a database? What do the tables look like? Which column or columns should be the primary key? Clustered index? Stored procedures? In-Memory? MAXDOP? Et, as they say, cetera. Ad, as they also say, nauseum.

Before any of that though, you need to pick the correct type of SQL Server to install. That’s right, just saying “SQL Server” is not enough. You must pick between:

SQL Server Developer’s Edition
SQL Server Express
Azure SQL Database
SQL Server Standard
SQL Server Enterprise

I could also add Microsoft Analytics Platform (APS) and Azure SQL Data Warehouse to that list, but that’s going to overly complicate things. We’ll just focus on the five core SQL Server types.

The list above is in the order in which you should be getting started with SQL Server. Let me explain why.

SQL Server Developer’s Edition

If you’re just getting started with SQL Server, SQL Server Developer Edition should be your first stop. With the release of SQL Server 2016 two weeks ago, this edition is now free. You should immediately go and download your own copy. You want to get this because it is everything that is available within SQL Server, all the way up to Enterprise Edition. It’s just licensed for development. If you want to learn about any aspect of SQL Server, start here. If you’re developing against any set of functionality within SQL Server, start here. If you need to support different parts of a SQL Server infrastructure, you start here. Prior to 2016, the Developer’s Edition was only $50-$60, and even then, I would have recommended it over free versions of the product. Now, with Developer Edition being completely free, there’s literally no excuse to not use this to get started learning SQL Server.

SQL Server Express

Assuming you’re just building out your first databases and your first server instances, you may be starting off very small, with only a few users and very little data. SQL Server Express allows you to grow your database up to 10gb, in a production environment (production and development are where licensing and costs differ wildly). This provides a mechanism for you to ensure that you really need SQL Server and all that it offers before you go into a full production installation and all that entails. If you’re only ever going to be very small, this offers a no-cost way to have a professional level database, despite the size. There are additional limitations in what Express supports and you can read about them at the link. It’s just a great way to help get you started.

Azure SQL Database

Here we begin to explore the paid versions of SQL Server. If you’re just getting started and you don’t have much experience administering SQL Server, then Azure SQL Database is a great option for you to start. It is a Platform as a Service offering. This means that it’s not a full instance of SQL Server like everything else listed here. Unlike everything else listed here, it’s not a full instance of SQL Server and that can be a good thing. Backups are managed for you as are a whole slew of other server-level settings, maintenance, etc. The cost model is completely based on what you need to store and how much activity you’re going to generate. You can start very small, and then grow as needed, only paying for what you use. This is extremely attractive if you’re just starting out. Further, the capabilities can grow with you as needed including setting up what would otherwise be extremely complicated things like geo-replication.

SQL Server Standard Edition

SQL Server Standard Edition is the workhorse for SQL Server. It does almost everything that most people need. It scales to the size that most people need to scale to. For your standard business and even standard enterprise needs, Standard Edition (note the name) works extremely well. There are limitations on what it can do. However, these limitations are primarily around extreme scale, or high-end behavior. If you’re just getting started, you don’t need to be messing with that kind of stuff.

SQL Server Enterprise Edition

SQL Server Enterprise Edition is the high end. Here is where you need to go to multi-terrabytes in size and you have massive transaction loads. You’re looking at very sophisticated availability and disaster recovery. Again, the name gives it away. You’re generally only going to this edition when you’re working at an enterprise level of scale and architecture. Since you’re just getting started, don’t worry about this.

Yet.

Conclusion

If you’re just starting to explore the SQL Server space, you should have a copy of Developer Edition. It costs nothing and does all you need. From there, expand into the other Editions as and where needed. Just remember to size your system according to how it’s being used, not how it might be used a year from now. Expanding to Standard from Express is easy. Adding a higher level of support in Azure is simple. Going from Standard to Enterprise is easy. Going backwards, that’s hard. Don’t pick the bigger more sophisticated Edition just in case. Make this choice carefully at the start.

Jun 06 2016

Elastic Query in Azure SQL Database and Views

The question came up, how do the constructs necessary for Elastic Query within Azure SQL Database affect your ability to create views that join across databases. Since I haven’t tested this myself, and I haven’t seen anyone else doing anything with it yet, I decided to set up a test. I recreated my cross database queries using the scripts posted here. Let’s create a view:

CREATE VIEW dbo.JoinedView
AS
SELECT  dt.Val,
        dt2.Val AS Val2
FROM    dbo.DB1Table AS dt
LEFT JOIN dbo.DB2Table AS dt2
        ON dt2.ID = dt.ID;

If I run the query, I get back results. Done. We can create views that join between Azure SQL Databases… But, views are all about masking right? What if I wanted to change the name of the table on my database. Could I do something like this?

CREATE EXTERNAL TABLE dbo.CanICheatThisName (
	ID int, 
	Val varchar(50)) 
WITH (
	DATA_SOURCE = DB2Access);

The CREATE script runs just fine with no errors. When I try to query it though:

Msg 46823, Level 16, State 2, Line 55
Error retrieving data from one or more shards.  The underlying error message received was: ‘Invalid object name ‘dbo.CanICheatThisName’.’.

Understanding how the Elastic Query works, of course this fails. So… what about faking it the other way. I’ll create a view in my second database:

CREATE VIEW dbo.FakeTable
AS
SELECT  *
FROM    dbo.DB2Table AS dt;

Now, I’ll create the EXTERNAL TABLE using the name of the view:

CREATE EXTERNAL TABLE dbo.FakeTable (
	ID int, 
	Val varchar(50)) 
WITH (
	DATA_SOURCE = DB2Access);

When I query this, the data comes across just fine.

Conclusion

Creating a view, or any other query, that joins across databases using Elastic Query works just fine. However, if you want to mask things using a view, you might need to get a little creative in how you implement Elastic Query. The good news is, Elastic Query is somewhat, shall we say, elastic in how you set it up. More so than it immediately appears.

Jun 02 2016

Resources for Learning Azure Data Platform

You want to start working with Azure and the Azure Data Platform, but getting started is not easy. Just knowing where to go to find useful information isn’t easy.

I’m here to help.

I’ve started a GitHub repository that is meant to provide a community-based resource that documents where and how you can learn about the Azure Data Platform. This somewhat duplicates my listing of Data Platform Instructors, but it actually frees that up so I can curate the list the way I want. I’ll probably make it a ranking soon. Why not. Anyway, I want to make sure you’re aware of this resource so that you can consume it or contribute to it. Please help me out if you have something to contribute. Otherwise, please help yourself to what I hope will become a great resource for learning about the Azure Data Platform.

Just know that, at this moment, it has a listing of speakers, mostly from here, and my blog with a link to Azure articles. I’m waiting to see what kind of contributions come in before I spend a lot more time creating material myself. My hope is that I can primarily curate the process and let others do the work (in short, I’m lazy).

Jun 01 2016

Independent Azure Data Platform Instructors

azureThe Azure Data Platform is taking off. I’m seeing more and more interest on the forums, at conferences and in my personal interactions. I’ve been teaching the data platform for six years. Almost as soon as it was available, I started working with it, putting up blog posts and setting up sessions. I’ve had stuff in production on the platform for almost that long too. I’m an advocate and, I hope, an independent voice on the topic. By independent in this case, I mean non-Microsoft. Don’t get me wrong, most of the people I learn from work for Microsoft. They are excellent instructors and more knowledgeable on the topic than I’ll ever be. I’m not questioning the ability of Microsoft people to deliver the very best Data Platform content. I just believe that people also want voices that are not directly beholden to the company.

That brings up my list. I have a list of Azure Data Platform Instructors. I’ve been maintaining this for about a eight weeks now. I’m adding to it regularly. At this point, the list is small and in alphabetical order. As it grows, I will begin the process of curating the list. I’ll probably start with categories only, but eventually, I’ll start to pick and choose who goes on the list based on my knowledge of the individuals involved. For now, these are the independent voices who are teaching Azure and the Azure Data Platform. If you’re getting started in Azure, this is the list you need to consult to find the very best independent voices.

May 31 2016

Precedence Goes to Query Store or Plan Guide?

While presenting at SQLDay in Wroclaw, Poland, on the Query Store, I was asked a pretty simple question, which takes precedence, the Query Store or a Plan Guide?

One of my favorite answers to questions is “I don’t know” because it gives me the opportunity to learn. Let’s figure this one out together.

I’ll post the code to recreate this experiment within AdventureWorks at the end of the article. I’m doing this because the code for forcing execution plans using Plan Guides can be pretty doggone long (you may need to generate your own XML from a plan on your own system, fair warning).

I have a stored procedure that I use frequently to demonstrate parameter sniffing and bad parameter sniffing, AddressByCity (listed below). This query when passed the value ‘London’ will generate a plan with a Merge Join. When passed the value ‘Mentor’ it will generate a plan with a Loops Join. These different plans are caused by the statistics for the different values. 434 rows for ‘London’ and 1 row for ‘Mentor’. To start the test, I’ll generate a plan for each value, dropping the plan from cache between executions, so that I have both plans in the Query Store. Now, we’re ready for phase 1.

Phase 1

I’m going to remove the current plan from cache. I will then use sp_query_store_force_plan to select the plan with the Loops join. I’ll run the query using ‘London’ to validate that the plan generated is NOT the plan for ‘London’ but rather the plan for ‘Mentor’. Done, and I can see the Use Plan results in the Properties of the Select operator:

QSvPG_UsePlan

I’ve highlighted a couple of interesting points. First, even though we are using a plan that is compiled for the value of ‘Mentor’, because this plan was compiled using the value ‘London’, that’s what gets stored, despite the plan used. You can then see down at the bottom, that the ‘Use plan’ property is set to true. Now, let’s force the plan using a plan guide (in my script below). To be very sure what happens, I’ll again clear the plan from cache.

If the Query Store wins, I should see a plan with a Loops Join. If the Plan Guide wins, I should see a plan with a Merge Join. The moment of truth:

QSvPG_Plan

Query Store wins! Let’s see how it shows in the Properties:

QSvPG_PlanGuideName

You’ll see that the Parameter Compile & Runtime values are the same and that the ‘Use plan’ property is the same. Note however, the PlanGuideName property is in play. In fact, the indication is that the optimizer thinks that a Plan Guide has been successfully applied. In fact, if I watch the Extended Events for the plan_guide_successful event, the best way to check how Plan Guides are being used, apart from looking at the execution plans, it shows success for the Plan Guide we created.

Phase 2

Precedence could matter. First, I remove the Plan Guide using sp_control_plan_guide. Then, I remove the Query Store force using sp_query_store_unforce_plan. I’ll also clear the plan out of cache again, just to be sure. Next, I create the Plan Guide. I’ll run the procedure using the value ‘Mentor’ to ensure that I see the Merge/’London’ plan, the PlanGuideName property in that plan, and a successful application of a Plan Guide from the plan_guide_successful ExEvent. All good.

Now, I once again force the plan from Plan Store. Rerun the plan…

The winner is Plan Store.

Phase 3

OK, so, if we try to force plans using Plan Guide, Query Store wins. What about if I’m just trying to apply a hint? Let’s clear everything up again and then create an OPTIMIZE FOR query hint in a Plan Guide. Simple right?

Query Store wins again… Except things get a little odd. Take a look at the Properties now:

QSvPG_Parameter

Normally, with a Plan Guide that includes an OPTIMIZE FOR hint, I’d see the Compiled Value as the defined value within the hint (in this case ‘London’). However, this time I’m seeing just the value of ‘Mentor’.

Conclusion

I don’t know what’s occurring in the internals. I just know the evidence I have in front of me. First, Query Store vs. Plan Guides? Query Store wins. However, internally, something is still going on. The Plan Guide is still successfully applied, at least according to what I can see. The processes that determine if a Plan Guide should be applied or not are not interfered with by the Query Store plan forcing. The fact that I’m seeing a Query Store plan and evidence of a successful Plan Guide though, that’s a problem that could cause issues when troubleshooting. Now you know, and knowing, as they say, is half the battle.


Let’s get together and talk about execution plans. In August, I’ll be doing an all day pre-con at SQLServer Geeks Annual Summit in Bangalore India.

I’m extremely exited to be in Oslo Norway for a pre-con before SQL Saturday Oslo in September.


/*NOTE: These scripts are not in the exact order in which
they must be run to re-create all my tests. These are
however, all the scripts you'll need.
*/
--the procedure
CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;
GO

--generating the first plan
EXEC dbo.AddressByCity
    @City = N'London';



--remove this from cache
DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.AddressByCity');

IF @PlanHandle IS NOT NULL
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO



--generate the second plan
EXEC dbo.AddressByCity
    @City = N'Mentor';




--get the query & plan id
SELECT  qsq.query_id,
        qsp.plan_id
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE   qsq.object_id = OBJECT_ID('dbo.AddressByCity');


--force the plan
EXEC sys.sp_query_store_force_plan 414,460;

--unforce the plan
EXEC sys.sp_query_store_unforce_plan 414,460;




--Get the plan_handle and offset 
SELECT  deqs.plan_handle,
        deqs.statement_start_offset
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.objectid = OBJECT_ID('dbo.AddressByCity');

--create a plan guide
EXEC sys.sp_create_plan_guide_from_handle
    @name = N'PlanGuidePrecedence',
    @plan_handle = 0x05000500C26A7610A03A4F53A600000001000000000000000000000000000000000000000000000000000000,
    @statement_start_offset = 108;

--remove plan guide
EXEC sys.sp_control_plan_guide
    @operation = N'DROP',
    @name = N'PlanGuidePrecedence';


--pull a plan out of cache. You'll need to make sure the right plan is there first
DECLARE @xmlplan NVARCHAR(MAX)

SELECT  @xmlplan = deqp.query_plan
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,DEFAULT,DEFAULT) AS deqp
WHERE   deqp.objectid = OBJECT_ID('dbo.AddressByCity');

SELECT @xmlplan


--copied and pasted the value from @xmlplan for easy reuse
DECLARE @xmlplan NVARCHAR(MAX) =
'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.1300.275"><BatchSequence><Batch><Statements><StmtSimple StatementText="CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City" StatementId="1" StatementCompId="3" StatementType="SELECT" StatementSqlHandle="0x0900D56A5A43E8B56B541DFF8DC13F149C6A0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="276196034" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.316799" StatementEstRows="434" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xDD75E124763781F2" QueryPlanHash="0x6C408B64C5E8A83F" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="360"><MissingIndexes><MissingIndexGroup Impact="89.7983"><MissingIndex Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]"><ColumnGroup Usage="EQUALITY"><Column Name="[City]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="720"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="260900" EstimatedPagesCached="65225" EstimatedAvailableDegreeOfParallelism="2"></OptimizerHardwareDependentProperties><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="434" EstimateIO="0" EstimateCPU="0.0069349" AvgRowSize="225" EstimatedTotalSubtreeCost="0.316799" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine1"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine2"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="City"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="PostalCode"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="Name"></ColumnReference></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="StateProvinceID"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[AdventureWorks2014].[Person].[StateProvince].[StateProvinceID] as [sp].[StateProvinceID]=[AdventureWorks2014].[Person].[Address].[StateProvinceID] as [a].[StateProvinceID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="StateProvinceID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="181" EstimateIO="0.00386574" EstimateCPU="0.0003561" AvgRowSize="65" EstimatedTotalSubtreeCost="0.00422184" TableCardinality="181" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="StateProvinceID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="StateProvinceID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Alias="[sp]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[AdventureWorks2014]" Schema="[Person]" Table="[StateProvince]" Index="[PK_StateProvince_StateProvinceID]" Alias="[sp]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="434" EstimateIO="0.0112613" EstimateCPU="0.0060321" AvgRowSize="177" EstimatedTotalSubtreeCost="0.30564" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine1"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine2"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="City"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="PostalCode"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="434" EstimateIO="0.257199" EstimateCPU="0.0217324" AvgRowSize="177" EstimatedTotalSubtreeCost="0.278931" TableCardinality="19614" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine1"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine2"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="City"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="PostalCode"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="AddressLine2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="City"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="StateProvinceID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="PostalCode"></ColumnReference></DefinedValue></DefinedValues><Object Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Index="[PK_Address_AddressID]" Alias="[a]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[AdventureWorks2014].[Person].[Address].[City] as [a].[City]=[@City]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2014]" Schema="[Person]" Table="[Address]" Alias="[a]" Column="City"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@City"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></Sort></RelOp></Merge></RelOp><ParameterList><ColumnReference Column="@City" ParameterCompiledValue="N'London'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'


EXEC sys.sp_create_plan_guide
    @name = N'PlanGuidePrecedence',
    @stmt = N'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;',
    @type = N'OBJECT',
    @module_or_batch = N'dbo.AddressByCity',
    @params = NULL,
    @hints = @xmlplan;


--create a plan guide with just a hint in it
EXEC sys.sp_create_plan_guide @name = 'PlanGuidePrecedence', -- sysname
    @stmt = N'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;', -- nvarchar(max)
    @type = N'Object', -- nvarchar(60)
    @module_or_batch = N'dbo.AddressByCity', -- nvarchar(max)
    @params = NULL, -- nvarchar(max)
    @hints = N'OPTION(OPTIMIZE FOR(@City = ''London''))'; -- nvarchar(max)
May 25 2016

A Moment For Reflection…

While this is a personal blog, I try to keep it focused on either technical topics or personal development and leadership related to technical topics. This post is a complete deviation from all of the above. Please, read it anyway.

I went to Auschwitz and Birkenau.

Words are failing me here.

Before I went, I was terribly conflicted about the trip. I had the opportunity to visit another Nazi concentration camp, Dachau, and I deferred. I know horror was committed there. Why should I voluntarily subject myself to it? As part of a trip to Poland, Aaron Bertrand suggested we should take a couple of extra days to see the sights, including visiting Auschwitz. I didn’t want to go. Then I started thinking.

Obligation. Honor. Remembrance. Humility. Respect.

I truly don’t consider myself to be all that good a person. However, I’m trying. I see how my betters behave and I try to appropriately ape that behavior. In this case Aaron was setting the path for me, so I followed.

What to say about the place?

The scale is staggering. You may have looked at pictures. You might have seen films. While a picture may paint a thousand words, you need millions of words to appropriately describe the utter enormity of the staggering horror that place represents. 1.3 million people in the door and 1.1 million died. That required massive, focused, efficiency, planning, thought and nearly limitless evil. It’s not the act of foaming at the mouth madmen. Standing in the middle of Birkenau, in the rail yard (a ******* rail yard for ******* humans, by all the gods… anyway…), you can feel and see all that, despite the fact that the place is mostly a ruin. Lots and lots of people visit Auschwitz. The name is synonymous with… all of it. However, fewer go over to Birkenau. Honestly, you have to go to Birkenau. Nothing can prepare you and nothing is equivalent.

Emotionally…

Horror, of course. Way too much rage. Sadness. And, frankly, and very surprisingly, a certain deadness inside. I actually got to the point where I read another plaque about X number of people being processed and there was nothing there. It had all been hollowed out. I think that was the single most surprising moment. My empathy pool and just been utterly drained. It only took 1.3 million people. Of course, with a little time between me and that place, I’m back to tearing up as I type this.

I’m not completely sure why I feel so compelled to share this, yet I must. I cannot recommend enough that you go and visit this place if you have the opportunity. If you don’t have the opportunity, you need to make it. I read  a lot of history, so the concept of “Never Again” is a sad joke. However, maybe, just maybe, if enough people stand in that rail yard, or in front of Incinerator #5 (which means there is 1-4…. rage again), maybe if enough people get the education that place provides, maybe “Never Again” will actually come to be more than just a vain hope.

 

May 24 2016

On the Buckeye Blitz!

In just a few weeks I’ll be doing the Buckeye Blitz. That is, a tour of user groups in Ohio (aka, the Buckeye state, after a tree, not a sports team), one per day across a week. Here’s how it breaks down:

Cleveland: 6/13
Toledo: 6/14
Columbus: 6/15
Cincinnati: 6/16

I’ll be talking on this topic at each of the groups:

Change Your Habits: Tips to Tune Your T-SQL
T-SQL proides many different ways to accomplish the same task, and as you might expect, some ways are better than others. In this session, you will learn specific techniques, that when followed make you a better T-SQL developer. The session is jam-packed with practical examples and is designed for administrators and developers who want to bring their T-SQL skills to the next level. You’ll write clearer and easier to read T-SQL as well as write better performing T-SQL. So useful, you can implement these tips immediately!

I hope you’ll come out and see me on the Buckeye Blitz. I’m really looking forward to it. Plus, I haven’t driven across Ohio since my first day on the job at Redgate. I’ll assume less snow in June.

May 23 2016

PASS Board 2016: Update #3

I didn’t post an update last month, but I did do something more important: I elicited your feedback on a question the Board needs to act on.

Here’s what I’ve been up to.

First, based on your feedback (thanks) and conversations that I’ve had with members of the Board, I’m putting together recommendations for how we deal with payment and PASS-branded (but not PASS run, that’s already covered in the By-Laws) events. I’ll be presenting that in June at the in-person Board meeting. Speaking of the Board meeting, I’ve also been soliciting topics (although really, my priceless partner at PASS HQ, Vicki, is doing all the real work). We’re also preparing the budget for FY 2017 (again, the real work is being led by an equally priceless Sandy at HQ). Beyond a series of meetings with each of the portfolio owners, that hasn’t been too much work for me… yet. It’s coming up and will involve quite a lot of time to have everything ready for the June meeting.

…And spreadsheets (BLECH!).

I attended the Business Analytics Conference. While I was there, instead of going to sessions (and there were a couple on R that I really wanted to attend), I sat in on Focus Group meetings with various sets of attendees in order to understand how PASS can best serve the BA community. These meetings are an excellent way to gather a ton of information from people; good feedback on how we’re doing. I’m advocating for doing them at the Summit this year too. While I was there I got to have my first in-person meeting with the rest of the Executive Committee. We talk on the phone, through Skype, and over email very regularly, but nothing beats face-to-face.

Something special I got to do, thanks again to other peoples work (in this case Annette Allen (b|t), one of the PASS Regional Mentors over in the UK) was sit down with the user group, chapter, and community leaders during SQLBits (I was over there to present a session). We talked about a whole bunch of things, but focused around how PASS, as an organization, can better serve, especially locally, like in the UK and Europe. It was a really constructive meeting and quite positive. I can share a few things from it. Based on those conversations, I’ve already set up a Slack channel that Ryan can experiment with to help improve communication between the chapters and PASS as well as the chapters with each other. I think you may also see some other changes in communication in and around chapters in the near future. We’re also going to look into ways to get more swag to Chapters (but not shipping from Canada, as international shipping is costly and efficient for PASS or organizers).

That’s about it. My involvement with the Board continues to be educational and rewarding. I won’t lie. It’s a time commitment. It is however, one I’m very happy to have the opportunity to make. If you’re considering running for the board, talk to me. I’ll tell you all the good things it brings.

May 16 2016

Query Store, Forced Plans, and New Plans

I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1).

Let’s say you have selected a plan that you want to force. You set it up. Now, let’s say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that’s going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store?

I have no idea. Let’s find out.

The Setup

To start with, a small stored procedure that I use all the time for bad parameter sniffing demos that reliably gets different plans with different values due to statistics skew:

CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS
SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;

If this procedure is called for a value of ‘London’ it gets a plan with a Merge Join. For most other value it gets a plan with a Loops Join. Here’s an example of the ‘London’ plan:

2016-02-22_10-38-43

I’ve run both queries on my test system. I can check their existence in Query Store this way:

SELECT  CAST(qsp.query_plan AS XML),
		qsq.query_id,
		qsp.plan_id,
		qsp.is_forced_plan
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

That’s going to return two (2) rows:

newplanresults

Now, I’m going to remove the second plan, the ‘Mentor’ plan, and I’m going to force the ‘London’ plan with two statements:

EXEC sys.sp_query_store_remove_plan @plan_id =219;

EXEC sys.sp_query_store_force_plan 2,2;

Now, when I execute the query, no matter what values I pass, I’m going to arrive at the ‘London’ plan. However, let’s pull the plan from cache in order to force a recompile:

DECLARE @PlanHandle varbinary(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.spAddressByCity');

DBCC FREEPROCCACHE(@PlanHandle);

The Reveal

When I execute the query using the value of ‘Mentor’ do I get that plan stored in the Query Store? I know which plan will be in the cache. The ‘London’ plan because of I have chosen to force that plan. But, when I query the Query Store again to see what’s there for my stored procedure:

newplannegativeresults

The short answer is, nope. Any possible new plans that could have been stored in Query Store are not there. Yes, we have gone into the compile process, but, due to plan forcing, that process is getting a small bypass and the plan is simply applied from the Query Store. This, even though, you’re going to see a compile or recompile event if you capture them using Extended Events. For more fun on recompiles and the Query Store, see this earlier post of mine.

Keep those questions coming.


I love talking about query tuning. If you want to spend the day with me talking query tuning, you sure can. In August, I’ll be doing an all day pre-con at SQLServer Geeks Annual Summit in Bangalore India.

I’ll be doing a pre-conference seminar in Oslo Norway before SQL Saturday Oslo in September.