SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but...only if the data isn't changing. But of course your data is changing - that's the whole point of doing these operations as resumable. If they weren't changing, we could just let the operations finish.
Snow Is Temporary
Cumulative Update 5 dropped for SQL Server 2017. Hooray.
Poor Taryn.
SQL Server Management Studio 17.5 is out, and new in this release is a SQL Data Discovery & Classification feature. The idea is that it'll scan your database, identify columns containing potentially sensitive data, and help you become compliant with regulations like PCI, HIPAA, and GDPR.
Let's see how it works on the Stack Overflow public data dump.
Row Whats?
Like it or not, the optimizer has row goals.
We're not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I'll be adding it to the FRK in an upcoming release.
Where have you been all my life?
Whenever I'm teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.
They're not mutually exclusive -- some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.
SQL Server 2017's new CXCONSUMER wait type was originally announced by Microsoft's Pedro Lopes, and now it's out. Here's what it means for performance tuners. According to Pedro, this wait is the "safe" type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn't evenly balanced across all of our cores. Pedro blogged…
Update
This seems to finally be fixed as of CU12 for SQL Server 2017. If you're not already patched up, head over to the link to get there.
Estimated Plans Need Not Apply
This is, of course, only available in actual plans.
All together now:
The Meltdown and Spectre attacks are newly announced ways of hacking CPUs. They affect both Windows and Linux. If you're running under virtualization or cloud IaaS, any other running guest can try to leverage the attacks to compromise your unrelated guest.
These are a really big deal.
Starting with yesterday's release of SQL Server 2017, Microsoft has a new servicing model: they're only delivering Cumulative Updates, and not doing Service Packs.
Pedro Lopes writes:
I'm only showing the English (1033) ones here, message ID and then text. Some of the new messages are very Azure-specific, and while I love to think that we'd get some of these features in the boxed product, I think Microsoft is just getting down to a standard shared list of message IDs.
However, some of them are rather...well, I'll just bold the ones that made me raise an eyebrow:
SQL Server 2017 is officially out, and here's what I've found so far. New Wait Stats in 2017 BACKUP_INMEM_DIFFLIST_READ_ACCESS BACKUP_INMEM_DIFFLIST_WRITE_ACCESS BLOB_CONTAINER_TABLE COLUMNSTORE_MIGRATION_BACKGROUND_TASK EXTERNAL_SCRIPT_NETWORK_IO FOREIGN_REDO GHOSTCLEANUP_UPDATE_STATS GLOBAL_QUERY_CANCEL GLOBAL_QUERY_CONSUMER GLOBAL_QUERY_PRODUCER HADR_THROTTLE_LOG_RATE_LOG_SIZE HADR_THROTTLE_LOG_RATE_SEEDING HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE MEMORY_GRANT_UPDATE MIGRATIONBUFFER PREEMPTIVE_OS_BCRYPTIMPORTKEY PREEMPTIVE_OS_NCRYPTIMPORTKEY PREEMPTIVE_XE_PROXY_ADDSESSION PREEMPTIVE_XE_PROXY_PROCESSBUFFER PREEMPTIVE_XE_PROXY_REMOVESESSION PREEMPTIVE_XE_PROXY_SESSIONCOMMIT PWAIT_AUDIT_CLOSE_EXPIRED_LOGS_MGR_RWLOCK PWAIT_AUDIT_SESSIONS_LOCK PWAIT_EXTERNAL_SCRIPT_LIBMGMT_DIR_PERMS PWAIT_PREEMPTIVE_APP_USAGE_TIMER PWAIT_PREEMPTIVE_XE_STUB_LISTENER PWAIT_SBS_FILE_OPERATION QDS_HOST_INIT RBIO_WAIT_VLF REMOTE_BLOCK_IO RESTORE_MSDA_THREAD_BARRIER SBS_DISPATCH SBS_RECEIVE_TRANSPORT SBS_TRANSPORT SECURITY_CNG_PROVIDER_MUTEX SLEEP_SETUP SNI_WRITE_ASYNC…
It's like an unboxing, but with setup.exe. For the vast majority of setup, it's the same as 2016:
And you're done! Time to reboot, then start querying DMVs looking for surprise gifts.
File this under...
Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up... well, I caught it last night. I probably could have written this sooner, huh?
So what's new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I've blogged about, and some... I have no idea what they are.
When Windows Server 2012 came out with Core, I heard some rather suspicious things at conferences like:
"I'll be able to take way less patching outages!"
"It'll be so much faster because it has less overhead!"
"Everyone will learn automation and be more powerful!"
"It's the way of the future! Learn it or your career is doomed!"
Yazoo
Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn't need, like skipping over the dialog in, uh... movies with really good fight scenes.
Car chases?
You still won't use them
And I predict that Extended Events will remain as niche-only use for specific troubleshooting scenarios. Very few monitoring tools use them, and the ones that do still rely on traces and DMV queries for the bulk of their collection. That's not a good sign, in 2017, considering how long Extended Events have been around.
Pokin'
SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command, with some filtering.
I think I've mentioned that the most fun part of new features is testing them with old ideas to see how they react.
It occurred to me that if Adaptive Joins didn't work with APPLY, I might cry.
If you're not aware of the performance problems scalar valued functions can (and often do) cause, well, uh... click here. We'll talk in a few days.
If you are, and you're worried about them crapping on Adaptive Joins, follow along.
But is it faster?
Now, I know. There are very few "always" things out there in SQL Server. This is also true for functions.
A lot of the time -- I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.
