Blogs

Joins, Predicates, and Statistics in SQL Server

on December 8, 2015 • 4 min read

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.SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate from dbo.FactInternetSales fis join dbo.DimSalesTerritory st on fis.SalesTerritoryKey=st.SalesTerritoryKey where st.SalesTerritoryCountry = N’NA’ GO

Actual Number of Rows Read in SQL Server Execution Plans

By Kendra Little on December 3, 2015 • 3 min read

Category: query-performance

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

on December 1, 2015 • 3 min read

Category: database-administration

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?

on November 25, 2015 • 3 min read

Category: query-performance

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

on November 24, 2015 • 2 min read

Category: indexing

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$

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

on November 19, 2015 • 3 min read

Category: database-administration

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?

By Kendra Little on November 17, 2015 • 8 min read

Category: query-performance

Tags: partitioning , execution-plans , partition-elimination

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.