Developer’s Choice: Hinting Query Execution model

This is a cross post with http://aka.ms/sqlserverteam Over the years you have read a number of blogs advocating for or against trace flags that influence SQL Server’s query execution model. You can see a number of query execution related trace flags are documented at http://aka.ms/traceflags. However, trace flags are deemed a hacky way of influencing…

0

More Showplan enhancements – UDFs

Cross post with http://aka.ms/sqlserverteam As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here. In this article I’ll talk about the second showplan improvement we worked on, to assist in the discoverability of UDF…

0

New in SSMS: Performance Dashboard built-in

Cross post with http://aka.ms/sqlserverteam Back in 2007, we released the Microsoft SQL Server 2005 Performance Dashboard Reports, which were designed to provide fast insight into performance issues from some newly created system views – DMFs (Dynamic Management Views). These were updated for SQL Server 2008 and later to SQL Server 2012, and while being very…


Correlating xEvents with DMVs

Cross post with http://aka.ms/sqlserverteam 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…


Extended per-operator level performance stats for Query Processing

Cross post with http://aka.ms/sqlserverteam Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016, such as CPU and I/O. Observe the example, where by executing the below query in AdventureWorks2014, on SQL Server 2016 SP1, we get…


New Showplan XML properties in SSMS October Release

Cross post with http://aka.ms/sqlserverteam Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the…

2

Added per-operator level performance stats for Query Processing

Cross post with https://aka.ms/sqlserverteam 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…


What’s new with SSMS Plan Comparison Tool?

Cross post with https://aka.ms/sqlserverteam With the SQL Server Management Studio February update, we released a new update for the Plan Comparison Tool. In case you missed it before, we introduced this tool inside SSMS back in October (CTP2.4), with which you can do offline comparison of execution plans, from SQL Server 2005 to the latest…


Comparison Tool released with latest SSMS

Cross post with aka.ms/sqlserverteam The ability to compare plans is something that a lot of people have to do for troubleshooting reasons. Maybe it’s to find why a query or batch suddenly slowed down; to understand the impact of a rewrite; or observe how a specific performance-enhancing change introduced to the design (like an index)…


Ninja Troubleshooting- User Interface freezing during SQL Server database restore.

Hello world 🙂 To sum up, i was working with one of my customers regarding the UI (User Interface) freezing while restoring (Symantec NetBackup) a database into SQL Server 2012, leading to a unresponsive behavior from the server. Scenario (VMware guest – WS2012R2): Started with gathering performance counters to jump into the analysis and even though…

1