Blog

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

Don’t Fear the Execution Plan! [Video]

Have you ever been curious about how SQL Server returns the results of your queries to you? Have you ever opened an execution plan but been bewildered by the results? Have you dabbled in query tuning, but weren’t sure where to focus your efforts? Join Jes as she takes you through the basics of execution plans. She’ll show you how to read them, how to spot common problems, how to spot help, and tools that will make your job easier.

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.)

SQL Server 2014 brings clustered columnstore indexes, and they’re totally writeable – you can insert, update, or delete into them. They’re best suited for wide data warehouse fact tables that have lots of columns, but your queries might only want a few of those columns, and they might pick any of the columns for filtering. These types of tables are notoriously difficult to index, and columnstore indexes can give you dramatic performance improvements here.

Inventor of The Locke Technique

Inventor of The Locke Technique

Books Online says you’d better be sure your access patterns all benefit from columnstore indexes, because you can’t add any nonclustered indexes to your columnstore tables. The CREATE CLUSTERED COLUMNSTORE INDEX syntax page explains that the clustered columnstore “is the only allowable index on the table,” meaning you can’t add non-clustered indexes.

Or can you?

Allow me to demonstrate what I call the Locke Technique:

CREATE TABLE dbo.SimpleTable(
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

CREATE VIEW dbo.vw_SimpleTable WITH SCHEMABINDING
AS SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.SimpleTable;
GO

CREATE UNIQUE CLUSTERED INDEX CLIX_ProductKey ON dbo.vw_SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED INDEX IX_OrderDateKey ON dbo.vw_SimpleTable(OrderDateKey);
GO

Presto – you can have as many nonclustered indexes as you need – and technically, in a strange twist, the clustered index on the view is a nonclustered index on the table as well.

These have the same drawbacks as any other nonclustered index: slower inserts/updates/deletes on the underlying table, more complex query tuning, more space requirements, and so on. They also have even more drawbacks because the schema-binding view means you can’t alter the columnstore table without first dropping the nonclustered indexes and the view.

After You’ve Been a DBA, What Do You Do?

You’ve been managing database servers for a few years.

Now what?

Get more technical – every time a new project comes in, sharpen your skills. Get more involved in failover clustering, storage, virtualization, or the cloud. Write technical presentations to teach your own company’s staff, and then the public.

Business intelligence – if you’re constantly surrounded by valuable data, and you’ve got a creative eye, you can help make sense out of all those letters and numbers. There’s never been a better time to help people get actionable insight out of your tables. It’s a wildly different career path than DBA – it involves more time with people, and less time with maintenance plans.

Consult – get technical enough, and develop a reputation for solving tough problems quickly, and you can change gears entirely. Instead of working for a single company, you can move around from company to company, giving guidance on how to put out more fires faster.

Contract – in some markets, you can keep doing what an employee does, but as an outsider. Consultants tell companies what to do, and contractors do what they’re told. If you enjoy the job duties and don’t really care for the big picture, contracting can be more lucrative.

Management – if you have enough IT pros around you, and you’ve got a knack for people skills, you can really shift gears and manage people instead of servers. Caution, though: this is nothing to do with databases whatsoever. The skills that served you so well troubleshooting a cluster may not help you motivate an employee you don’t like to do a task they hate.

Architecture – your constant interactions with developers teach you more and more about building large systems. Because you’re more senior than other folks in the shop, you end up giving design guidance. Eventually you spend more time doing guidance than managing databases, or they hire a junior DBA in to backfill your production duties.

Stagnate – keep doing exactly the same thing. You’re going to get all of the servers into pretty good shape – not perfect, but good enough that your phone isn’t ringing every night. You’ll have more time to spend playing 2048 at work, but you’re not going to make dramatically more money if you’re not doing dramatically better work.

So what’s your plan?

Finding One Problem Query With Extended Events

It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen?

Server Side Trace

You could set up a SQL Server Server Side Trace. This is an acceptable solution if you’re using SQL Server 2008 or earlier. There are a few problems with this approach.

Problem the first – the SQL Server Profiler UI is less than beautiful. The GUI interface is somewhat cumbersome and the scripting interface is hilariously bad. To make matters worse – to only capture a single query requires string based filtering. There’s a plan handle property available in profiler, but it’s not available for TSQL or stored procedures.

Something appears to be missing

Something appears to be missing

As much as I like string matching in T-SQL (hint: I don’t), I think it’s fair to say that Profiler/Server Side Trace are best avoided unless you’re using SQL Server 2008 or earlier.

Extended Events

Extended events are nowhere near as hard as you’d think. Trust me on this. How do I know? I set up an Extended Events session to grab a particular query.

Here’s what you do:

  1. Create a crappy stored procedure. Use your imagination.
  2. Run the stored procedure a lot.
  3. Grab the query_hash for that stored procedure. The easiest way
    I’ve found to do this is to use our plan cache query to identify it. My query_hash is 0x2B42702322C10821. What’s yours?
  4. Create an extended events session to capture that specific query hash.

Step 4 isn’t as easy as it sounds. Astute readers will note that I never said this was easy, just that it’s not as hard as you would think.

Even though the query_hash is stored as a hexadecimal in SQL Server, Extended Events would prefer to see an unsigned 64-bit integer. To get this magic number, take your query_hash and cast it as either a BIGINT or a DECIMAL(20,0):

SELECT CAST(0x2B42702322C10821 AS DECIMAL(20,0));
/* my magic number is 3117177188303046689. */

Once you convert the query_hash to a magic_number, create your Extended Events session. I chose to capture the sp_statement_completed and sql_statement_completed events and a number of server level events like time, app name, and client host name. Here’s what it would look like capturing this to a file:

CREATE EVENT SESSION [query hash] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(package0.collect_system_time,
           sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(3117177188303046689.))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,
           sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(3117177188303046689.)))
