Profiler Is Dead, Long Live Extended Events

Jan 7, 2019

OK, so profiler isn’t actually dead, it has however been deprecated since 2012 and has had no new features since then. Extended Events on the other hand, is getting new events and fields added all the time. And Yet… And yet, for the most part, people are still using Profiler and ignoring Extended Events. In this post, I’m going to go over a couple of common Profiler use cases and show how it’s actually easier to use in Extended Events once you’ve got your head around it.

I’m not going to go into too much detail on all the different aspects of extended events here, I’m just going to concentrate on a couple of common profiles I used to use and what they look like in Extended Events. If you want more of a deep dive on Extended Events then Jonathan Kehayias has some great posts on it here https://www.sqlskills.com/blogs/jonathan/category/extended-events/.

Let’s take 4 common profiles that I used to use all the time before switching to extended events

  1. Show RPC Completed/Statement Completed events from user X
  2. Show RPC Completed/Statement Completed events where the duration is over x seconds
  3. Show memory grants over xGB
  4. Show deadlock graphs

Run the following TSQL snippets to create but not start Extended Event sessions for the above 4 scenarios.

RCP/Statement Completed For User X

CREATE EVENT SESSION [RpcAndBatchCompletedForUserX] ON SERVER 
ADD EVENT sqlserver.rpc_completed
(
  ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[server_principal_name],N'domain\user') AND 
      [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master') AND 
      [sqlserver].[is_system]<>(1)
   )
),
ADD EVENT sqlserver.sp_statement_completed
(
   ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[server_principal_name],N'domain\user') AND 
      [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master') AND 
      [sqlserver].[is_system]<>(1)
   )
),
ADD EVENT sqlserver.sql_batch_completed
(
   ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[server_principal_name],N'domain\user') AND 
      [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master') AND 
      [sqlserver].[is_system]<>(1)
   )
)
WITH (MAX_MEMORY=4096 KB)
GO

RPC/Statement Completed Over X Seconds

CREATE EVENT SESSION [RpcAndBatchCompletedOverXSeconds] ON SERVER 
ADD EVENT sqlserver.rpc_completed
(
   ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [package0].[not_equal_boolean]([sqlserver].[is_system],(1)) AND 
      [duration]>(10000000)
   )
),
ADD EVENT sqlserver.sp_statement_completed
(
   ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [package0].[not_equal_boolean]([sqlserver].[is_system],(1)) AND 
      [duration]>(10000000)
   )
),
ADD EVENT sqlserver.sql_batch_completed
(
   ACTION
   (
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.nt_username,
      sqlserver.server_principal_name,
      sqlserver.session_id
   )
   WHERE 
   (
      [package0].[not_equal_boolean]([sqlserver].[is_system],(1)) AND 
      [duration]>(10000000)
   )
)
WITH (MAX_MEMORY=4096 KB)
GO

Memory Grants Over 1GB

CREATE EVENT SESSION MemoryUsageAbove1GB ON SERVER 
ADD EVENT sqlserver.query_memory_grant_usage
(
   ACTION(sqlserver.sql_text)
   WHERE ([granted_memory_kb] > 1000000) --Above 1GB
)
WITH (MAX_MEMORY=4096 KB)

Deadlocks With Graphs

CREATE EVENT SESSION DeadLocks ON SERVER 
ADD EVENT sqlserver.lock_deadlock
(
   ACTION(sqlserver.sql_text)
),
ADD EVENT sqlserver.lock_deadlock_chain
(
   ACTION(sqlserver.sql_text)
),
ADD EVENT sqlserver.xml_deadlock_report
WITH (MAX_MEMORY=4096 KB)

All of the above can be done through the extended event wizard in SSMS but I like to save these scripts and edit them when I need to create new Extended Event sessions to save time. If you’re not sure what is available a good place to start is the wizard and from there you can click the script button to script the whole thing and tidy it up. Some of the above may look a bit lengthy at first glance but if you break it down it’s really just a list of…

  1. Events that you want to capture
  2. Actions that you want to capture on those events (Basically fields of data available on those events)
  3. Filters on the events e.g Duration > X
  4. Targets (Not used in the above), these define where to store the data you collect.
  5. Settings for things like max memory, retention, max size, startup state etc…

Once you’ve run the above scripts you’ll see the 4 new Extended Event Sessions in SSMS…

Extended Event List In SSMS

Notice they all have a stopped icon on them as none of them are currently running, to start one right click and select Start Session. You can then right click and “Watch Live Data” to see the events in real time.

