A Fine Slice Of SQL Server
Wolfgang Strasser looks at a new feature in Azure Purview:
There was one big thing missing so far – the scanning of SQL Server / Azure SQL Views. Which – well – in many cases (customer databases) was a huge loss of information in the data catalog.
This known limitation is listed on the documentation page but many of us overread this sentence.
But check out Wolfgang’s most recent finding and it’s clear that the team is working on it.
Comments closedJack Vamvas covers the SORT_IN_TEMPDB flag for index operations:
Question:There is a SORT_IN_TEMPDB option for SQL Server index create and rebuilds . Is it possible to exploit this option to improve the SQL Server index rebuild performance?
There can be some reasons to do this, particularly if tempdb is on much faster storage than your database files, but read the whole thing for caveats.
Comments closedJoey D’Antoni gives us a primer on database documentation techniques:
Although writing better queries and building the right indexes are important parts of improving database performance, building clear database documentation can also contribute to this goal by helping you understand your database architecture. Painting a clear picture of the structure of your database gives you insight into your data flows and helps you identify redundant data and clarify business processes.
Let’s take a look at a few approaches you can take to documenting your database and your data, depending on the nature of your application.
I do like the idea of using extended properties for documentation purposes, though for the longest time, the tooling to show those extended properties was fairly limited and it was easy to miss them on scripting.
Comments closedSean Gallardy wins the jackpot:
If, somehow, you’ve managed to see this error in your errorlog then congratulations, you’ve won an instance of SQL Server that probably won’t be doing much.
I found out about this message a few months ago, but it has been in the product for years and I went this long without ever even knowing it existed (congrats me!) until I was asked about it and coincidentally ended up finding it in an errorlog the same week. Clearly, I have too much fun packed into my weeks. I asked around, only one other person had ever found this in an errorlog before… that’s either impressive, depressing, or some perfect quantity of both – mellow it out to a smooth melancholy.
Click through to see more information about the 1000000 IO error message and when you might find it.
Comments closedMax Trinidad can keep a secret:
Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.
Read on for a walkthrough and demonstration.
Comments closedLine 3 is a named parameter called $badParam. This becomes important a little later. Lines 4, 5, 6, and 7 are named parameters. Now, how can we enforce that someone uses those named parameters.
Read on for an interesting technique to ensure that your callers are using named parameter rather than positional parameter calls.
Comments closedThe Hadoop in Real World team shows how to deduplicate rows in a DataFrame in Spark:
It is a pretty common use case to find the list of duplicate elements or rows in a Spark DataFrame and it is very easy to do with a groupBy() and a count()
Where “easy” has as a modifier just how many columns you’re dealing with in the DataFrame.
Comments closedAjit Chelat compares two popular log agents:
Log collectors, or aggregators, are critical aspects of the log management infrastructure. They help collect logs from various systems and parse and groom them for ingestion into a monitoring or observability tool for further visualization and analysis. DevOps and SRE teams are quickly adding log collectors to their toolchain. With millions of users across domains, two log collectors have risen to the forefront of log collection—Fluentd and Logstash.
This article compares the two and sees which one is the best for your log management and analysis initiatives—Fluentd vs. Logstash.
Click through for the round-by-round comparison and see which one comes out on top in your scenario.
Comments closedGarry Bargsley has a solution to an annoyance:
Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence. This behavior causes a bloated Error Log that can make it hard to find what you need quickly.
Luckily, that same SQL Server team built in a solution to this situation.
Read on to see what the solution is, as well as how to use it.
Comments closedKevin Chant uses the GitHub CLI:
In this post I want to share some advice about using GitHub CLI with GitHub Actions for Data Platform deployments. Because I showed that at SQLDay last week.
For those who were not aware, there is a GitHub CLI you can use from the command line. You can download GitHub CLI from here.
Anyway, GitHub CLI was recently updated to support commands for GitHub Actions. GitHub Actions is the CI/CD mechanism that is now available in GitHub. Which I have covered in a few other posts, including the one you can find by clicking here.
Click through to learn more.
Comments closed