A Fine Slice Of SQL Server
Sheldon Hull has a hoarding problem to solve:
In my case, I’ve run into 50TB of old backups due to tooling issues that prevented cleanup from being successful. The backup tooling stored a sqlite database in one subdirectory and in another directory the actual backups.
I preferred at this point to only perform the lifecycle cleanup on the backup files, while leaving the sqlite file alone.
Click through to see how to do this using Powershell.
Leave a CommentJess Pomfret shows how we can copy a database using dbatools:
We’re working hard on the AdventureWorks2017 database, perhaps getting it ready for an upgrade – since it’s now 3+ years out of date.
dbatools has so many functions, and I know I’ve mentioned it before, but
Find-DbaCommandis a great way of looking for what we need. I want to know what the default backup path is set to, and since I’m just backing up and restoring to the same server, we already know that the instance has the required permissions here. If only there was an easy button for this…
Spoiler alert: there is.
Leave a CommentInstant File Initialization is especially helpful when large file growths are occurring. Without Instant File Initialization, SQL Server has to write zeros to the disk in order to initialize it before SQL Server can use it for new data. As with autogrowth, the amount of time this process takes depends on the size. The more disk space that is needed, the longer it’s going to take for the disk to be zeroed for use. Fortunately, IFI exists to skip the need to write out zeroes. Instead, disk space can immediately be used as needed.
IFI can also make a noticeable difference when restoring databases. If you’re working on getting your recovery time objective (RTO) down, check the status of IFI. Enabling IFI may help cut that number down to an acceptable value.
This is particularly interesting because, for the most part, I’ve only seen posts showing how to enable it at installation time.
Leave a CommentMonica Rathbun gives us a primer on storage concepts:
“One Gerbil, Two Gerbils or Three Gerbils?” is a common DBA joke about server and storage performance. No matter how many gerbils power your storage, you need to know what type they are and the power that they provide. Storage is not about gerbils it is about IOPs, bandwidth, latency, and tiers.
As a DBA it is important for you to understand and know what kind of storage is attached to your servers and how it is handling your data. It is not important to master everything about it, but it is very advantageous to be able to talk to your storage admins or “Gerbil CoLo, LLC” provider intelligently especially when you experience performance issues. Here is a list of things to I encourage you to know and ask.
Click through for the cheat sheet.
Leave a CommentEd Elliott continues a series on Spark examples:
The next example is how to do a ROW_NUMBER(), my favourite window function.
Ed’s example is ROW_NUMBER() but it also applies to other partitioning window functions such as RANK(), DENSE_RANK(), and NTILE().
Holger von Jouanne-Diedrich lays out the basics of gradient descent:
Gradient Descent is a mathematical algorithm to optimize functions, i.e. finding their minima or maxima. In Machine Learning it is used to minimize the cost function of many learning algorithms, e.g. artificial neural networks a.k.a. deep learning. The cost function simply is the function that measures how good a set of predictions is compared to the actual values (e.g. in regression problems).
The gradient (technically the negative gradient) is the direction of steepest descent. Just imagine a skier standing on top of a hill: the direction which points into the direction of steepest descent is the gradient!
Click through for an example in R.
Leave a CommentBrenda Bentz lays out some of the benefits of table partitioning in SQL Server:
Table partitioning in MS SQL Server is an advanced option that breaks a table into logically smaller chunk (partition) and then stores each chuck to a multiple filegroups. Each chuck can be accessed and maintained separately. Partitioning is not visible to users; it behaves like one logical table to the end user. Partitioning was introduced in SQL 2005 as an Enterprise edition feature but starting with SQL 2016 SP1 it is available on all the editions.
Table partitioning can work to improve performance in specific circumstances, though you’ll definitely want to do a lot of testing before rolling it out. H/T Amanda White.
Leave a CommentReza Rad shows how we can write DAX measures against a Power BI streaming dataset:
In Power BI, you can have a dataset with Imported data, DirectQuery, Live Connection, or Composite mode. You can build all of those types of Power BI datasets in the Power BI Desktop. However, there is a single type of dataset, which you can only build through the service, called the Streaming dataset.
A streaming dataset is for building reports with real-time response time. For example, if you want to build a Power BI dashboard that shows the room temperature as soon as captured by a temperature sensor. For this type of dataset, you send the data rows using Power BI REST API, which can be called using a custom C# application, or PowerShell scripts, or even from a Power Automate flow process.
Read on to see how.
Leave a CommentMatthew McGiffen never promised us there would be no math:
Before using values from the statistics, the Optimizer will check to see if the statistics are “stale”, i.e. the modification counter exceeds a given threshold. If it does, SQL will trigger a resampling of the statistics before going on to form an execution plan. This means that the plan will be formed against up to date statistics for the table.
Read on for some experimentation on when stats updates kick in.
1 CommentAx Sharma reports on a nasty bug in Windows:
In August 2020, October 2020, and finally this week, infosec researcher Jonas L drew attention to an NTFS vulnerability impacting Windows 10 that has not been fixed.
When exploited, this vulnerability can be triggered by a single-line command to instantly corrupt an NTFS-formatted hard drive, with Windows prompting the user to restart their computer to repair the corrupted disk records.
The researcher told BleepingComputer that the flaw became exploitable starting around Windows 10 build 1803, the Windows 10 April 2018 Update, and continues to work in the latest version.
Read the whole thing. It looks like this also affects Windows XP, which makes me figure it might affect Windows 7, 8, and 8.1 too. H/T Mark Wilkinson.
Leave a Comment