Forem: Amar Abaz

What happens when SQL Server PAGE gets corrupted ?

Amar Abaz — Sat, 14 Feb 2026 19:45:14 +0000

In this article, I will explore how SQL Server works with PAGES, the smallest unit of data storage. We will explore how SQL Server organizes data within these pages and how to interact with them. Additionally, I am gonna simulate page corruption, run a validity check, and demonstrate how to fix it.

Data files are divided into 8KB pages, which consist of

Page Header (96 bytes); Contains metadata about the page.
Data Rows (8060 bytes); Holds the actual data of your table.
Slot Array (36 bytes); Manages the location of data rows within the page.

Together, these components make up 8192 bytes, or 8KB, which is the standard size for a SQL Server page.
An extent in SQL Server consists of 8 contiguous 8KB pages, totaling 64KB.
There are two types of extents UNIFORM EXTENTS, where all 8 pages are used by a single object, and MIXED EXTENTS, where the pages are shared by multiple objects. This helps optimize space usage and performance, and behaviour is menagable in your database Options.

SELECT [name], [is_mixed_page_allocation_on] FROM sys.databases;

These pages are stored in physical files, categorized as

Primary or Master Data File (.mdf)
The main file where system and user data are stored by default. It contains also sys data of your database.
Secondary or Next Data File (.ndf)
Additional data files used to expand storage, but optional for better arhitecture and sizing.
Transaction Log Data File (.ldf)
Stores the transaction logs, which track changes made to the database to ensure consistency and recovery.

These files work together to manage your database storage and ensure data integrity.

Let's get to it..

Lets now check the allocation and structural integrity of the pages in the AdventureWorks2022 database, (skipped checking the indexes). I will now focus on table Person where we will do our corruption test.

DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

DBCC CHECKALLOC ('AdventureWorks2022', NOINDEX);

Identifying Pages in Your Database

You can identify which pages a table occupies by using DBCC IND.
This command provides a breakdown of where the table's data is stored across pages and files.

DBCC IND('AdventureWorks2022', 'Person.Person', -1)

Once you've identified a page, you can go deeper into its content using the DBCC PAGE command.
Lets show the internal structure of the page, including the Page Header, m_pageId, m_objId, and the data rows stored in it.

For example, to inspect page 1314 in file 1.

DBCC PAGE('AdventureWorks2022', 1, 1314, 1) WITH TABLERESULTS;

Simulating Page Corruption

To demonstrate how corruption can occur, let's intentionally modify a page.

ALTER DATABASE [AdventureWorks2022] SET  SINGLE_USER WITH NO_WAIT;
DBCC WRITEPAGE('AdventureWorks2022', 1, 1314, 0, 1, 0x11, 1);

Verifying the Corruption

Now run DBCC CHECKDB or CHECKTABLE again to see how SQL Server detects the integrity corruption. Do not use CHECKALLOC because it only checks for allocation problems not integrity.

DBCC CHECKDB('AdventureWorks2022');
DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

These commands will perform an integrity check on the database and report any issues they find, including corruption in the affected pages.
If we select the whole table we have an error.

Msg 824, Level 24, State 2, Line 32
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x246e3cb2; actual: 0x24663cb2). It occurred during a read of page (1:1314) in database ID 5 at offset 0x00000000a44000 in file 'C:\MSSQL\DATA\AdventureWorks2022.mdf'. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

More about DBCC Commands on my earlier topic here:
MSSQL DBCC How good are they really ? 👌
.
.
.

Now lets talk about the ways how to fix this and what to keep in mind..

🤞First Option: Try REPAIR_REBUILD

DBCC CHECKTABLE ('Person.Person', REPAIR_REBUILD);

REPAIR_REBUILD can fix certain types of index corruption or inconsistent structures without causing data loss. It is safer than REPAIR_ALLOW_DATA_LOSS because it doesn't drop data but will rebuild corrupted indexes and structures.
However REPAIR_REBUILD won't help in our case.

Msg 8928, Level 16, State 1, Line 39
Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data): Page (1:1314) could not be processed. See other errors for details.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8939, Level 16, State 98, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data), page (1:1314). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
Repairing this error requires other errors to be corrected first.
Msg 8976, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data). Page (1:1314) was not seen in the scan although its parent (1:1568) and previous (1:1313) refer to it. Check any previous errors.
Repairing this error requires other errors to be corrected first.
Msg 8978, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416, alloc unit ID 72057594057523200 (type In-row data). Page (1:1315) is missing a reference from previous page (1:1314). Possible chain linkage problem.
Repairing this error requires other errors to be corrected first.
There are 19967 rows in 3807 pages for object "Person.Person".
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'Person.Person' (object ID 2101582525).

😥Second Option: REPAIR_ALLOW_DATA_LOSS

DBCC CHECKTABLE ('Person.Person', REPAIR_ALLOW_DATA_LOSS);

This should be your last option and only to be used if you are okay with the possibility of data loss. It attempts to repair the corruption in the table, but any corrupted data that cannot be repaired will be removed. Before proceeding, it is crucial to do BACKUP DATABASE to ensure you have a recovery point in case the repair process causes unintended consequences.

🤔Third Option: PAGE RESTORE
Page restore is a technique used to restore specific corrupted pages in a database from an available backup. The goal is to replace the corrupted pages with uncorrupted pages from a previous backup, minimizing the impact on the rest of the database and avoiding the need for a full restore. However, to successfully restore a page, it's critical to identify when the page was last consistent and healthy.

When you have regular log backups as part of your Recovery Time Objective (RTO) strategy, you can typically recover your database to the point of the last log backup. However, even when using CHECKSUM and VERIFY during backups, these options only validate the integrity of the backup file and do not detect database corruption.

Now let's check and confirm the corrupt page id using command bellow.
This select shows those pages that SQL Server has flagged as suspect, which means it gives you a history of past corruption events.

SELECT * FROM msdb.dbo.suspect_pages;

Execute commands below and do not forget to apply the log restore after a page restore, if not it will break the continuity of your database's transaction LOG chain. After that make another log backup and restore, and check again the whole database with DBCC CHECKDB. This should be the way to do it with large databases, because we will not restore the whole database only page and logs.

RESTORE DATABASE [AdventureWorks2022] PAGE='1:1314' 
FROM  DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\FULL\w19$SQ_AdventureWorks2022_FULL_20260214_122842.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 5, ONLINE   -- online is supported by Enterprise edition only
RESTORE LOG [AdventureWorks2022] FROM  DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\w19$SQ_AdventureWorks2022_LOG_20260214_122900.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 5

BACKUP LOG [AdventureWorks2022] TO DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' WITH INIT
RESTORE LOG [AdventureWorks2022] FROM DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' WITH RECOVERY

