A Fine Slice Of SQL Server
Chrissy LeMaire has a few more breaking changes for us:
It’s been a busy couple days! Here’s a list of our breaking changes
Invoke-Sqlcmd2 has been removed and replaced with a warning to use Invoke-DbaQuery
The NetworkShare parameter has been renamed to SharedPath
UseLastBackups has been renamed to UseLastBackup
There are also several new commands as well, so check them out.
Comments closedGinger Grant continues her series on Analysis Services Tabular best practices:
Optimize your DAX Code
While it is not easy to performance tune DAX you can do it, by evaluating the DAX Query Plan and VeritPaq Queries, and SQLBI’s VertiPaq Analyzer. Also, you can also look to use functions which perform better, for example COUNTROWS instead of DISTINCTCOUNT or ADDCOLUMNS instead of SUMMARIZE. Whenever possible use the CALCULATE function instead of the FILTER function, as CALCULATE filters for context inside the parenthesis and are more efficient. Also all of the iterative functions SUMX, COUNTX etc., should be used sparingly as the row-by-row transactions they create are less efficient and should be used only when SUM or COUNT will not work. When evaluating if a value missing, if it is possible, use ISEMPTY instead of ISBLANK as ISEMPTY looks only for the presence of a row, which is faster than the evaluation performed by ISBLANK.
Read on for several more items in this vein.
Comments closedNed Otter gives us two things to look forward to with SQL Server 2019:
So far, there’s been only one publicly announced enhancement for In-Memory OLTP in SQL 2019: system tables in TempDB will be “Hekatonized”. This will forever solve the issue of system table contention in TempDB, which is a fantastic use of Hekaton. I’m told it will be “opt in”, so you can use this enhancement if you want to, but you can also back out of it, which would require a restart of the SQL Server service.
But there’s at least one other enhancement that’s not been announced, although the details of its implementation are not yet known.
Read on to learn about this not-yet-announced update.
Comments closedHugo Kornelis takes a look at index spools:
The Index Spool operator is one of the four spool operators that SQL Server supports. It retains a copy of all data it reads in an indexed worktable (in tempdb), and can then later return subsets of these rows without having to call its child operators to produce them again.
The Index Spool operator is quite similar to Table Spool, except that Index Spool indexes its data, giving it the option to return a subset, and Index Spool lacks the option to read data from a spool created by another operator. The other two spool operators are quite different: Row Count Spool is optimized for specific cases where the rows to be returned are empty, and Window Spool is used to support the ROWS and RANGE specifications of windowing functions.
Eager and Lazy Spool operators rank high on my list of “troublesome when I see them” operators. The reason is not so much that eager or lazy spools are inherently bad—they’re not, as they are efficient ways to perform a particular query given the constraints of that query—but if I see one of them in conjunction with a slowly-performing query, it’s a good sign that I want to optimize away the need for spooling.
Comments closedChris Webb shows us how we can batch calls to M-driven web services:
One of the most common issues faced when calling web services in M is that the the easiest way of doing so – creating a function that calls the web service, then calling the function once per row in a table using the Invoke Custom Function button – is very inefficient. It’s a much better idea to batch up calls to a web service, if the web service supports this, but doing this forces you to write more complex M code. It’s a problem I wrestled with last year in my custom connector for the Cognitive Services API, and in that case I opted to create functions that can be passed lists instead (see here for more information on how functions with parameters of type list work); I’m sure the developers working on the new AI features in dataflows had to deal with the same problem. This problem is not limited to web services either: calculations such as running totals also need to be optimised in the same way if they are to perform well in M. The good news is that there is a new M function Function.ScalarVector() that allows you to create functions that combine the ease-of-use of row-by-row requests with the efficiency of batch requests.
As Chris notes in the post and in the comments, this is mostly useful when you can batch together individual calls to improve performance. For functions which operate serially (like opening Excel workbooks), you won’t see much (if any) gain.
Comments closedJason Brimhall shows how you can create an extended event to track whenever databases go offline:
The other day, I shared an article showing how to audit database offline events via the default trace. Today, I will show an easier method to both audit and monitor for offline events. What is the difference between audit and monitor? It largely depends on your implementation, but I generally consider an audit as something you do after the fact. Monitor is a little more proactive.
Hopefully, a database being taken offline is a known event and not a surprise. Occasionally there are gremlins, in the form of users with too many permissions, that tend to do very strange things to databases and database servers.
Click through for the script.
Comments closedGerhard Brueckl has a Powershell module for interacting with Databricks, either Azure or AWS:
As most of our deployments use PowerShell I wrote some cmdlets to easily work with the Databricks API in my scripts. These included managing clusters (create, start, stop, …), deploying content/notebooks, adding secrets, executing jobs/notebooks, etc. After some time I ended up having 20+ single scripts which was not really maintainable any more. So I packed them into a PowerShell module and also published it to the PowerShell Gallery (https://www.powershellgallery.com/packages/DatabricksPS) for everyone to use!
This looks like a pretty good module if you work with Databricks.
Comments closedDinakar Nethi announces a new public preview of SQL Server Management Studio 18:
We are very excited to announce the release of Public Preview 5 of SQL Server Management Studio (SSMS) 18.0. This release has a number of new features and capabilities and several bug fixes across SQL Server Management Objects (SMO), UI, etc.
You can download SSMS 18.0 Public Preview 5 here.
The most interesting thing in it for me is probably the menu item for CREATE OR ALTER with scripts.
Joey D’Antoni points out a potential big performance problem with clustered columnstore indexes:
In the last year or so, with a large customer who makes fairly heavy use of this pattern, I’ve noticed another concern. Sometimes, and I can’t figure out what exactly triggers it, the execution plan generated, will do a seek against the nonclustered index and then do a key lookup against the columnstore as seen below. This is bad for two reasons–first the key lookup is super expensive, and generally columnstores are very large, secondly this key lookup is in row execution mode rather than batch and drops the rest of the execution plan into row mode, thus slowing the query down even further.
Joey also has a UserVoice item as well, so check it out.
Comments closedMax Trinidad shows us how to grab the latest version of Powershell Core if you aren’t using Windows:
So, if PowerShell Core isn’t available in the package repository, with a few steps you can download and install PowerShell. But, the first thing I do is to remove it before installing.
Ubuntu
## - When PowerShell Core isn't available in their repository: (download and execute install) cd Downloads wget https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell_6.1.1-1.ubuntu.18.04_amd64.deb sudo dpkg -i powershell_6.1.1-1.ubuntu.18.04_amd64.deb ## - When available in Apt/Apt-Get repository: sudo apt install -y powershell #-> Or, powershell-preview
Click through for demos of CentOS (or any other yum-based system) and MacOS X.
Comments closed