A Fine Slice Of SQL Server
Jeff Hicks finds your terms acceptable:
I just saw a very, very handy thing on Twitter where you can set a registry key that will automatically accept all EULA prompts for the SysInternals tools. I know there is a command-line switch I can use, but I never remember to use it. Setting the registry key appears to accept EULAs globally. Naturally, I wanted a PowerShell script I could run.
Read on for that script.
Comments closedNiko Neugebauer reports on some performance improvements:
There are multiple phases of the SQL MI database restore process, which, aside from the usual SQL Server restore operations, includes registering databases as an Azure asset for visibility & management within the Azure Portal. Additionally, the restore process forces a number of specific internal options, and some property changes such as forcing the switch to the FULL recovery model and forcing the database option PAGE_VERIFY to CHECKSUM, as well as eventually performing a full backup to start the log chain and provide full point-in-time- restore options through the combination of full and log database backups.
The restore operation on SQL MI also includes log truncation, and the execution time for the truncation has been vastly improved, which means that customers can expect their entire database restore process to become faster on both service tiers.
Click through to see what kind of performance gains you can expect.
Comments closedGilbert Quevauvilliers provides access:
I was recently doing some testing and when I went to share a Power BI report with an external (B2B) user I could not initially find out how to do this.
NOTE: I had already created my External (B2B) users, you can follow this guide below if you still need to create the external (B2B) users: Distribute content to external guest users with Azure AD B2B – Power BI | Microsoft Docs
I thought it would be good to share the steps on how I achieved this below.
Read on to see how. Also check out the insightful question in the comments section.
Comments closedMax Fisher takes us through an important feature for Spark streaming:
When building real-time pipelines, one of the realities that teams have to work with is that distributed data ingestion is inherently unordered. Additionally, in the context of stateful streaming operations, teams need to be able to properly track event time progress in the stream of data they are ingesting for the proper calculation of time-window aggregations and other stateful operations. We can solve for all of this using Structured Streaming.
For example, let’s say we are a team working on building a pipeline to help our company do proactive maintenance on our mining machines that we lease to our customers. These machines always need to be running in top condition so we monitor them in real-time. We will need to perform stateful aggregations on the streaming data to understand and identify problems in the machines.
This is where we need to leverage Structured Streaming and Watermarking to produce the necessary stateful aggregations that will help inform decisions around predictive maintenance and more for these machines.
Read on to see how watermarking works in various scenarios, including when you join together streams.
Comments closedShoham Dasgupta announces a new preview program:
Transparent data encryption (TDE) in Azure SQL Database and Managed Instance helps protect against the threat of malicious offline activity by encrypting data at rest. TDE with Customer-Managed Key (CMK) enables Bring Your Own Key (BYOK) scenario for data protection at rest, by allowing a key stored in a customer-owned and customer-managed Azure Key Vault to be used as the TDE Protector on the server or managed instance.
When using TDE with Customer-Managed Key, one of the important responsibilities that customers need to perform on a regular basis is key rotation, that is, rotating the TDE Protector on the server by switching to a new key (or new version of the earlier key) from Azure Key Vault. Key rotation is a critical activity for an organization that is required to meet security and compliance objectives.
Automated key rotation for Azure SQL Database and Managed Instance is now available in preview, simplifying key management responsibilities for customers.
Click through to see how this works.
Comments closedDid you know that 1/2=0? If you didn’t you could be in for some interesting surprises.
1/2 returns 0 because 1 and 2 are both integers. When you divide two integers you get back an integer. Now, we, as human beings, know that what you probably wanted was to get back 0.5. Unfortunately, computers really aren’t all that smart and we have to be very explicit with them.
Read on for more details, as well as a chart of which conversions may be implicit and which must be explicit.
Comments closedEitan Blumin has a script for us:
Running the above script with the parameter @RCA set to 0 will output a list of the top query hashes (sorted based on their size in bytes so that you’d see the most impactful queries first) that have multiple different query plan hashes cached for them.
Running the script with the parameter @RCA set to 1 will output the same list, with additional columns that will help you do deeper root cause analysis.
Unlike the similar script provided by Brent, my script counts the number of query plan hashes rather than the query plan handles per each query hash. I found that this results in far fewer “false positives”. But you can still set the parameter @CountByPlanHandleInsteadOfPlanHash to 1 if you want it to count based on query plan handles instead.
Click through for the script, details on how it works, and various classes of reason why you might get to this outcome.
Comments closedStuart Ainsworth writes a script:
Just a quick blog; am working on a script that requires credentials to run against a REST API, and a developer wanted to convert that script to use command-line parameters. I built this script (and quick test) to show that the command-line parameters create the same object as the Get-Credential object.
The one thing that has me on edge a little is that the password comes in as plaintext. I do understand that sometimes this is the best route, though I’d put the pressure on devs to make sure there isn’t a route in which plaintext passwords get encrypted before transmission, even in a local environment like this.
Comments closedParameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens.
SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized the first three letters sometimes and not others, or why they only abbreviate it as “PSP optimization” – as long as we’re abbreviating, why not call it PSPO? Optimization is the word in there with the most syllables – why the hell would we leave that one as the one that isn’t abbreviated?
Because I make things go faster for a living, we’re gonna call it PSPO. You’ll see how I pronounce it later, and why.
Erik Darling has also spent a lot of time talking about this. And at this point, I don’t know that how many (if any) of Brent’s critiques get fixed before RTM.
Comments closedAndrea Allred runs into a weird issue on AWS RDS:
We tracked down every job that was touching the server and started to tune it, thinking that was just pushing us over the edge. We worked with AWS and finally one of our engineers noticed that our MAX Server Memory Setting was back at the SQL default. You know that insane default? Yes, it was there. But we had properly set that…three months ago when this stack was put in place.
Click through for the entire story, including symptoms and resolution.
Comments closed