🫡Fourth Option: Remote restore and recreate
Restore to a new location and recreate that Table (For small databases).
Keep it simple if the corruption is isolated to a single table or a small part of the database, and the database size allows for it. An alternative approach is to restore the database to a new location and then recreate the corrupted table on primary db. While doing that you could also make a Trigger to deny any interaction if SQL Server still allows insert, update, delete to be performed.

CREATE TRIGGER tr_tableDENY ON Person.Person
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    RAISERROR ('This table is temporarily locked for investigation.', 16, 1);
    ROLLBACK TRANSACTION;
END

Final Advice

Make sure to run DBCC CHECKDB regularly. This is crucial for maintaining the integrity of your database. However, don't rely solely on DBCC checks. Set up real time alerts for corruption detection.

Here's my advice, create an SQL job that constantly monitors the msdb.dbo.suspect_pages table for new entries and sends you an alert as soon as any corruption is detected. You can clean this table after alert was processed.

Moreover, since Error 823, 824, and 825 are associated with corruption in database pages, you should set up an alert for these specific errors as well. That way, you can be notified immediately when they happen.
To do this go to SQL Server Agent, Right click on Alerts and select New Alert. Make Alert for every Error and set to use Operator for mail response.

Error 823: This error indicates that SQL Server encountered an I/O error while reading or writing to a disk.
Error 824: This error occurs when SQL Server detects a logical consistency error in the data read from a page.
Error 825: This error signifies a page failure during an I/O operation.

Reference:

AbeMon MS SQL Tool v1.0.0 Monitor and Debug

Amar Abaz — Sun, 01 Feb 2026 17:28:37 +0000

AbeMon is a powerful, lightweight toolkit designed for database administration, offering all the essential checks needed for troubleshooting and maintenance in your daily tasks.
Enjoy a 30 day free trial to explore its full potential and capabilities.
If you find it useful, feel free to get in touch.
This App is designed to deliver smooth performance while using minimal system resources, consuming just 15-20MB of RAM.


git clone "https://github.com/abeamar/AbeMon.git"

Choose the installation method that works best for you!

Using the .exe or .msi Installer:


https://github.com/abeamar/AbeMon/main/AbeSetup.msi

https://github.com/abeamar/AbeMon/main/setup.exe

Or Manual Installation (Copying the Folder abeSetup) After copying, you can run the application directly from that folder.

What this tool provides:

Disk usage and growth
Memory and CPU utilization
Database health, security and size checks
Backup and restore history checks
Monitor active backup and restore operations with predictions
Table sizes and index fragmentation checks
SQL Server error logs
Windows Events error logs
SQL Agent job failures checks
Schema object definitions
Raw SQL object extraction
Active sessions and blocking queries
Deadlock, block tracking
Custom session level monitoring for your SQL Server instance

This is a fully functional and very useful tool to have in your DBA arsenal.

😎
Stay tuned for regular updates as I dive deeper into how this tool can be used in various DBA scenarios. Whether you're troubleshooting issues or optimizing your SQL Server environment, this tool will be your best friend.

Example in Action:

CASE: Tracking Restore & Backup Operations with Real Time Insights

Database restore is dragging on longer than expected, and you have no idea who initiated it or which backup was used. Or maybe you've got conflicting reports about when a restore actually happened, and you're stuck chasing down clues through endless logs.

With just a few clicks, you can access detailed restore history that shows exactly who performed the restore, which backup was used, and when it happened.
Or Instantly see who's performing restores, which backups are in use, and predict how long they'll take.
Whether it's active restores or ongoing backup jobs, this tool will provides the crucial data you need.

CASE: Diagnosing long running transactions when resources are stuck

You show up to work, and your services are down. They are not processing anything. Maybe it’s just waiting for resources, or worse, it's hung up because your tempdb or transaction log is full. The usual suspects, but it's hard to figure out exactly where the bottleneck is.

That's where this tool comes in. One quick check will show you all the active sessions in your databases, sorted by execution time, so you can pinpoint the longest running transactions that might be blocking resources. But you don't have to stop there, you can combine this with other checks for tempdb usage, overall session activity, and transaction log health to get a full picture of what's happening and address it before it affects the rest of your system.
Sometimes the answer is hiding in plain sight, SQL Server logs and Windows Event logs often tell the full story once you know where to look.

CASE: Handling Max Identity value reached in your production

Just a quick example of how this tool can save you from unexpected surprises. Imagine you're in production and service app suddenly can't insert data into a key table because your identity column (whether INT, BIGINT, or SMALLINT) has maxed out. It happens.

With one of the many checks the tool runs this is just one of the daily checks you would keep an eye on. For every table with incremental identity column in your databases you can get a heads up before that becomes a problem. If you know this early, you can take action, like altering the column to a bigger type or creating a new table, so things don't grind to a halt.

Always keep an eye on your table size.
In SQL Server, a well maintained index and table size go a long way in ensuring fast queries and low overhead.

Take it for a spin..

Download AbeMon and explore its full range of features at your own pace. From real time monitoring to detailed backup and restore insights, this tool is designed to make your workflow smoother. I am sure you'll appreciate the difference once you see it in action.

MSSQL DBCC How good are they really ?

Amar Abaz — Wed, 21 Jan 2026 20:48:58 +0000

What does DBCC mean ? Well, it stands for Database Console Commands, of course.
When you're working with SQL Server, these commands are like the secret weapons for any DBA. They're super powerful tools for troubleshooting, monitoring, and ensuring your data stays in check and safe.

But how effective are these commands really ? Are they as good as they seem ?
Let's dive into some of the most useful DBCC commands, how they work, and when you might want to use them.

To explore all commands (documented and undocumented) we first need to enable the trace flag for detailed output, then use the command to view them.

DBCC TRACEON(2588) WITH NO_INFOMSGS;
DBCC HELP ('?') WITH NO_INFOMSGS;

No, if you're not sure how to use some of those commands like for example DBCC CHECKDB, you can simply run it like this,

DBCC HELP ('CHECKTABLE') WITH NO_INFOMSGS;

There's an extensive list of DBCC commands, each serving a specific purpose.
This will give you a complete breakdown of how to use the command and its various options.

Bellow are some of the most common ones, with real practical scenarios and steps how to use them ..

DBCC USEROPTIONS

Very useful command to check and troubleshoot session settings for a user, including the language, date format, read settings and other stuff on session level.

DBCC USEROPTIONS;

DBCC CHECKDB

This is the GO TO command for verifying the physical and logical integrity of an entire database. It's typically executed when corruption is suspected, or as part of regular health checks. Create an SQL job to execute this daily or weekly depending on your database size.

When corruption is detected, especially if multiple objects are affected then this command often becomes your first line of defense.

Before attempting any repair, isolate the database to prevent active connections from interfering with the process.

