Erik Darling goes spelunking for new and modified internal objects in SQL Server 2017:
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_group_stats
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.
Garland MacNeill uses the announcement to think about how the role of the DBA is changing:
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.
And Victoria Holt has a link-filled wrapup:
SQL Graph
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.
The SQL Server team announces CTP 2.0 of SQL Server 2017:
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.
David Smith reports that Microsoft R Open 3.3.3 is now available:
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.
Daniel Janik shows that query-level wait stats depend on the version of Management Studio:
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?
No.
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.
Randolph West details the changes in max server memory for Standard Edition in SQL Server 2016 SP1:
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.
Thomas LaRock notes that SQL Slammer is still out there:
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.
The SQL Server team has announced a new CTP:
Key CTP 1.3 enhancement: Always On Availability Groups on Linux
In SQL Server v.Next, we continue to add new enhancements for greater availability and higher uptime. A key design principle has been to provide customers with the same HA and DR solutions on all platforms supported by SQL Server. On Windows, Always On depends on Windows Server Failover Clustering (WSFC). On Linux, you can now create Always On Availability Groups, which integrate with Linux-based cluster resource managers to enable automatic monitoring, failure detection and automatic failover during unplanned outages. We started with the popular clustering technology, Pacemaker.
In addition, Availability Groups can now work across Windows and Linux as part of the same Distributed Availability Group. This configuration can accomplish cross-platform migrations without downtime. To learn more, you can read our blog titled “SQL Server on Linux: Mission Critical HADR with Always On Availability Groups”.
That’s a big headline. In the Other Enhancements section, I like resumable online index rebuilds as well.
Vinay Shulka announces Apache Zeppelin 0.7.0:
SPARK IMPROVEMENTS
This release also adds support for Spark 2 including version Spark 2.1. Zeppelin now also links to Spark History Server UI from Zeppelin so users can more easily track Spark jobs. The Livy interpreter now supports specifying packages with the job.
SECURITY IMPROVEMENTS
The major security improvement in Zeppelin 0.7.0 is using Apache Knox’s LDAP Realm to connect to LDAP. Zeppelin home page now lists only the nodes to which the user is authorized to access. Zeppelin now also has the ability to support PAM based authentication.
The full list of improvements is available here
This visualization platform is growing up nicely.
The SQL Server team has announced that the latest CTP of vNext is available:
Key CTP 1.2 enhancement: Support for SUSE Linux Enterprise
In SQL Server v.Next, a key design principle has been to provide customers with choice about how to develop and deploy SQL Server applications: using technologies they love like Java, .NET, PHP, Python, R and Node.js, all on the platform of their choosing. Now in CTP 1.2, Microsoft is bringing the power of SQL Server to SUSE Linux Enterprise Server, providing more deployment options and a streamlined acquisition process.
That makes three mainline distributions supported: Ubuntu, Red Hat, and now SuSE.
Kevin Feasel
2017-04-24
Internals, Versions