Blog

Watch Brent Tune Servers [Video] #MSIgnite

Last week at Microsoft Ignite 2015 in Chicago, I demonstrated how to tune a few SQL Server workloads with my favorite process:

  • Measure how fast the SQL Server is going
  • Check its bottlenecks
  • Apply some easy-to-tweak configuration settings and measure the difference

There’s no camera in this hour-long video, just a screen capture, so you’ll have to imagine my jazz hands.

You can grab the demo scripts here, and grab our related performance tuning resources here.

Watch Brent Tune Queries [Video]

Ever wonder how somebody else does it? Watch over my shoulder for this beautifully recorded one-hour session at SQLRally Nordic in Copenhagen from a couple weeks ago:

The resources, scripts, and my Be Creepy process are all free too. Enjoy, and check out all of the session recordings for even more free learnin’.

The Basics of Storage Tiering [Video]

In our Senior DBA Class of 2015 session in Denver last week, one of my sessions explained storage tiering, snapshots, and replication using a table of bloggers. Here’s the first ten minutes of it:

To learn more, here’s additional resources:

How SQL Server Licensing Works [Video]

You have a sneaking suspicion that your servers aren’t all paid for, and you need to get a rough idea of how SQL Server licensing works. You’ve never bought a box of SQL Server before, and you have no idea where to get started.

Microsoft Certified Master Brent Ozar will break it down into a few simple, easy-to-understand slides and show you the most popular licensing options. He’ll also explain 3 classic licensing mistakes and help you avoid ‘em in this 20-minute video.

To ask licensing questions after watching the video, join our weekly webcast for live Q&A. Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!

Have questions? Feel free to leave a comment so we can discuss it on Tuesday!

How to Work Around Standard Edition’s Limits [Video]

64GB RAM, no partitioning, no AlwaysOn Availability Groups, no encryption – what’s a DBA to do? Brent Ozar gives you a few easy workarounds in this 30-minute video.

Be aware that audio on this one sucks pretty bad – it accidentally recorded from the wrong microphone instead of our good professional ones. Sorry about that!

Doug Broke It: Reporting Services Encryption Keys [Video]

In this 5-minute video, Doug tells about the time he deleted the Reporting Services encryption key, and what it took to fix the damage. You’ll learn about what the SSRS encryption key protects, as well when to:

  • Back up the encryption key
  • Restore the encryption key
  • Change the encryption key
  • Delete the encryption key

Making SQL Server Work in VMware and Hyper-V [Video]

Which SQL Servers should you virtualize first? How should you license them? How many virtual CPUs and how much memory should you start with? How do you know when that’s not enough, and how should you react?

I cover these questions in this 30-minute webcast video:

We’ve got more virtualization best practices too.

Doug Broke It: Minimal Logging [Video]

In this 5-minute video, Doug explains how he kept filling SQL Server’s transaction log during ETL development, and how minimal logging could have prevented a lot of the log writes. You’ll learn about:

  • What minimal logging is
  • How to turn minimal logging on
  • How to tell if minimal logging is working
  • Using minimal logging for schema changes
  • Words of caution about minimal logging

The Day the SQL Server Quit [Video]

In this 5-minute video, Doug tells a story about a DBA whose SQL Server got fed up with being misconfigured and decided to do something about it. Topics include CPU settings like MAXDOP and Cost Threshold for Parallelism, memory and disk configuration.

Refactoring T-SQL with Windowing Functions

You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to write.

Looking for the scripts? Grab them below the video!

Script 1: Create Windowing View

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 1: Create Windowing View
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
GO

/* Duplicates the data with a DueDate field set to [year] - 1. */

CREATE VIEW vWindowing
AS
    ( SELECT    CustomerID ,
                CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue ,
                DATEPART(mm, DueDate) AS DueMonth ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END AS DueQtr ,
                DATEPART(yy, DueDate) AS DueYear
      FROM      sales.SalesOrderHeader
      GROUP BY  CustomerID ,
                DATEPART(mm, DueDate) ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END ,
                DATEPART(yy, DueDate)
      UNION
      SELECT    CustomerID ,
                CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue ,
                DATEPART(mm, DueDate) AS DueMonth ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END AS DueQtr ,
                DATEPART(yy, DueDate) - 1 AS DueYear
      FROM      sales.SalesOrderHeader
      GROUP BY  CustomerID ,
                DATEPART(mm, DueDate) ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END ,
                DATEPART(yy, DueDate) - 1
    )

	GO

