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
Show RPC Completed/Statement Completed events from user X
Show RPC Completed/Statement Completed events where the duration is over x seconds
Show memory grants over xGB
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
RPC/Statement Completed Over X Seconds
Memory Grants Over 1GB
Deadlocks With Graphs
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…
Events that you want to capture
Actions that you want to capture on those events (Basically fields of data available on those events)
Filters on the events e.g Duration > X
Targets (Not used in the above), these define where to store the data you collect.
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…
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.
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.
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…
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…
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…
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…
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…
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…
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…
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.
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…
On my machine the statistics output I get is this…
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.
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…
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!
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…
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.
Let’s now drop our compressed index and try a Columnstore index…
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.
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.
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…
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…
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).
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.