ADD TARGET package0.asynchronous_file_target
(SET filename = 'C:\temp\XEventSessions\query_hash.xel',
     metadatafile = 'C:\temp\XEventSessions\query_hash.xem',
     max_file_size=5,
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS);
GO

Once you have that up and running, you should be able to start the session, if it isn’t already started, by running:

ALTER EVENT SESSION [query hash] ON SERVER
STATE = START ;

Just like that, you’ll be capturing your terrible queries to disk where you can mine the extended events files for gold and glory at your own convenience. If you want to query it, it’d look something like this:

WITH events_cte AS (
    SELECT 
        DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
            xevents.event_data.value('(event/@timestamp)[1]',
            'datetime2')) AS [event time] ,
        xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
          AS [client app name],
        xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
          AS [client host name],
        xevents.event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)')
          AS [database name],
        xevents.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') 
          AS [duration (ms)],
        xevents.event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') 
          AS [cpu time (ms)],
        xevents.event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS [logical reads],
        xevents.event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint')  AS [row count]
    FROM sys.fn_xe_file_target_read_file
         ('C:\temp\XEventSessions\query_hash*.xel',
          'C:\temp\XEventSessions\query_hash*.xem',
          null, null) 
    CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
)
SELECT *
FROM events_cte
ORDER BY [event time] DESC;

Cleaning Up After Yourself

Once you’re done watching a specific query or queries, make sure you clean up after yourself. There’s no reason to add extra load to SQL Server when you aren’t watching. Make sure to stop and remove your Extended Events session:

/* Stop the Extended Events session */
ALTER EVENT SESSION [query hash] ON SERVER
STATE = STOP;
/* Remove the session from the server.
   This step is optional - I clear them out on my dev SQL Server
   because I'm constantly doing stupid things to my dev SQL Server. */
DROP EVENT SESSION [query hash] ON SERVER;

Augment Your Techniques

This won’t replace any other techniques that you’re using to find poorly performing queries. However, you can use this technique to find problem queries as they’re happening.

Different monitoring techniques provide you with a different view of the data. Our plan cache query shows you a historical record of what’s been going on, but it can only show so much. If a query has been run 14,400 times over the last 24 hours, does that mean it’s been run 10 times a minute for that entire period or is there a period of activity where that query gets run 1,440 times a minute for 10 minutes straight?

This isn’t going to replace the need for other tools you have at your disposal. It’s just another way to get the job done.

Rollback: What happens when you KILL a session?

It’s Friday afternoon. You, the DBA, are looking forward to a long weekend. You’re going to walk out the door in 30 minutes and enjoy some family time. The phone rings and you pick it up. On the other end, Joe Developer says, “There’s a query that’s been running for a long time. Can you kill it?”