ALTER DATABASE [AbeDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

This ensures only privileged users can access the database while SQL Server rolls back all active transactions.

You can now rerun DBCC CHECKDB with a repair parameter.

DBCC CHECKDB ('AbeDB', REPAIR_FAST); --minimal repair
DBCC CHECKDB ('AbeDB', REPAIR_REBUILD); --rebuild bad index
DBCC CHECKDB ('AbeDB', REPAIR_ALLOW_DATA_LOSS); --can result dataloss

REPAIR_ALLOW_DATA_LOSS option should never be your first choice in production. If REPAIR_FAST fails, restoring from a clean backup is almost always the safer option.

Avoid data loss unless absolutely necessary. Ensure consistent and verified log backups.
Also do not use command DBCC REPAIRDB. This is an older and not recommended command that has been replaced by CHECKDB (Since SQL Server 2005).

DBCC CHECKALLOC

This command is especially useful when you suspect on storage level or allocation related corruption. It checks and ensures all pages are correctly allocated, for data integrity you should use CHECKDB.

DBCC CHECKALLOC ('AbeDB'); --check
DBCC CHECKALLOC ('AbeDB', NOINDEX) WITH NO_INFOMSGS; --no index check

DBCC CHECKTABLE & CHECKINDEX

Another good option if you don't want to scan the whole database and want to focus on specic table or index, or you already know where the problem.. then use this commands with WITH EXTENDED_LOGICAL_CHECK.
It will include better logical integrity checks and check index consistency, foreign keys, null and unique constraints.

Go by logic "Start broad, then narrow down".

DBCC CHECKTABLE ('AbeTable');
DBCC CHECKTABLE ('AbeTable', REPAIR_REBUILD); --repair
DBCC CHECKTABLE ('AbeTable', REPAIR_ALLOW_DATA_LOSS); --data loss

ALTER INDEX IndexName ON AbeTable REBUILD; --rebuild if errors exist

DBCC OPENTRAN

Used to display information about the oldest active transaction within a specified database. With this helpful command you can find blocking processes that need to be terminated to free up resources. Used when troubleshooting why the transaction log is not shrinking or is growing uncontrollably, as open transactions can prevent log truncation during backup.

DBCC OPENTRAN('AbeDB');

DBCC FREEPROCCACHE & DBCC FREESYSTEMCACHE

FREEPROCCACHE is used if you suspect that the cache contains bad or suboptimal execution plans. You can clear specific cached plan.
When a query is executed, sql server reuses an existing execution plan from the cache if it exists, which can improve performance because it avoids the need to recompile the query each time.

DBCC FREEPROCCACHE (0x05004320D5B7S12701B91BBE5B5082B6) WITH NO_INFOMSGS;

FREESYSTEMCACHE allows you to clear various caches based on the context you choose. The PLANCACHE option is less impactful than FREEPROCCACHE because it only clears the execution plans, without affecting other system caches such as the buffer pool.

DBCC FREESYSTEMCACHE ('ALL');
DBCC DROPCLEANBUFFERS; --pages of data read from disk
DBCC FREESYSTEMCACHE ('PLANCACHE'); --recompile queries with the new index
DBCC FREESYSTEMCACHE ('COLUMNSTORE');--columnstore indexes

DBCC SHOWCONTIG

This command displays fragmentation info. I use this a lot to check status of indexes using T-SQL. Second parameter is for the index on the table you focus on.

DBCC SHOWCONTIG('Person.Person',2);

DBCC SQLPERF

Quickly checks the transaction log space usage in SQL Server. Great for monitoring and intervening with log backups or shrinking when needed.

DBCC SQLPERF('logspace');

DBCC TRACEON & TRACEOFF & TRACESTATUS

For deeper analysis, these commands allow you to enable, disable, and inspect SQL Server trace flags.

Trace flags are commonly used for debugging, performance troubleshooting, and internal behavior analysis. They can be enabled at the session level or globally, and you can even configure them at server startup if permanent behavior changes are required.
They are primarily used to either change the behavior of the SQL Server engine or to log additional details in the SQL Server logs.
Be cautious not to overwhelm your server with excessive logging. That's why it's important to disable trace flags after you've completed your analysis.

Bellow is a few examples and most commont traces you should use.

Trace 1222: Gives detailed information about deadlock into SQL Server error log..

DBCC TRACEON(1222); --enable flag
DBCC TRACEON(1222,-1); --enable globaly
DBCC TRACEON(1204, 1222); --enable multiple flags
DBCC TRACESTATUS(1222); --check active flags
DBCC TRACEOFF(1222); --turn off

To ensure your configuration persists after every server restart, you need to add the trace flag at the server level,

Open SQL Server Configuration Manager,
Right click on SQL Server Services,
Select Properties, go to the Startup Parameters tab,
Add a new line with the trace flag: -T1222.

Here are some other useful Trace Flags,

1222 - log deadlock in server logs
1204 - locking info with quick analysis
3226 - stops successful database backups from logging
9567 - compression for fast AG Automatic seeding
3042 - disable pre allocation of space during backup
3604 - send output to client
1117 - autogrow all files together
460 - truncation enhance info ..... And many more.

So, how good are they really ?

While DBCC commands can catch many types of corruption, they aren't a one-stop solution. They may not catch all possible issues, and some might require deeper troubleshooting.
The answer lies in your knowledge of commands and the specific needs of your database.
The best way to truly understand the power of DBCC is to experiment with different commands in your own test environment.
With the right knowledge and caution, DBCC commands can be incredibly effective tools in your DBA arsenal.

Let's have a look at those arsenal again 🤯🤯

dbcc sqlperf
dbcc inputbuffer
dbcc checkdb 
dbcc memorystatus
dbcc freeproccache
dbcc useroption
dbcc ind
dbcc page
dbcc writepage
dbcc freesystemcache
dbcc show_statistics
dbcc dropcleanbuffers
dbcc showcontif
dbcc opentran
dbcc addtempdb 
dbcc checkalloc 
dbcc checkcatalog 
dbcc checkindex 
dbcc checkstorage
dbcc checktable
dbcc checkverify 
dbcc complete_xact
dbcc engine
dbcc fix_texT
dbcc forget_xact
dbcc indexalloc
dbcc monitor
dbcc pravailabletempdbs
dbcc reindex
dbcc serverlimits
dbcc stackused
dbcc tablealloc
dbcc zapdefraginfo 
dbcc quorum
.....
...
..

References:

Modular Approach to Database Performance and Maintainability

Amar Abaz — Sun, 18 Jan 2026 12:12:03 +0000

Let's talk about a smart, modular approach to filegroups on MSSQL.
The idea is simple but powerful, isolate your schemas into their own filegroups.
This not only gives you better control over performance and backups, but it also opens up options for maintenance and recovery that you wouldn't have with a one filegroup strategy.
Sounds good ? Let's dive in.

Why You Should Care About Filegroup Architecture

Here’s the deal, your filegroup setup might seem like a minor part of database design, but it can affect how well your database performs, how fast you can back it up, and how easily you can manage it over time.
The way you set up your filegroups can also determine how easy it is to recover from an issue or even move data around when needed.

Instead of tossing everything into the PRIMARY filegroup or continuing adding more filegroups on top (just to split size of files), isolating schemas into separate filegroups gives you more control over your environment.

That means:

Better Performance: Distribute the workload across different physical disks for faster I/O.
Faster and Smarter Backups: Want to restore just part of the database ? No problem.
Minimal Downtime for Maintenance: You can work on one part of the database while the rest stays up and running.

Benefits You'll Love..

1. Selective Restore means Less Downtime

One of the coolest things about isolating schemas into their own filegroups is that you can restore them individually.
Let's say you have a corruption issue in the "[abe_eu]" schema. Because "[abe_eu]" is in its own filegroup "[abe_eu_fg]", you can just restore that filegroup without affecting the rest of the database.
This can save you hours (or even days) of downtime, depending on your database size.

Plus, because the PRIMARY filegroup holds system objects, SQL Server can still bring the database online even if other filegroups are down.
Your database stays mostly available while you're restoring just the affected part.

2. Offline Filegroups for Easy Maintenance

Want to move a file around, or switch up the storage location of some .ndf files ?
If you need to do some heavy maintenance (like moving data to a new disk), you can take just one filegroup offline while leaving the rest of the database online.

For example, if you need to migrate the abe_eu_fg FileGroup but don't want to disrupt everything else, you can just take abe_eu_fg offline temporarily.
The rest of your database will still be accessible to users, and you're free to do your thing with minimal impact on the service.

3. Better Schema Isolation and Flexibility

By isolating each schema in its own filegroup, you give yourself more flexibility for a ton of future use cases. Here's why it matters:

Security: You can set specific access policies at the filegroup level. If you want more control over who can access what, this gives you an easy way to manage that.
Data Movement: Moving a schema around becomes way easier. If you need to archive or migrate.
Performance Tuning.

SQL Code example:
Set up schema and filegroup

USE [AbeDB]
CREATE SCHEMA [abe_eu]
CREATE SCHEMA [abe_us]
GO

ALTER DATABASE [AbeDB] ADD FILEGROUP [abe_eu_fg]
ALTER DATABASE [AbeDB] ADD FILEGROUP [abe_us_fg]
GO

ALTER DATABASE [AbeDB] 
ADD FILE ( NAME = N'abe_eu_fg', FILENAME = N'D:\MSSQL\YourInstance\abe_eu_fg.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 
TO FILEGROUP [abe_eu_fg]
GO
ALTER DATABASE [AbeDB] 
ADD FILE ( NAME = N'abe_us_fg', FILENAME = N'D:\MSSQL\YourInstance\abe_us_fg.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) 
TO FILEGROUP [abe_us_fg]
GO

Set up your tables when creating them

CREATE TABLE [abe_eu].[TableTest]
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
) ON [abe_eu_fg];

Set up permissions on role

CREATE ROLE [abe_eu_r]
GO
ALTER ROLE [abe_eu_r] ADD MEMBER [testUser]
GO
GRANT SELECT ON SCHEMA::[abe_eu] TO [abe_eu_r]
GO

One of the most powerful aspects of this filegroup architecture is the ability to restore individual filegroups, rather than the entire database.
For example, imagine you encounter a corruption in one of your schema/filegroup. Since it is isolated in its own filegroup, you can restore other filegroups, leaving the rest of the database operational.

SQL Code example:
Do your maintenance

ALTER DATABASE [AbeDB] MODIFY FILE ( NAME = abe_eu_fg,   
   FILENAME = 'F:\MSSQL\TempFolder\abe_eu_fg.ndf'); 
GO
ALTER DATABASE [AbeDB] MODIFY FILE (NAME = 'abe_eu_fg', OFFLINE);
GO

After maintenance

RESTORE DATABASE [AbeDB] FILEGROUP = 'abe_eu_fg' WITH RECOVERY
GO

By taking the time to design your filegroup strategy early on, you're not only optimizing for current requirements but future-proofing your database architecture.
Whether you're dealing with large datasets, frequent schema changes, or high availability requirements, a modular filegroup approach will give you the tools to keep your SQL Server environment running smoothly and efficiently.
Check your database status, with filegroups, and sizes

 SELECT [databasefile].NAME  AS [FileName],
  [filegroup].NAME           AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id

Don’t miss my other posts for more tips and best practices.

Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver17

MSSQL Encryptions, Certificates, A-Symmetric Keys and more (Vol 1)

Amar Abaz — Sun, 11 Jan 2026 15:36:22 +0000

In today's world, protecting sensitive data is a top priority.
I'll be covering this topic in two volumes. I will explore encryption strategies within Microsoft SQL Server, focusing on best practices for securing your data. The second volume will dive into practical examples and some extra tips. In this section, we will focus on the best practices and available options, including

Service Master Key (SMK) and Database Master Key (DMK): Key components of key management in SQL Server. Important in securing your database and ensuring key recovery.
Symmetric and Asymmetric Keys: Difference and use cases for symmetric (single key) encryption versus asymmetric (public/private key) encryption, and how they protect data at rest and in transit.
Certificates: They are a more secure and scalable solution for key management, and they improve your encryption strategy.
Transparent Data Encryption (TDE): TDE automatically encrypts an entire database to protect data at rest.
TLS Encryption: Protecting data in transit, ensuring that the data moving between your SQL Server instance and client applications is encrypted and secure.
Column-Level Encryption: A look at how you can protect sensitive data at the column level, offering flexibility for encryption on specific data within your database.

Properly managing Certificates, Symmetric keys, and Asymmetric keys can be tricky. That is why it's important to avoid relying on simple passwords, and why you should instead leverage Database Master Keys (DMK), Service Master Keys (SMK), and Certificates for a more robust and recoverable encryption strategy.

Database Master Key (DMK) and Service Master Key (SMK)

Both the DMK and SMK are crucial for key management in SQL Server.
The DMK is used to protect certificates and symmetric keys within a database. It's essential for managing encryption at the database level.
The SMK is tied to the SQL Server instance and is used to encrypt the DMK and other server level secrets. After restarting SQL Server if the SMK and DMK are in place and were correctly created, they are reloaded into memory automatically when SQL Server starts. All your certificates (including those signed by the DMK) should be accessible because the SMK and DMK are available in memory to decrypt them.

Why Backups Matter
It's important to back up both the SMK and DMK to ensure that your encryption strategy is recoverable. Backups are critical for disaster recovery, key restoration, and server migrations. They are especially necessary if your server crashes, is rebuilt, or if keys are accidentally deleted or corrupted.

To check if you have SMK and DMK use this command:

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DBMasterKey##';

Command to create them:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password$1';

BACKUP MASTER KEY 
TO FILE = 'C:\ABEDMK' ENCRYPTION BY PASSWORD = 'Password';

BACKUP SERVICE MASTER KEY 
TO FILE = 'C:\ABESMK' ENCRYPTION BY PASSWORD = 'Password';

To check whether your certificates are protected by password or DMK use this command:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates;

Certificates

While SQL Server allows you to use passwords for encryption, using Certificates for key management is a much more secure and scalable solution.
The flexibility of certificates ensures that you can easily back up and recover keys without relying on passwords alone.
Using certificates not only enhances encryption but also allows you to elevate User rights by signing stored procedures. This can be incredibly useful in special cases where users need elevated permissions to perform certain tasks at the system level, such as sending emails or executing jobs from user database.
Certificates offer multiple encryption options, including algorithms like SHA-256 and RSA.
These algorithms ensure that your encryption processes are not only secure but also meet industry standards, providing a higher level of protection for your data.

Create and Backup your certificate with PK and secure it by this command.
Don't forget to write expiry date because by default it is one year.
RSA 2048 is the default encryption algorithm.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='dbPassword$1';

CREATE CERTIFICATE ABECert
WITH SUBJECT='Data_Encryption',
EXPIRY_DATE = '2040-12-31';

BACKUP CERTIFICATE ABECert
TO FILE = 'C:\ABECert'
WITH PRIVATE KEY (file='C:\ABECert_key.pvk',
ENCRYPTION BY PASSWORD='Password');

Because i haven't use ENCRYPTION BY PASSWORD, my certificate is protected by DMK.

Symmetric Keys combined with Certificate (for server-side encryption)

With symmetric keys, you can encrypt sensitive columns at the database level, ensuring that data is encrypted at rest and can only be accessed by authorized users or applications.
My advice for you is not to use encryption by passwords while creating them but rely solely on your protected Certificate.
This approach ensures that sensitive information is kept secure even if the database is compromised, as the data will only be readable when the permission to Certificate is available to user.
Once set up, you can encrypt your columns and decrypt them during SELECT, UPDATE, or INSERT operations.
We'll dive into a practical example of how to implement this case in the next volume.

List all symmetric key by this command:

SELECT * FROM sys.symmetric_keys;

With this command, I am creating a symmetric key that will rely on my certificate that I created earlier.

CREATE SYMMETRIC KEY ABESymmKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ABECert;

Asymmetric Keys and Always Encrypted (for client-side encryption)

Always Encrypted is an excellent feature that uses asymmetric keys for encrypting and decrypting sensitive data transparently.
This way allows you to encrypt sensitive columns directly from the application, ensuring that the decryption key never leaves the client-side application. This ensures maximum data protection.
Always Encrypted ensures that data is encrypted on the client side and stays encrypted when it’s stored in the database, with no possibility of anyone (including DBAs) reading the sensitive data without the decryption keys.

Check if exist by using this command:

SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys;

When you INSERT, UPDATE or SELECT, SQL Server will automatically encrypt it using the column encryption key (CEK).
There are two ways of encryption, DTERMINISTIC and RANDOMIZED. Difference is that with Deterministic encryption, identical values (example "Abeamar", "Abeamar") will always encrypt to the same hash.

Once you create your Certificate (in my case CMK_ABEAE_Cert), import it into the server's credentials. Then, to enable Always Encrypted on a column, use the following command:

CREATE COLUMN MASTER KEY [AbeMK]
WITH
(
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', 
    KEY_PATH = 'LocalMachine/My/AbeMK_Cert');

CREATE COLUMN ENCRYPTION KEY [AbeCEK]
WITH VALUES
(
    COLUMN_MASTER_KEY = [AbeMK],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0064003900620064006600370032003100650033003400660031003000640033006600390065003200310062003800310035003100310033003300640062003700610061003700660039003100620035001532B80FF86E6773B5DDB0E4A83B1568DA8646400A9612556FED5210F598D8198F7F89E440E891A3DC99ACE3B381FD40E92FFCE7351F1F74DADBE7C591CBA95C94F9640181AA8D3111F20B39FF4F4A0DBA34F1CB1402B7429893BCA3F89607D208FE63236FA13032CB546F5D521360E761E4555DA78D42C061D09B3C55287928CE25FE182C237CA94B57697BE6F0935655DD0F208E2EEEDA357EB99FC27816D79D217E8D913FCDD1690AF7291EF8D452D340266F4A9AE9F964F05B7718F211A400850AB4C95AF417F5F3080CFAD51CA7AE01634FE24DBF050D7C2FAB6759FB92D1302EB4467E55591C1AD44E3F44051BC545F979A6B951962C6C7237B92ABF1FD94E1197AF7C26A3D9C9BF6CF6C7C42C4FE1032084F49233743C5831F38199633A0F86145FEF95EA4187E6F98A67BF206DE5A36258D0A37B3E2D6E353ACF357F1FAEE7B9E59A81C8D223B3A4F70E55C3614AD7134A7C564F0A386C61998CB9E859423D855F23E57E069A70FD568BA9783472EC44354DACA35D5D4FA109DFC444F39143E2A4BD76810D184F7A36CFBF1449D25A0C011DB502D355AB41C47BCF48A6A74D9B53D51C68C431D4CE47FD5C7CF3A050B2CB58B540CDFBDE977C3BCB1CD3C7C7EB7783F30B8BCA9FD3BD30CBF5C8A71AC0480357CB50410E832DD70F790888FE9F14825E64639B5CA59980AAD20C20C22B9207E98AB4CF1D817EF104B4
)
GO

CREATE TABLE Abe_Users
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    phones NVARCHAR(11)
);

Now, all you need to do is provide your clients with the CEK and inform them to enable Always Encrypted so they can decrypt the data.

To encrypt a column "phones", simply right click on the table and select "Encrypt Column".

Keep in mind that if your application involves frequent data imports or exports, or handling large volumes of data every day the encryption and decryption process may slow down overall performance. Each time data is accessed, it must be decrypted for processing and then encrypted again before storage, which can introduce overhead.

Final thoughts on A-Symmetric options

Use Symmetric Encryption when performance is a priority and the database or infrastructure team controls the environment, with appropriate safeguards for key management.
Use Always Encrypted with Asymmetric Keys if data security is more important than performance, especially if you need to ensure that even DBAs can't access the data.

And if the level of security required isn't extremely high, and you're more focused on preventing unauthorized access in a lighter way, data masking function is an excellent option. It strikes a balance between usability and security, with practically no performance overhead, no encryption needed. Make it simple, just mask it.

ALTER TABLE [Abe_Users]
ALTER COLUMN [phones] ADD MASKED WITH (FUNCTION = 'partial(2,"XXXXXXX",1)');

Transparent Data Encryption (TDE)

This is another powerful encryption method that encrypts the entire database, including the transaction log, automatically and transparently.
It relies on a Database Encryption Key (DEK), which is encrypted by a certificate stored in the master database.
TDE is useful for securing data at rest, particularly in scenarios where you need to protect the physical files of the database.
We'll dive into a practical example of how to implement TDE in the next volume.
You can check if your database is encrypted, or how much time would it take after triggering encryption using this command:

        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
                 WHEN '0' THEN 'No encryption'
                 WHEN '1' THEN 'Unencrypted'
                 WHEN '2' THEN 'In progress'
                 WHEN '3' THEN 'Encrypted'
         WHEN '4' THEN 'Key change in progress'
         WHEN '5' THEN 'Decryption in progress'
         WHEN '6' THEN 'Protection change in progress'
                 ELSE 'No Status'
              END,
        percent_complete, 
        create_date, 
        key_algorithm, key_length,
        encryptor_thumbprint, 
        encryptor_type  
        FROM sys.dm_database_encryption_keys;

By using certificates for encryption, employing Database Master Keys (DMK) and Service Master Keys (SMK) for recovery and security, and leveraging Transparent Data Encryption (TDE) for database protection, you can create a robust, recoverable encryption strategy that ensures your data is safe even in the worst case scenario.

😎
Don't miss my other posts for more tips and best practices.
I'll be covering this topic in two volumes, with the second one focusing more on practical examples and also on TLS protections to encrypt data transmitted between a SQL Server instance and a client application, ensuring data privacy and integrity.
Stay tuned..

References:

Craft and Control Resource Pools on MSSQL

Amar Abaz — Wed, 07 Jan 2026 21:52:46 +0000

Time to take control of your server's resources..
On your MS SQL instance not all workloads should be treated equally.
One heavy query executed from SQL Server Management Studio (SSMS) can easily consume CPU and memory, impacting production workloads.

Resource Governor is an underused but powerful feature in SQL Server.
With a simple function and resource pool, you can dramatically improve stability and predictability in multi-user environments.

In this post, I’ll show how to use SQL Server Resource Governor to,

Detect connections coming from SSMS,
Assign them to a limited workload group,
Restrict their CPU and memory usage.

What is Resource Governor ?
Resource Governor is a SQL Server feature that allows you to control how much CPU and memory different workloads can consume.
This approach is especially useful for production safety, or DBA-controlled access.

After this setup any query executed from Microsoft SQL Server Management Studio will be assigned to a limited resource pool.
All other connections remain in the default pool.

This also means you can customize resource pools to suit your specific needs by modifying the function below.

You can control CPU, RAM resources for:

Specific SQL users,
Reporting or BI tools,
ETL jobs,
Ad-hoc users,
Third-party applications,
Nightly or background workloads. All you need to do is modify the function and route those sessions into the appropriate workload group.

Let's begin with example on limiting CPU & RAM for sessions comming from SSMS Studio,
1. Enable Governor.

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

2. Create your function.
This will decides which workload group a session should use.

CREATE FUNCTION [dbo].[ResourceGroup_Users]
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup SYSNAME;
DECLARE @ProgramName SYSNAME;

SET @ProgramName = CONVERT(SYSNAME, PROGRAM_NAME());
IF @ProgramName like 'Microsoft SQL Server Management Studio%'
BEGIN
    SET @WorkloadGroup = 'LimitedResourceGroup';
END
ELSE
BEGIN
    SET @WorkloadGroup = 'default';
END

RETURN @WorkloadGroup;

END
GO

Also, if you want to restrict specific SQL users, you can easily modify this function by adding an additional condition - IF, or ELSE IF.

IF SUSER_NAME() = 'testuser' THEN ...

This allows you to route specific users to a resource pool with customized limits.

3. Create a Resource Pool.
This ensures SSMS queries can never consume more than 30% CPU, 30% memory.

CREATE RESOURCE POOL [LimitedResourcePool]
WITH
(min_cpu_percent=0,
max_cpu_percent=30,
min_memory_percent=0,
max_memory_percent=30
)

4. Create a Workload Group.
Now we link a workload group to the limited resource pool.

CREATE WORKLOAD GROUP [LimitedResourceGroup]
USING [LimitedResourcePool];
GO

5. Enable the Classifier Function.
Finally, tell Resource Governor to use our function and reconfigure it.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=[dbo].[ResourceGroup_Users]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Now you check what you have created with these commands,

SELECT * FROM sys.resource_governor_configuration
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups

Keep in mind that,

These changes apply only to new sessions, not to existing ones. You should restart the SQL service or terminate sessions for changes to affect.
Resource Governor is available in SQL Server Standard Edition starting with SQL Server 2025. For all previous versions, it is an Enterprise-only feature.

To monitor which sessions are currently using your resource pool, you can run the following query. This will help you ensure that the sessions are properly classified and using the intended resources.

SELECT
s.session_id,
s.login_name,
rg.name AS resource_pool_name
FROM
sys.dm_exec_sessions AS s
JOIN
sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
JOIN
sys.dm_resource_governor_resource_pools AS rg
ON wg.pool_id = rg.pool_id
WHERE
s.is_user_process = 1;

6. Limit RAM/CPU usage for the entire SQL Server instance
While Resource Governor allows you to assign specific resource limits to workloads, it’s also important to know that you can limit the total memory and CPU usage for the entire SQL Server instance itself.
This prevents SQL Server from consuming all the available memory, leaving resources for other processes.
You can limit the maximum amount of RAM using this command:

EXEC sp_configure 'max server memory', 4096;  --Set maximum memory to 4 GB
RECONFIGURE;

You can limit the CPU usage by altering the worker thread using this command:

EXEC sp_configure 'max worker threads', 512;  --Limit worker threads
RECONFIGURE;

Now go ahead and put those resource pools to work.
I hope this helps you keep your SQL Server running smoothly!
Don’t miss my other posts for more tips and best practices.

Track Blocking and Deadlocks in MSSQL with my custom script

Amar Abaz — Sun, 04 Jan 2026 13:05:14 +0000

Blocking happens when one process holds a lock on a resource (a row or table..) and another process is waiting for that resource to be released.
The second process is blocked and will have to wait for the first process to release the lock.

Deadlocks, on the other hand, occur when two or more processes are waiting for each other to release locks on resources, creating a circular dependency.
Process 1 has Resource A locked, and Process 2 has Resource B locked.
They each need the resource the other holds to proceed, and since neither can proceed without the other releasing a lock, the sessions are at a stalemate. One of them is going to be a victim and the other session will be processed.

I wanted to have accurate information about locks on my databases. I did that using the right extended events and then created a custom SELECT query to extract the right information from the created file and present it in my custom reports.

- Key columns for deadlock sessions are deadlock victim, deadlock object, sql_text and the users in process. All with clear overview.

- Key columns for blocking sessions are blocking start and end time, duration, sql text and users in process. It's all displayed in ONE line, so it gives you a clear overview.

Below is an overview of the information you get at the end.

First we need to enable blocked process treshold and then create Extended Event. Treshold min value is 5 sec to check for blocks.

exec sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
exec sp_configure 'blocked process threshold (s)',5;
GO
RECONFIGURE;
GO

Blocking Event

CREATE EVENT SESSION blckCapture
ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.client_hostname
    ))
