Adaptive Query Processing in SQL Server 2017

ADAPTIVE QUERY PROCESSING IN SQL SERVER 2017 The next release of Microsoft SQL Server, announced as SQL Server 2017, is available for download in CTP2 at http://tinyurl.com/SQLvNextLinux and offers many cool new features. One set of features that I really like to focus on is adaptive query processing. These new features speed processing of workloads, especially those that have historically been the hardest to tune. READ MORE... Originally Posted July 05, 2017 … [Read more...]

ICYMI: Parameter Sniffing, Friends with Partition Benefits, and SELECT INTO Parallelism – SQL Server Performance

Great topics for further reading about tuning SQL Server performance: Exploring SQL Server 2014 SELECT INTO Parallelism Joe Sack (b | t) provides us with a peak into the performance improvements and new behaviors of SQL Server 2014 in the way it can now parallelize large SELECT INTO operations. Parameter Sniffing, Embedding, and the RECOMPILE Options Parameter sniffing is a term used to describe the processes by which SQL Server attempts to reuse execution plans where certain parameters might frequently change. If you’re only familiar in passing with the term, it probably has … [Read more...]

ICYMI: Partitioning Advice, Purging Data Bloat, and an example of TVF Misbehavior – SQL Server Performance

Here are some oldies, but goodies that I recommend you read: Two Partitioning Peculiarities and Aggregates and Partitioning Most of us know that partitions are one of the best ways to scale very large databases or very large IO workloads.  However, there are risks, two of which are explored in detail by internals master Paul White (b | t). The Price of Not Purging Holding on to data long after its useful lifespan contributes to bloat and added preventative maintenance headaches. Read this informative post from Erin Stellato (b | t), of SQLSkills.com, where you’ll find a lot of useful … [Read more...]

ICYMI: Query Optimization Phases, Observer Overhead, and Secrets of SP_Updatestats – SQL Server Performance

Do you do a bit of query tuning? Then check out this treasure trove of performance tuning advice! Optimization Phases and Missed Opportunities and Working Around Missed Optimizations Paul White (b | t) writes two fascinating blog posts that go hand-in-hand concerning query optimization.  He goes into some deep details about how the query optimizer works, comparing various versions of SQL Server, and shows you several useful ways to assess query performance. Plus, there’s one handy trace flag too! Observer Overhead and Wait Type Symptoms Joe Sack (b | t) does a deep dive on … [Read more...]

ICYMI: Troubleshooting CPU, Logging Myths, Missing Indexes, and Query Tuning Examples

Great Articles at SQLPerformance.com There are so many good articles from SQLPerformance.com this ICYMI article that it’s hard to know which are best to highlight in the limited space. So, I’ll be arbitrary and simply choose a few of my favorite entries as well look back: Troubleshooting SQL Server CPU Performance Issues This outstanding article from Joe Sack (b | t) of SQLSkills steps you through a methodical and insightful series of DMVs and queries that can pinpoint CPU issues on your SQL Server instances. Don’t just blindly create those “missing” indexes! Aaron Bertrand (b | … [Read more...]

ICYMI: Troubleshooting Availability Groups and the Schema Switcharoo – SQL Server Performance

Aaron Bertrand (b | t) is on a roll this month's SQL Server performance highlights with more great articles from SQLPerformance.com.   Troubleshooting AlwaysOn – Sometimes it takes many sets of eyes Aaron Bertrand (b | t) hit some obstacles recently when configuring an Availability Groups lab environment. This post shows how some assistance from the community helped him isolate and solve the issues. Another argument for stored procedures Learn the subtle ways that ad hoc queries can interfere with SQL Server performance by taking up more space in the plan cache than … [Read more...]

ICYMI: the Halloween Problem and DBCC CheckConstraints – SQL Server Performance

Don't miss these great articles on SQLPerformance.com from the industry’s top experts: A Look At DBCC CHECKCONSTRAINTS and I/O Erin Stellato (b | t) of SQLskills takes a deep look into the SQL Server performance impact, especially I/O, of running DBCC CHECKCONSTRAINTS, including explaining why some check methods may consume a lot more I/O than others. The Halloween Problem, Part 1 of a 4-Part Series Paul White (b| t) provides unique insight into a long-standing data modification issue, the infamous Halloween Problem.  Much has been written over the years about understanding and … [Read more...]

ICYMI: Ten Query Techniques Every SQL Programmer Should Know (Video)

Update 09-Mar-2015: The original PASS links I provided were behind a firewall. Thanks reader MMcDonald, we have a working link on YouTube. One of the benefits of joining PASS is that you get access to their expansive library of conference videos. (Membership is free. So join now. What'dya want, a hand-written invitation?!?) I want to take a moment to point out a good one that I think anybody who writes SQL code on SQL Server should watch. If you know anything about the staff here at SQL Sentry, then you’re probably expecting me and my colleague Microsoft MVP Aaron Bertrand (b | t) to be … [Read more...]

The SQL Sentry forum for SQL Statement Tuning

The SQL Sentry forum for SQL Statement Tuning Have you ever looked at our community site specifically dedicated to helping users tune their SQL queries, http://answers.SQLPerformance.com? You can easily upload an execution plan directly from our free Plan Explorer tool or from good ol' SQL Server Management Studio to the discussion forum.  Once uploaded, our internal team and forum members will provide plenty of tips and input on how to improve the plan and/or explain what the plan is actually doing.  The renown SQL execution plan expert, Paul White (b|t), is the moderator of the site. But … [Read more...]

ICYMI: Sequences, CPUs, and Trimming T-Logs – SQL Server Performance Topics

Have you read any of the great articles on SQLPerformance.com? The deep-dive technical info about SQL Server performance issues authored by the SQL Server industry’s top experts. Here are a few great articles from the early days of the website, January 2013:   Generate a set or sequence without loops Aaron Bertrand (b|t) provides detailed performance information about a variety of methods used to generate sets and sequences in this first in a three part series. Selecting a Processor for SQL Server 2012 Glenn Berry (b|t) of SQLskills.com sheds light on the best CPU to select … [Read more...]