Alan Yu announces a new version of SQL Server Management Studio:
SSMS 17.9 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.
In addition to enhancements and bug fixes, SSMS 17.9 comes with several new features:
- ShowPlan improvements
- Azure SQL support for vCore SKUs
- Bug Fixes
View the Release Notes for more information.
It looks like the big push for this release was bug fixes, and there are quite a few of them.
Todd Greenstein announces Databricks Runtime 4.3:
In addition to the performance improvements, we’ve also added new functionality to Databricks Delta:
Truncate Table: with Delta you can delete all rows in a table using truncate. It’s important to note we do not support deleting specific partitions. Refer to the documentation for more information: Truncate Table
Alter Table Replace columns: Replace columns in a Databricks Delta table, including changing the comment of a column, and we support reordering of multiple columns. Refer to the documentation for more information: Alter Table
FSCK Repair Table: This command allows you to Remove the file entries from the transaction log of a Databricks Delta table that can no longer be found in the underlying file system. This can happen when these files have been manually deleted. Refer to the documentation for more information: Repair Table
Scaling “Merge” Operations: This release comes with experimental support for larger source tables with “Merge” operations. Please contact support if you would like to try out this feature.
Looks like a nice set of reasons to upgrade.
Dinesh Chandrasekhar walks us through some of the updates to Hortonworks Data Flow version 3.2:
Kerberos keytab isolation
Kerberos keytabs can now be isolated at a per principal level. This allows for users in a multi-tenant environment to safely be able to reference specific keytabs and principals. This ensures that just because a user has access to a HDFS keytab they will not have access to all of the HDFS principals. This provides a more granular control so that users are limited to only the principals they require.Kafka 1.1.1 Support
In HDF 3.2, Kafka has been upgraded from 1.0.0 to 1.1.1. Key features and improvements have been added with respect to security and governance. In addition to these bug fixes, an important new feature was added to capture producer and topic metrics at partition level without instrumenting or configuring interceptors on the clients. This provides a non-invasive approach to capture important metrics for producers without refactoring/modifying your existing Kafka clientsHive 3 support
Apache NiFi now supports Hive 3 running on HDP 3.0. This support ensures better performance for Hive streaming to HDP, Hive streaming to S3, and the ability to write directly to ORC from NiFi without first converting your datasets to Avro. Writing directly to ORC for better Hive query performance is accomplished by using the NiFi PutORC processor. With HDF 3.2, a few other processors related to HBase and HDFS have also been updated and enhanced.
Looks like there are some good updates to this version.
With Confluent Platform 5.0, operators can secure infrastructure using the new, easy-to-use LDAP authorizer plugin and can deliver faster disaster recovery (DR) thanks to automatic offset translation in Confluent Replicator. In Confluent Control Center, operators can now view broker configurations and inspect consumer lag to ensure that they are getting the most out of Kafka and that applications are performing as expected.
We have also introduced advanced capabilities for developers. In Confluent Control Center, developers can now better understand the data in Kafka topics due to the new topic inspection feature and Confluent Schema Registry integration. Control Center presents a new graphical user interface (GUI) for writing KSQL, making stream processing more effortless and intuitive as well. The latest version of KSQL itself introduces exciting additions, such as support for nested data, user-defined functions (UDFs), new types of joins and an enhanced REST API. Furthermore, Confluent Platform 5.0 includes the new Confluent MQTT Proxy for easier Internet of Things (IoT) integration with Kafka. The latest release is built on Apache Kafka 2.0, which features several new functionalities and performance improvements.
Looks like there have been some nice incremental improvements here.
Alan Yu announces a new version of SQL Operations Studio:
Highlights for this release include the following.
- SQL Server Agent preview extension Job configuration support
- SQL Server Profiler preview extension Improvements
- Combine Scripts Extension
- Wizard and Dialog Extensibility
- Social content
- Fix GitHub Issues
For complete updates, refer to the Release Notes.
Alan also has demos for each of these. I still wish that they wouldn’t call their Extended Events viewer “Profiler” because that makes it harder for us to explain the difference between “good Profiler” and “bad Profiler.”
Microsoft has a round of announcements for public previews on Azure SQL Database. First up is Kevin Farlee announcing approximate count distinct:
The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.
This function is designed for use in big data scenarios and is optimized for the following conditions:
- Access of data sets that are millions of rows or higher AND
- Aggregation of a column or columns that have a large number of distinct values
Assuming these conditions, the accuracy will be within 2% of the precise result for a majority of workloads.
I’m liking this change. Sometimes I simply need an approximate number but I want it fast.
Shreya Verma announces MATCH support in the MERGE operator:
We will be further expanding the graph database capabilities with several new features. In this blog we will discuss one of these features that is now available for public preview in Azure SQL Database, MATCH support in MERGE DML for graph tables.
The MERGE statement performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a target table based on differences between the target table and the source table. Using MATCH predicates in a MERGE statement is now supported on Azure SQL Database. That is, it is now possible to merge your current graph data (node or edge tables) with new data using the MATCH predicates to specify graph relationships in a single statement, instead of separate INSERT/UPDATE/DELETE statements.
I’ll use that approximately the day they fix all of the bugs with the MERGE operator.
Joe Sack announces row mode memory grant feedback:
In Azure SQL Database, we are further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family. In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview, row mode memory grant feedback. Row mode memory grant feedback expands on the memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.
Key feature benefits:
Reduce wasted memory. For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant. Consecutive executions will then request less memory.
Decrease spills to disk. For an insufficiently sized memory grant that results in a spill to disk, memory grant feedback will trigger a recalculation of the memory grant. Consecutive executions will then request more memory.
This was big for batch mode operators, and I’m happy to see it move to row mode operators as well.
Finally, Joe also announces table variable deferred compilation:
In Azure SQL Database, we will be further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family. In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview in Azure SQL Database, table variable deferred compilation.
Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. During optimization and initial compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. This accurate row count information will be used for optimizing downstream plan operations.
This one has the potential to be a pretty big performance improvement as well.
Aaron Bertrand shows how he finds hidden features in new SQL Server builds:
One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don’t make it to the public, and documentation for these builds is typically sparse. In order to get a head start on testing things out, I often need to explore on my own. And so I wrote some scripts for that, which I’ve talked about in previous blog posts:
- How I spot not-yet-documented features in SQL Server CTPs
- More ways to discover changes in new versions of SQL Server
When I install a new version of SQL Server (be it a cumulative update, the final service pack for a major version, or the first CTP of vNext), there are two steps:
Create a linked server to the build that came before it
Create local synonyms referencing the important catalog views in the linked server
It’s a good way to get a glimpse at which features devs are currently working on but haven’t enabled yet.
Andy Leonard has a bunch of new presents to unwrap:
Varigence keeps giving away cool stuff! Nowhere is Varigence’s commitment to community more evident than in the feature list for BimlExpress 2018. The previous version – BimlExpress 2017 – included the Preview Pane. BimlExpress 2018 includes the ability to Convert SSIS Packages to Biml:
How cool is that? And it’s in the free (FREE!) version!
As with BimlFlex and BimlStudio, there are too many cool features to list here. Head over to the BimlExpress 2018 feature page to learn more.
Converting existing packages to Biml was a great feature that I could never afford. It’s exceedingly nice of Scott Currie & crew to make that available in the free product.
Reza Rad has a new version of Power BI Helper:
I started Power BI Helper with the intention to help to find issues in Power BI reports faster and easier. This tool over time became better and better. I’m excited now to let you know that the version 2.0 of this product is now available for everyone to use and enjoy. This version comes with these features:
Connecting to more than one Power BI model. Selection option for the model.
Showing the connection mode of the Power BI file.
Showing list of tables that are NOT used in any visualization, and can be hidden from the report.
Modeling advises
- List of both directional relationships
- List of inactive relationships
Some minor bug fixes
It looks like quite the useful tool.
Roni Fontaine and Saumitra Buragohain announce Hortonworks Data Platform version 3.0:
Other additional capabilities include:
Scalability and availability with NameNode federation, allowing customers to scale to thousands of nodes and a billion files. Higher availability with multiple name nodes and standby capabilities allow for the undisrupted, continuous cluster operations if a namenode goes down.
Lower total cost of ownership with erasure coding, providing a data protection method that up to this point has mostly been found in object stores. Hadoop 3 will no longer default to storing three full copies of each piece of data across its clusters. Instead of that 3x hit on storage, the erasure encoding method in Hadoop 3 will incur an overhead of 1.5x while maintaining the same level of data recoverability from disk failure. The end result will be a 50% savings in storage overhead, reducing it by half.
Real-time database, delivering improved query optimization to process more data at a faster rate by eliminating the performance gap between low-latency and high-throughput workloads. Enabled via Apache Hive 3.0, HDP 3.0 offers the only unified SQL solution that can seamlessly combine real-time & historical data, making both available for deep SQL analytics. New features such as workload management enable fine grained resource allocation so no need to worry about resource competition. Materialized views pre-computes and caches the intermediate tables into views where the query optimizer will automatically leverage the pre-computed cache, drastically improve performance. The end result is faster time to insights.
Data science performance improvements around Apache Spark and Apache Hive integration. HDP 3.0 provides seamless Spark integration to the cloud. And containerized TensorFlow technical preview combined with GPU pooling delivers a deep learning framework that makes deep learning faster and easier.
Looks like it’s invite-only at the moment, but that should change pretty soon. It also looks like I’ve got a new weekend project…
Kevin Feasel
2018-09-07
SQL Server Management Studio, Versions
No Comment