A Fine Slice Of SQL Server
Ed Elliott notes that there is a new version of SQLCover out:
There have been a couple of fixes in SQLCover this week, kindly submitted by John Mclusky (https://github.com/jmclusky):
and
DeclareTableVariableStatement statements cannot be covered, so report falsely as missing coverage
Go check out SQLCover.
Comments closedMelissa Coates discusses some of the planning involved with creating a data lake:
Does a Data Lake Replace a Data Warehouse?
I’m biased here, and a firm believer that modern data warehousing is still very important. Therefore, I believe that a data lake, in an of itself, doesn’t entirely replace the need for a data warehouse (or data marts) which contain cleansed data in a user-friendly format. The data warehouse doesn’t absolutely have to be in a relational database anymore, but it does need a semantic layer which is easy to work with that most business users can access for the most common reporting needs.
On this question, my answer is “Absolutely not.” Data warehouses are designed to answer specific, known business questions. They’re great for regulatory reporting, quarterly reports to shareholders, weekly reports to management, etc. Data lakes are designed for ad hoc analysis of information. Read the whole thing.
Comments closedDan Guzman notes a change in behavior in how datetime fields are upconverted:
SQL Server 2016 and Azure SQL Database V12 use the raw datetime internal value without rounding during conversion to another temporal type. The value is rounded only once during conversion, to the target type precision. The end result will be the same as before SQL Server 2016 when the target type precision is 3 or less. However, the converted value will be different when the target type precision is greater than 3 and the internal time unit interval is not evenly divisible by 3 (i.e. rounded source datetime millisecond value is 3 or 7). Note the non-zero microseconds and nanoseconds in the script results below and that rounding is based on the target type precision rather than the source.
This is a good thing on net, but be aware of this if you try to compare datetime versus datetime2 values.
Comments closedThe 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.
Comments closedJessica 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.
Comments closedRaul 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.
Comments closedSteve 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.
Comments closedChris 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.
Comments closedBill 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.
Comments closedOK, 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.
Comments closed