Sliding Window Table Partitioning: What to Decide Before You Automate

Before you do all the work to map out a complex sliding window table partitioning scheme for your SQL Server tables, here’s the top five questions I’d think through carefully:

1) Do You Need Table Partitioning?

Some folks think they need partitioning for performance– but it really shines as a data management feature. Just because you’ve got tables with millions of rows in them doesn’t necessarily mean that partitioning will make queries faster. Make sure you’ve worked through traditional indexing and query re-writes first. Partitioning is lots of work, so don’t skip this question.

Continue reading

Does Query Store Regressed Queries View Catch Nasty Parameter Sniffing?

SQL Server 2016’s new Query Store feature has an option that looks for “regressed” query plans.

But does it catch “bad” parameter sniffing?

Continue reading

How to Find Missing Index Requests in Query Store

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.

Continue reading

Useful Whitepapers for Planning SQL Server Migrations

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.

Continue reading

Join me at SQLBits for a Day of Index Design and Tuning

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.

Continue reading

Why Teaching Makes You a Better DBA and Performance Tuner

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.

Continue reading

Joins, Predicates, and Statistics in SQL Server

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

Continue reading