A Fine Slice Of SQL Server
Meagan Longoria shares thoughts on a change to the Azure portal:
Once your Azure SQL resources are created, you can use the new centralized management hub to administer them. Locate the Azure SQL resources blade to see a list of all of your single databases, database servers, elastic pools, managed instances, and virtual machines running SQL.
If your subscription has a lot of resources, this can be a timesaver.
Comments closedRandolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later:
ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling ADR requires around 10% additional storage in your database file.
The reason for this post, however, is to mention that SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance.
Read on to see how you enable this.
Comments closedKenneth Fisher goes over one of the things in SQL Agent which make me shudder:
Occasionally I’ve seen date and time stored separately as integers. This had some practical applications back before we had date and time data types but there’s still lots of legacy code out there that use them (I’ll give you a really really common example in just a minute).
Unfortunately, you can’t convert datetime to date and time ints directly but it isn’t all that difficult.
Kenneth notes the function you can use as well as a quick query to calculate duration.
Comments closedSometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on.
I came across a situation recently while automating a process to refresh test environments where this exact scenario came up. As part of the process several scripts were run to obfuscate production data. While these ran all the UPDATE triggers were firing. Not only were the triggers adding a significant amount of time to the process, they were also updating dates and other values that we’d prefer kept their original values.
Jess has a tiny bit of Powershell code which does this work for you.
Comments closedStuart Moore shows us how we can lock Azure resources to prevent accidental mistakes:
A resource deletion may not sound like too much of a big thing if you’re deploying Infrastructure as code, hey we’ll just
terraform applyagain and it’ll pop backup.In theory that’s a great idea, just with one big problem. The new resource isn’t the old resource!
For an example, an Azure SQL Database server is a unique resource. If you delete one you lose any backups you’ve taken as they’re hosted on the server. Spinning up a new one isn’t going to get them back!
Stuart shows us how to take these locks. Because I have my doubts that everyone has all of their infrastructure prepped as terraform scripts, Stuart’s point is even more relevant.
Comments closedCreate a difference variable between the current value and the next value
This is also known asleadandlag– especially in a time series dataset this varaible becomes very important in feature engineering. In Excel, This is simply done by creating a new formula field and subtracting the next cell with the current cell or the current cell with the previous cell and dragging the cell formula to the last cell.
These things aren’t as easy to do as in Excel—it’s hard to get simpler than “push a button” or “click and drag your mouse”—but they are useful to know in R. H/T R-bloggers
Comments closedSteve Jones has the roundup for this month’s T-SQL Tuesday:
The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic.
Either everyone is on vacation or not many people think about them. Or maybe they aren’t interesting. In any case, here’s the roundup.
I’d like to say it’s because everyone’s on vacation, but I think In-Memory OLTP is an underutilized technology. Granted, there are reasons why it’s not used as much as it should be—early versions were too limiting and could have weird consequences on your servers—but if you’re on SQL Server 2017, it’s worth another look.
Comments closedTim Weigel continues a series on change tracking:
If you don’t provide a retention period, SQL Server’s default is 2 days. Auto-cleanup defaults to ON unless you tell it otherwise.
Easy!
The table level commands aren’t any more complicated. Before we get started, please note that change tracking requires a primary key on the table you want to track. This is reasonable – you need some kind of unique identifier to tell you which row has changed.
Read on for the scripts and further explanation.
Comments closedAlberto Ferrari explains how different DAX functions treat blank rows differently:
DAX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The difference between the two is subtle. To understand it better, we first need to introduce the concept of the blank row. The blank row is a special row added to a table, in case the table is on the one-side of a strong relationship and the relationship is invalid. Let us elaborate on this.
Click through for the detailed explanation, along with plenty of examples.
Comments closedTeo Lachev troubleshoots a customer issue:
Scenario: A client reports a memory spike during processing. They have a Tabular semantic model deployed to Azure Analysis Services. They fully process the model daily. The model normally takes less than 50 GB RAM but during processing, it spikes five times and Azure Analysis Services terminates the processing task complaining that it “reached the maximum allowable memory in our pricing tier”. Normally, fully processing the model should take about twice the memory but five times?
Teo gives us the explanation for this problem as well as a recommendation on how to fix it.
Comments closed