A Fine Slice Of SQL Server
As of CTP 2.1 for SQL Server 2017 a set of new environment variables are available. These variables allow us to configure each SQL Server container as we spin them up. The first version of SQL Server on Linux came with:
ACCEPT_EULA
SA_PASSWORDThese had to be set for the container to start. The SA_PASSWORD has be a complex password or the container will not start. CTP 2.1 introduced:
Read on for the new variables and an example on how to use them.
Comments closedDan English points out some changes to the installation GUI in SQL Server 2017 CTP 2.1:
So we see that now for Machine Learning there is also Python support and that SSIS has new scale out capabilities. In addition to this Reporting Services – Native installation has been removed (and no more SharePoint integration!).
Dan covers several tools, including Power BI Report Server and client.
Comments closedKendra Little discusses max degree of parallelism and cost threshold for parallelism:
When you run a query, SQL Server estimates how “expensive” it is in a fake costing unit, let’s call it Estimated QueryBucks.
If a query’s Estimated QueryBucks is over the “Cost Threshold for Parallelism” setting in SQL Server, it qualifies to potentially use multiple processors to run the query.
The number of processors it can use is defined by the instance level “Max Degree of Parallelism” setting.
When writing TSQL, you can specify maxdop for individual statements as a query hint, to say that if that query qualifies to go parallel, it should use the number of processors specified in the hint and ignore the server level setting. (You could use this to make it use more processors, or to never go parallel.)
Read the whole thing, or watch/listen to the video.
Comments closedDenny Cherry discusses the performance downsides of having too many NUMA nodes on a server:
At one client I was working with in 2015 they had a server which was configured very similarly to what I’ve described above. They had 32 cores, with 11 NUMA nodes. One NUMA node has 4 cores, the next 9 had three cores each, while the 11th NUMA node had one core in it.
When monitoring the performance on the server what we saw was that three cores were running at 100% (or close to it) while the other cores were running at about 4%.
The reason for this, is that SQL Server is NUMA aware, and it was trying to get the best performance possible out of the configuration that it had access to.
Moderation in everything. Also, it’s important to plan growth and check every once in a while for oddities like this.
Comments closedNiko Neugebauer looks at a new feature in SQL Server 2016:
In the first version/iteration of the Database Scoped Configurations, there are 5 functionalities are supported on per Database level:
– Procedure Cache cleaning
– MAXDOP control
– Query Optimizer Cardinalitiy Estimation Model activation control
– Parameter Sniffing control
– Query Optimizer Hotfixes enabling
Niko also shows an example of how the different MAXDOP settings interact.
Comments closedGlenn Berry has a new update of his diagnostic queries, focused around SQL Server 2014 and 2016:
This month, I have several improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. One new feature is a series of six dashes after each query to make them easier to parse by people who use PowerShell to run the queries and collect the results. I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that pretty soon.
If you don’t already use Glenn’s queries, I highly recommend them as a starting point in tracking down issues.
Comments closedMirek Sztajno introduces us to new database-scoped configurations in SQL Server 2016:
Enable or disable PARAMETER_SNIFFING at the database level. Disable this option to instruct the query optimizer to use statistical data instead of the initial values for all local variables and parameters when the query is compiled and optimized. This is equivalent toTrace Flag 4136 or the OPTIMIZE FOR UNKNOWN query hint
Enable or disable QUERY_OPTIMIZER_HOTFIXES at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199
CLEAR PROCEDURE_CACHE which allows to clear procedure cache at the database level without impacting other databases and without requiring sysadmin permission. This command can be executed using ALTER ANY DATABASE SCOPE CONFIGURATION permission on the database, and the operation can be executed on the primary and/or the secondary
This is an early implementation of functionality, but I think this is a step in the right direction. Getting finer-grained and database-level configuration settings gets us one step closer to that 2012 dream of containerized databases.
Comments closedDavid Klee uses the registry to generate blue screens:
Voila! Use the USB keyboard registry key. Set it and reboot the machine. To trigger it, hold right-control and hit scroll lock twice.
BOOM! Immediate manually-initiated BSOD. Neat, huh?
Me, I just need to update my video card drivers; that gives me all the blue screens I want…
Comments closedAndy Mallon teaches us when we need to use WITH OVERRIDE on RECONFIGURE:
Should I use WITH OVERRIDE?
Probably not.
That was an easy post. All done! Thanks for reading.
……Oh, right. Let’s talk about why. Books Online actually has the answer:
If that’s too too heavy of reading, you can spend the day on the Transformers Wiki. I mean, it is Friday, after all.
Comments closed