Extended Event Live Data

Once you’re done, then right click the extended event session again and click stop. Whilst for the most part Extended Events are lighter weight than the old Profile Traces that doesn’t mean you can’t still cause horrible side effects by capturing lots of heavy weight events and actions e.g all query plans on a busy server.

The sessions I’ve created above are all currently scripted not to store the session data anywhere so at the minute all we have is the Live Data View. You can configure storage targets to persist this data but I’m not going to go into that here. If you want more information on that I recommend the Microsoft Docs here https://docs.microsoft.com/en-us/sql/database-engine/sql-server-extended-events-targets?view=sql-server-2014

As mentioned above Extended Events also has a nice Wizard built into SSMS for setting up new sessions, To access this right click the Sessions folder under the Extended Events node in SSMS and click “New Session Wizard”. This wizard allows you to browse and search through all the available Events and Actions which can be a great way to find what you can capture. At the end of the wizard you can either click to create the session or even better click the script button to get the TSQL for it so you can save it away for future.

Why This Is Better Than Profiler

  • It’s not deprecated - Profiler could go away with any next major version of SQL Server.
  • One tool SSMS - No more having to drop in and out of SSMS to Profiler
  • Easy to create and filter with TSQL - I find with Extended Events I’m much more likely to script my sessions or just leave them stopped on the server ready to run again. It always felt a bit clunky to say templates in profiler so I rarely did it.
  • WAY WAY more events! - Profiler hasn’t had any new events added to it since SQL Server 2008, Extended Events on the other hand now has massively more events than Profiler that you can watch for with more getting added with most releases.
  • Yuo can leave you’re stopped sessions saved ready to start from SSMS anytime with no further setup required. Profiler always seemed like a hssle to open, connect, find the template/create one and start it. With Extended Events it’s all there in the tool you probably already have open and are using.

SQL Server Removing Duplicates

Jan 3, 2019

I know, I know your data could never possibly have duplicates because obviously, you have all the constraints in place to stop this bad data before it gets into the system… But we all make mistakes right? Let’s imagine someone did let the little critters in, how can we then write a query that will remove the duplicates?

We actually have a few options, some good, some not so good. Before we run through them if you want to follow along then run the following SQL to create two tables and populate them with some duplicated data…

CREATE TABLE DupesWithNoUniqueKey
(
    Superhero NVARCHAR(20)
)
GO
CREATE TABLE DupesWithUniqueKey
(
    Id INT IDENTITY PRIMARY KEY,
    Superhero NVARCHAR(20)
)
GO

INSERT INTO DupesWithNoUniqueKey (SuperHero)
SELECT 'Batman'
UNION ALL SELECT 'Batman'
UNION ALL SELECT 'Batman'
UNION ALL SELECT 'Black Widow'
UNION ALL SELECT 'Black Widow'
UNION ALL SELECT 'Luke Cage'
UNION ALL SELECT 'Jessica Jones'

INSERT INTO DupesWithUniqueKey (SuperHero)
SELECT 'Batman'
UNION ALL SELECT 'Batman'
UNION ALL SELECT 'Batman'
UNION ALL SELECT 'Black Widow'
UNION ALL SELECT 'Black Widow'
UNION ALL SELECT 'Luke Cage'
UNION ALL SELECT 'Jessica Jones'

De-Duping Without Window Functions

Let’s imagine for whatever reason you’re still rocking SQL Server 2000 and don’t have access to the hotness that is window functions. What are our options for de-duping data?

We currently have 2 tables, one with a unique key and one without. Let’s start with the one with no unique key…

In the DupesWithNoUniqeKey table with have absolutely nothing unique to use in our filters to say delete this record but leave at least one copy, we also don’t have access to window functions so we can’t stick a row number on each record and delete all the ones with a row number greater than one. This really leaves us with only one option and it’s a pretty painful option, especially if it’s a large table…

BEGIN TRAN
SELECT DISTINCT SuperHero INTO #NoDupes FROM DupesWithNoUniqueKey
TRUNCATE TABLE DupesWithNoUniqueKey
INSERT INTO DupesWithNoUniqueKey
SELECT SuperHero FROM #NoDupes
COMMIT

We’re having to do a SELECT DISTINCT into a new table to remove the duplicates, then we’re having to clear out our original table and re-insert all the data.

Let’s now look at our other table that does have a unique key which allows us to differentiate between the duplicate records…

We can run the following query to see all the duplicate records and filter out the first occurrence of them…

