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 closedAndy Levy saves us all several characters at a time:
Every now and then, we encounter data that needs to be cleaned up because it’s got leading and/or trailing spaces. Or maybe you’re storing short data in a
CHAR(N)field, so when you query it, you’re getting trailing spaces. For time immemorial, we’ve had to wrap these fields inrtrim(ltrim(fieldname))to do the deed.Effective with SQL Server 2017, that’s no longer the case.
The eight keystrokes add up over time. In all seriousness, I am happy that TRIM() is a thing in SQL Server 2017. And Andy gives us a little bonus to make it worth your refactoring while.
Erik 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 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 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 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 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 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 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 closedTejas Shah announces a couple new versions of Linux distributions SQL Server 2019 supports:
We are also glad to announce the availability of SQL Server 2019 CU3 container image build on Ubuntu 18.04. It is fully supported for production use. You can read about how to deploy the container at following page.
Running SQL Server 2019 CU3 container with Ubuntu 18.04 base: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&p…
You could run SQL Server on these versions of Ubuntu and SUSE, but now you get production support, making it prudent to run on these. Ubuntu 20.4 is coming out soon, so we’ll see when that gets supported.
Comments closed