ADD TARGET package0.event_file(SET filename=N'C:\blckSessions.xel',max_file_size=(100),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 kB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=36000 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION blckCapture ON SERVER STATE = START;
ALTER EVENT SESSION blckCapture ON SERVER WITH (STARTUP_STATE=ON);
GO

Deadlock Event

CREATE EVENT SESSION deadlckCapture
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\dlckSessions.xel',max_file_size=(10),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION deadlckCapture ON SERVER STATE = START;
ALTER EVENT SESSION deadlckCapture ON SERVER WITH (STARTUP_STATE=ON);
GO

Create Views

Last step is to create Views in database of your choice. And after that you can create your reports.

CREATE VIEW vw_blckSessions AS
WITH blckData AS (
    SELECT
        DATEADD(HOUR, 1, event_data.value('(event/@timestamp)[1]', 'DATETIME')) AS EventTime,
        blocked_process.value('@spid', 'INT') AS BlockedSPID,
        blocking_process.value('@spid', 'INT') AS BlockingSPID,
        blocked_process.value('@hostname', 'NVARCHAR(256)') AS BlockedHostname,
        blocked_process.value('@loginname', 'NVARCHAR(256)') AS BlockedLoginName,
        blocked_process.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS BlockedSQLText,
        blocking_process.value('@hostname', 'NVARCHAR(256)') AS BlockingHostname,
        blocking_process.value('@loginname', 'NVARCHAR(256)') AS BlockingLoginName,
        blocking_process.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS BlockingSQLText
    FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file('C:\blckSessions*.xel', NULL, NULL, NULL)) AS Data
        CROSS APPLY event_data.nodes('//event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report') AS XEventData (blocked_report)
        CROSS APPLY XEventData.blocked_report.nodes('blocked-process/process') AS BlockedProcessNode (blocked_process)
    CROSS APPLY XEventData.blocked_report.nodes('blocking-process/process') AS BlockingProcessNode (blocking_process))
    ,blckData2 AS (SELECT
                    CONVERT(VARCHAR(19), MIN(EventTime), 120) AS Eventime_start,
                    CONVERT(VARCHAR(19), MAX(EventTime), 120) AS Eventime_last,
                    DATEDIFF(SECOND, MAX(EventTime), MIN(EventTime)) as Duration,
                    BlockingSPID,
            BlockingHostname,
            BlockingLoginName,
            BlockingSQLText,
            BlockedSPID,
                BlockedHostname,
                BlockedLoginName,
                BlockedSQLText
            FROM blckData
            GROUP BY BlockedSPID, BlockedHostname, BlockedLoginName, BlockedSQLText, BlockingSPID, BlockingHostname, BlockingLoginName, BlockingSQLText)
    SELECT
                    Eventime_start
                    ,Eventime_last
                    ,ABS(Duration) AS Duration
                    ,BlockingSPID
                    ,BlockedSPID
                ,BlockingHostname
                ,BlockingLoginName
                ,BlockingSQLText
                ,BlockedHostname
                ,BlockedLoginName
            ,BlockedSQLText
    FROM blckData2;

