A Fine Slice Of SQL Server
Mark Wilkinson snuck up on a bug and caught it:
So now on to the good stuff, why you are all here. Where I work, we add comments to the top of all of our procedures (above the
CREATE) to add notes about changes that were made. Recently we had a procedure that compiled fine, but would not execute. It reported a syntax error, which is odd since it compiled fine:
Read on for the bug, which is a weird one.
Comments closedAx Sharma reports on a nasty bug in Windows:
In August 2020, October 2020, and finally this week, infosec researcher Jonas L drew attention to an NTFS vulnerability impacting Windows 10 that has not been fixed.
When exploited, this vulnerability can be triggered by a single-line command to instantly corrupt an NTFS-formatted hard drive, with Windows prompting the user to restart their computer to repair the corrupted disk records.
The researcher told BleepingComputer that the flaw became exploitable starting around Windows 10 build 1803, the Windows 10 April 2018 Update, and continues to work in the latest version.
Read the whole thing. It looks like this also affects Windows XP, which makes me figure it might affect Windows 7, 8, and 8.1 too. H/T Mark Wilkinson.
Comments closedPaul Andrew hits on some growing pains:
Just last week we heard the announcement from Microsoft that Azure Synapse Analytics is now generally available (GA)… A full year on, plus a few weeks, since first seeing Synapse at the big USA conferences in November 2019.
Today I’ve been attempting to use the resource with a view to implementing it for several customer projects. Although GA, it would seem that many part of the technology are far from ready.
In this brief blog I’m exposing some of the pain I’ve faced so far in simply trying to deploy a second instance of Azure Synapse Analytics using ARM templates.
Click through for three that Paul found. I’d expect that most of these will be tidied up in the next few months.
Comments closed1. Open the “New Session…” GUI for extended events in SSMS
2. Give it a name, it’s not important
3. Add an event with a Duration Field, I’ve added sp_statement_completed and configure it to be higher than an arbitrary value
And then watch it go boom in SSMS 18.7. Click through for a demo of the issue and a workaround.
Comments closedEitan Blumin has a write-up of an interesting bug in SQL Server:
The Access Violation error is triggered when an execution plan with parallelism involves specific system table functions. We found that the error occurs ONLY with parallel execution plans.
Therefore, in order to reproduce it, you’ll need:
– SQL Server instance with MaxDOP setting not equal to 1
– At least 2 available CPU cores
Click through for additional details, including a script to generate the same error yourself.
Comments closedEric Cobb gives us a warning around disabling indexes:
Here at work we have a very large, very intensive data load that disables and rebuilds indexes as part of the process. We recently added compression to many of the tables and indexes in the database because it was growing quite large (around 28TB at the time). After adding compression, we got the database size down to somewhere around 17TB.
So you can imagine our surprise when the DB size jumped back up to over 30TB after the last data load! In trying to figure out what happened I discovered that most of the data compression was gone.
That’s…not great. Eric shows us a demo as well and notes that it still applies to SQL Server 2019. I’d be apt to call it a bug, myself.
Comments closedJohn Sterrett takes us through a bug in SQL Server:
When a bug jumps out and surprises me I like to share it so others do not run into the same unexpected result. I don’t think there is any reason for a bug to bite multiple people in the butt. Therefore, I want to show you why persist sample percent IS NOT PERSISTED!
Read on for the repro and upvote the Feedback item.
Comments closedPaul White writes up a bug report:
Looking at the execution plan, it is hard to see how deleting a row (at the Clustered Index Delete) then inserting it again (at the Clustered Index Insert) could possibly result in a duplicate key in the index. Remember there is only one row, one column, and one index.
Logically, the only way this error can occur is if the Delete operator does not delete the row.
Read the whole thing. It’s probably not something you’ll ever come across yourself, hopefully.
Comments closedJosh Darnell takes us through a tricky problem:
I came across a bug in SQL Server 2016 where the Availability Group (AG) health check can get stuck in an infinite loop after removing and re-adding a database from an AG.
Unfortunately, I don’t know exactly what version this bug was introduced. I first noticed the problem on SQL Server 2016 SP2 CU7 GDR (13.0.5366.0). It may have existed before then, but I never encountered it.
Read on for a workaround. And hopefully there will be a proper fix soon. Also, it’d be interesting to see if it can be reproduced in 2017 or 2019.
Comments closedI follow up on a bug from SQL Server 2019 CU2:
Back with SQL Server 2019 CU2, I reported an error with PolyBase connecting to Excel when trying to select TOP(10) from the table. I’m using the Microsoft Access Database Engine 2016 Redistributable’s Excel driver.
Click through for an explanation of what’s happened since then on this front.
Comments closed