A Fine Slice Of SQL Server
Alan Yu announces the February 2020 release of Azure Data Studio:
Azure Data Studio is a multi-database, cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. To learn more, visit our Github.
The key highlights to cover this month include:
– Improved Azure sign in support.
– Find in Notebook support.
– Visual Studio Code merge to 1.42.
– Bug fixes.
Read on for more details on each topic.
Comments closedMilos Radivojevic takes us through improvements with interleaved execution in SQL Server:
As you might know, the Interleaved Execution is the member of the Intelligent Query Processing family of features. It has been introduced with SQL Server 2017 (as a part of the Adaptive Query Processing). It is designed to improve the performance of queries referencing multi-statement table-valued functions (MSTVF). Actually, it addresses currently only queries using MSTVF, but is hopefully designed for much more. The query optimizer usually has two issues with queries using MSTVF:
– MSTVF is a black-box for the optimizer; it does not know what’s inside, it cannot perform cross-statement optimization (as it is a case with inline TVFs) and it assumes it is a cheap and fast operation
– MSTVF has a fixed cardinality of 100 (prior to SQL Server 2014, it was 1)Interleaved execution does not improve the first issue (MSTVF is still a black-box for the optimizer), but solves the cardinality issue.
Read on to understand how this second aspect has changed for the better.
Comments closedJohn Mount has a public service announcement:
In a lot of our R writing we casually say “install from CRAN using
install.packages('PKGNAME')” or “update your packages by usingupdate.packages(ask = FALSE, checkBuilt = TRUE)(and answering ‘no’ to all questions about compiling).”We recently became aware that for some users this isn’t complete advice.
The above depends on your R install pointing to a repository that is in fact up to date. To check what repositories you are using please use the command
options('repos').
The specific example here is around the Microsoft R Archive Network (MRAN), which stays at fixed dates. This is for a good reason: because it helps companies standardize on a known set of versions of R packages by default. That way you don’t have version 1.8 of a package in dev and then get 1.9 in production and find out that something broke between the two versions.
Comments closedErin Stellato gives us a moment of zen:
A question I’ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level the database needed in order to use Query Store.
The quick answer: it doesn’t matter.
Read on for a demonstration.
Comments closedThe Hadoop in Real World group takes us through schema changes in Apache Kafka:
Meetup.com went live with this new way of distributing RSVPs – that is through Kafka. Both the producer and consumer agrees on the Schema and everything is great. It is silly to think that the schema would stay like that forever. Let’s say meetup.com didn’t feel the value in providing member_id field and removes it. What do you think will happen – will it affect consumers?
member_id field doesn’t have a default value and it is considered a required column so this change will affect the consumers. When a producer removes a required field, the consumer will see an error something like below –
Caused by: org.apache.kafka.common.errors.SerializationException: Error deserializing Avro message for id 63
Caused by: org.apache.avro.AvroTypeException: found com.hirw.kafkaschemaregistry.producer.Rsvp,
expecting com.hirw.kafkaschemaregistry.producer.Rsvp, missing required field member_id
This is an interesting review of the schema registry in Kafka and what the different modes allow for.
Comments closedJohn Sterrett explains how you can find prior SQL Server updates:
Back in the day when we were young but not a kid anymore there were service packs and cumulative updates. We could download these separately and all of the updates were easy to find. Now today, if you click on a KB article to download an update you get pointed to the latest update as shown below.
But there is a method, as John shows us.
Comments closedMax Trinidad has a nice update for us:
The moment everyone has been waiting for some time is here, PowerShell Release Candidate is available for download. This a “Go Live” release officially supported in production by Microsoft.
Everyone in the Microsoft PowerShell Team, with the help of the community, has done an excellent job with the evolution of this new version of PowerShell. Read all about it on the PowerShell DevBlogs recent post “Announcing the PowerShell 7.0 Release Candidate“.
Make sure to read all previous posts as they perfectly outlined under the “Why is PowerShell 7 so awesome?” section of the release candidate post.
Click through for more details. One of the nice things in this RC is a consistent Out-Gridview experience, so it’s not just for Windows anymore.
Adrian Buckman announces version 2.0 of Undercover Inspector:
There is a new setting in the Settings table called ‘ReportDataDetailedSummary’ this setting is on or off (0 or 1) and will control the level of detail logged in the summary column. When set to a 1 you will get granular detail of Warning/Advisory counts per server per module, setting this setting to 0 will return it back to the original way of logging which was to summarize the entire report into Warning count and advisory count.
There are a lot of changes in here.
Comments closedPaul Randal walks through an installation error with SQL Server 2019:
I have a brand new Windows 10 laptop that I use solely for teaching, as the HDMI and SVGA connectors on my main laptop somehow have bad connections to the motherboard after a few years of traveling around the world. On the new laptop I have SQL Server 2017, and now that SQL Server 2019 RTM has shipped, and I’ve finished teaching for the year, I set out to install 2019 side-by-side with 2017 so I can move my teaching environment to 2019 and work on demos of the new features pertinent to what I teach.
Thinking this would be a smooth process, I kicked off the install wizard, went through it, hit go, and walked away. I came back half an hour later to see that all install steps had failed.
Read on for Paul’s solution.
Comments closedAsad Khan announces the general availability of SQL Server 2019:
As you saw from our launch announcement earlier today, over a year ago at Microsoft Ignite we announced our first preview of SQL Server 2019 and today our latest release is now generally available.
You have told us that in today’s demanding world of massive data, wide variety of data sources, and expectations of near real-time application and query performance you need more than just a database engine. You need a modern data platform.
If you’re an early adopter, note that there is a patch for it already.
Comments closed