CREATE VIEW vw_dlckSessions AS
    SELECT
    event_data.value('(event/@timestamp)[1]', 'DATETIME') AS DeadlockStartTime,
    deadlock_node.value('@hostname', 'NVARCHAR(256)') AS Hostname,
    deadlock_node.value('@loginname', 'NVARCHAR(256)') AS LoginName,
    deadlock_node.value('@spid', 'INT') AS SPID,
    deadlock_node.value('(inputbuf)[1]', 'NVARCHAR(MAX)') AS SQLText,
    resource_node.value('@objectname', 'NVARCHAR(256)') AS ObjectName,
    CASE
        WHEN deadlock_node.value('@id', 'NVARCHAR(256)') = victim_node.value('@id', 'NVARCHAR(256)')
        THEN 1
        ELSE 0
    END AS Victim,
    CASE
        WHEN deadlock_node.value('@id', 'NVARCHAR(256)') = victim_node.value('@id', 'NVARCHAR(256)')
        THEN 'Yes'
        ELSE 'No'
    END AS Evicted
    FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\dlckSessions*.xel', NULL, NULL, NULL)) AS Data
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/process-list/process') AS ProcessNode (deadlock_node)
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/resource-list/keylock') AS ResourceNode (resource_node)
CROSS APPLY Data.event_data.nodes('//event[@name="xml_deadlock_report"]/data[@name="xml_report"]/value/deadlock/victim-list/victimProcess') AS VictimNode (victim_node);