You log into SSMS and use sp_who2 to confirm that the user has an open transaction. You issue the (infamous) KILL command.

Twenty-five minutes later, you’re still sitting there, waiting for the transaction to roll back. You’re sweating. You really want to leave for the weekend. Another ten minutes pass, and finally, it completes.

What happened? Why did SQL Server take so long to kill the process? What happens in a rollback situation?

What Happens in a Transaction

When a transaction is being committed, each step is written into the transaction log. Each value that is being inserted, updated, or deleted is recorded so that if there is a failure before the log is written to the data file, the appropriate rollback or roll forward can take place. The transaction may take and release locks on tables or indexes.

Want to see this in action? You can use the undocumented fn_dblog command to view the entries in the log.

What Happens in a Rollback

When we issue a KILL, every step of the transaction that has been written to the transaction log must be undone. The object the transaction was changing must be put back to the state it was in prior to the change. Was a record inserted? It needs to be deleted. Was a record updated? The previous value must be restored. Was a record deleted? It needs to be added back in. You can use fn_dblog to see this in action, too.

If SQL Server is simply undoing what was once done, why does it sometimes take longer?

If the transaction had to take a lock on a row or table to perform the operation, it will have to take that lock again – and other processes might be using that object now. Also, consider that a rollback is mostly single-threaded. If the transaction ran using four cores initially, and the rollback is now only using one, it will take more time.

Try going down backwards, hopping on one foot. I dare you!

Try going down backwards, hopping on one foot. I dare you!

Imagine this: you’ve decided to climb a tower with 10,000 stairs. You get to stair 9,999 and decide you don’t want to complete the climb. You can’t wish yourself to the bottom step – you have to go back down. But now, you’re tired – and have to do this single-threaded. Imagine having to hop down the stairs backwards, on one foot.

Tracking Progress

You are able to track the progress of a rollback. After you issue a KILL command, run KILL [Session ID] WITH STATUSONLY. This will show you approximately how much time is remaining in the rollback – but other processes in the database could affect that.. Consider the information here a guideline, not a hard-and-fast time.

Another thing to keep in mind: what you might not have known when you issued that KILL on Friday at 4:30 pm is that the transaction was going to finish at 4:32 pm. But, you had to wait until 5:05 for the rollback to complete. Always be cautious when using this command!

Solving the root cause

A one-time KILL issued to solve an “oops” may not need further investigation. But if you find yourself having to issue these commands for the same application, at the same time each week, it’s time to dig deeper. You want to work with the owners of the application or process to determine why this keeps happening, repeatedly. If it’s a long-standing process or transaction, what’s changed to make it take so long now? Perhaps you’re doing a mass insert or update, and could break it into chunks. If it’s a new process, look at what else is going on at that time. Could the statements be written more efficiently? Could it be scheduled at another day or time?

How to Get Your First Job as a DBA (video)

You’d love to become a Database Administrator, but how do you get your foot in the door? Kendra Little has hired junior DBAs and helps employers develop interview procedures for database administrators. In this 30 minute video you’ll learn what employers look for, steps you can take to make yourself a great candidate, and how to build the right resume to get your first DBA job.

Looking for the links from the webcast? Scroll down below the video!

Links:

When Do You Fire the DBA?

I know a lot of DBAs, but it’s really, really rare that I’ve seen one get fired. I don’t think it’s ever happened during one of my consulting engagements, and I’ve seen some incredibly horrific database disasters (not to mention a whole lotta ugly near-misses).

So I asked Twitter:

Not Telling Management About Problems

That’s fair – I can understand if someone’s databases have problems that they don’t know about, because we can’t all know about every risk. But if you do know about a risk and you don’t inform management, that’s bad. Which leads to…

Doing Dirty Deeds

What if someone came to you and offered you fifty thousand bucks for a copy of your company’s database backups? Or more realistically, just asked you to up their login permissions to see more databases as a favor?

The devil doesn’t come wearing red horns and a cape. He comes as a friend who needs a favor and will pay you handsomely for it.

Being Untrainable

Not Having Backups

This one’s interesting because it happens a lot. No, seriously, it’s the first thing I check when I start a consulting engagement, and maybe 1/4 to 1/2 of the time, the backups aren’t working the way the DBA assumed.

