A Fine Slice Of SQL Server
Tracy Boggiano asked about Query Store and got some good responses:
Happy St. Patrick’s Day (are you wearing something green)! For March I asked everyone to blog something related to Query Store, here is a summary of in alphabetical order of the blog posts we received although Rob Farley gets credit for being first.
To be fair, Rob does live in the future.
Comments closedJamie Wick takes us through the less-trodden path:
SQL Server containers are gaining popularity as a way of enhancing and standardizing development environments for Windows & Linux based SQL databases. SQL containers allow developers to have their ‘own’ dedicated copy of a database, usually without the need for extensive server infrastructures. Additionally, a single computer can host multiple containers, each with a different edition/version of SQL Server. This allows the user to quickly switch between environments, without the need to reinstall. Currently, a popular option for implementing containers on Windows-based computers uses Docker.
For those not familiar with containerization, here is a Microsoft article on Windows containers.
I’d definitely prefer to use Linux containers, even on Windows machines. But if Windows-based containers is your thing (or you need to use them for some reason), Jamie’s got you covered.
Comments closedErik Darling explains why index tuning is typically not a one-shot process:
For many people, index tuning means occasionally adding an index when there’s a report about a slow query. Those indexes might come from a query plan, or from the missing index DMVs, where SQL Server stores every complaint the optimizer files when it thinks an index might make a query better.
Sure, there are some people who think index tuning means rebuilding indexes or running DTA and checking all the boxes, but I ban those IP addresses.
The prudent choice, of course. Read on for Erik’s thoughts on the subject, which are quite good.
Comments closedCode repository service GitHub is in the process of acquiring the preeminent software registry for Node.js and JavaScript modules, npm, the two companies announced Monday.
GitHub plans to invest in npm’s infrastructure, with the hopes of bringing some much-needed modernization to the platform, GitHub CEO Nat Friedman promised in a blog post. It also may help free the rapidly-growing registry from the considerable financial and personnel turmoil that it has been inflicted with over the past few years.
I for one welcome leftpad by Microsoft.
Comments closedGuy Shilo shows how you can back up an Elasticsearch cluster:
Elasticsearch is facing the same challenge and it’s built-in backup method is snapshots. Unlike classic storage snapshots, Elasticsearch snapshot can be stored remotely on external storage systems, and that is supposed to enable them deal with large amounts of data.
Snapshots can be stored on a shared file system (mounted on all cluster nodes), on all major cloud storage providers (Amazon S3, Azure and GCS) and on HDFS.
The documentation can be found here.
Read on to see the demo. Even if your Elasticsearch data is not the home of record and you could rebuild the cluster doesn’t mean ignoring backups is wise.
Comments closedDavid Klee offers some advice on Dynamic Resource Scheduling:
Dynamic Resource Scheduling (DRS) should be enabled for all VMware host clusters, especially those who run SQL Server. It provides for resource consumption load balancing functionality into a host cluster. Consider enabling the DRS load balance based on consumed memory rather than active memory (available as of vSphere 6.7), as the active memory counter for SQL Server-based VMs is not a true representation of memory usage by the SQL Server layer.
Read on for more details.
Comments closedI have a post documenting a bug in SQL Server 2019:
Here’s the short version of the bug. If you are connected using a Windows authenticated account and attempt to perform a PolyBase-related action, such as creating an external data source or querying from an external table, you receive the following error:
Msg 46721, Level 20, State 1, Line 5
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.Because this is an error with a severity level of 20, it kills your session.
Click through for the workaround. I had hoped that this would have been fixed with CU3, but it’s still in there.
Comments closedPaul White does some amazing things with T-SQL, news at 11:
I will be looking for distinct values in the
BountyAmountcolumn of thedbo.Votestable, presented in bounty amount order ascending. The Votes table has just under 53 million rows (52,928,720 to be exact). There are just 19 different bounty amounts, includingNULL.The Stack Overflow 2013 database comes without nonclustered indexes to minimize download time. There is a clustered primary key index on the
Idcolumn of thedbo.Votestable. It comes set to SQL Server 2008 compatibility (level 100), but we will start with a more modern setting of SQL Server 2017 (level 140):
Getting the query down from 10.5 seconds to 1ms is crazy.
Comments closedPaul Andrew has a new name for the metadata-driven framework in Azure Data Factory:
Hi data friends! Version 1.1 of my ADF.procfwk is ready!
Following the great response I got to the version 1.0 blog series I decided to push ahead and get version 1.1 out there as soon as possible. The main thing I’ve addressed with this release is the big problem of having hard coded service principal credentials in the body of the Azure Function request that called our execution pipelines. Hopefully we can all agree this was/is not great for a number of reasons.
Read on for more details including a detailed changelog, and check out the GitHub repo.
Comments closedWhy is this important? When you’re tuning the performance of a Power BI report the first thing to do is to look at the performance of the individual DAX queries run and make them run as fast as possible. However when a Power BI report is rendered any one query is likely to be run at the same time as several other queries run for the same report, and this will have an impact on its performance. How much of an impact there is will depend on how many queries need to be run and the number of back-end v-cores available on your Premium capacity, or the number of QPUs available on your Azure Analysis Services instance if you’re using a Live connection to AAS. The more v-cores/QPUs you have available, the more of the work needed for a query that can be run in parallel; you can see a table listing the number of v-cores for each Premium SKU here, and the number of QPUs for each Azure Analysis Services SKU here. As a result of this if you have reports with a large number of visuals that generate slow DAX queries, scaling up your Power BI Premium capacity or AAS instance may improve overall report performance. Reducing the number of visuals on your report and/or reducing the number of visuals needed to display the same information will also reduce the number of queries that need to be run and therefore improve overall performance.
Read on for the demo.
Comments closed