A Fine Slice Of SQL Server
Azure Data Studio – open an issue in the Github repo. While you open an issue, Github helps by searching the existing issues as you’re typing, so you’ll find out if there’s already a similar existing issue.
Click through for all of the links. I personally just yell skyward in the hopes that they hear me and fix my problems. It doesn’t work very often so I don’t recommend it as a strategy.
Comments closedLonny 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.
Comments closedErin Stellato has a roundup of Query Store bugfixes as of January 2019:
The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade. As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13. Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build. Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs. I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017. I’m planning to update this post if the fixes are added down the read. So here we go, in descending CU order…
This post is a great reason to keep those SQL Server instances up to date.
Comments closed2 Weeks ago I was working in a very interesting case in SQL 2016. I received an email from one of my customers saying that they were having intermitent issues within their app that was executing some SP_Execute_External_script SP calls to the database.
[…]
We also restarted the launchpad service but with no luck….. The biggest challenge was that sometimes the service responded fine and sometimes it showed the issue that I paste above (And this for me was absolutelly new…). From SQL Side we executed an extended events session with all R services counters but nothing appeared… From sys.dm_Exec_session_Wait_stats we just observed that the session was waiting for SATELLITE_SERVICE_SETUP wait which points that SQL Was waiting an answer from the R service itself.
Click through for the solution.
Comments closedDavid Fowler ran into an issue with the Query Store process experiencing waits due to spinlocks:
We were enjoying a nice peaceful afternoon when we hear panicked shouting that a SQL Server had become unresponsive and the customers were unable to do anything.
We moseyed on down to the server in question to take a look at it. One thing stood out immediately, CPU was pegged out at 100% but SQL itself didn’t actually seem to be doing anything, transactions\second was on the floor. Unfortunately this happened a while back and I didn’t think to capture any graphs or metrics at the time so you’re just going to have to take my word for this.
The issue David ran into was subsequently fixed, making this a cautionary tale to keep those SQL Server instances patched.
Comments closedNed Otter runs into a tricky issue:
I have a client that used Itzik Ben-Gan’s solution of creating a filtered nonclustered columnstore index to achieve batch mode on a rowstore (in fact I proposed that the client consider it). They have an OLTP system, and often perform YTD calculations. When they tested, processing time was reduced by 30 to 50 percent, without touching a single line of application code. If that ain’t low hanging fruit, I don’t know what is —
However, during testing, I noticed some intermittent blocking that didn’t make sense to me. But I couldn’t nail it down, and they went live with the “filtered nonclustered columnstore index” solution.
Once they deployed – and there was a lot of concurrency – I could see what had eluded me during my proof of concept: blocking in tempdb.
Read on for the repro and check out Ned’s UserVoice bug report.
Comments closedSolomon Rutzky digs into UTF-8 support in SQL Server 2019 and has found a few bugs:
Let’s start with what we are told about this new feature. According to the documentation, the new UTF-8 Collations:
can be used …
- as a database-level default Collation
- as a column-level Collation
- by appending “
_UTF8” to the end of any Supplementary Character-Aware Collation (i.e. either having “_SC” in their name, or being of level140or newer)- with only the
CHARandVARCHAR
(implied) have no effect on
NCHARandNVARCHARdata (meaning: for these types, the UTF-8 Collations behave the same as their non-UTF-8 equivalents
“This feature may provide significant storage savings, depending on the character set in use.” (emphasis mine)
Solomon takes his normal, thorough approach to the problem and finds several issues.
Comments closedRandolph West shares an example of an issue with BULK INSERT:
Fellow Canadian Doran Douglas brought this issue to my attention recently, and I wanted to share it with you as well.
Let’s say you have a file in UTF-8 format. What this means is that some of the characters will be single-byte, and some may be more than that.
Where this becomes problematic is that a fixed-width file has fields that are, well, fixed in size. If a Unicode character requires more than one byte, it’s going to cry havoc and let slip the dogs of truncation.
Click through for an example. This seems like a bug to me—I interpret fixed-width as fixed number of characters, not fixed number of bytes. At the very least, it’s liable to cause confusion.
Comments closedTibor Karaszi points out an annoying bug which can happen when viewing an Extended Events file:
Here’s the relevant text in the message box, to facilitate searching:
The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)
Cannot view the function ‘fn_MSXe_read_event_stream’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
Read on for the solution, and if you’re interested in changing that behavior, vote up the UserVoice submission.
Comments closedWe have recently become aware of a regression in one of the TCP/IP functions that manages the TCP port pool which was introduced in the July 10, 2018 Windows updates for Windows 7/Server 2008 R2 and Windows 8.1/Server 2012 R2.
This regression may cause the restart of the SQL Server service to fail with the error, “TCP port is already in use”. We have also observed this issue preventing Availability Group listeners from coming online during failover events for both planned and/or unexpected failovers. When this occurs, you may observe errors similar to below in the SQL ERRORLOGs:
Error: 26023, Severity: 16, State: 1.
Server TCP provider failed to listen on [ <IP ADDRESS> <ipv4> <PORT>]. Tcp port is already in use.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Read on for the solution.
Comments closed