A Fine Slice Of SQL Server
Grant Fritchey answers a question:
I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan?
I didn’t know the answer for certain, so I said what I always say: I don’t know, but I’ll see if I can find out.
Read on for one false start but then the solution of how you can do it.
Comments closedSteve Jones reminds you to check those cumulative updates:
How can I quickly get a CU patch for a system that’s out of date? I’ll discuss that situation.
You might think you get to patch every instance every few months, and you may be able to. But most of us have laggards in any decent-sized estate. Someone always wants to avoid patching, or skip patching on the day you’ve scheduled every other system.
Steve’s solution involves using Redgate Monitor, though you could also do this on your own or using something like dbatools to get information about your estate.
Comments closedAndy Brownsword needs to make a call out:
One method for transferring a database to a different environment is using a Data-Tier Application – in the form of a DACPAC (for schema) or BACPAC (for schema and data).
Trying to use this approach with multi-database solutions is a challenge though as Data-Tier Applications don’t play nicely with cross-database objects.
Let’s look at how we can ease that pain.
Read on for the solution.
Comments closedChad Callihan resolves an issue:
I connected to the latest SQL Server, opened SSMS, and tried to restore from there. Sure enough, I was presented with the error:
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.
Read on for the solution, which was easy enough, but serves as a reminder that having (and occasionally running!) idempotent configuration scripts can be quite useful.
Comments closedAndy Brownsword has a script for us:
In Management Studio we can view object details by hitting F7 in Object Explorer. It gives us basic metrics but I find it very slow to load for the details I typically need.
For that reason I though I’d share a script to turn to for metrics I commonly need. This query returns:
- The table details (schema, name, created date)
- The primary storage (Heap, Clustered, or Columnstore)
- The numbmer of Nonclustered / Columnstore Indexes
- The number of records and rough size for data / indexes
Click through for the script and an example of what it looks like.
Comments closedKendra Little shares some advice:
Every time I share a recommendation to use data compression in SQL Server to reduce physical IO and keep frequently accessed data pages in memory, I hear the same concern from multiple people: won’t this increase CPU usage for inserts, updates, and deletes?
DBAs have been trained to ask this question by many trainings and a lot of online content – I used to mention this as a tradeoff to think about, myself– but I’ve found this is simply the wrong question to ask.
In this post I’ll share the two questions that are valuable to ask for your workload.
Kendra’s advice is very good, and to add my own two cents to the mix: the last place I was at did, in fact, see a pretty reasonable reduction in CPU utilization by performing page-level compression on any index where it made sense—and this was a very busy OLTP environment. The exceptions would be indexes making prominent use of things like Guids or chunks of binary, which don’t compress very well at all. In all my FTE and consulting years, I’ve never run into a circumstance in which compression caused a significant gain in CPU utilization.
Comments closedAndrew Atkinson shrinks a table:
In this post, you’ll learn a recipe that you can use to “shrink” a large table. This is a good fit when only a portion of the data is accessed, the big table has become unwieldy, and you don’t want a heavier solution like table partitioning.
This recipe has been used on tables with billions of rows, and without taking Postgres offline. How does it work?
Click through to find out.
Comments closedQuestion : Executing the following Database shrinkfile activity and getting the error message
use myDatabase
DBCC SHRINKFILE (N’myDatabase_log’ , 0, TRUNCATEONLY)Msg 3023, Level 16, State 2, Line 4
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Read on for the answer.
Comments closedShane Borden looks back at a directive:
I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive
Now that Postgres has incorporated the “MERGE” functionality into Postgres 15 and above, I wanted to ensure that there was no “strange” behavior as it relates to vacuum when using merge.
Read on to see how things look now.
Comments closedAnthony Nocentino continues a series on T-SQL snapshot backups:
In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.
Read on for the script and plenty of details.
Comments closed