WITH cte_dupes AS
(
SELECT 
    d.*
FROM 
    DupesWithUniqueKey d
    INNER JOIN (
        SELECT MIN(id) minId, SuperHero 
        FROM DupesWithUniqueKey 
        GROUP BY SuperHero
    ) m ON m.SuperHero = d.SuperHero
WHERE   
    id <> m.minId
)
SELECT * FROM cte_dupes

This is basically getting all duplicates except the one with the lowest ID, this will allow us to keep the first occurrence of every duplicate.

Once we’ve run that and looked at the output to confirm it’s returning the records we want to delete we can use the same CTE to do our delete…

WITH cte_dupes AS
(
SELECT 
    d.*
FROM 
    DupesWithUniqueKey d
    INNER JOIN (
        SELECT MIN(id) minId, SuperHero 
        FROM DupesWithUniqueKey 
        GROUP BY SuperHero
    ) m ON m.SuperHero = d.SuperHero
WHERE   
    id <> m.minId
)
DELETE s
FROM cte_dupes 
    INNER JOIN DupesWithUniqueKey s 
        ON s.id = cte_dupes.id

This time we’re only touching the deleted rows and not having to move the whole table which in most cases will be far less overhead and cause less blocking.

De-Duping With Windows Functions

Things get a lot better with the introduction of Window Functions in SQL Server 2005, we no longer need to worry about having a unique key or some way to differentiate duplicates from each other, We can now use PARTITION BY to define what needs to be unique in order for it to be classed as a duplicate and ROW_NUMBER() to give each duplicate an ascending ID. At which point we can then delete anything with an ID > 1…

WITH cte_dupes AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY SuperHero ORDER BY(SELECT NULL)) AS RowNumber
    FROM DupesWithNoUniqueKey
)
DELETE FROM cte_dupes WHERE RowNumber<>1

Notice we’re ordering by SELECT NULL this is because we have no real ID and so don’t care which version of a duplicate gets deleted.

Let’s imagine on our other table that does have a duplicate ID we only want records after the first one to be deleted, we can do that by ordering by ID in our partition…

WITH cte_dupes AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY SuperHero ORDER BY id) AS RowNumber
    FROM DupesWithUniqueKey
)
DELETE FROM cte_dupes WHERE RowNumber<>1

It’s worth noting that everything above can be achieved without the use of CTE’s, I use them so I can first run a select against the CTE to check what I’m going to be deleting, for example…

WITH cte_dupes AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SuperHero ORDER BY id) AS RowNumber
    FROM DupesWithUniqueKey
)
/*DELETE FROM cte_dupes  WHERE RowNumber<>1*/
SELECT * FROM cte_dupes WHERE RowNumber<>1

In the above, I’ve changed the CTE to “SELECT *” so we can see all the data and also commented out the delete statement to replace it with a select. With this method, you can always run the select first and when you’re happy just comment it out and uncomment the delete line.

SQL Server Adventures In Reducing IO

Dec 10, 2018

In the interests of curiosity I’m going to take a query that runs a relatively simple aggregation over a large table and see how much I can reduce the IO. I’m not suggesting anything here should be blindly followed, as with all things there are trade-offs. but the results are I think interesting none the less. Disks are getting faster and cheaper all the time, however no amount of progress in this area will ever give you free IO, the cheapest IO will always be the IO you don’t make. If we can tune our query to do less it will often give a far better bang for buck than any advancements in hardware.

If you want to follow along the Stack Overflow database I’m using can be downloaded from here.

Let’s take this fairly simple query on the Stack Overflow database to get the top 5 users by posts and return their name along with the amount of posts they have…

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

On my machine the statistics output I get is this…

Table ‘Posts’. Scan count 5, logical reads 800230, physical reads 0, read-ahead reads 797892, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Users’. Scan count 0, logical reads 121, physical reads 39, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 7294 ms, elapsed time = 7178 ms.

So roughly 7 seconds to execute the query with our biggest hit on reads being 799999 on the Posts table and 121 on Users. I should probably add that at this point both our posts and users table have a single index and that’s a clustered index on their ID columns. Let’s start on the small but easy low hanging fruit….

Lightweight Covering Index

The user table is currently scanning the clustered index to get the DisplayUsername, because the clustered index contains all the fields it’s having to read data we don’t need, we can create a lightweight covering index with just Id and an include on DisplayName to stop the query reading any pages with data in them that it doesn’t need.

