SQL Server 2016’s new Query Store feature makes it easier than ever for DBAs and developers to identify the most important queries to tune– and perhaps apply a quick fix by pinning an execution plan.
Planning to move to new hardware for your SQL Server? Techniques like log shipping and database mirroring can be incredibly useful to make the change fast and painless– but you’ve got to pick the right techniques for your environment ahead of time, and know how to do a few things that aren’t in the GUI.
Here are some of my favorite whitepapers and resources to get you going.
Wednesday, May 4, 2016 Liverpool Exhibition Centre One day session* *£349 until April 9, 2016. £399 thereafter – This training day has sold out!
I will be presenting this session in a Training Day at the SQLBits conference in Liverpool, UK. Reserve your seat for this one day session here.
I’ve known for a long time now that teaching makes me a better database administrator and performance tuner. But I’ve had a hard time figuring out why.
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
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).
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.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.