Press "Enter" to skip to content

Curated SQL Posts

Finding Buffer Pool Distribution by Table

Published 2022-01-11 by Kevin Feasel

Guy 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 closed

Thoughts on Page Life Expectancy

Published 2022-01-11 by Kevin Feasel

Chad 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 closed

Data and Compute in Azure ML

Published 2022-01-10 by Kevin Feasel

I continue a series on low-code machine learning with Azure ML:

Once you have a datastore, you’re going to want to create at least one dataset. Datasets are versioned collections of data in some datastore. The Azure ML model is quite file-centric, and this concept makes the most sense with something like a data lake, where we have different extracts of data over different timeframes. Perhaps we get an extract of customer behavior up to the year 2018, and then the next year we get customer behavior up to 2019, and so on. The idea here is that you can use the latest training data for your models, but if you want to see how current models would have stacked up against older data, the opportunity is there.

Once you have data and compute, the world is your oyster. Or something like that.

Comments closed

The Performance Impact of Dissimilarly-Sized tempdb Files

Published 2022-01-10 by Kevin Feasel

Chris 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 closed

Executing sp_configure from Powershell

Published 2022-01-10 by Kevin Feasel

Jeff 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_configure to 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 closed

Reasons Azure SQL Databases Cannot Move to Serverless

Published 2022-01-10 by Kevin Feasel

Ahmed 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 closed

Monitoring Power BI Dataset Refreshes with Log Analytics

Published 2022-01-10 by Kevin Feasel

Chris 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 closed