With these two views you can monitor Session IDs on your sql instance with all the necessary information about time, history, sql text and more to debug.

SELECT * FROM vw_dlckSessions 
ORDER BY DeadlockStartTime DESC;

SELECT * FROM vw_blckSessions 
ORDER BY Eventime_last DESC;

To wrap things up, here are some tips to prevent deadlocks and blocking by optimizing your queries for better performance.

Deadlock Prevention Tips,

Lock Resources in Order. Always acquire locks in the same sequence to avoid circular waiting.
Keep Transactions Short. Minimize the duration of locks by limiting transaction size.
Use Retry Logic. Automatically retry transactions that are killed due to deadlocks.

Blocking Prevention Tips,

Optimize Queries. Efficient queries reduce execution time and lock duration.
Use Proper Indexing, speed up data access to minimize lock contention.
Limit Simultaneous Writes, reduce conflicting updates.

Linked Server vs OPENQUERY vs OPENROWSET vs OPENDATASOURCE

Amar Abaz — Sat, 03 Jan 2026 23:04:58 +0000

MS SQL Server provides several ways to execute remote (distributed) queries, but choosing the wrong approach can easily lead to performance bottlenecks, security exposure, and long-term maintenance issues.
Remote queries allow you to access tables on different servers or instances, perform data filtering and transformation across servers.