After all, think about your own databases – you’re just assuming the backups worked okay last night because you didn’t get an email. If someone walked into your cube right now and checked every database for a clean (non-corrupt) backup that made it to tape or offsite, are you ready to bet your job on it? Especially if other teams are involved in sweeping the backups off to tape?

I’m much more tolerant of this mistake now because I see it so often. DBAs get distracted by performance issues because that’s what users complain about. Nobody complains about missing backups until it’s too late.

Making a Fatal Mistake

I love those last two words from Allan – stated consequences. If the company makes it clear ahead of time that certain mistakes are unforgivable, then yep, that can be a recipe for firing. If you’re in that kind of shop, you’d be wise to print out that list of unforgivable mistakes in a really large font and stick it to your wall near your monitor as a constant reminder.

Not Being Customer-Friendly

Over on DBAreactions, I make a lot of bad jokes about developers, SAN admins, network admins, sysadmins, and your momma.

The reality, though, is that I love these people because they’re struggling just like us DBAs are. They’re working to get better, and I have to help them get better as part of my own struggles. As much as we’d like to think we DBAs know everything about everybody else’s jobs, often our attitudes create a problem that’s a bigger liability than our perceived knowledge.

Not Improving Despite Guidance

Buck sums up the real thing DBAs need to be aware of.

Ideally, you build your own learning plan to up your game and keep your data safeguarded. You sharpen your own knives, and you train for the day that you have to respond to the unthinkable outage.

Less-than-ideally, your managers notice that your knives are those plastic ones you get in coach class on airplanes, and they write you a plan as part of your annual review. You make progress on it with that jump start, and you keep treading water in your career.

Or not-ideally-at-all, you put your company’s data at risk, they take you to Human Resources, and you sign off on a very urgent plan to get your learn on.

So, go get your learn on before the company notices.

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad.

Collecting Blocked Process Reports and Deadlocks Using Extended Events

When you want to find blocking, you probably turn to the blocked process report. You mess around with profiler on your SQL Server 2012 box. You probably feel a little bit dirty for clunking around in that old interface, but it gets the job done.

There’s a better way… Well, there is at least a less awful way: Extended Events.

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

/* Make sure this path exists before you start the trace! */

With that, you’ve created an Extended Events session to grab blocked processes and deadlocks. Why both? The blocked process report makes use of the deadlock detector. Since large amounts of blocking are frequently synonymous with deadlocking, it makes sense to grab both at the same time. There are a few other things we’ll need to do to make sure you can collect blocked processes:

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

At this point, you’ll be collecting the blocked process report with Extended Events. There’s no profiler session to set up, just start and stop the Extended Event session at your leisure.

Reading the Block Process Report from Extended Events

We’re saving the blocked process report to disk using Extended Events. Now what?

We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.

WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
    ('C:\temp\XEventSessions\blocked_process*.xel',
     'C:\temp\XEventSessions\blocked_process*.xem',
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

In this query, you read from an Extended Events session that’s being saved to disk and perform XML shredding to get client information. It isn’t a pretty query, but it does the job very well.

Viewing the Extended Events Deadlock Graphs

Extended Events deadlock graphs use a slightly different XML schema than what SSMS expects. You should see an error along the lines of “There is an error in XML document”. For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer.

Viewing the Extended Events Blocked Process Report

But what about the blocked process report? After all, your users are complaining about blocking, right?

Michael J. Swart has created tools to view the blocked process report. It’d be awesome if you could use it, but Michael’s blocked process report viewer uses the output of a server side trace to read blocking information. These Extended Events files are different enough that you can’t use them outright. You can, however, create a table that will let you use the blocked process report viewer:

CREATE TABLE bpr (
    EndTime DATETIME,
    TextData XML,
    EventClass INT DEFAULT(137)
);
GO

WITH events_cte AS (
    SELECT
        DATEADD(mi,
        DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]',
           'datetime2')) AS [event_time] ,
        xevents.event_data.query('(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
            AS blocked_process_report
    FROM    sys.fn_xe_file_target_read_file
        ('C:\temp\XEventSessions\blocked_process*.xel',
         'C:\temp\XEventSessions\blocked_process*.xem',
         null, null)
        CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
INSERT INTO bpr (EndTime, TextData)
SELECT
    [event_time],
    blocked_process_report
FROM events_cte
WHERE blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NOT NULL
ORDER BY [event_time] DESC ;

EXEC sp_blocked_process_report_viewer @Trace='bpr', @Type='TABLE';

While you still have to read the XML yourself, this will give you a view into how deep the blocking hierarchies can go. Collecting this data with Extended Events mean that you won’t have to sit at your desk, running queries, and waiting for blocking occur.

Extended Events – Not That Hard

Extended Events aren’t difficult to use. They provide a wealth of information about SQL Server and make it easier to collect information from complex or difficult to diagnose scenarios. You really can collect as much or as little information as you want from SQL Server. When you get started, the vast majority of your work will be spent either looking up Extended Events to use or formatting the output of the queries into something meaningful.

T-SQL Tuesday: Stop Tuning with Wait Stats Percentages

TSQL2sDay150x150For this month’s T-SQL Tuesday topic, Michael J. Swart told us to argue against a popular opinion, and man, is that right up my alley. I’ve told you to stop worrying about index fragmentation, stop backing up your logs once an hour, and to stop running $14k of licensing on $3k of hardware.

You’re probably checking SQL Server wait stats periodically to find your SQL Server’s bottleneck – and that’s a good thing. Instead of checking Perfmon counters and trying to figure out what’s the slowest part of your server, at least wait statistics tell you what SQL Server has been waiting on while running queries.

But it all falls apart when you say, “67% of the time, SQL Server is waiting on ____, so we should focus there.”

We need to understand the difference between latency and throughput.

Explaining Latency and Throughput with Shipping Companies

If we run an online store and we wanted to measure how fast a delivery service works, we could call them to pick up a single envelope, and then measure the amount of time it took to arrive at its destination. We could then say, “That service can deliver exactly one package per day. If our business is going to grow, we’re gonna have to find a delivery service that can ship faster, because we need to move more than one package per day.” We could then focus all our efforts trying to use local courier services, or spreading our load across multiple shipping companies.

Ernie arrives at her destination.

Ernie arrives at her destination.

But we would be morons.

Instead, we need to put MORE packages out on our doorstep and call the delivery service to get it. They’ll send a truck, pick up all the packages, and deliver them to various destinations. As we try to ship more and more packages, we’ll probably need to upgrade to a loading dock, or maybe even multiple loading docks, and set up an arrangement with our shipping company to send more trucks simultaneously.

Latency is the length of time it takes to deliver a single package.

Throughput is the number of packages they can deliver per day.

Just because our business is waiting overnight for a single package to be delivered doesn’t mean we have to go finding a new shipping company. It’s completely normal. We need to keep pushing our business to figure out where the breaking point is. Are packages piling up at the door because the shipping company only has a single small cargo van? Sure, that’s the point at which we worry.

How This Relates to SQL Server Wait Stats

In a recent load testing engagement, the DBA told me, “We’ve only got a few end users hitting the system, and we’re already seeing 80-90% of our waits in PAGEIOLATCH. The data file storage simply isn’t able to keep up.”

We switched from using his wait stats script to sp_AskBrent® instead, which shows the cumulative amount of time spent waiting on each wait type. In any given 5-second span, the server was spending less than a second waiting on PAGEIOLATCH. Furthermore, the average wait time was less than 5 milliseconds each time – indicating that the storage was responding fairly quickly to each request.

The server was sitting idle, and the DBA was reading wait stats incorrectly. Sure, the majority of time spent waiting was due to storage, but there just wasn’t much time spent waiting period.

“Crank up the load,” I said. “Quadruple the amount of work you’re throwing at the server.”

Everybody in the room looked at me like I was crazy, but they agreed – and the SQL Server still didn’t flinch. We kept upping and upping the load, and finally we did find a breaking point, but it wasn’t storage. Just as you can pile up a lot of boxes in front of your house and the shipping company will pick them all up to deliver them in the same amount of time, the SQL Server’s storage kept right on delivering every result within 5-6 milliseconds.

The Moral of the Story

When using wait stats for monitoring, make sure you’re looking at the total number of seconds spent waiting per second on the clock. If you sample waits for 5 seconds on a 16-core server, don’t freak out about 5 seconds worth of wait. Each core can have multiple queries piled up, all waiting on different resources, so even 15-20 seconds of wait time during a 5-second period may not indicate problems.

css.php