New in SSMS: Searching in Showplan

Have you ever analyzed a somewhat big plan? And while doing so did you ever wish you could search for something like table name, index name or column name? In SSMS 17.2, we are including the ability to search all these and more in graphical showplan. Let’s look at an example. I executed a query…


Correlating xEvents with DMVs

Since the inception of xEvents there has been the need to occasionally correlate data from xEvent collections and DMVs. Specifically, using the query_hash and query_plan_hash actions and correlate with DMVs such as sys.dm_exec_requests and sys.dm_exec_query_stats for example. The blog post on Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for…


Developers Choice: Query progress – anytime, anywhere

One of the most important actions when a performance issue hits, is to get precise understanding on the workload that’s executing and how resource usage is being driven. The actual execution plan is an invaluable tool for this purpose. Query completion is a prerequisite for the availability of an actual query plan, but with LQS…


Memory grant related diagnostics

Back in March I blogged about Addressing large memory grant requests from optimized Nested Loops. To further enhance discoverability of memory grant related issues, in SQL Server 2016 we released a new xEvent (query_memory_grant_usage). This fires at the end of query processing for all queries with a memory grant over 5MB, and provides insight into…


Added per-operator level performance stats for Query Processing

When you are troubleshooting query performance, metrics are never too much. With that in mind, based on customer feedback, but also our own CSS support, we have added runtime information to Showplan, and exposed a new Extended Event in SQL Server 2016 RC0. We are adding the new Showplan info and Extended Event in SQL Server…


SQL Server 2016 – Added information on TempDB Spill events – xEvents

My previous post was about new information added to spill warnings. Those are visible at the query plan level, but what about if you need to track for an extended period of time? While spills are something that denotes some type of inefficiency, starting with IO usage where only memory usage was expected, but also…


Cardinality Estimation for Correlated Columns in SQL Server 2016

When we introduced the new CE back in SQL Server 2014, several changes were done regarding the assumptions of the model. These changes were introduced because the vast potential for variations in data distribution, volume and query patterns, made some of the model assumptions not applicable. The 4 assumptions for the pre-2014 model were: Independence:…


New extended events for database recovery progress

In yesterday’s blog post, I talked about a new extended event for backup and restore. Continuing with the theme of insights into progress of long running tasks, another area we have invested in is database recovery. As most of you can attest, there is limited information available during database recovery activities such as Analysis, Redo…

0

New extended event to track backup and restore progress

Backup and restore are long running tasks in SQL Server with limited insights into progress. Often a question asked is “How much longer will it take for this operation to complete?”. Though there are trace flags and DMVs that provide some information, they are either unstructured (ex: trace flags send output to error logs and…

0