Upgrade your SQL Server to scale before adding new hardware

In SQL Server Tiger team, we closely partner with our customers and partners to ensure they can successfully run and scale their Tier 1 mission critical workloads and applications on SQL Server. Based on our experience and interactions with customers, we found that most customers running older releases of SQL Server and experiencing performance and…


New in SSMS: Performance Dashboard built-in

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 helpful they had a…


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…


New in SSMS: Query Performance Troubleshooting made easier!

The community already uses tools that can make it easier to read and analyze query plans (including SSMS), but these require significant expertise in understanding query processing and plans in order to be able to actually find and fix root causes. In the latest version of SSMS that released last week, we debut a new…


Columnstore Index: SQL Server 2016 – Improved DMV performance

SQL product team has made significant improvements in columnstore index functionality, supportability and performance in SQL Server 2016 based on the customer feedback. This blog post focuses on the performance improvements on a DMV done as part of our latest servicing release of SQL Server 2016, SQL Server 2016 SP1 CU1 and SQL Server 2016 RTM CU4. For…


Extended per-operator level performance stats for Query Processing

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 a Spill Warning in…


New Showplan XML properties in SSMS October Release

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 context of the appropriate…

4

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications. The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2012 and SQL Server 2014 for optimal scalability and performance. A SQL Server 2016 primary goal was ‘It Just Works.’ Out of the box a customer should not have to engage…

0

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…


Comparison Tool released with latest SSMS

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) has effectively changed the…