Quick Tip – Speed a Slow Restore from the Transaction Log

Here's a quick tip for you: During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time.  Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations.  To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement.  For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers. If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and … [Read more...]

SQL Server 2016 is Simply Faster… A Lot Faster

SQL SERVER 2016 IS SIMPLY FASTER…A LOT FASTER I had the pleasure to spend some time with my old friend Mark Souza, a general manager in the Data Platform team at Microsoft, while speaking at the SQL Saturday event in Dublin, Ireland in the summer of 2016. Now keep in mind that Mark and I have known each other since the 1990s when SQL Server was just being ported to a brand new operating system called Windows NT. Mark and I were having a laugh and more than a twinge of nostalgia about how much SQL Server has improved over the decades and now sits atop the heap on most analysts' "best database" … [Read more...]

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...]

Major Feature Reconfiguration For SQL Server 2016 Service Pack 1

MAJOR FEATURE RECONFIGURATION FOR SQL SERVER 2016 SERVICE PACK 1 One of the long-standing differentiators between SQL Server's Enterprise Edition (EE) and Standard Edition (SE), besides price, were the large number of features available only in EE. This is also true for other lower editions of SQL Server such as Web Edition and Express Edition. But there's big news in the air! READ MORE... Original Posted January 03, 2017 … [Read more...]

How Can the SQL Server Resource Governor Help You?

MANAGING ‘NOISY NEIGHBORS’ IN SQL SERVER If you've been managing databases for any length of time, you've probably had to contend with the "noisy neighbors" scenario. In this scenario, you have several applications, each with their own database residing on a single instance of SQL Server, which don't share the resources of the server nicely. To deal with this issue, you might want to investigate Resource Governor as an alternative. READ MORE... Originally Posted in DBTA Magazine in June 09, 2015 … [Read more...]

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...]

Get All of the Freebies for “End-to-End Troubleshooting Checklist for Microsoft SQL Server”

The Presentation: Slides, TSQL-Scripts, and Video One of my most enduring and popular presentations is called End-to-End Troubleshooting Checklist for Microsoft SQL Server". In this presentation, I take you through my six-step checklist from detection of a performance issue on SQL Server through identification of the root cause to remediation and finally post-mortem steps to ensure the problem is prevented (or at least detected immediately) in the future. Over the years, I've had many inquiries about the slides, T-SQL scripts, and additional troubleshooting information. I'm happy to … [Read more...]

My Best Blog Posts from 2015

ALWAYS LEARNINGAbout 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 … [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...]

For #SQLFamily, a Most Wonderful Time of the Year!

It’s the holiday season, a time for cheer and goodwill towards men. That got me thinking about the whole “most wonderful time of the year” tune playing in the background and how that has some special implications for the SQL Server world. Here’s a bit of context: I'm writing this article for you in the midst of the biggest gathering of SQL Server professions in any given year, the PASS Summit. One of the most visible activities when attendees get together for the very first time at the registration desk or the assembly hall for the first keynote address is the huge number of hugs, … [Read more...]