Blogs

Joins, Predicates, and Statistics in SQL Server

December 8, 2015

Joins can be tricky. And where you put your ‘where’ clause may mean more than you think! Take these two queries from the AdventureWorksDW sample database. The queries are both looking for data where SalesTerritoryCountry = ‘NA’ and they have the same joins, but the first query has a predicate on SalesTerritoryCountry while the second has a predicate on SalesTerritoryKey. /* Query 1: Predicate on SalesTerritoryCountry */ select ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, fis.

Actual Number of Rows Read in SQL Server Execution Plans

in sql

December 3, 2015

Execution plans got a cool new piece of diagnostic information in SQL Server 2012 SP3, SQL Server 2014 SP2, and SQL Server 2016: “Number of Rows Read”. In fancy language, this is “better diagnostics” when a query plan has “residual predicate pushdown” (KB 3107397).

My Six Month Plan, Post Brent Ozar Unlimited

in sql

December 1, 2015

It’s a big week for me! I’m moving on from Brent Ozar Unlimited.

Does OPTION (RECOMPILE) Prevent Query Store from Saving an Execution Plan?

in sql

November 25, 2015

Recompile hints have been tough to love in SQL Server for a long time. Sometimes it’s very tempting to use these hints to tell the optimizer to generate a fresh execution plan for a query, but there can be downsides.

My SQLPASS 2015 Session Evaluations

in sql

November 24, 2015

I recently gave a day long session at the SQLPASS Summit called “SQL Server Index Therapy Session”. 61 people attended, and let me tell you I was thrilled with that number. I’ve co-presented lots of day and week long sessions, but this was the first day long session that I’d ever done solo at a conference and 61 people was a terrific number.

Telemetry_xevents, NT SERVICE\SQLTELEMETRY, and XE_LIVE_TARGET_TVF in SQL Server 2016

in sql

November 19, 2015

Update, Feb 2017:

Here’s Microsoft’s overview of what the telemetry collects: https://www.microsoft.com/EN-US/privacystatement/SQLServer/Default.aspx

Did My Query Eliminate Table Partitions in SQL Server?

in sql, partitioning

November 17, 2015

Working with table partitioning can be puzzling. Table partitioning isn’t always a slam dunk for performance: heavy testing is needed. But even getting started with the testing can be a bit tricky!

Here’s a (relatively) simple example that walks you through setting up a partitioned table, running a query, and checking if it was able to get partition elimination.

Resources for Learning Python

in sql

November 10, 2015

I’ve been thinking about taking up a new programming language as a hobby for a little while. There’s a vibrant software community here in Portland, and working in more languages than TSQL opens up lots more opportunities for fun. I have no intention of becoming a full-time Python programmer, I just believe it’s fun to code and good to challenge your brain in new ways.