A Fine Slice Of SQL Server
Thomas Rushton shows how to send an e-mail with the outputs of a SQL Agent job:
I came across a need for SQL Server Agent job output to be emailed out to the developer who created the code that the job was running. For various reasons, they don’t necessarily have access to the SQL Server itself – hurrah – but need to see what’s being generated by their scripts in case something goes wrong.
So, I ended up writing this extremely rough and ready SP
Rough and ready, but it works.
Comments closedAlan Yu announces the most recent set of changes around Azure Data Studio:
The notebooks viewlet in Azure Data Studio now includes a dynamic search experience. When you are dealing with hundreds of notebooks, it can be tricky navigating and finding the notebook you need. With this experience, we make it faster to search through notebook content.
Once notebooks are listed in the Notebooks viewlet, users can easily search for content across all notebooks and see how many instances the search term appears in a certain notebook. You can then interact with the notebook.
It’s a notebook-heavy month.
Comments closedRishit Mishra walks us through Azure Key Vault:
As the name suggests, Azure Key Vault is used to store and manage keys securely. Key Vault can be used to store the cryptographic secrets and keys such as authentication keys, storage account keys, data encryption keys, passwords and certificates.
Azure Key Vault enables developers to create the keys for development and testing in minutes, and they can further migrate this setup seamlessly onto the production environment.
The centralized key store/vault can be securely managed by the Key Vault owner who manages permissions to this key store and would be responsible for keeping the secrets secure.
Key Vault becomes quite useful in managing secrets in tools like Azure Databricks and Azure Data Factory without saving a bunch of keys in configuration files. And it’s a lot safer than that option, too.
Comments closedKasper de Jonge works around a limitation with the Power BI UI and calculation groups:
AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).
You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.
But Kasper has us covered with a bit of DAX, so check that out.
Comments closedJoe Obbish sets up a challenge:
48 billion rows for a single operator is certainly a large number for most workloads. I ended up completely missing the point and started wondering how quickly a query could process a trillion rows through a single operator. Eventually that led to a SQL performance challenge: what is the fastest that you can get an actual plan with at least one operator processing a trillion rows? The following rules are in play:
1. Start with no user databases
2. Any query can run up to MAXDOP 8
3. Temp tables may be created and populated but all such work needs to finish in 30 seconds or less
4. A RECOMPILE query hint must be present in the final query
5. Undocumented features and behavior are fair game
Read on to see what Joe learned.
Comments closedMonica Rathbun gives an important lesson around tracking index utilization in Azure SQL Database:
If the index has not shown any utilization I investigate to determine if it is one that can be removed. However, this week something caught my attention. I was looking at a client’s indexes and noted the values for these were not as high as I would have expected. I know that these index statistics are reset upon every SQL Server Service restart, but in this case, I was working on an Azure SQL Database. which got me wondering exactly how that worked. With an Azure Virtual Machine or an on Prem SQL Server instance this is easy to figure out. But with an Azure SQL Database we do not have control over when restarts are done, and what about the Serverless offering (which pauses unutilized databases to reduce costs), how do those behave? I really want to make sure before I remove any indexes from a database that I am examining the best data possible to make that decision. So, I did some digging.
Read on to see what Monica discovered.
Comments closedHarini Guptha walks us through some of what’s upcoming in Python 3.9:
The latest beta preview python 3.9.0b5 is released on July 20th, 2020. Yes, you heard it right. This is the last of the five planned beta release previews. This beta release gives an opportunity to the community to test the new features.
If you are planning to go for a python certification, make sure that you check out what functionalities are deprecated and what features are newly added in this release. Its always good to get yourself updated with the latest information. Without further ado, let’s go through the new features of Python 3.9.0b5.
Click through for the list.
Comments closedPaul Randal is offering up mentorship time:
If I remember correctly, I think I helped 8 or more people decide to change jobs for a better work environment suited to their goals, and several people go it alone as consultants. It was hugely satisfying to help so many people with their careers and lives, in a non-technical capacity.
Now it’s time to do it again, as I haven’t done any public mentoring since 2015, so this blog post serves as a call for prospective mentees!
Please read the rest of this post carefully, so you’re clear how this works. We’re making a time commitment to each other so I want to be up-front about a few things.
I was one of those 54 mentees back in 2015 and can recommend it. I will say, though, that you get out of it exactly what you put in—this isn’t some “I want to advance my career” easy mode.
Comments closedBrent Ozar uses most of Resource Governor:
You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are.
To solve this problem, we’re going to use Resource Governor.
Wait. Come back.
I’ve always liked the idea behind Resource Governor and since about SQL Server 2016, it has been quite a useful product because “make some queries slow down” can absolutely be the right answer when those queries are harming the performance of queries which matter more.
Comments closedGarry Bargsley points out the importance of a tiny flag:
I put on my investigator hat and begin looking around. I started with the Windows File Server to see what was actually on the drive in question. Just as I thought, three SQL backup files were in there proper folder. Although there were only three files, something else caught my attention. One backup file was 800GB and another 1TB. That was strange as I don’t think the source databases are that big. Sure enough, I look and one database is 60GB and the other is 45GB.
Something is not right here!! So, next, I run a RESTORE HEADERONLY against one of the backup files. What did I see?
Read on to learn what Garry saw, and then what Garry didn’t see.
Comments closed