A Fine Slice Of SQL Server
Slava Murygin has some information on the DBCC UPDATEUSAGE command:
As you can see, UPDATEUSAGE found three problems with that table, while was running in “ESTIMATEONLY” mode. When it run in “Fix” mode it also found and fixed only three problems. The number of rows was still left unfixed.
I think it’s fair to say that this is a relatively uncommonly-used DBCC command, but can definitely be useful in a subset of circumstances.
Comments closedAaron Bertrand walks through display differences between SQL Sentry Plan Explorer and SSMS:
Now, in the process of developing Plan Explorer, we have discovered several cases where ShowPlan doesn’t quite get its math correct. The most obvious example is percentages adding up to over 100%; we get this right in cases where SSMS is ridiculously off (I see this less often today than I used to, but it still happens).
Interpreting execution plans is not a trivial exercise, and this is an interesting look at how SQL Sentry developers (and supporters within the broader community) have worked on it through the years.
Comments closedSteve Jones warns us away from Write-Host in Powershell:
I’ve written a few scripts and programs lately, mostly just for fun. In those scripts, I’ve used Write-Host to return output. To me, it’s been like “Print” in various languages where I can get output of a program. Often I’ll use a method/function to get info and then use print to output that to the caller.
However a few people noted that in my last script, Write-host wasn’t necessary. When I asked why, both Mike Fal and Drew Furgiule responded.
Mike and Drew are smart cookies. Write-Host has some major limitations which hinder developers’ abilities to modularize and package viable code.
Comments closedThere is a very usable support for Columnstore Indexes within the temporary objects, but they are not appearing in any of the DMV’s to be analysed or optimised. This is especially sad in the relation to the global temporary tables which are some of the more useful temporary objects.
For the most part, I’d consider these reasonable results. Hopefully we can get columnstore stats on temp tables, but even that’s not a huge loss.
Comments closedRob Sewell notes that Azure blob storage container names are case-sensitive:
If you use SQL Backup to URL to backup your databases to Azure blob storage remember that for the container name case is important
Despite all of my Linux love and C-based language tolerance, case sensitivity for filenames and development languages is a relic of a barbaric past. Nevertheless, that’s the minefield we must traverse.
Comments closedThe SQL Server development team was able to remove the PInvoke and PUnInvoke activities during T-SQL execution for many of the spatial methods. A critical aspect of the change is that the change is fully compatible across the server and client scenarios.
The same source code is used to build the managed C++ implementation and the unmanaged C++ implementation. At the risk of understating this work the C++ managed code can be compiled with the C++ /CLI compiler, creating the managed assembly and a few, cleaver templates and macros bridge the native variations allowing C++ native compilation. Any change to the code is made in one source file and built in two different ways.
They also have a couple of demos and point out that if you’re using a spatial index appropriately, the performance benefit from switching to 2016 is upwards of 3x. A 3x performance improvement with no code changes is nothing to sneeze at.
Comments closedRichie Lee shows off Get-TimeSpan and New-TimeSpan:
As is the case with most things, when I find a way for getting something done in a script that is “good enough”, I’ll tend to stick with that method until that method no longer becomes fit for purpose. One such method is printing out the time that something took in PowerShell: many of the scripts on my site use this method to get the duration of a task, and I’ve been using this since PowerShell 1.0
This is certainly an improvement over the old version.
Comments closedDavid Klee discusses an important hypervisor-level metric:
VMware’s CPU Co-Stop metric shows you the amount of time that a parallelized request spends trying to line up the vCPU schedulers for the simultaneous execution of a task on multiple vCPUs. It’s measured in milliseconds spent in the queue per vCPU per polling interval. Higher is bad. Very bad. The operating system is constantly reviewing the running processes, and checking their runtime states. It can detect that a CPU isn’t keeping up with the others, and might actually flag a CPU is actually BAD if it can’t keep up and the difference is too great.
This is extremely useful information for DBAs in virtualized environments. My crude and overly simplistic answer is, don’t over-book vCPUs on hosts running important VMs like your SQL Server instances.
Comments closedReza Rad notes that there’s a new String_Split function in SQL Server 2016:
The new String_Split function is a table function which has two inputs:
String_Split(<main string as input>,<delimiter>)
Usage of it should be within From clause of your query because this is a table function.
I’m curious to see how this compares performance-wise to CLR and tally table split methods.
Comments closedKendra Little discusses having a reusable event logging tool for your database work:
You can’t, and shouldn’t log everything, because logging events can slow you down. And you shouldn’t always log to a database, either– you can keep logs in the application tier as well, no argument here.
But most applications periodically do ‘heavy’ or batch database work. And when those things happen, it can make a lot of sense to log to the database. That’s where this logging comes in.
Bonus points if you feed this kind of logging into Splunk (or your logging analysis tool of choice) and integrate it with application-level logging.
Comments closed