It was at this point that I realized just how many candidates there are for “most useless” things lying around the product. So I decided to create my own tweet. I asked for the most useless feature, anytime between version 7.0 (which I would call the beginning of SQL Server’s “modern era”) and now. I received quite a few suggestions, and so I have decided to catalog them here—along with a bit of personal commentary on each one.
The list that follows is mostly unordered and culled straight from what I received on Twitter. Hopefully I haven’t missed anything due to Twitter’s weird threading and “priority” mechanisms. And please let me know in the comments if your favorite useless feature is missing, or you’d like to add a comment and/or argument about one of these. Perhaps we can find some way to turn these dark and ugly corners into things of beauty? Well, we shall see…
I almost completely agree with Adam’s opinions on this long list. I’d emphasize, though, that In-Memory OLTP is by no means useless.
Restoring 27 databases; they all restored properly, but 15 of them gave a warning along these lines:
Warning: Wordbreaker, filter, or protocol handler used by catalog ‘FOOBARBAZ’ does not exist on this instance. Use sp_help_fulltext_catalog_components and sp_help_fulltext_system_components check for mismatching components. Rebuild catalog is recommended.
Read on for the solution.
Integrating EC with HDFS can maintain the same fault-tolerance with improved storage efficiency. As an example, a 3x replicated file with 6 blocks will consume 6*3 = 18 blocks of disk space. But with EC (6 data, 3 parity) deployment, it will only consume 9 blocks (6 data blocks + 3 parity blocks) of disk space. This only requires the storage overhead up to 50%.
Since Erasure coding requires additional overhead in the reconstruction of the data due to performing remote reads, thus it is generally used for storing less frequently accessed data. Before deploying Erasure code, users should consider all the overheads like storage, network and CPU overheads of erasure coding.
Now to support the Erasure Coding effectively in HDFS they made some changes in the architecture. Lets us take a look at the architectural changes.
There are some nice features coming to Hadoop version 3.
What have we here?
Of particular interest are last_sql_handle, query_hash, and query_plan_hash. It appears that we’ll finally be able to easily tie missing index requests to their queries, without doing a lot of painful XML processing. I had planned on adding something like this, but couldn’t find a good fit between 1) adding XML processing to sp_BlitzIndex, or adding more DMV queries and rather unpleasant XML processing to sp_BlitzCache. This will make implementing it far easier, assuming it works the way it looks like it will work.
Erik has three examples of interesting additions in CTP 2.0.
Forthwith some Data Amp reactions. Steve Hughes hits some of the highlights:
Migration Project for Azure SQL DB
Whether you have SQL Server, Oracle, or MySQL, you should be able to migrate your database to Azure SQL DB in “five simple steps”. While a great tool, I am interested in exploring this more with Oracle in particular. You can create a project in Azure that let’s you choose the source database and platform and target a Azure SQL DB then move the schema and load the database. While I am skeptical on the full capability of this solution, I look forward to exploring it more.
Which brings me to my ultimate realization after today’s presentation. Career wise, DBAs have to evolve. I know it’s been said a thousand times, but the days of running backups, granting permissions, and the other daily dba tasks are riding off into the sunset for all but those who work in data centers such as Azure, Google, or AWS.
That means for the rest of the DBAs to continue to be employed as data professionals they have to learn new skills. Unless I am missing something, that means focusing on development skills. Not just SQL either. They are going to have to know things like R, Python, PowerShell, and probably some C#, including .NET framework. That might not be the only thing, as Thomas LaRock (b/t) thinks there might be other areas to invest in as well.
Storing and analyzing graph data relationships. This includes full CRUD support to create nodes and edges and T-SQL query language extensions to provide multi-hop navigation using join-free pattern matching. The SQL Server engine integration enables querying across SQL tables and graph data.
Good reading all.
Microsoft is excited to announce a new preview for the next version of SQL Server! We disclosed a name for this next release, SQL Server 2017, today at the Microsoft Data Amp event. Community Technology Preview (CTP) 2.0 is the first production-quality preview of SQL Server 2017, and it is available on both Windows and Linux. In this preview, we added a number of new capabilities, including the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, and other capabilities that help you manage SQL Server for high performance and uptime, including the Adaptive Query Processing family of intelligent database features and resumable online indexing.
I can finally call it “SQL Server 2017” instead of “SQL Server vNext.” I don’t know why there was such a hubbub about the name 2017, but there you go. Anyhow, I’ve grabbed the CTP and am raring to go.
Microsoft R Open (MRO), Microsoft’s enhanced distribution of open source R, has been upgraded to version 3.3.3, and is now available for download for Windows, Mac, and Linux. This update upgrades the R language engine to R 3.3.3, upgrades the installer, and updates the bundled packages.
R 3.3.3 makes just a few minor fixes compared to R 3.3.2 (see the full list of changes here), so you shouldn’t encounter any compatibility issues when upgrading from MRO 3.3.2. For CRAN packages, MRO 3.3.3 points to CRAN snapshot taken on March 15, 2017 but as always, you can use the built-in checkpoint package to access packages from an earlier date (for compatibility) or a later date (to access new and updated packages).
Click through for more details. As a side note, CRAN R 3.4 is scheduled for release this month, so given their recent cadence, I’d guess MRO 3.4 to be out late this year.
The waitstats don’t appear at all in my older Surface which has a newer version of SQL. So what is 4202.2? It’s a refresh for Master Data Services and R. Could that really have broken my query plan waitstats?
I doubt it but maybe. I updated to make the two equal. Did the waitstats go away?
When reading the solution, it seems obvious, but this is a good reminder that there are a lot of moving parts here, and one of the early troubleshooting steps for “It works here, so why not over here?” types of issues is to make sure software is at the same version number.
The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that are read from disk into memory — in other words, the database itself).
For servers containing more than 128GB of physical RAM, and running SQL Server 2016 with Service Pack 1 or higher, we now have options.
Randolph has a couple good clarifications on memory limits outside the buffer pool, making this worth the read.
But all of that is in the past. Here’s what you need to know about SQL Slammer today.
First, this worm infects unpatched SQL 2000 and MSDE instances only. About a month ago, I would have thought that the number of such installs would be quite small. But the recent uptick in Slammer tells me that there are enough of these systems to make Slammer one of the top malware detected at the end of 2016. And a quick search at Shodan shows thousands of public-facing database servers available. And if you want to have some real fun at Shodan®, Ian Trump (blog | @phat_hobbit) has a suggestion for you.
Click through for ways to protect yourself. The best way to protect yourself is not to have SQL Server 2000 around anymore.