CREATE NONCLUSTERED INDEX ndx_user_id_include_displayname 
	ON Users(Id) INCLUDE(DisplayName)

Now let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Users’. Scan count 0, logical reads 69, physical reads 21, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

It’s a small gain but we’ve halved the reads required for our join to the users table.

Now to tackle that posts table, this is a bit more difficult as we have to touch every record in order to group and count. An obvious place to start is to create a nonclustered index with a sort order more suited to our aggregation and without any of the fields we don’t need…

CREATE NONCLUSTERED INDEX ndx_posts_owner_userId
	ON Posts(OwnerUserId)

Then let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 5, logical reads 6539, physical reads 0, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1157 ms, elapsed time = 738 ms.

For me, the query now runs in less than a second and our reads on the posts table have gone from 799999 down to 6539. We could happily stop here but in the interest of this post, I wanted to see how much further I could take it.

We’re now at the point where our query is reading only information it absolutely needs in order to complete, so how can we reduce reads further? Compression!

Compressed Indexes

We have a couple of options here, we can compress our indexes with Row or Page level compression or we can change tracks a little and use a Columnstore index. Let’s compare these options…

First up lets set our posts index to use page compression…

ALTER INDEX ndx_posts_owner_userId 
	ON Posts REBUILD WITH (DATA_COMPRESSION = PAGE)

Then let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 5, logical reads 4137, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1457 ms, elapsed time = 695 ms.

That’s just over another 2000 reads knocked off and about 200ms faster. We’ve traded off reads for CPU a little here as the compression/decompression process will add overhead on the processor.

Columnstore

Let’s now drop our compressed index and try a Columnstore index…

DROP INDEX ndx_posts_owner_userId ON Posts
CREATE NONCLUSTERED COLUMNSTORE INDEX ndx_cs_owner_user_id ON Posts(OwnerUserId)

I’ll leave you to read more about Columnstore elsewhere but just know they work great on large reporting tables with lots of duplication, because of the way they store data a lot of duplication in the storage is removed.

Now let’s see what this does…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 6382, lob physical reads 0, lob read-ahead reads 22818.

Table ‘Posts’. Segment reads 5, segment skipped 0.

SQL Server Execution Times: CPU time = 187 ms, elapsed time = 276 ms.

Clearly, our read counts have shot up here, whilst we only read 6382 pages (Similar to our non compressed index) 22818 were pre-fetched in anticipation that we might need them as can be seen in the “lob read-ahead reads”. So in the interest of just trying to reduce reads, our columnstore was a failure, however I should also add that this query ran in less than 300ms being more than twice as fast as our previous compressed covering index. The compression of a Columnstore index will vary massively depending on how much duplication you have in your data, the more duplication the more compression you will see.

Indexed Views

We’ve created lightweight indexes to reduce the data touched, we’ve compressed them to reduce IO and we’ve tried Columnstore for it’s aggregation and compression wizardry. So what next? This one feels a bit like cheating but we can harness indexed views to pre-calculate our aggregations and automatically manage them going forwards…

CREATE VIEW vw_TopPosters_Indexed WITH SCHEMABINDING
AS
SELECT 
   Users.Id,
  Users.DisplayName,
  COUNT_BIG(*) Posts
FROM 
  dbo.Posts
  INNER JOIN dbo.Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Users.Id,Posts.OwnerUserId,Users.DisplayName
GO

CREATE UNIQUE CLUSTERED INDEX pk_PostAggregates ON vw_TopPosters_Indexed(Id)
CREATE NONCLUSTERED INDEX ndx_test ON vw_TopPosters_Indexed(Posts)

We’ll need to make a couple of changes to our query to get it to use the indexed view and return the results we need…

SELECT TOP 5
   DisplayName,
   Posts
FROM vw_TopPosters_Indexed WITH(NOEXPAND)
ORDER BY Posts DESC

Table ‘vw_TopPosters_Indexed’. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 283 ms.

22 reads! Jackpot? Maybe, the indexed view is a bit of a cheat as it’s just moved the IO to the writes rather than reads. Depending on how read or write heavy your system is you may or may not see this as a worthwhile tradeoff. Something to highlight here that I think is often missed is whilst the clustered index on an indexed view has a lot of restrictions, once you’ve created it you can create a restriction free nonclustered index (not unique, non grouped fields etc).

Summary

Any thing I’ve missed? Do you have other tricks for lowering IO? Let me know in the comments or drop me a message on one of the social networks in the header/footer.


subscribe via RSS