We recently performed a major version upgrade of our CRM product, including upgrading to SQL 2016 (see my working environment for details on our environment, and this post for part of our CRM upgrade shenanigans). As part of Thomas LaRock’s guidance we planned on updating all statistics. For our CRM environment we have a large amount of data and not a lot of time for the upgrade and post-upgrade maintenance, so I needed to make it as optimal as possible. Enter parallel updating of statistics, new to SQL 2016! (Ok, and backported to SQL 2014 SP1CU6). Continue reading
SQL Server Statistics
I love ’em. They are near and dear to my heart. They are so very important to the query optimizer, and when they ain’t happy ain’t nobody happy. I discovered stats early on in my career, via Inside Microsoft SQL Server 2000 by Kalen Delaney (which was my bible). What some people don’t realize is that when SQL Server is auto updating stats it’s generally while a query is running, where the optimizer realizes the rows have changed sufficiently to invalidate the stats and needs to rebuild them and does so as part of that query execution; as such, the implementation needs to be relatively fast so that query execution isn’t necessarily taking 1000% times longer than it should. With the db option to auto update statistics asynchronously the default behavior changes so the triggering query keeps on with the original stats but a separate thread is fired to update the stats; in this case the same sampling logic is still used, it’s just not affecting that one query. Well, with great speed comes smaller and smaller amounts of data being sampled: the sampling ratio is generally inverse to the size of the data, so the larger your data the fewer rows are sampled; with larger data sets it’s easy to see sampling ratios of 0.001%. One thousandth of a percent. That’s not a realistic number for the optimizer to use. Continue reading
In our SQL Failover Cluster Instance (FCI) environments (overall environment description available here) we generally use 2 servers plus a disk witness (quorum drive) – we only have one data center so we don’t take the overhead of using node majority. That quorum drive is part of the resource group called “Cluster Group”, commonly known as cluster core resources. The core cluster resources have no relationship with any other cluster resources (e.g. the SQL Server resource), and they can fail over independently from any other cluster resource. They also stay in place if someone fails over the other resources (i.e. SQL). Continue reading
As part of a major CRM upgrade we upgraded from Windows 2008R2 and SQL Server 2008R2 to Windows 2012R2 and SQL Server 2016 (woo-hoo!). Our CRM product is a little complex (see my working environment for some details), and it’s large (over 1TB for the main database), and we needed to perform the upgrade in all of our non-prod environments (4 total for this one). Since it’s a critical revenue-generating system, our production upgrade would need to be as fast as possible. Due to the size of the data and the amount of systems and integrations, we were already expecting to have a multi-day outage for the upgrade and full post-upgrade testing. Continue reading
I created this post to use as a reference for how my current working environment is configured, so I don’t have to repeat details in my other posts; I can just reference this post.
What we have
Our shop is predominantly Microsoft, so .NET and MSSQL, with a few others thrown in (MySQL and PHP, really, but I have almost nothing to do with them (yay)), and we’re also in the process of integrating Azure into the mix. Other than our production environment we have 2-3 non-prod tiers for almost all of our ecosystems: DEV and QA for almost all, and PLT (performance load testing) for a couple. We are a VMWare shop and aside from a few older systems almost 100% of our environment is virtualized (of the machines which can be virtualized, of course). Because of this, we have dedicated ESX hosts for all of our SQL Servers and license the hosts; this allows us to have as many VMs as we need (as an enterprise client we have Software Assurance) as well as use Enterprise Edition, which also allows us to create dedicated SQL Servers for individual ecosystems and not have to worry about the issues with sharing. We currently have around 90 SQL Server instances in production. Continue reading