Welcome to Session 01 of the JBSWiki AI Learning Series, a 17-session journey to understand and work with Azure AI.
In this session, we explore how Generative AI works, what Large Language Models (LLMs) are, and how tools like ChatGPT generate responses.
Our examples throughout this series use BlueFern Travel, a fictional travel agency that wants to build an AI-powered customer support assistant called Fern.
What will you learn?
In this session, we cover:
Artificial Intelligence (AI): Understanding AI, Machine Learning, Deep Learning and Generative AI.
Large Language Models (LLMs): What they are and how they generate text.
Tokens: How text is broken into smaller pieces that models understand.
Next-token prediction: How ChatGPT generates responses one piece at a time.
Context window: Why models have limits on how much information they can process.
Hallucinations: Why AI sometimes generates convincing but incorrect answers.
Microsoft Foundry: An introduction to the model catalog and chat playground.
Responsible AI: Understanding security, fairness and human oversight.
We also demonstrate how to count tokens using Python and experiment with a model in the Microsoft Foundry chat playground.
Watch the video
Watch the complete step-by-step walkthrough here:
Download Session 01 Materials
Use the following materials to follow along with the video and complete the exercises.
use_case_worksheet.md – Exercise to identify suitable and unsuitable Generative AI use cases.
Practice Assignment
After watching the video, try these exercises:
Install Python 3.10 or later.
Install the tiktoken Python package.
Run python count_tokens.py and examine the token counts.
Test three sentences of your own, including one in your mother tongue.
Complete the use-case worksheet by classifying the ten BlueFern Travel scenarios.
Read the Microsoft Learn introduction to Microsoft Foundry.
Note: You do not need an Azure subscription for the initial learning exercises. An Azure environment is required for the Foundry playground demonstration.
What’s Next? – Session 02
In Session 02, we prepare our development environment for Azure AI projects.
We will install and configure:
Python
Visual Studio Code
Azure CLI
Git
We will also create the BlueFern project folder structure and make our first REST API call using Python.
Follow the Complete Series
This is Session 01 of 17. Subscribe to JBSWiki to follow the complete Azure AI learning journey.
JBSWiki – Helping You Master Technology, One Session at a Time!
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
When dozens of threads hit the same query simultaneously on a cold cache, SQL Server compiles the same plan over and over — burning CPU, wasting resources, and degrading throughput right when your system is most vulnerable. This is a compilation storm, and SQL Server 2025 has a one-line fix for it.
What is a compilation storm?
Every query needs a compiled execution plan before SQL Server can run it. Plans are cached — but when the cache is cold (after a restart, failover, or deployment) and many threads fire the same query at once, each thread independently compiles the same plan:
One thread pays the compilation cost. Everyone else waits a millisecond and reuses for free.
Step-by-step demo script
The full script below walks through all 10 steps end to end. Run each section in order.
1) Create the database and tables
Sets up jbexecute with Customers, Products, Orders, and a CompilationMetrics table to store before/after results.
USE master;
GO
IFEXISTS(SELECT1FROM sys.databases WHERE name = N'jbexecute')
BEGIN
ALTER DATABASE jbexecute SET SINGLE_USER WITHROLLBACKIMMEDIATE;
(SELECT TOP 1*FROM dbo.CompilationMetrics WHERE Phase ='BEFORE'ORDERBY MetricID DESC) b
CROSSJOIN
(SELECT TOP 1*FROM dbo.CompilationMetrics WHERE Phase ='AFTER'ORDERBY MetricID DESC) a;
GO
10) Cleanup
ALTER EVENT SESSION [Demo_CompilationStorm] ON SERVER STATE = STOP;
DROP EVENT SESSION [Demo_CompilationStorm] ON SERVER;
GO
USE master;
ALTER DATABASE jbexecute SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE jbexecute;
GO
Things to know before enabling in production
SQL Server 2025 only. OPTIMIZED_SP_EXECUTESQL is not available on 2022 or earlier. ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY is available from 2022 onwards.
Waiting threads add latency. Non-first threads wait for the compiling thread. Negligible in most cases — but test carefully if you have genuinely slow-compiling queries.
sp_executesql only. Ad-hoc SQL, stored procedures, and batches where the SQL text varies between calls are not covered by this feature.
Does not fix bad plans. This serializes compilation — it does not improve plan quality. Use Query Store for plan stability alongside this.
Test before production. Validate with Extended Events as shown in this demo. Every workload is different.
Watch the Full Demo
I’ve recorded a complete walkthrough of this setup on my YouTube channel JBSWiki. If you’re a visual learner, go check it out!
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
Database backups are one of the most critical parts of any data platform strategy. Whether you are protecting transactional systems, reporting environments, or large enterprise workloads, backups directly influence storage consumption, recovery objectives, operational overhead, and even infrastructure cost.
With SQL Server 2025, backup compression gets a major upgrade through support for ZSTD (Zstandard) compression. This is a significant enhancement for database administrators and architects looking to reduce backup size, improve efficiency, and gain more flexibility in how backup workloads are tuned.
In this post, I will walk through what ZSTD compression is, why it matters, and how to test it using a simple end-to-end backup and restore demo.
What is ZSTD Compression?
ZSTD, or Zstandard, is a modern lossless compression algorithm designed to deliver an excellent balance between:
High compression ratio
Fast compression speed
Very fast decompression
Flexible tuning through compression levels
For years, backup compression has helped reduce storage usage and improve I/O efficiency. But as database sizes continue to grow, traditional compression methods may not always provide the best balance between speed and storage savings.
That is where ZSTD becomes exciting.
SQL Server 2025 now allows backups to use the ZSTD algorithm, giving DBAs a newer and more efficient option for compressing database backups.
Why This Matters
As backup volumes increase, organizations typically face a common set of challenges:
Backup files consume too much space
Backup windows become longer
Restore operations need to stay fast and reliable
Storage and archival costs continue growing
Sending backups across environments or regions becomes more expensive
ZSTD helps address these challenges by improving backup compression efficiency while still maintaining strong decompression performance.
In practical terms, this means you may be able to:
Store more backups using less space
Improve backup storage utilization
Reduce backup repository growth
Optimize retention strategies
Improve overall operational efficiency
Key Benefits of ZSTD Backup Compression
1. Better Compression Efficiency
One of the biggest advantages of ZSTD is its ability to compress data more efficiently than older approaches in many scenarios. This can result in noticeably smaller backup files, especially for large databases with compressible data patterns.
2. Faster Decompression
Backup is only one half of the story. Restore performance is equally important. ZSTD is known for fast decompression, which is valuable during restore operations when time matters most.
3. Compression Levels for Flexibility
SQL Server 2025 introduces the ability to choose different compression levels when using ZSTD. This is useful because not every environment has the same priorities.
For example:
If your priority is faster backup completion, a lower level may be enough
If your priority is maximum storage reduction, a higher level may be better
If you want a balance, medium can be a good starting point
4. Familiar Backup Workflow
Another great advantage is that ZSTD integrates directly into the backup syntax DBAs are already familiar with. There is no need to redesign the backup process from scratch. You simply use the appropriate compression options while taking the backup.
Demo Objective
In this walkthrough, the goal is to compare:
A normal compressed backup
A ZSTD backup with the default compression level
A ZSTD backup with MEDIUM compression level
A ZSTD backup with HIGH compression level
After each backup, we also validate the backup metadata and restore the database to separate target names and file paths. This gives us a complete end-to-end validation of both backup creation and restore success.
For this demo, we will use the JBFinance database and the exact script provided below.
What We Will Validate
This demo helps validate several things:
Backup command executes successfully
Backup header can be read
Backup file can be restored successfully
Different ZSTD compression levels can be tested easily
Separate restored copies can be created for comparison and verification
Step 1: Review the Source Database
Before taking backups, it is always useful to review the source database size and file layout.
USE [master]
GO
sp_helpdb JBFinance
GO
This gives you a quick overview of the database structure and helps confirm the logical file names that will later be used during restore.
Step 2: Take a Regular Compressed Backup
First, take a standard compressed backup using the familiar compression option.
This command creates a compressed backup of the JBFinance database and writes it to the specified backup location.
Why this matters
This serves as your baseline. You can compare this backup later with the ZSTD-based backups to understand whether ZSTD offers better storage efficiency or operational benefits in your environment.
Step 3: Inspect the Backup Metadata
After the backup completes, inspect the backup header.
If your main goal is maximum backup size reduction, this option is worth testing. In some environments, HIGH can offer the most aggressive storage savings, though it may also require more CPU resources during backup creation.
When you run this demo in your environment, pay close attention to the following:
1. Backup File Size
Compare the sizes of:
JBFinance_normal.bak
JBFinance_ZSTD.bak
JBFinance_ZSTD_MEDIUM.bak
JBFinance_ZSTD_HIGH.bak
This helps you understand how each compression option affects storage savings.
2. Backup Completion Time
Capture how long each backup takes to complete. Higher compression levels may reduce backup size further, but they can also use more CPU.
3. Restore Success
Each backup should restore successfully into its own database copy. This confirms backup reliability and end-to-end usability.
4. Compression Trade-Offs
The best compression level is not always the smallest file. In many real-world environments, the right choice depends on:
Backup window
CPU availability
Storage cost
Restore expectations
Workload sensitivity
4. My Test details
Practical Guidance
Here are a few practical recommendations when evaluating ZSTD backup compression in your environment.
Start with LOW or MEDIUM
If you are testing this feature for the first time, LOW or MEDIUM is a practical place to begin.
Measure Before Standardizing
Do not assume one level is best for every database. Compression results vary depending on:
Data types
Existing data compression
Row patterns
Repetitive versus random data
Binary or already compressed content
Test Restore Performance Too
Do not focus only on backup size. Make sure you also validate restore workflows, especially for recovery-critical systems.
Use Realistic Data
Whenever possible, test this against an actual workload or database that resembles production.
Final Thoughts
ZSTD compression in SQL Server 2025 is a meaningful enhancement for modern backup strategies. It gives database professionals more flexibility in how they balance storage efficiency, backup throughput, and operational cost.
The biggest advantage is not just smaller backup files. It is the ability to tune compression behavior based on your environment and priorities.
If your organization manages large backups, retention-heavy workloads, or storage-sensitive environments, this feature is definitely worth testing.
The script used in this post provides a simple and effective way to compare:
Standard compressed backup
ZSTD LOW
ZSTD MEDIUM
ZSTD HIGH
and validate the complete backup-and-restore workflow.
Watch the Full Demo
I’ve recorded a complete walkthrough of this setup on my YouTube channel JBSWiki. If you’re a visual learner, go check it out!
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.