Script 2: The Old Way of Querying

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 2: The Old Way of Querying
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
go

SET STATISTICS IO ON
GO

/* --------------------------------------- */
-- The old way to blend YTD and detail.
-- Pretty simple so far with just one join.
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
FROM dbo.vWindowing AS vdet
JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
ORDER BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth

/* --------------------------------------- */
-- That's great, but what if you need other metrics?
-- How do we put QTD and YTD in the same T-SQL statement?
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
FROM dbo.vWindowing AS vdet
LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear
																		AND vsum.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
;

/* --------------------------------------- */
-- Looks good. What about same YTD last year?
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
, SUM(vsum2.TotalDue) AS LastYTDTotalDue
FROM dbo.vWindowing AS vdet
LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear
																		AND vsum.DueMonth <= vdet.DueMonth
LEFT JOIN dbo.vWindowing AS vsum2 ON vsum2.CustomerID = vdet.CustomerID AND vsum2.DueYear = vdet.DueYear - 1
																		AND vsum2.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
;

/* --------------------------------------- */
-- And now we have problems. What WILL give us
-- the Last YTD number we want?
/* --------------------------------------- */
;
WITH va AS
	(SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	WHERE vdet.CustomerID = 11091
	GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, vdet.QTDTotalDue
	, vdet.YTDTotalDue
	, vsum.QTDTotalDue AS SameQTDLastYearTotalDue
	, vsum.YTDTotalDue AS SameYTDLastYearTotalDue
FROM va AS vdet
LEFT JOIN va as vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueMonth = vdet.DueMonth AND vsum.DueYear = vdet.DueYear - 1

/* --------------------------------------- */
-- That's great but now we're four joins deep.
-- How much deeper dare we go? Roll 2d8 to find out.

-- Roll		Joins
-- -----	-----
-- 2-6		6
-- 7-10		8
-- 11-15	10
-- 16		Save vs. Traps or suffer 1d6 + 2 damage.

-- And how's the execution plan looking?
/* --------------------------------------- */

/* --------------------------------------- */
-- Let's add the TotalDue for everyone over that same period
-- ...if we dare.
/* --------------------------------------- */
;
WITH va AS
	(SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	WHERE vdet.CustomerID = 11091
	GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)
	, vAll as
	(SELECT vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	GROUP BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)

	SELECT * FROM vAll
	-- ...and so on...

/* --------------------------------------- */
-- I'll just stop here and say
-- it's a whole lotta work.
/* --------------------------------------- */

Script 3: The New Way of Querying

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 3: The New Way of Querying
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

/* --------------------------------------- */
-- Remember the old way got us roughly one
-- table join per metric?
--
-- Hold on to your seat.
/* --------------------------------------- */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* We'll start slow by just adding QTD */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr
						ORDER BY DueMonth
						ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* + YTD */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* What are we waiting for -- let's really see what this can do! */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID) AS AllTimeTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS RemainingFutureTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
	/SUM(TotalDue) OVER (PARTITION BY CustomerID) AS PctRemainingFutureTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth) AS ThisQtrTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth) AS ThisYearTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS Rolling12TotalDue
, TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr) AS PctOfThisQtrTotalDue
, TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear) AS PctOfThisYearTotalDue
, LAG(TotalDue, 1) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS PrevMonthTotalDue
, LAG(TotalDue, 3) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevQtrTotalDue
, LAG(TotalDue, 12) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevYearTotalDue
, AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDue

/* ------------------ */
-- If I want to see how this customer stacks up to others,
-- I can remove the where clause, add these columns, and filter at the client

--, AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDueAllCustomers
--,	AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
--		/ AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PctVarYTDAvgTotalDueVsAllCustomers
/* ------------------ */

FROM dbo.vWindowing AS va
WHERE CustomerID = 11091
ORDER BY CustomerID, DueYear, DueMonth
css.php