Andy Levy explains why he wants script update mode to run in parallel:
When you have 8000+ databases on an instance, this is a huge deal. You’re looking at over two and a half hours just to bring SQL Server online after installing an SP or CU. While the instance is in script update mode, incoming connections are locked down and the service remains in the Starting status. Only the Dedicated Administrator Connection can be used to connect to the instance remotely.
Taking advantage of having a Failover Cluster Instance to patch the passive node in advance is great for minimizing downtime for Windows updates. But whether you have an FCI or not, SQL Server will remain in the “Starting” state until all of your databases have been through this process after installing an update. What was once a 10-minute failover is now a multi-hour ordeal, and maintenance windows become a lot harder to negotiate.
Andy’s pretty far over on the right-hand side of that Bell curve, but I like his SQL Server suggestion because even with just a few hundred or a couple thousand databases, you’re still talking real time savings.
SQL Server does have a fairly useful dynamic management view, or DMV, which provides insight that can be leveraged in this area. The DMV I’m talking about is the set of DMVs around missing indexes, consisting of
sys.dm_db_missing_index_groups,sys.dm_db_missing_index_details, etc. I’m not saying the missing indexes DMVs are a panacea that will enable you to fix every performance situation you run into, but they can be useful if you know where to look. This post doesn’t go into a lot of depth about how to use those DMVs for the purpose of actually creating indexes, however I will show you how you can create multi-column stats objects as an interim performance booster while evaluating the need for those indexes.
I’ve never had great luck with multi-column stats versus simply creating indexes but that could simply be a case of me doing it wrong.
The good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) and Credential Spec Files. A number of people have already documented their efforts. Some were more successful than others.
Click through for a detailed guide to getting this working.
Lonny Niederstadt tracked down an ugly bug in SQL Server 2016 SP2 CU4:
When I started investigating, the error was known only as an access violation, preventing some operations related to data cleansing or fact table versioning.
It occurred deep within a series of stored procedures. The execution environment included cross-database DELETE statements, cross-database synonyms, lots of SELECT statements against system views, scalar UDFs, and lots and lots of dynamic SQL.
And… I don’t have access to the four systems where the access violation occurred.
I was able to have some investigation performed on those systems – we learned that by disabling ‘auto stats update’ for the duration of the sensitive operations, the error was avoided. We also learned that reverting a system from SQL Server 2016 SP2 CU4 to SP2 CU2 avoided the errors. On those systems, reverting to SP2 CU2 or temporarily disabling ‘auto stats update’ were sufficient temporary mitigations.
Very interesting sleuthing work. It also appears the issue might have been limited to SP2 CU4, as SP2 CU3 and SP2 CU5 return different results in Lonny’s repro.
Max Trinidad shares some tools you can use to automate SQL Server processes with Powershell:
For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)
PowerShell Core GA version 6.1.3
PowerShell Core Preview 6.2.0 Preview 4Here are some important PowerShell Modules to use for SQL Server management scripting:
*SQLServer – This module currently can be use on SQL Server 2017 and greater.
*DBATools – This a community supported module that will work with SQL Server 2000 and greater.
DBAReports – Supports for Windows SQL Server.
DBCheck – Support for Windows SQL Server.
Automation is a great DBA’s best weapon. Knowing the tools which help you automate your tasks is critical.
Eduardo Pivaral shows that you should clear out some of the bigger SSIS tables occasionally:
If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.
If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.
This kind of batched delete works for more than just the Integration Services tables; it’s a good plan wherever you have a large table and need to delete a fairly significant number of records from it.
Randolph West explains why the default value of cost threshold for parallelism is too low at 5:
Unfortunately, the default setting of 5 means that queries are likely to hit that threshold more often than not on modern hardware, and the optimizer is forced to look at parallel plans unnecessarily. A side-effect to this is that queries running in parallel will block queries running on a single thread, so short-running queries will be delayed by long-running queries needing the same resources.
Therefore, if I am setting up a SQL Server instance from scratch, I will set this value to 50 by default, and monitor the performance counters. Once a query cost exceeds 50, then it can use all the cores up to the limit defined by the max degree of parallelism.
50 is a reasonable first stab at a default. I’ll be honest, though: I’m not sure I know exactly where to set the cost threshold for parallelism. I agree that 5 is too low and 50 is better, but I don’t have a great feel for when it should move up or down short of “everything’s going parallel in your database, so increase the value” or “nothing’s ever going parallel, so maybe decrease it a skosh.”
Let’s start with what services may require you to use a data gateway.
You will need a data gateway when you are using Power BI, Azure Analysis Services, PowerApps, Microsoft Flow, Azure Logic Apps, Azure Data Factory, or Azure ML with a data source/destination that is in a private network that isn’t connected to your Azure subscription with a VPN gateway. Note that a private network includes on-premises data sources and Azure Virtual Machines as well as Azure SQL Databases and Azure SQL Data Warehouses that require use of VNet service endpoints rather than public endpoints.
There are a few of them so check out Meagan’s post and take notes.
The next blade will show you an active console of the virtual machine. From here you are able to determine what the current status of the virtual machine might be. You will also noticed that you can gain access to the serial log (shown below), which will give you more detailed information about the boot process.
Once we click on Boot Diagnostics, we will then see the initial startup screens of the server:
This is useful if you have some huge misconfiguration and the server’s failing for some reason.
Randolph West is not a big fan of Buffer Pool Extension in practice:
Unfortunately there are some practical problems with the Buffer Pool Extension in 2019.
Firstly, let’s talk about the licensing contradiction with SQL Server Standard Edition. Since 2016 Service Pack 1, we have been able to access 128 GB of memory for the buffer pool, plus additional RAM for in-memory OLTP and Columnstore indexes. The practical limits of Standard Edition have been — for the most part — all but lifted. By the same token, if we can afford Enterprise Edition, we can afford more physical RAM. In other words, if we need more than 128 GB RAM for the buffer pool, perhaps we should move away from Standard Edition.
Title aside, Randolph makes a really good point: Buffer Pool Extension was one of those features which sounded great during development, but hardware quickly overtook it and made it all but irrelevant. On the whole, that’s a positive for us as SQL Server users.
Kevin Feasel
2019-02-28
Administration