A Fine Slice Of SQL Server
Reiss McSporran walks us through using Azure Backup to manage virtual machine backups:
Backups are configured for each VM individually, selecting their own retention policies and routines. They can however utilise the same storage and vaults.
Click through for a step-by-step guide on setting up a backup, using a backup to create a new VM, and replacing an existing VM with a backup.
Comments closedChad Callihan gives us a few high-level thoughts on page life expectancy and the buffer pool:
The buffer pool in SQL Server is an area in memory for caching data. Once data is read from disk, it can be kept in the buffer pool and SQL Server can check here for data when it needs to be found in the future. If requested data is not found in the buffer pool, a hard page fault can occur, meaning data needs retrieved from disk. It’s possible that data is found somewhere else still in memory which is called a soft page fault.
Click through for Chad’s thoughts on what a good page life expectancy looks like. My minor addition is that the number isn’t as important as the shape of the curve: if you have a fairly stable PLE above some arbitrary threshold (well above 300 seconds!), you’re probably in good shape. If your PLE sawtooths, your server’s RAM Pez dispenser needs refilled.
Comments closedGuy Glantser has a script to track buffer pool size by table:
Sometimes we need to troubleshoot memory pressure issues in SQL Server or in Azure SQL. One of the things that can help in these cases is to view the contents of the buffer pool.
I wrote a simple script that displays the contents of the buffer pool in terms of tables in the current database. For each table, it presents the total table space and the space consumed by the table in the buffer pool. The script is based mainly on the sys.dm_os_buffer_descriptors dynamic management view.
Click through for the script.
Comments closedThe Hadoop in Real World team shows how to move tables between databases in Hive:
Let’s see how to move a Hive table from one database to another in action.
We are currently inside a database named hirw. Let’s list the table under hirw.
We got one table named employee under database hirw. Let’s create a new database named hirw_2
Click through to see how.
Comments closedJeff Hill shows off another feature of dbatools:
If you’ve been responsible for an instance of SQL Server for any length of time you have probably dealt with
sp_configureto change configuration settings at the server level. I have been using SQL Server since v6.5 and it was a thing then too. This is not a post about what the settings are or what they should be set to. There are plenty of resources out there for both. This is about how to see and set these options.
Click through for the process and what you can do with it.
Comments closedChris Taylor puts on the lab coat and safety goggles:
tldr: Over the years I’ve read a lot of blog posts and watched a lot of videos where they mention that you should have your tempdb files all the same size. What I haven’t seen much of (if any) is what performance impact you actually see if they are not configured optimally. This blog post aims to address that
It is not too long, so do read.
Comments closedChris Webb continues a series on DicrectQuery over Log Analytics:
In the first post in this series I showed how it was possible to create a DirectQuery dataset connected to Log Analytics so you could analyse Power BI query and refresh activity in near real-time. In this post I’ll take a closer look into how you can use this data to monitor refreshes.The focus of this series is using DirectQuery on Log Analytics to get up-to-date information (remember there’s already an Import-mode report you can use for long-term analysis of this data), and this influences the design of the dataset and report
Click through for some KQL and explanatory instructions.
Comments closedAhmed Mahmoud troubleshoots an Azure SQL Database migration issue:
We sometimes see customers cannot move their SQL database from provisioned compute tier to serverless while the scaling operation fails with error signature like:
Failed to scale from General Purpose: Gen5, 2 vCores, 32 GB storage, zone redundant disabled to General Purpose: Serverless, Gen5, 2 vCores, 32 GB storage, zone redundant disabled for database: .
Error code: .
Error message: An unexpected error occured while processing the request. Tracking ID: ‘xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx’
Click through for several possible reasons.
Comments closedMeagan Longoria waits in line:
I’ve been working on a project to populate an Operational Data Store using Azure Data Factory (ADF). We have been seeking to tune our pipelines so we can import data every 15 minutes. After tuning the queries and adding useful indexes to target databases, we turned our attention to the ADF activity durations and queue times.
Data Factory places the pipeline activities into a queue, where they wait until they can be executed. If your queue time is long, it can mean that the Integration Runtime on which the activity is executing is waiting on resources (CPU, memory, networking, or otherwise), or that you need to increase the concurrent job limit.
Click through to see how you can calculate queue times across activities, pipelines, and data factories.
Comments closedIwas helping out with a SQL Server upgrade recently, going from 2016 to 2019. We ran into a problem when trying to run replmerg.exe for a merge replication subscription. Specifically, we were getting error code 20084, which means that the replication process couldn’t connect to one of the instances. Interestingly, the process couldn’t connect to the local instance, and the failure was immediate—that is, within a couple of milliseconds. There was nothing in the management logs on either the distributor server or the subscriber server which indicated a problem. We were able to connect both sides together just fine—from the subscriber, we could connect to the distributor, and from the distributor, we could connect to the subscriber.
Click through for what error code 20084 typically means, as well as what turned out to be the problem here.
Comments closed