In this article, I will compare the four most common approaches,

For each method, I will cover,
How it works, where is it useful, what are the cons and a simple example.
At the end, I will explain why OPENQUERY is usually the best choice in real-world production systems, when combined with proper Linked Server configuration.
Before each test, enable statistics with command below.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

These settings will help us compare Logical reads, CPU time, execution behavior.

Linked Server

This is the most common way to query remote data in SQL Server.
Easy to write and read. No need for dynamic SQL for execution and variables.
Linked Server passwords are not hashed, but they are stored encrypted and never exposed in clear text, it provides a secure authentication.
Has mapping options (impersonate current login, use a specific remote login, or a fallback).
Using Linked Servers ensures that there is a clear, controlled configuration of the remote connection with proper authentication, encryption, and validation.
Filters are not always pushed to the remote server and this can result in large data transfers, high network usage, poor performance on large tables. In many cases, SQL Server will pull all rows from the remote table and apply the WHERE filter locally.
Connection is reusable.

sp_addlinkedserver 'w19';
SELECT * FROM [w19].[AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > '2011-05-31';

CPU Time: 4717 ms
Elapsed Time: 7734 ms

OPENQUERY

Query is executed entirely on the remote server. You must use Dynamic SQL. Filters and joins are applied remotely. It is a best choice for large tables and production workloads. OPENQUERY inherits Linked Server security and configuration. When querying non-SQL Server databases (ORACLE, MySql, PostgreSQL) this is the way. Connection is reusable.

SELECT * FROM OPENQUERY([w19], '
SELECT * FROM [AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > ''2011-05-31''');

CPU Time: 3367 ms
Elapsed Time: 5443 ms

OPENQUERY is designed to work with linked servers, meaning it sends a query to another server, which could be a remote database or a file system via a linked provider (like OLE DB or an ODBC driver) but you can do for example CSV also. For large files OPENROWSET tends to be faster.
You would need first to create path with Linked Server, and then you can use OPENQUERY. Code below:

EXEC sp_addlinkedserver 
    @server = 'FileServer', 
    @srvproduct = '', 
    @provider = 'Microsoft.ACE.OLEDB.12.0', 
    @datasrc = 'C:\Temp\EmployeeData.csv',
    @provstr = 'Text;HDR=YES;FMT=CSVDelimited';
SELECT * 
FROM OPENQUERY(FileServer, 'SELECT * FROM [EmployeeData.csv]')

One of the major advantages of using Linked Servers with OPENQUERY is the ability to centrally control and redirect connections. In a single script, you can point your links to different servers, for example switching between DEV, UAT, or production environments, without changing your query code.

SELECT name, provider, data_source FROM sys.servers;
DECLARE @name sysname = 'w19', @datasource sysname = 'w19\sql';
EXECUTE sp_setnetname @server = @name, @netname = @datasource;

The next two options - OPENROWSET and OPENDATASOURCE require AdHoc Distributed Queries to be enabled on the instance.
They are disabled for security reasons. When this feature is enabled, it allows users to execute queries that can directly access remote data sources without needing to configure a Linked Server.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

OPENROWSET

It is mainly designed for ad-hoc access to external data sources. Harder to govern. Unlike Linked Server and OPENQUERY, it does not reuse connections. Each execution performs a new connection handshake, authentication, query execution, and disconnect. This makes it unsuitable for frequent or high-volume workloads. However it is good for reading external files (CSV, Excel), temporary data access without creating a Linked Server (connection is direct). It allows 3-part-name in connection.
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended. Requires DSN or inline credentials (Password Exposure).

SELECT * FROM OPENROWSET(
    'SQLNCLI',
    'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;',
    'SELECT * FROM Sales.SalesOrderDetail 
WHERE ModifiedDate>= ''2011-05-31''''
);

-- CSV/Excel example:
SELECT * FROM OPENROWSET(
    BULK 'C:\\Temp\\EmployeeData.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) AS data;
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=NO;
   Database=C:\Temp\EmployeeData.xlsx',
   [Sheet1$]);

CPU Time: 4503 ms
Elapsed Time: 7594 ms

OPENDATASOURCE

This is very similar to OPENROWSET and shares most of the same limitations. It also performs a new connection handshake on each execution.
You do not send a query string, you expose the remote server as a temporary data source and reference objects (4-part-name in connection)..
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended.
Requires DSN or inline credentials (Password Exposure).

SELECT * FROM OPENDATASOURCE(
    'SQLNCLI',
    'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;'
).AdventureWorks2022.Sales.SalesOrderDetail;

-- CSV example:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\Temp;HDR=YES')..[EmployeeData#csv]

CPU Time: 4592 ms
Elapsed Time: 7718 ms

I was using AdventureWorks sample database for these examples.

WinForms in 2025? Here's the Slick MSSQL Monitoring App I Built

Amar Abaz — Tue, 07 Oct 2025 22:49:26 +0000

Monitor MSSQL performance with this custom app

Ever had to dig through multiple tools just to debug a failed MSSQL backup or trace an error switching from multiple MS SQL Instances ? 😥🤯

That’s why I built a small tool in Visual Studio, using C#, WinForms, Powershell, T-SQL, WMI to monitor SQL Server health, logs, and performance stats in one place.

The primary focus of my project was to make an app that will have all the necessary tools for database administrator in their everyday monitoring work.

What this tool monitors:

Disk usage and growth
Memory and CPU utilization
Database health and size
Backup and restore history
Active backup and restore operations
Table sizes and index fragmentation
SQL Server error logs
SQL Agent job failures
Schema object definitions
Raw SQL object extraction
Active sessions and blocking queries
Deadlock tracking
Custom session-level monitoring for your SQL Server instance

Feel free to contact me if you like what you see or have ideas what to improve or add....
I'd love to hear your feedback! 🤞

Video link: https://vimeo.com/1124492178

I've published the app also on,
https://www.producthunt.com/products/abemon

🧑‍🚀 Check the app on my GitHub:

abeamar / AbeMon

App created using Visual Studio, C#, WinForms, T-SQL, WMI. This App is for Light DBA everyday work on monitoring and troubleshooting.

AbeMon App 1.0.0

App created using Visual Studio, C#, WinForms, T-SQL, PowerShell, WMI. This App is for Light DBA everyday work on monitoring and troubleshooting on MSSQL.

1. About
2. Options
2.1. System
2.2. Errors
2.3. Sessions
2.4. Locks
2.5. Export
2.6. Debug
3. Conclusion

1. About

The primary focus of this project is to make an App that will have all the necessary tools for database administrator in their everyday monitoring work. This app is a fully functional tool.
The app is provided as a trial for 30 days, allowing you to experience its full functionality. Feel free to contact me if you like what you see. This App is designed to deliver smooth performance while using minimal system resources, consuming just 10-20MB of RAM.
Full Article on dev.to

🐔 AbeMon Install options:

git clone "https://github.com/abeamar/AbeMon.git"

Choose the installation method that works best for you!

Using the .exe or…

View on GitHub

Cheers,
Amar.