Learn More About the SQL Server Cardinality Estimator

COOL FEATURES OF SQL SERVER 2014: IMPROVED CARDINALITY ESTIMATES With all the cheerleading and the steady drumbeat of new features being released to Azure, it's easy to lose track of the many cool and valuable new features released in the on-premises version of SQL Server. One of the crown jewels of SQL Server, the cardinality estimator (CE), underwent a large redesign for SQL Server 2014 to improve performance. Cardinality estimates are an extremely important part of query processing. In a nutshell, cardinality estimates are what the relational engine predicts for the number of rows affected … [Read more...]

My Best Blog Posts from 2015

ALWAYS LEARNING About mid-way into August of 2015, I learned some important new factors in Search Engine Optimization (SEO) that caused me to make some big changes to the way in which I blogged. Namely - simply reposting a slight variant of a given blog post could hurt your overall SEO rankings for any of the given posts. Now you might be saying to me "Kevin, those aren't new factors. They've been around for a long time, you idgit". And I would say in response "Ok then. But I had other things on my mind". (Of course, if you have other recommendations for improving one's blog performance, … [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: 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: 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: 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...]

Amazingly Useful SQL Server Articles on SQLPerformance.com

Me and Aaron Bertrand, SQL Sentry flagbearers. One of the things we've been working on at SQL Sentry is building up institutional knowledge for the SQL Server community. There are three main ways that we're doing that on-line: Hard-core tech articles on SQLPerformance.com, edited by long-time SQL Server MVP Aaron Bertrand ( b | t ). Helping tune tough SQL Server transactions on Answers.SQLPerformance.com for free. Streaming videos at SQLSentry.TV. In addition, our monthly eNews newsletter keeps you up to date on all the latest happenings on all of these websites. Please subscribe! … [Read more...]

Squishy Limits in SQL Server Express Edition

It's an old story you've probably heard before.  Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of cannibalizing sales of your commercial products.  Microsoft has take this strategy with SQL Server Express Edition, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications. One such limitation of SQL Server Express Edition is that it … [Read more...]

Learn More About SQL Server IO and Query Tuning in These Webcasts

I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.   SSDs are a Game Changer for SQL Server Storage No, don't let the title mislead you.  This session is not exclusively about SSDs.  But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks.  As Bob Dylan said "Times, they are a'changin'".  This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology.  I'll … [Read more...]

Let’s Talk Licensing and Virtualization for SQL Server

I have two new articles up on Database Trends & Applications magazine.  I'd love to get your thoughts and feedback! Welcome to the Weird, Wild World of SQL Server Licensing Not long in the past, SQL Server licensing was an easy and straightforward process. You used to take one of a few paths to get your SQL Server licenses. The first and easiest path was to buy your SQL Server license with your hardware. Want to buy a HP Proliant DL380 for a SQL Server application? Why not get your SQL Server Enterprise Edition license with it at the same time? Just pay the hardware vendor for the … [Read more...]