The developers then took a long hard look at how to make this more efficient on such large systems. As described before, since operations on the cache are read-intensive, there was a thought to leverage reader-writer primitives to optimize locking. However, any changes to this spinlock had to be validated extensively before releasing publicly as they may have a drastic impact if incorrectly implemented.
The implementation of the reader / writer version of this spinlock was an intricate effort and was done carefully to ensure that we do not accidentally affect any other functionality. We are glad to say that the final outcome, of what started as a late night investigation in the SQLCAT lab, has finally landed as an improvement which you can use! If you download and install Cumulative Update 2 for SQL Server 2016 RTM, you will observe two new spinlocks in the sys.dm_os_spinlock_stats view:
- LOCK_RW_CMED_HASH_SET
- LOCK_RW_SECURITY_CACHE
These are improved reader/writer versions of the original spinlocks. For example, LOCK_RW_CMED_HASH_SET is basically the replacement for CMED_HASH_SET, the spinlock which was the bottleneck in the above case.
Click through for the full story.
Jessica Kerr discusses methods for determining data lineage, particularly in distributed systems:
Can you take a piece of data in your system and say what version of code put it in there, based on what messages from other systems? and what information a human viewed before triggering an action?
Me neither.
Why is this acceptable? (because we’re used to it.)
We could make this possible. We could trace the provenance of data. And at the same time, mostly-solve one of the challenges of distributed systems.
This is an interesting essay; read the whole thing.
Raul Gonzalez looks at how the different cardinality estimators handle multi-column statistics:
The thing we can learn from this is that is impossible to be always right when you have to estimate the number of rows if your only resource is statistics, doesn’t matter single or multi-column, there is a set of values out there ready to defeat your logic.
However I think it’s a good idea that SQL Server 2016 gets back to look into multi-column for a simple reason, these are user created stats and therefore gives us (DBA’s, DEV’s) more power over how rows are estimated.
Multi-column stats are probably among the most under-utilized tools in SQL Server.
Steve Jones has an intro-level post on shredding an extended event to get to the relevant portion:
I was playing with some Extended Events recently. If you haven’t tried, I’d encourage you to do so. However, working with XML is not my favorite. I know I can get the GUI in SSMS 16.x to show me events, but I sometimes want to query.
Here was my quick adventure in XML and XQUERY. I should know this stuff better, but I think I’m working with XML so rarely that I’m constantly re-learning things.
Read on for the code.
Chris Bell has a Powershell script to find all sysadmins on a SQL Server instance:
The script below identifies the accounts on your SQL Server that have full sysadmin rights, either on their own or via an Active Directory Group.
To run this, you need a few things setup first.
A file named Instances.txt that has each instance you are going to check on its own line. Just the name, nothing more. You can see the reference to the location at the beginning of the script, just change it to wherever you put your file.
Rights to read the AD information for the domain. This way we can get the members of any groups granted access to your SQL environment.
Click through for the script.
Bill Fellows explains a workaround he uses to set the project-level connection GUID in Biml:
There is no attribute in the Connections collection to assign a guid. It’s simply not there. If you want to associate an Id with an instance of a Connection your choices are the Project node and the Package node. Since we’re dealing with project level connection managers, we best cover both bases to ensure Ids synchronize across our project. If you wish, you could have embedded this Projects node in with the Connections but then you’d have to statically set these Ids. I feel like showing off so we’ll go dynamic.
To start, I define a list of static GUID values in the beginning of my file. Realistically, we have these values in a table and we didn’t go with “known” values. The important thing is that we will always map a guid to a named connection manager. If you change a connection manager’s definition from being project level to non, or vice versa, this will result in the IDs shifting and you’ll see the same symptoms as above.
There’s plenty of code over on Bill’s site to help you as well.
OK, we’ve got notifications. We need SQL Server to give us more information on who is involved in the blocking.
I like to use the built-in Blocked Process Report for this. This has been in SQL Server for a long time, and it’s extremely useful.
The Blocked Process Report shows you the “input buffer” of the commands involved – it may be partial information and not the full text of the query. It will also show you the login name for who is running what, and the type of lock requests involved.
You don’t have to spend extra money to get good diagnostic information, at least about these items.
James Serra gives an introductory walkthrough to Cortana Intelligence Solutions:
Cortana Intelligence Solutions is a new tool just released in public preview that enables users to rapidly discover, easily provision, quickly experiment with, and jumpstart production grade analytical solutions using the Cortana Intelligence Suite (CIS). It does so using preconfigured solutions, reference architectures and design patterns (I’ll just call all these solutions “patterns” for short). At the heart of each Cortana Intelligence Solution pattern is one or more ARM Templates which describe the Azure resources to be provisioned in the user’s Azure subscription. Cortana Intelligence Solution patterns can be complex with multiple ARM templates, interspersed with custom tasks (Web Jobs) and/or manual steps (such as Power BI authorization in Stream Analytics job outputs).
So instead of having to manually go to the Azure web portal and provision many sources, these patterns will do it for you automatically. Think of a pattern as a way to accelerate the process of building an end-to-end demo on top of CIS. A deployed solution will provision your subscription with necessary CIS components (i.e. Event Hub, Stream Analytics, HDInsight, Data Factory, Machine Learning, etc.) and build the relationships between them.
James also walks through an entire solution, so check it out.
Erik Darling warns us against tearing the fabric of time by dividing by zero:
There are several options for fixing this. For instance, you can use a CASE expression, or COALESCE, but I find they get a tad muddled to write after a while, especially if we’re safeguarding multiple columns from our mathematical disaster. Plus,under the covers, the functions I like to use are just case expressions anyway. Isn’t it nice that SQL Server will save you a touch of typing? I think so. What a considerate piece of software!
This is a bit of a beginner tip, but it came up while we were at DBA Days, so I figured I’d write about it in case anyone runs across it.
Read on for Erik’s favorite solution to the problem.
Some of the problems that we have noticed are querying tables that have big datatypes like time(3-7), timestamp, and a few others. Casting or converting the datatypes doesn’t help. If we pull the table into a view without the big datatype columns, we are able to query the view from another server, but never the base table. It has been a bit frustrating, but we are still hopeful that we can find a solution or that Microsoft with fix ODBC connections. If there is a better way to do this, please reach out to me. We have things we need to solve and could use some help.
This is a nice walkthrough of how she set it up, but that sounds like a rather frustrating error.
Kevin Feasel
2016-09-30
Performance Tuning, Versions