FEED Validator

for Atom and RSS and KML

Congratulations!

This is a valid RSS feed.

Recommendations

This feed is valid, but interoperability with the widest range of feed readers could be improved by implementing the following recommendations.

line 60, column 0: content:encoded should not contain iframe tag [help]

<p><iframe title="Migrate SSRS Reports from one report server to another | S ...

line 64, column 0: content:encoded should not contain loading attribute (35 occurrences) [help]
```
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/sel ...
```
line 64, column 0: content:encoded should not contain data-src attribute (38 occurrences) [help]
```
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/sel ...
```
line 64, column 0: content:encoded should not contain data-lazy-load attribute (38 occurrences) [help]
```
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/sel ...
```
line 64, column 0: content:encoded should not contain data-srcset attribute (35 occurrences) [help]
```
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/sel ...
```
line 64, column 0: content:encoded should not contain sizes attribute (35 occurrences) [help]
```
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/sel ...
```
line 563, column 0: content:encoded should not contain data-start attribute (53 occurrences) [help]
```
<h2 data-start="840" data-end="885">Best Practices to Prevent Invalid Object ...
```
line 563, column 0: content:encoded should not contain data-end attribute (53 occurrences) [help]
```
<h2 data-start="840" data-end="885">Best Practices to Prevent Invalid Object ...
```

Source: https://www.sqlservercentral.com/feed

<?xml version="1.0" encoding="UTF-8"?><rss version="2.0"
xmlns:content="http://purl.org/rss/1.0/modules/content/"
xmlns:wfw="http://wellformedweb.org/CommentAPI/"
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:atom="http://www.w3.org/2005/Atom"
xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
>
<channel>
<title>Articles - SQLServerCentral</title>
<atom:link href="https://www.sqlservercentral.com/articles/feed" rel="self" type="application/rss+xml" />
<link>https://www.sqlservercentral.com</link>
<description>The #1 SQL Server community</description>
<lastBuildDate>Mon, 22 Sep 2025 17:08:44 +0000</lastBuildDate>
<language>en-GB</language>
<sy:updatePeriod>
hourly </sy:updatePeriod>
<sy:updateFrequency>
1 </sy:updateFrequency>
<generator>https://wordpress.org/?v=6.0.3</generator>
<item>
<title>Migrate SSRS Reports to a New Server in Just a Few Clicks</title>
<link>https://www.sqlservercentral.com/articles/migrate-ssrs-reports-to-a-new-server-in-just-a-few-clicks</link>
<dc:creator><![CDATA[Kunal Rathi]]></dc:creator>
<pubDate>Mon, 13 Oct 2025 00:00:04 +0000</pubDate>
<category><![CDATA[Reporting Services (SSRS)]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4642061</guid>
<description><![CDATA[Learn how to migrate SSRS reports to a new server using SSRS Reports Migration Wizard. Step-by-step guide to move reports, data sources, and datasets in just a few clicks.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/migrate-ssrs-reports-to-a-new-server-in-just-a-few-clicks">Migrate SSRS Reports to a New Server in Just a Few Clicks</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[Managing SSRS report migrations can be a time-consuming and error-prone process, especially when dealing with large enterprise report servers that contain hundreds of reports, datasets, data sources, and subscriptions. To simplify this process, we created the <a href="https://ssrsmigrationwizard.azureops.org/" target="_blank" rel="noreferrer noopener">SSRS Reports Migration Wizard</a>, a Visual Studio extension and standalone utility that helps you automate and validate migrations with minimal manual steps. In this guide, we’ll walk through how to migrate SSRS reports to new server in just a few clicks.
<h2 class="wp-block-heading">What is SSRS Reports Migration Wizard?</h2>
SSRS Reports Migration Wizard is a tool that enables you to:
<ul>
<li>Migrate reports, shared datasets, and shared data sources</li>
<li>Copy standard subscriptions</li>
<li>Migrate between servers, folders, or environments</li>
<li>Export and import SSRS items using an export file format</li>
<li>Perform offline review and validation before actual migration</li>
</ul>
The wizard is available as a Visual Studio extension and as a standalone tool.
<h2>Prerequisites:</h2>
Before starting, ensure:
1. You have access to both source and target SSRS servers.
2. Your user account has:
-at least Browser role on the source report server.
-at least Publisher role on the target report server.
If you’re unsure how to grant access, follow the <a href="https://ssrsmigrationwizard.azureops.org/docs/prerequisites.html#-granting-access-to-ssrs-report-server" target="_blank" rel="noopener">instructions</a> in the Granting Access Guide.
<h2 class="wp-block-heading">Use Cases</h2>
<ul class="wp-block-list">
<li class="">Migrating from dev to test/prod environments</li>
<li class="">Consolidating multiple SSRS servers</li>
<li class="">Backing up and archiving SSRS artifacts</li>
<li class="">Migrating within the same server to new folders</li>
</ul>
<h2 class="wp-block-heading">Steps to Migrate SSRS reports to new server</h2>
<iframe title="Migrate SSRS Reports from one report server to another | SSRS Reports Migration Wizard" width="500" height="281" src="https://www.youtube.com/embed/U-U-PAzOLP4?feature=oembed" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
Follow the below steps to achieve SSRS report migration from one server to another.
<h3 class="wp-block-heading">Step 1: Select the Source</h3>
Choose your source SSRS server or a <code>.SRMW</code> file created from a previous export.
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-source.png"><img loading="lazy" class="alignnone wp-image-4642192 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-source.png" data-lazy-load alt="migrate ssrs reports to new server" width="593" height="493" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-source.png 593w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-source-300x249.png 300w" sizes="(max-width: 593px) 100vw, 593px" /></a></h3>
<h3 class="wp-block-heading">Step 2: Select the Target</h3>
Provide connection details for the destination SSRS server. You can migrate to a different server, or a different folder on the same server.
<h3><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-target.png"><img loading="lazy" class="alignnone wp-image-4642193 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-target.png" data-lazy-load alt="migrate reporting services to another server" width="596" height="492" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-target.png 596w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/select-target-300x248.png 300w" sizes="(max-width: 596px) 100vw, 596px" /></a></h3>
<h3 class="wp-block-heading">Step 3: Choose Report Items to Migrate</h3>
Select specific folders, reports, datasets, and data sources using a tree view. Filters and checkboxes make large-scale selection easy.
<img src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://ssrsmigrationwizard.azureops.org/media/select-report-items.png" data-lazy-load />
<h3 class="wp-block-heading">Step 4: Update Data Source Connection Strings</h3>
SSRS Reports Migration Wizard includes an optional step to update data source connection strings during the migration process. This is particularly useful when migrating reports and datasets from a source server to a target server that uses different connection details.
<h4>1. Fetch Data Sources</h4>
On this screen, click the Fetch Data Sources button. The wizard will scan the selected SSRS items (from previous screen) and populate a grid with all connections that can be updated.
<img src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://ssrsmigrationwizard.azureops.org/media/fetch-data-sources.gif" data-lazy-load />
<h4>2. View and Manage Connections</h4>
The grid displays the Extension, Connection String, and Credential Retrieval method for each data source. Click the Edit link in the Manage column, which allows you to modify the connection.
Convert to shared data sources if connection exists - If this option is selected, the wizard will attempt to convert embedded data sources in reports to shared data sources during deployment, provided a shared data source with the same connection details is available in the selected SSRS items for deployment.
<h4>3. Update Properties</h4>
When you click Edit, a new dialog box appears (labeled “Manage Connection Properties”). Here you can:
<ul>
<li>Review the Shared Data Source(s) using this connection and Report(s) using this connection as an embedded data source.</li>
<li>Modify the Connection String.</li>
<li>Specify Credentials (e.g., Windows credentials or a user name and password) under “Log into the data source”.</li>
<li>Click Save to apply the changes.</li>
</ul>
<img src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://ssrsmigrationwizard.azureops.org/media/update-connection-string.png" data-lazy-load />
<h4 id="important-notes">Important Notes</h4>
<ul>
<li>The SSRS Reports Migration Wizard cannot retrieve passwords from embedded data sources on the source server. It is therefore essential to manually manage and input the connection strings with their corresponding passwords on this screen.</li>
<li>The Dependent Objects shown in the grid are those that have a connection embedded within them. These objects are directly using this connection.</li>
<li>The changes you make are not committed to the target server until the wizard completes the migration.</li>
<li>If no connections are found for the selected items, a message box will inform you that there are no connections available for update.</li>
</ul>
<h3 class="wp-block-heading">Step 5: Review Changes</h3>
The wizard shows a migration summary with new, changed, or skipped items. You can export this list or proceed with the migration.
<a href="https://www.sqlservercentral.com/wp-content/uploads/2025/09/review.jpg"><img loading="lazy" class="alignnone size-full wp-image-4647216" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/review.jpg" data-lazy-load alt="" width="599" height="499" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/review.jpg 599w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/review-300x250.jpg 300w" sizes="(max-width: 599px) 100vw, 599px" /></a>
<h3 class="wp-block-heading">Step 6: Finish and Validate</h3>
Once migration is complete, a final summary screen shows migrated item counts and logs. You can rerun this process anytime.
<a href="https://www.sqlservercentral.com/wp-content/uploads/2025/09/finish-1.jpg"><img loading="lazy" class="alignnone size-full wp-image-4647222" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/finish-1.jpg" data-lazy-load alt="" width="596" height="495" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/finish-1.jpg 596w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/finish-1-300x249.jpg 300w" sizes="(max-width: 596px) 100vw, 596px" /></a>
<h2 id="how-to-install" class="wp-block-heading">Installation</h2>
SSIS Catalog Migration Wizard can be installed as an extension to:
1. Visual Studio 2017, Visual Studio 2019 & Visual Studio 2022.
2. SSMS 18, 19, 20, 21(preview)
3. Standalone tool.
<h2 class="wp-block-heading">Standalone Usage</h2>
Don’t use Visual Studio? You can still run the wizard as a standalone tool:
1. Download the .vsix from <a href="https://marketplace.visualstudio.com/items?itemName=AzureOps.srmw2022" target="_blank" rel="noopener">Visual Studio Marketplace</a>.
2. Rename the .vsix to .zip and extract it.
3. Launch SSRS.Reports.Migration.Wizard.exe.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/migrate-ssrs-reports-to-a-new-server-in-just-a-few-clicks">Migrate SSRS Reports to a New Server in Just a Few Clicks</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Schema Design Basics for Power BI</title>
<link>https://www.sqlservercentral.com/articles/schema-design-basics-for-power-bi</link>
<dc:creator><![CDATA[Additional Articles]]></dc:creator>
<pubDate>Mon, 13 Oct 2025 00:00:27 +0000</pubDate>
<category><![CDATA[Uncategorized]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4656308</guid>
<description><![CDATA[You have a Power BI project that generates real-time reports for an inventory management system that uses SQL Server. You are aware that Power BI performance is heavily influenced by how your data is structured in SQL Server but don’t have a clear understanding of how to optimize your SQL data structure for Power BI. In this article, we look at different ways to structure the data and tables to help improve Power BI query performance.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/schema-design-basics-for-power-bi">Schema Design Basics for Power BI</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/schema-design-basics-for-power-bi">Schema Design Basics for Power BI</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Distributed Availability Group Health: T-SQL and Zabbix</title>
<link>https://www.sqlservercentral.com/articles/distributed-availability-group-health-t-sql-and-zabbix</link>
<dc:creator><![CDATA[Pablo Echeverria]]></dc:creator>
<pubDate>Fri, 10 Oct 2025 00:00:09 +0000</pubDate>
<category><![CDATA[Availability Group (AG)]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4645997</guid>
<description><![CDATA[Spotting synchronization disconnects between a primary and a secondary data center in a distributed availability group configuration can be hard, so here are valuable tools to help you with it
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/distributed-availability-group-health-t-sql-and-zabbix">Distributed Availability Group Health: T-SQL and Zabbix</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[After creating a distributed availability group using the steps described in my article, <a href="https://www.sqlservercentral.com/articles/sql-server-2022-clusterless-distributed-availability-group">SQL Server 2022 Clusterless Distributed Availability Group</a>, how can we verify if the synchronization between data centers is healthy?
In SQL Server Management Studio, when you right-click a distributed availability group, you will notice that there is no dashboard to confirm whether SQL Server is properly synchronizing data across data centers:
<img loading="lazy" class="alignnone size-full wp-image-4646022" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/imagen1.png" data-lazy-load alt="SSMS Distributed AG options" width="447" height="111" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/imagen1.png 447w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/imagen1-300x74.png 300w" sizes="(max-width: 447px) 100vw, 447px" />
Even if you check the dashboards of the primary and secondary data centers separately, you will not see if there is a problem in-between.
To solve this, we can run a query on the primary node of the primary data center:
<pre class="prettyprint lang-mssql">CREATE TABLE #Info (
AG_Name SYSNAME, is_distributed BIT, is_chained BIT, replica_server_name VARCHAR(256), [Database] SYSNAME,
role_desc VARCHAR(10), synchronization_health TINYINT, synchronization_health_desc VARCHAR(20), log_send_queue_size BIGINT, log_send_rate BIGINT,
redo_queue_size BIGINT, redo_rate BIGINT, suspend_reason_desc VARCHAR(30), last_sent_time DATETIME, last_hardened_time DATETIME,
last_redone_time DATETIME, last_commit_time DATETIME, secondary_lag_seconds BIGINT);
INSERT INTO #Info
SELECT ag.name AG_Name, ag.is_distributed, 0 'is_chained', ar.replica_server_name AS [AG], dbs.name AS [Database],
ars.role_desc, drs.synchronization_health, drs.synchronization_health_desc, drs.log_send_queue_size, drs.log_send_rate,
drs.redo_queue_size, drs.redo_rate, drs.suspend_reason_desc, drs.last_sent_time, drs.last_hardened_time,
drs.last_redone_time, drs.last_commit_time, drs.secondary_lag_seconds
FROM sys.databases dbs
INNER JOIN sys.dm_hadr_database_replica_states drs ON dbs.database_id = drs.database_id
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
UNION ALL
SELECT ag.name AG_Name, 1 'is_distributed', 1 'is_chained', ar.replica_server_name AS [AG], dbs.name AS [Database],
ars.role_desc, drs.synchronization_health, drs.synchronization_health_desc, drs.log_send_queue_size, drs.log_send_rate,
drs.redo_queue_size, drs.redo_rate, drs.suspend_reason_desc, drs.last_sent_time, drs.last_hardened_time,
drs.last_redone_time, drs.last_commit_time, drs.secondary_lag_seconds
FROM [VSRVSQLON].master.sys.databases dbs
INNER JOIN [VSRVSQLON].master.sys.dm_hadr_database_replica_states drs ON dbs.database_id = drs.database_id
INNER JOIN [VSRVSQLON].master.sys.availability_groups ag ON drs.group_id = ag.group_id
INNER JOIN [VSRVSQLON].master.sys.dm_hadr_availability_replica_states ars ON ars.replica_id = drs.replica_id
INNER JOIN [VSRVSQLON].master.sys.availability_replicas ar ON ar.replica_id = ars.replica_id;
UPDATE i
SET i.last_sent_time = i2.last_sent_time, i.secondary_lag_seconds = i2.secondary_lag_seconds, i.synchronization_health = i2.synchronization_health
FROM #Info i
INNER JOIN #Info i2 ON i2.[Database] = i.[Database] AND i2.is_distributed = 1 AND i2.is_chained = 0 AND i.is_distributed = 1 AND i.role_desc='PRIMARY';
DELETE
FROM #Info
WHERE is_distributed = 1 AND is_chained = 0;
ALTER TABLE #Info DROP COLUMN is_chained;
SELECT AG_Name, is_distributed, replica_server_name, [Database], role_desc,
synchronization_health, synchronization_health_desc, log_send_queue_size, log_send_rate, redo_queue_size,
redo_rate, suspend_reason_desc, last_sent_time, last_hardened_time, last_redone_time,
last_commit_time, secondary_lag_seconds
FROM #Info;
</pre>
This script requires a linked server to the primary node of the secondary data center. This linked server needs the security to “Be made using the login’s current security context”. Notice how we copy the distributed AG information (returned by the primary data center) to the corresponding record (primary node of the secondary data center); this ensures we only return one row per server.
Here is an example of the output for one database:
<img loading="lazy" class="alignnone size-full wp-image-4646168" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen2-1.png" data-lazy-load alt="T-SQL output" width="736" height="76" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen2-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen2-1-300x31.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
As the number of databases grows, spotting synchronization issues manually becomes impossible; that’s why monitoring software like Zabbix is so valuable:
<img loading="lazy" class="alignnone size-full wp-image-4646167" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen3-1.png" data-lazy-load alt="Zabbix AG Distributed dashboard" width="736" height="429" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen3-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen3-1-300x175.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
With it you can create a dashboard that shows the synchronization health of every database across all servers, and it can report immediately any disconnection. This also helps non-DBAs, and even non-technical people, to monitor things easily.
You can either download the prebuilt <a href="https://github.com/pabechevb/Zabbix/blob/main/Zabbix_7_Template_AGDistributed.xml">template from my GitHub repository</a> and adjust it, or you can follow the detailed 10-step guide below to build your own.
<h2>Step 1: Create a folder for custom SQL queries</h2>
If you installed Zabbix under “C:\Program Files\Zabbix Agent 2\”, create the folder: C:\Program Files\Zabbix Agent 2\Custom Queries\MSSQL
Then add a file named “AGDistributed.sql” and paste the SQL command shown earlier.
<h2>Step 2: Configure the Zabbix MSSQL plugin</h2>
In “C:\Program Files\Zabbix Agent 2\zabbix_agent2.d\”, locate “mssql.conf” and add:
<pre class="prettyprint lang-plain">Plugins.MSSQL.CustomQueriesDir=C:\Program Files\Zabbix Agent 2\Custom Queries\MSSQL</pre>
<h2>Step 3: Restart the Zabbix agent and test</h2>
Restart the agent after making configuration changes and after modifying any SQL file:
<pre class="prettyprint lang-plain">net stop "Zabbix Agent 2"</pre>
First test connectivity to the database replacing the tags with their real values, unquoted (check in my previous article <a href="https://www.sqlservercentral.com/articles/monitor-sql-server-using-zabbix">Monitor SQL Server using Zabbix</a>, the part related to Macros):
<pre class="prettyprint lang-plain">& "C:\Program Files\Zabbix Agent 2\zabbix_agent2.exe" -t mssql.ping[{$MSSQL.URI}, {$MSSQL.USER}, {$MSSQL.PASSWORD}]</pre>
Then assess the custom query (omitting the “.sql” extension):
<pre class="prettyprint lang-plain">& "C:\Program Files\Zabbix Agent 2\zabbix_agent2.exe" -t mssql.custom.query[{$MSSQL.URI}, {$MSSQL.USER}, {$MSSQL.PASSWORD}, AGDistributed]</pre>
Finally, start the service:
<pre class="prettyprint lang-plain">net start "Zabbix Agent 2"</pre>
<h2>Step 4: Create a new template in Zabbix</h2>
In Zabbix, click Data Collection --> Templates. Click Create template (top-right corner):
<img loading="lazy" class="alignnone wp-image-4646166 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen4-1.png" data-lazy-load alt="Create template" width="736" height="411" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen4-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen4-1-300x168.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen4-1-128x72.png 128w" sizes="(max-width: 736px) 100vw, 736px" />
Enter the following details, then click Add to save:
<ul>
<li>Template name: AG Distributed</li>
<li>Template groups: Templates/Database</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646165 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen5-1.png" data-lazy-load alt="New template" width="326" height="253" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen5-1.png 326w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen5-1-300x233.png 300w" sizes="(max-width: 326px) 100vw, 326px" />
This template will hold all the items, rules, and dashboards.
<h2>Step 5: Add a template item</h2>
In the template list, locate AG Distributed, then click Items:
<img loading="lazy" class="alignnone wp-image-4646164 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen6-1.png" data-lazy-load alt="Template - Items" width="486" height="193" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen6-1.png 486w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen6-1-300x119.png 300w" sizes="(max-width: 486px) 100vw, 486px" />
Click Create item (top-right corner) and fill in the details, then click Add:
<ul>
<li>Name: AG Distributed</li>
<li>Type: Zabbix agent (active)</li>
<li>Key: mssql.custom.query[{$MSSQL.URI},{$MSSQL.USER},{$MSSQL.PASSWORD},AGDistributed]</li>
<li>Type of information: Text</li>
<li>Update interval: 1m (one minute, increase later if needed)</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646163 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen7-1.png" data-lazy-load alt="Template new item" width="736" height="577" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen7-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen7-1-300x235.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
This item will capture the query results and make them available in JSON format.
<h2>Step 6: Associate the template with a host</h2>
Go to Monitoring --> Hosts, select the host where the SQL Server instance runs, under Configuration choose Host:
<img loading="lazy" class="alignnone wp-image-4646162 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen8-1.png" data-lazy-load alt="Host configuration" width="450" height="368" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen8-1.png 450w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen8-1-300x245.png 300w" sizes="(max-width: 450px) 100vw, 450px" />
In the Templates, type “AG Distributed” or use the Select button to find it, then click Update:
<img loading="lazy" class="alignnone wp-image-4646161 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen9-1.png" data-lazy-load alt="Add template to host" width="538" height="186" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen9-1.png 538w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen9-1-300x104.png 300w" sizes="(max-width: 538px) 100vw, 538px" />
Go to Monitoring --> Hosts, select the host where the SQL Server instance runs, under View, choose Latest data. In the Name field, search for “AG Distributed”. Hover over the Last value column; you should see the JSON output from the SQL script:
<img loading="lazy" class="alignnone wp-image-4646160 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen10-1.png" data-lazy-load alt="Item latest data" width="667" height="268" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen10-1.png 667w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen10-1-300x121.png 300w" sizes="(max-width: 667px) 100vw, 667px" />
If you see an Info icon in the last column, there is a problem with the data collection.
<h2>Step 7: Add a discovery rule</h2>
Discovery rules let Zabbix automatically generate monitoring items for every availability group, SQL instance, and database.
In the template list, locate AG Distributed, then click Discovery:
<img loading="lazy" class="alignnone wp-image-4646159 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen11-1.png" data-lazy-load alt="Template - Discovery" width="589" height="130" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen11-1.png 589w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen11-1-300x66.png 300w" sizes="(max-width: 589px) 100vw, 589px" />
Click Create discovery rule and fill in the details:
<ul>
<li>Name: AG Distributed</li>
<li>Type: Dependent item</li>
<li>Key: ag.distributed</li>
<li>Master item: select “AG Distributed” (the item created in step 5)</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646158 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen12-1.png" data-lazy-load alt="Create discovery rule" width="531" height="513" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen12-1.png 531w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen12-1-300x290.png 300w" sizes="(max-width: 531px) 100vw, 531px" />
Switch to the LLD macros tab (LLD stands for Low Level Discovery). Add three macros that uniquely identify each record:
<ul>
<li>{#AGNAME}: Availability group name</li>
<li>{#REPLICASERVERNAME}: SQL Server instance name</li>
<li>{#DATABASE}: Database name</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646157 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen13-1.png" data-lazy-load alt="Low level discovery" width="586" height="265" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen13-1.png 586w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen13-1-300x136.png 300w" sizes="(max-width: 586px) 100vw, 586px" />
Example: for a database named DB1 in two 2-node AGs (SQLPRODAG and SQLPREMAG), you will have:
<ul>
<li>SQLPRODAG CLOUD1 DB1</li>
<li>SQLPRODAG CLOUDDR2 DB1</li>
<li>SQLPREMAG ONPREMISE1 DB1</li>
<li>SQLPREMAG ONPREMISEDR2 DB1</li>
</ul>
LLD macros are keys created by Zabbix when the data comes. They must be uppercase, without spaces, underscores, or quotes. Choose any names that make sense to you – you will reference them in later objects.
The JSONPath is a query that returns a value; they must match exactly the name in the JSON (the names of the columns defined in our SQL query): they are case sensitive and do not allow spaces or quotes.
After saving, go to Monitoring --> Hosts, select the host where the SQL Server instance runs, and under Configuration choose Discovery. Confirm the new discovery rule, and any time you update this or to any underlying object, check the Info column for errors:
<img loading="lazy" class="alignnone wp-image-4646156 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen14-1.png" data-lazy-load alt="Created discovery rule" width="736" height="78" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen14-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen14-1-300x32.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
<h2>Step 8: Add an item prototype</h2>
In the template list, locate AG Distributed, then click Discovery. On the discovery rule, click Item prototypes:
<img loading="lazy" class="alignnone wp-image-4646155 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen15-1.png" data-lazy-load alt="Template - Item prototypes" width="568" height="63" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen15-1.png 568w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen15-1-300x33.png 300w" sizes="(max-width: 568px) 100vw, 568px" />
Click Create item prototype and enter the details:
<ul>
<li>Name: Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}</li>
<li>Type: Dependent item</li>
<li>Key: avoid using commas, enter synchronization_health[{#AGNAME}-{#REPLICASERVERNAME}-{#DATABASE}]</li>
<li>Type of information: Numeric (unsigned)</li>
<li>Master item: select “AG Distributed”</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646154 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen16-1.png" data-lazy-load alt="Create item prototype" width="713" height="601" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen16-1.png 713w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen16-1-300x253.png 300w" sizes="(max-width: 713px) 100vw, 713px" />
Go to the Preprocessing tab, and Add a Preprocessing step:
<ul>
<li>Name: JSONPath</li>
<li>Parameters: $[?(@.AG_Name == "{#AGNAME}" && @.replica_server_name == "{#REPLICASERVERNAME}" && @.Database == "{#DATABASE}")].synchronization_health.first()</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646153 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen17-1.png" data-lazy-load alt="Item preprocessing" width="736" height="184" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen17-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen17-1-300x75.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
This preprocessing searches in the JSON the three keys defined earlier in the LLD macros, then gets the field synchronization_health, and dynamically creates items; for example: Synchronization health of SQLPRODAG CLOUD1 DB1.
Search under Monitoring --> Hosts --> Items the name “synchronization health of”, and confirm no issues on Info on the discovered items:
<img loading="lazy" class="alignnone wp-image-4646152 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen18-1.png" data-lazy-load alt="Discovered items" width="735" height="163" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen18-1.png 735w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen18-1-300x67.png 300w" sizes="(max-width: 735px) 100vw, 735px" />
Go to Monitoring --> Hosts, select the host where the SQL Server instance runs, under View choose Latest data. Search the name “synchronization health of” to see the values received, and confirm no issues on Info:
<img loading="lazy" class="alignnone wp-image-4646151 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen19-1.png" data-lazy-load alt="Discovered items data" width="736" height="221" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen19-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen19-1-300x90.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
<h2>Step 9: Add a trigger prototype</h2>
In the template list, locate AG Distributed, then click Discovery. On the discovery rule, click Trigger prototypes:
<img loading="lazy" class="alignnone wp-image-4646150 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen20-1.png" data-lazy-load alt="Template - Trigger prototypes" width="589" height="48" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen20-1.png 589w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen20-1-300x24.png 300w" sizes="(max-width: 589px) 100vw, 589px" />
Click Create trigger prototype and enter the details:
<ul>
<li>Name: Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}</li>
<li>Severity: Disaster</li>
<li>Expression: click Add and enter the details:
<ul>
<li>Item: click Select prototype and select Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}</li>
<li>Condition: <> (not equal)</li>
<li>Value: 2 (healthy; see the <a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql">official documentation</a>)</li>
</ul>
</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646149 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen21-1.png" data-lazy-load alt="New trigger prototype" width="589" height="223" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen21-1.png 589w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen21-1-300x114.png 300w" sizes="(max-width: 589px) 100vw, 589px" />
<img loading="lazy" class="alignnone wp-image-4646148 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen22-1.png" data-lazy-load alt="Tigger prototype condition" width="589" height="209" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen22-1.png 589w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen22-1-300x106.png 300w" sizes="(max-width: 589px) 100vw, 589px" />
Once added, you will see alerts under Current problems or in the host's problems:
<img loading="lazy" class="alignnone wp-image-4646147 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen23-1.png" data-lazy-load alt="Current problems" width="736" height="114" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen23-1.png 736w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen23-1-300x46.png 300w" sizes="(max-width: 736px) 100vw, 736px" />
If you have email alerts configured for Disaster, you will also receive one.
<h2>Step 10: Create the dashboard</h2>
Finally, let us visualize everything. In the template list, locate AG Distributed, then click Dashboards:
<img loading="lazy" class="alignnone wp-image-4646146 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen24-1.png" data-lazy-load alt="Template dashboards" width="589" height="144" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen24-1.png 589w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen24-1-300x73.png 300w" sizes="(max-width: 589px) 100vw, 589px" />
Click Create dashboard (top-right), enter a name (i.e., “AG Distributed”) and click Apply. On the canvas, add your first widget in the upper left corner:
<ul>
<li>Type: Graph</li>
<li>Name: enter the data center and server name, e.g., SQLPRODAG CLOUD1</li>
<li>Data set #1 item patterns: enter Synchronization health of SQLPRODAG CLOUD1 * replacing the actual data center and server name; do not forget the asterisk</li>
<li>Draw: points</li>
<li>In the Problems tab, enable show problems</li>
</ul>
<img loading="lazy" class="alignnone wp-image-4646145 size-full" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen25-1.png" data-lazy-load alt="Dashboard widget" width="613" height="213" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen25-1.png 613w, https://www.sqlservercentral.com/wp-content/uploads/2025/09/Imagen25-1-300x104.png 300w" sizes="(max-width: 613px) 100vw, 613px" />
Add additional widgets as needed. Each widget displays up to fifty items, so create multiple depending on the number of availability groups, nodes, and databases. When done, click Save changes (top-right). You can now go to Monitoring --> Hosts, select the host where the SQL Server instance runs. Under View, choose Dashboards and select AG Distributed to view the synchronization health.
That completes the setup. With this dashboard, you will have a clear, near-real-time view of distributed availability group health. It makes detecting and resolving synchronization issues far easier, and it is simple enough for both DBAs and non-technical users to interpret.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/distributed-availability-group-health-t-sql-and-zabbix">Distributed Availability Group Health: T-SQL and Zabbix</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Secure PostgreSQL in Docker: SSL, Certificates & Config Best Practices</title>
<link>https://www.sqlservercentral.com/articles/secure-postgresql-in-docker-ssl-certificates-config-best-practices</link>
<dc:creator><![CDATA[Additional Articles]]></dc:creator>
<pubDate>Fri, 10 Oct 2025 00:00:30 +0000</pubDate>
<category><![CDATA[PostgreSQL]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4656341</guid>
<description><![CDATA[Running Postgres in Docker is great for a quick test but what if you want it to behave like a proper, production-style setup with SSL encryption, certificate-based authentication, persistent volumes, and custom configurations? In this article, we’ll find out how
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/secure-postgresql-in-docker-ssl-certificates-config-best-practices">Secure PostgreSQL in Docker: SSL, Certificates & Config Best Practices</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/secure-postgresql-in-docker-ssl-certificates-config-best-practices">Secure PostgreSQL in Docker: SSL, Certificates & Config Best Practices</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Use SQL to Find a String in Database Tables, Stored Procedures, and User Defined Functions</title>
<link>https://www.sqlservercentral.com/articles/use-sql-to-find-a-string-in-database-tables-stored-procedures-and-user-defined-functions</link>
<dc:creator><![CDATA[Additional Articles]]></dc:creator>
<pubDate>Wed, 08 Oct 2025 00:00:24 +0000</pubDate>
<category><![CDATA[T-SQL]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4656307</guid>
<description><![CDATA[Searching for a string in database tables has been discussed over the years. There are several approaches to the problem (see References at the end of the article). See how to use SQL to find a string in different types of SQL Server objects and data.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/use-sql-to-find-a-string-in-database-tables-stored-procedures-and-user-defined-functions">Use SQL to Find a String in Database Tables, Stored Procedures, and User Defined Functions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/use-sql-to-find-a-string-in-database-tables-stored-procedures-and-user-defined-functions">Use SQL to Find a String in Database Tables, Stored Procedures, and User Defined Functions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Find Invalid Objects in SQL Server</title>
<link>https://www.sqlservercentral.com/articles/find-invalid-objects-in-sql-server-2</link>
<dc:creator><![CDATA[Nisarg Upadhyay]]></dc:creator>
<pubDate>Mon, 06 Oct 2025 00:00:31 +0000</pubDate>
<category><![CDATA[Advanced SQL]]></category>
<category><![CDATA[Database Administration]]></category>
<category><![CDATA[Database objects]]></category>
<category><![CDATA[MSSQL administration]]></category>
<category><![CDATA[#invalidObject]]></category>
<category><![CDATA[#SQL script]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?p=4160113&post_type=ssc_article&preview_id=4160113</guid>
<description><![CDATA[This article shows how you can find which objects in your database might not be valid after schema changes.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/find-invalid-objects-in-sql-server-2">Find Invalid Objects in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[One of the most important responsibilities of any database administrator is to maintain the integrity of the database, schema, and data stored in the underlying tables of any database. This article will teach us how to identify invalid or broken objects.
A broken or invalid object in a database means it is a database object referencing another object renamed or deleted. For example, if a stored procedure is referencing a table and the table is dropped/renamed, the stored procedure can be considered invalid/broken. Often, we find invalid objects when we deploy any patch or any script on the database, so it is a good practice to regularly check the list of invalid objects so we can fix them proactively.
In this article, I have explained the use cases with a simple demonstration.
<h2>Demo Setup</h2>
For demonstration, I created a database, named HospitalManagement, and a sample schema containing five tables, two stored procedures, and one view. The details are following:
<table style="height: 371px;" width="1299">
<tbody>
<tr>
<td>Database Object type</td>
<td>Object name</td>
</tr>
<tr>
<td>Tables</td>
<td>
<ul>
<li>Patients: The table stores the data of patients admitted to the hospital.</li>
<li>Doctors: The table stores the details of the doctors working in the hospital.</li>
<li>Appointments: The table stores the details of the appointment of patients and doctors.</li>
<li>Medication: The table stores the details of the medications prescribed by the doctors.</li>
<li>Medical_Records: The table stores the details of the patients, doctors, diagnoses, and treatments.</li>
</ul>
</td>
</tr>
<tr>
<td>Stored Procedures</td>
<td>
<ul>
<li>sp_Get_Doctor_Patient: The stored procedure provides the details of the Patients along with the doctor's name, diagnosis, and treatment.</li>
<li>sp_Get_Patient_Medications: The stored procedure provides the list of medications prescribed to the patient.</li>
</ul>
</td>
</tr>
<tr>
<td>View</td>
<td>
<ul>
<li>vw_PatientData: The view populates the list of patients admitted to the hospital.</li>
</ul>
</td>
</tr>
</tbody>
</table>
The schema diagram looks like the following image:
<a href="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478.png"><img loading="lazy" width="1043" height="851" class="alignnone size-full wp-image-4147648 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478.png 1043w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478-300x245.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478-1024x835.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51e26bf478-768x627.png 768w" sizes="(max-width: 1043px) 100vw, 1043px" /></a>
The script to create tables is following:
<pre class="prettyprint lang-mssql">USE hospitalmanagement
go
CREATE TABLE patients (
patient_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
address VARCHAR(100),
phone_number VARCHAR(15),
emergency_contact_name VARCHAR(50),
emergency_contact_phone VARCHAR(15)
);
CREATE TABLE doctors (
doctor_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
specialization VARCHAR(100),
phone_number VARCHAR(15),
years_of_experience INT
);
CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
appointment_date DATE,
appointment_time TIME,
complaint VARCHAR(500),
FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);
CREATE TABLE medications (
medication_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
medication_name VARCHAR(100),
dosage VARCHAR(100),
start_date DATE,
end_date DATE,
FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);
CREATE TABLE medical_records (
record_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
diagnosis VARCHAR(500),
treatment VARCHAR(500),
date_of_visit DATE,
FOREIGN KEY (patient_id) REFERENCES patients (patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);
</pre>
The script to create the stored procedure is here:
<pre class="prettyprint lang-mssql">CREATE PROC sp_Get_Doctor_Patient
@PatientID int
AS
Begin
SELECT P.first_name + ' ' + P.last_name AS 'Patient Name',P.date_of_birth,P.address,d.first_name + ' ' + D.last_name AS 'Doctor Name' ,mr.diagnosis,mr.treatment
FROM patients p INNER JOIN medical_records mr ON mr.patient_id=P.patient_id
INNER JOIN doctors d ON mr.doctor_id=D.doctor_id
WHERE P.patient_id=@PatientID
END
CREATE PROC sp_Get_Patient_Medications
@MedicationName VARCHAR(100)
AS
Begin
SELECT P.first_name + ' ' + P.last_name AS 'Patient Name',P.date_of_birth,P.address,
m.medication_name AS 'Medicine Name'
FROM patients p RIGHT JOIN medications m WITH (INDEX(IDX_medications_medication_name)) ON m.patient_id=P.patient_id
WHERE m.medication_name=@MedicationName
END
</pre>
The script to create a view is the following:
<pre class="prettyprint lang-mssql">CREATE VIEW vw_PatientData
AS
SELECT P.first_name + ' ' + P.last_name AS 'Patient Name',P.date_of_birth,P.address,d.first_name + ' ' + D.last_name AS 'Doctor Name' ,mr.diagnosis,mr.treatment, m.medication_name,m.dosage
FROM patients p INNER JOIN medical_records mr ON mr.patient_id=P.patient_id
INNER JOIN doctors d ON mr.doctor_id=D.doctor_id
LEFT JOIN medications m ON p.patient_id=m.patient_id</pre>
Now, First, drop the medications table by running the below query:
<pre class="prettyprint lang-mssql">DROP TABLE medications</pre>
Once a table is dropped, execute the stored procedure named sp_Get_Patient_Medications.
<pre class="prettyprint lang-mssql">EXEC sp_Get_Doctor_Patient @PatientID = 1</pre>
Query screenshot
<a href="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a.png"><img loading="lazy" width="1176" height="311" class="alignnone size-full wp-image-4147650 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a.png 1176w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a-300x79.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a-1024x271.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f1823b8a-768x203.png 768w" sizes="(max-width: 1176px) 100vw, 1176px" /></a>
The stored procedure returns an error because the SELECT statement within the stored procedure uses using medication table to populate the data.
Let us run the SELECT query on vw_PatientData to populate the data of the patients.
<pre class="prettyprint lang-mssql">USE HospitalManagement
go
SELECT * FROM vw_PatientData vpd</pre>
Query Screenshot
<a href="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735.png"><img loading="lazy" width="1176" height="289" class="alignnone size-full wp-image-4147653 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735.png 1176w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735-300x74.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735-1024x252.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51f7172735-768x189.png 768w" sizes="(max-width: 1176px) 100vw, 1176px" /></a>
It also returns the error binding errors because the SELECT query used to create a view cannot find the underlying table.
<h2>How to Find Invalid Objects</h2>
Unlike Oracle, SQL Server does not have any meta-data table which can be used to find invalid/broken database objects. Some<a href="https://www.devart.com/dbforge/sql/sql-tools/?utm_source=article&utm_medium=referral&utm_content=find+invalid+objects+in+sql+server"> SQL Tools</a> can be used to find invalid objects; however, we will use a custom script to help us identify invalid or broken database objects.
To do that, first, we must find the object dependency of the dropped database object. For example, we have created a view using two tables, and we want details of the tables used to create a view. Such object dependencies can be found by querying<a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-ver16"> sys.sql_expression_dependencies</a> and<a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-objects-transact-sql?view=sql-server-ver16"> sys.all_objects</a>. The following script helps us to identify the list of invalid objects.
<pre class="prettyprint lang-mssql">SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#invalid_db_objects') IS NOT NULL
DROP TABLE #invalid_db_objects
CREATE TABLE #invalid_db_objects (
invalid_object_id INT PRIMARY KEY
, invalid_obj_name NVARCHAR(1000)
, custom_error_message NVARCHAR(3000) NOT NULL
, invalid_obj_type CHAR(2) NOT NULL
)
INSERT INTO #invalid_db_objects (invalid_object_id, invalid_obj_name, custom_error_message, invalid_obj_type)
SELECT
cte.referencing_id
, obj_name = QUOTENAME(SCHEMA_NAME(all_object.[schema_id])) + '.' + QUOTENAME(all_object.name) ,
'Invalid object name ''' + cte.obj_name + ''''
,all_object.[type]
FROM ( SELECT
sed.referencing_id
, obj_name = COALESCE(sed.referenced_schema_name + '.', '') + sed.referenced_entity_name
FROM sys.sql_expression_dependencies sed
WHERE sed.is_ambiguous = 0 AND sed.referenced_id IS NULL
) cte
JOIN sys.objects all_object ON cte.referencing_id = all_object.[object_id]
SELECT invalid_obj_name [Invalid OBJECT NAME] , custom_error_message [Error Message], invalid_obj_type [Object Type] FROM #invalid_db_objects</pre>
Let us run the above script on the HospitalManagement database to find invalid/broken objects.
Query output
<a href="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff.png"><img loading="lazy" width="1176" height="499" class="alignnone size-full wp-image-4147669 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff.png 1176w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff-300x127.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff-1024x435.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/img_63e51fee3edff-768x326.png 768w" sizes="(max-width: 1176px) 100vw, 1176px" /></a>
The above screenshot shows that the query has returned a list of invalid objects. We can automate the execution of this script using the SQL Server Agent job. The regular evaluation of script output can help us to maintain database schema by removing/fixing the invalid database objects.
Here are some best practices that you can implement to prevent invalid objects.
<h2 data-start="840" data-end="885">Best Practices to Prevent Invalid Objects</h2>
<ul data-start="886" data-end="1484">
<li data-start="886" data-end="1035">
Use Schema-Bound Views or functions: The views or functions created using <code data-start="943" data-end="963">WITH SCHEMABINDING</code> option became a schema bound and you can not alter or drop the underlying column or a table.
</li>
<li data-start="1036" data-end="1159">
Implement Change Management Policies: Establish a change management policies. For example, before production deployment, any scripts that changes the database schema, must be reviewed and approved by DBAs.
</li>
<li data-start="1160" data-end="1275">
Automated Testing: Before production release, always run validation scripts in staging/UAT environments. You can use the script that I have explained in this article.
</li>
<li data-start="1362" data-end="1484">
Scheduled Monitoring: Automate the process of finding invalid objects via SQL Server Agent jobs and send alerts with Database Mail. I will cover the same in our next article.
</li>
</ul>
<h2 data-start="7638" data-end="7651">Conclusion</h2>
Broken or invalid database objects can cause runtime failures in applications and can cause an outage. Since SQL Server doesn’t natively track the broken objects and DBAs should implement proactive monitoring using scripts. In this article we understand how to identify the broken or invalid objects in SQL Server. In my next article, I will explain how to automate the process of identifying and email the list of invalid database objects.
Stay tuned..!!!
 
 
 
 
 
 
 
 
 
 
Hope this article helps to identify the invalid database objects.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/find-invalid-objects-in-sql-server-2">Find Invalid Objects in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>SQL Server 2025 Makes Memory Troubleshooting Easier.</title>
<link>https://www.sqlservercentral.com/articles/sql-server-2025-makes-memory-troubleshooting-easier</link>
<dc:creator><![CDATA[Additional Articles]]></dc:creator>
<pubDate>Mon, 06 Oct 2025 00:00:42 +0000</pubDate>
<category><![CDATA[Performance and Tuning]]></category>
<category><![CDATA[SQL Server 2025]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4656311</guid>
<description><![CDATA[SQL Server 2025 introduces a new sys.dm_os_memory_health_history view to make it easier for meatbags like you and robots like Copilot to know if the SQL Server has been under memory pressure recently.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/sql-server-2025-makes-memory-troubleshooting-easier">SQL Server 2025 Makes Memory Troubleshooting Easier.</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/sql-server-2025-makes-memory-troubleshooting-easier">SQL Server 2025 Makes Memory Troubleshooting Easier.</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Oracle Performance Tuning: Practical Techniques Every DBA Should Master</title>
<link>https://www.sqlservercentral.com/articles/oracle-performance-tuning-practical-techniques-every-dba-should-master</link>
<dc:creator><![CDATA[Udaya Veeramreddygari]]></dc:creator>
<pubDate>Fri, 03 Oct 2025 00:00:00 +0000</pubDate>
<category><![CDATA[Oracle]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4639869</guid>
<description><![CDATA[Here are a few things every Oracle DBA should know about performance in their databases.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/oracle-performance-tuning-practical-techniques-every-dba-should-master">Oracle Performance Tuning: Practical Techniques Every DBA Should Master</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[As database professionals, we've all been there – that dreaded moment when users start complaining about slow queries, and suddenly everyone's looking at you with that "fix it now" expression. Oracle performance tuning can feel overwhelming, especially when you're under pressure, but the good news is that most performance issues stem from a handful of common culprits. Let me walk you through some battle-tested techniques that have saved my bacon more times than I can count.
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Start with the Low-Hanging Fruit: Statistics and Execution Plans</h2>
Before diving into complex tuning strategies, always check if your statistics are current. Oracle's cost-based optimizer relies heavily on accurate statistics to make smart decisions about query execution paths. I've seen queries run 10x slower simply because someone forgot to gather stats after a large data load.
Here's the thing about statistics gathering – <code class="bg-text-200/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.4rem] px-1 py-px text-[0.9rem]">SIZE AUTO</code> might seem convenient, but it can be a performance killer on large databases. Oracle decides whether histograms are needed and how many buckets to create, which can lead to extremely long statistics collection times. For production environments, I prefer <code class="bg-text-200/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.4rem] px-1 py-px text-[0.9rem]">SIZE REPEAT</code> to maintain consistent performance while keeping the histogram settings that already work well.
<pre class="prettyprint lang-mssql">-- Check when statistics were last gathered
SELECT table_name, last_analyzed, num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name IN ('CUSTOMERS', 'ORDERS', 'ORDER_ITEMS')
ORDER BY last_analyzed DESC;
-- Gather fresh statistics - performance-conscious approach
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'CUSTOMERS',
estimate_percent => 10, -- Fixed percentage for predictable performance
method_opt => 'FOR ALL COLUMNS SIZE REPEAT', -- Maintains existing histogram settings
cascade => TRUE,
degree => 4 -- Parallel execution for faster collection
);
END;
/
-- For initial setup or when you need to establish histograms
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'CUSTOMERS',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE 254', -- Explicit bucket count
cascade => TRUE
);
END;
/</pre>
Once your stats are current, examine execution plans for problematic queries. The <code class="bg-text-200/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.4rem] px-1 py-px text-[0.9rem]">EXPLAIN PLAN</code> statement is your best friend here, but don't just run it and walk away – actually analyze what Oracle is telling you.
<pre class="prettyprint lang-mssql">-- Get execution plan with cost information
EXPLAIN PLAN FOR
SELECT c.customer_name, COUNT(o.order_id) as order_count,
SUM(oi.quantity * oi.unit_price) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= DATE '2023-01-01'
GROUP BY c.customer_id, c.customer_name;
-- Display the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);</pre>
Look for red flags like full table scans on large tables, nested loop joins with high cardinalities, or operations with suspiciously high costs. These usually point you toward your performance bottlenecks.
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Indexing: The Art of Strategic Access Paths</h2>
Proper indexing can make or break your database performance. But here's the thing – more indexes aren't always better. Each index comes with maintenance overhead, so you want to be strategic about what you create.
Before rushing to create new indexes, dig deeper into how your queries have actually performed over time. Check the AWR repository to see if Oracle has found better execution paths using hash joins or other methods:
<pre class="prettyprint lang-mssql">-- Review historical execution plans for a specific query
SELECT sql_id, plan_hash_value, executions_total,
elapsed_time_total/1000000 as elapsed_seconds_total,
cpu_time_total/1000000 as cpu_seconds_total,
buffer_gets_total, disk_reads_total
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id' -- Replace with your SQL_ID
ORDER BY snap_id DESC;
-- Get the actual execution plan details
SELECT operation, options, object_name, cost, cardinality, bytes
FROM dba_hist_sql_plan
WHERE sql_id = '&sql_id'
AND plan_hash_value = &plan_hash_value -- Use the best performing plan
ORDER BY id;</pre>
This historical data reveals crucial insights – maybe Oracle is already using hash joins effectively, or perhaps a different execution plan performed much better in the past. Sometimes a full table scan with a hash join outperforms an index-based nested loop, especially when dealing with larger result sets.
For the query above, let's say you notice consistent full table scans on the orders table and the historical data shows this is indeed a bottleneck. A composite index might be exactly what you need:
<pre class="prettyprint lang-mssql">-- Create a composite index to support the WHERE clause and JOIN
CREATE INDEX idx_orders_date_customer
ON orders (order_date, customer_id);
-- For queries that frequently filter by customer and date range
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);</pre>
The order of columns in composite indexes matters enormously. Oracle can use an index even if you don't specify all columns, but only if you include the leading columns. Think of it like a phone book – you can find "Smith, John" easily, but finding all the "Johns" regardless of last name requires scanning the entire book.
Don't forget about function-based indexes for queries that use expressions:
<pre class="prettyprint lang-mssql">-- If you frequently search by uppercase customer names
CREATE INDEX idx_customers_upper_name
ON customers (UPPER(customer_name));
-- Now this query can use the index
SELECT * FROM customers
WHERE UPPER(customer_name) = 'JOHN SMITH';</pre>
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">SQL Tuning: Writing Smarter Queries</h2>
Sometimes the issue isn't with indexes or statistics – it's with how we write our SQL. Here are some patterns I've learned to watch for:
Avoid functions in WHERE clauses on indexed columns:
<pre class="prettyprint lang-mssql">-- This prevents index usage
SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2023-06-15';
-- This allows index usage
SELECT * FROM orders
WHERE order_date >= DATE '2023-06-15'
AND order_date < DATE '2023-06-16';</pre>
Use EXISTS instead of IN with subqueries:
<pre class="prettyprint lang-mssql">-- Less efficient for large datasets
SELECT * FROM customers c
WHERE c.customer_id IN (
SELECT o.customer_id FROM orders o
WHERE o.order_date >= DATE '2023-01-01'
);
-- More efficient - stops at first match
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= DATE '2023-01-01'
);</pre>
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Memory Configuration: Getting the SGA Right</h2>
Oracle's System Global Area (SGA) configuration can dramatically impact performance. The three key components you should focus on are the buffer cache, shared pool, and PGA.
<pre class="prettyprint lang-mssql">-- Check current SGA configuration
SELECT component, current_size/1024/1024 as size_mb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;
-- Monitor buffer cache hit ratio (should be > 90%)
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets from cache',
'consistent gets from cache',
'physical reads cache');</pre>
If your buffer cache hit ratio is consistently below 90%, consider increasing the <code class="bg-text-200/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.4rem] px-1 py-px text-[0.9rem]">DB_CACHE_SIZE</code> parameter. However, don't just throw memory at the problem without understanding what's causing the cache misses.
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Query Optimization with Hints (Use Sparingly!)</h2>
The old advice of "hints as a last resort" isn't quite accurate anymore. Since Oracle 11g, hints have evolved into a legitimate tool for addressing optimizer challenges, especially with complex nested views, poor database designs, or when the cost-based optimizer simply can't make optimal decisions with available statistics.
Modern Oracle provides several approaches to guide query execution, each building on the previous:
The Performance Management Evolution:
<pre class="prettyprint lang-mssql">Manual Hints → SQL Profiles → SQL Baselines → SQL Plan Management → SQL Plan Directives
↓ ↓ ↓ ↓ ↓
Direct control Automatic Stable plans Comprehensive Adaptive
in SQL code optimization across changes plan evolution feedback</pre>
Here's how to use hints strategically in modern Oracle:
<pre class="prettyprint lang-mssql">-- Force a specific join order when the optimizer chooses poorly
SELECT /*+ ORDERED USE_NL(o oi) */
c.customer_name, o.order_date, oi.product_name
FROM customers c, orders o, order_items oi
WHERE c.customer_id = o.customer_id
AND o.order_id = oi.order_id
AND c.customer_id = 12345;
-- Force index usage when you're certain it's better
SELECT /*+ INDEX(orders idx_orders_date_customer) */
* FROM orders
WHERE order_date >= DATE '2023-01-01';</pre>
Beyond Manual Hints - Modern Oracle Options:
When hints become unwieldy or you need more sophisticated control, Oracle offers several automated alternatives:
SQL Profiles - Oracle can automatically generate optimized execution plans:
<pre class="prettyprint lang-mssql">-- Check existing SQL profiles
SELECT name, sql_text, status, created
FROM dba_sql_profiles
ORDER BY created DESC;</pre>
SQL Baselines - Ensure consistent performance across database changes:
<pre class="prettyprint lang-mssql">-- Create a baseline for a well-performing query
DECLARE
baseline_name VARCHAR2(128);
BEGIN
baseline_name := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '&your_sql_id'
);
END;
/</pre>
SQL Plan Management - The most comprehensive approach for complex environments where you need to evolve plans safely while maintaining performance stability.
These tools work behind the scenes using hint-like mechanisms but provide much more sophisticated control. They're particularly valuable when dealing with application code you can't modify or complex reporting queries where manual hint management becomes impractical.
The key is choosing the right tool for your scenario: use direct hints for quick fixes and testing, profiles for automatic optimization, baselines for stability, and full plan management for comprehensive performance governance in large, complex environments.
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Monitoring and Continuous Improvement</h2>
Performance tuning isn't a one-time activity. Set up monitoring to catch issues before your users do. However, be mindful of the performance impact your monitoring queries can have on production systems.
Smart Monitoring with v$sqlstats(Avoiding v$SQL whenever possible)
<pre class="prettyprint lang-mssql">-- Find your most expensive queries using v$sqlstats (better for production)
SELECT sql_id, plan_hash_value, executions,
elapsed_time_total/1000000 as elapsed_seconds_total,
cpu_time_total/1000000 as cpu_seconds_total,
buffer_gets_total, physical_read_bytes_total
FROM v$sqlstats
WHERE elapsed_time_total > 10000000 -- More than 10 seconds total
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;</pre>
AWR-Based Performance Analysis (Requires Diagnostic Pack License):
For production environments with proper licensing, AWR provides much more accurate and less intrusive monitoring:
<pre class="prettyprint lang-mssql">-- Top SQL by DB Time from AWR (last 7 days)
SELECT sql_id, plan_hash_value,
executions_total,
elapsed_time_total/1000000 as elapsed_seconds,
(elapsed_time_total/1000000) / (SELECT SUM(value)/1000000
FROM dba_hist_sys_time_model
WHERE stat_name = 'DB time') * 100 as pct_db_time
FROM dba_hist_sqlstat s
WHERE s.snap_id > (SELECT MAX(snap_id) - 7*24 FROM dba_hist_snapshot)
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;
-- Average Active Sessions (AAS) analysis - more meaningful than blocking sessions
SELECT to_char(sample_time, 'YYYY-MM-DD HH24') as hour,
event,
COUNT(*) as active_sessions,
ROUND(COUNT(*) / 60, 2) as avg_active_sessions
FROM dba_hist_active_sess_history
WHERE sample_time >= SYSTIMESTAMP - INTERVAL '24' HOUR
GROUP BY to_char(sample_time, 'YYYY-MM-DD HH24'), event
HAVING COUNT(*) > 30 -- Focus on significant wait events
ORDER BY hour DESC, active_sessions DESC;</pre>
Performance Trend Monitoring:
<pre class="prettyprint lang-mssql">-- Monitor top 10 SQL performance changes over time
WITH baseline AS (
SELECT sql_id,
AVG(elapsed_time_total/executions_total) as avg_elapsed_baseline
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN (SELECT MAX(snap_id) - 14*24 FROM dba_hist_snapshot)
AND (SELECT MAX(snap_id) - 7*24 FROM dba_hist_snapshot)
GROUP BY sql_id
HAVING SUM(executions_total) > 100
),
current_period AS (
SELECT sql_id,
AVG(elapsed_time_total/executions_total) as avg_elapsed_current
FROM dba_hist_sqlstat
WHERE snap_id > (SELECT MAX(snap_id) - 7*24 FROM dba_hist_snapshot)
GROUP BY sql_id
HAVING SUM(executions_total) > 100
)
SELECT b.sql_id,
b.avg_elapsed_baseline/1000 as baseline_ms,
c.avg_elapsed_current/1000 as current_ms,
ROUND((c.avg_elapsed_current - b.avg_elapsed_baseline) / b.avg_elapsed_baseline * 100, 1) as pct_change
FROM baseline b
JOIN current_period c ON b.sql_id = c.sql_id
WHERE ABS((c.avg_elapsed_current - b.avg_elapsed_baseline) / b.avg_elapsed_baseline) > 0.2
ORDER BY ABS(pct_change) DESC;</pre>
Important Licensing Note: The AWR-based queries above require Oracle's Diagnostic and Tuning Pack license. If you don't have this license, stick with the <code class="bg-text-200/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.4rem] px-1 py-px text-[0.9rem]">v$sqlstats</code> approach or use Statspack as a free alternative.
Why This Approach is Better:
<ul class="[&:not(:last-child)_ul]:pb-1 [&:not(:last-child)_ol]:pb-1 list-disc space-y-1.5 pl-7">
<li class="whitespace-normal break-words">v$sqlstats provides historical aggregated data without the concurrency issues of v$sql</li>
<li class="whitespace-normal break-words">AAS analysis gives you a more meaningful picture of database load than simple blocking session counts</li>
<li class="whitespace-normal break-words">Trend analysis helps you catch performance degradation before it becomes critical</li>
<li class="whitespace-normal break-words">Focus on DB Time percentage helps prioritize which SQL statements truly impact your users</li>
</ul>
The key is establishing baseline performance metrics and monitoring for significant deviations, rather than just looking for blocking sessions or arbitrary thresholds.
<h2 class="text-xl font-bold text-text-100 mt-1 -mb-0.5">Wrapping Up</h2>
Oracle performance tuning is both an art and a science. Start with the fundamentals – current statistics, proper indexing, and well-written SQL. Monitor your system regularly and don't be afraid to question the optimizer's decisions when something doesn't make sense.
Remember, the best performance improvement is often the simplest one. Before implementing complex solutions, make sure you've covered the basics. Your future self (and your users) will thank you for taking a methodical approach to performance tuning.
The key is to develop a systematic approach: identify the problem, understand the root cause, implement a targeted solution, and measure the results. With these techniques in your toolkit, you'll be well-equipped to handle whatever performance challenges Oracle throws your way.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/oracle-performance-tuning-practical-techniques-every-dba-should-master">Oracle Performance Tuning: Practical Techniques Every DBA Should Master</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Natural Language Query for SQL Server</title>
<link>https://www.sqlservercentral.com/articles/natural-language-query-for-sql-server</link>
<dc:creator><![CDATA[Additional Articles]]></dc:creator>
<pubDate>Fri, 03 Oct 2025 00:00:31 +0000</pubDate>
<category><![CDATA[Artificial Intelligence (AI)]]></category>
<category><![CDATA[SQL Server 2025]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4656306</guid>
<description><![CDATA[Data is usually the most important asset in organizations, but only SQL developers can frequently access that data. Technical teams often write queries for non-technical users. This restricts agility, slows decision-making, and creates a bottleneck in data accessibility. One possible remedy is natural language processing (NLP), which enables users to ask questions in simple English and receive answers without knowing any code. Still, the majority of NLP-to-SQL solutions are cloud-based, which raises issues with cost and privacy.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/natural-language-query-for-sql-server">Natural Language Query for SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/natural-language-query-for-sql-server">Natural Language Query for SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
<item>
<title>Unlocking High-Concurrency Inserts in SQL Server with OPTIMIZE_FOR_SEQUENTIAL_KEY</title>
<link>https://www.sqlservercentral.com/articles/unlocking-high-concurrency-inserts-in-sql-server-with-optimize_for_sequential_key</link>
<dc:creator><![CDATA[Chandan Shukla]]></dc:creator>
<pubDate>Wed, 01 Oct 2025 00:00:20 +0000</pubDate>
<category><![CDATA[Performance and Tuning]]></category>
<guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&p=4628558</guid>
<description><![CDATA[Learn how a setting an improve high concurrency inserts with the OPTIMIZE_FOR_SEQUENTIAL_KEY option.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/unlocking-high-concurrency-inserts-in-sql-server-with-optimize_for_sequential_key">Unlocking High-Concurrency Inserts in SQL Server with OPTIMIZE_FOR_SEQUENTIAL_KEY</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></description>
<content:encoded><![CDATA[<h2>Introduction</h2>
High-concurrency systems always look impressive on paper. You throw in dozens of CPU cores, crank up memory, design a schema with lightweight inserts, and proudly tell yourself, “This thing is going to fly.” And to be fair, under low load it probably does. A single session inserting rows into a simple table barely makes SQL Server break a sweat.
But the story changes once you start pushing that system with hundreds of parallel inserts. Suddenly, all that horsepower doesn’t matter anymore, because every thread is fighting over the same tiny spot in memory: the last page of a clustered index. This is the classic last-page insert contention problem. It creeps in whenever your clustered index key is sequential — the typical IDENTITY, DATETIME, or NEWSEQUENTIALID() setup. Each new row naturally gravitates to the end of the B-tree. That sounds orderly and efficient, but under concurrency it’s a trap. Instead of spreading inserts across multiple pages, they all dogpile onto one hot page.
When that happens, SQL Server has no choice but to serialize access with latches. You’ll see PAGELATCH_EX waits climbing, worker threads spinning, and throughput collapsing. What once looked like “minimal locking” in theory becomes a traffic jam where every insert has to wait its turn just to grab an in-memory latch. It feels almost unfair. You aren’t even blocked by logical locks — you’re blocked by something much lower level, a memory latch guarding page structure. The result is simple: your scalable design gets throttled by the physics of a single hotspot in a B-tree.
To make this concrete, imagine a basic table for orders:
<pre class="prettyprint lang-mssql">INSERT INTO SalesOrders(CustomerID, Amount) VALUES (123, 99.99);
</pre>
Run that across a handful of concurrent sessions and it’s fine. Scale it out to dozens or hundreds of threads, and suddenly you’re staring at latch contention charts instead of clean performance graphs.
Microsoft knew this was a real-world problem, so in SQL Server 2019 they slipped in a fix, called OPTIMIZE_FOR_SEQUENTIAL_KEY. The best part? It doesn’t require schema redesign, application rewrites, or fancy tricks. With a single ALTER INDEX statement, you can tell SQL Server to activate an internal backoff strategy — like a smart traffic cop that spaces out the cars instead of letting them all pile into the same junction.
This article is about understanding that problem, showing you what happens when you test it under stress, and seeing how OPTIMIZE_FOR_SEQUENTIAL_KEY quietly smooths things out.
<h2 data-start="1414" data-end="1458">The Problem: Last Page Insert Contention</h2>
Let’s say you have a simple sales table:
<pre class="prettyprint lang-mssql">CREATE TABLE dbo.SalesOrders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATETIME DEFAULT GETDATE(),
CustomerName VARCHAR(100),
Amount DECIMAL(10,2)
);
</pre>
Under a single-threaded workload, inserts here are fine. But with many sessions inserting rows, every new record goes to the last page of the clustered index. Multiple sessions all want an exclusive latch on that page → SQL Server serializes them → PAGELATCH_EX waits dominate.The ironic part? You’re not fighting over logical locks — you’re fighting over a memory latch that guards physical page structure. Traditional workarounds like reversing keys, changing schema, or sharding are rarely practical in production.
<pre class="prettyprint lang-mssql">INSERT INTO dbo.SalesOrders (CustomerName, Amount)
VALUES ('TestUser', 99.99);
</pre>
Since OrderID is increasing, all rows go to the last page of the clustered index. The result?
<ul>
<li>Multiple sessions try to get an exclusive latch on that last page.</li>
<li>SQL Server serializes those requests → PAGELATCH_EX wait.</li>
<li>Throughput drops, CPU remains idle, and inserts crawl.</li>
</ul>
The ironic part? You’re not fighting over logical locks — you’re fighting over a memory latch, which guards physical page structure. And traditional fixes — changing schema, sharding, or reversing keys — often aren’t practical in real-world production systems.
<h2 data-start="2448" data-end="2485">What happens under the hood with OPTIMIZE_FOR_SEQUENTIAL_KEY</h2>
When multiple sessions try to insert into the same last page, they all rush at once to grab an exclusive latch (PAGELATCH_EX). Without OFSK (Optimize for Sequential Key), SQL Server basically says: “Everyone line up, first one gets the latch, the rest spin and wait.”
So 50 sessions will slam the latch at once. The unlucky ones burn CPU spinning or just wait, adding latency. That’s why you see signal wait times pile up in sys.dm_os_wait_stats.
With OPTIMIZE_FOR_SEQUENTIAL_KEY = ON, SQL Server becomes smarter. Instead of letting every thread dogpile, it introduces a backoff algorithm. Internally, it watches contention patterns and tells incoming sessions:
“Hold on, I see too many cooks in the kitchen. You go next, you wait a tick, you back off a tick more.”
It’s not random. SQL Server spaces out the latch requests in microsecond-scale delays. That way, instead of 50 sessions colliding and blocking, they get serialized in a more orderly way. The end result is:
<ul>
<li>Less spinning (threads don’t hammer the latch in a tight loop)</li>
<li>Lower CPU overhead</li>
<li>More predictable throughput under concurrency</li>
<li>Total runtime closer to “ideal parallel”</li>
</ul>
Yes, some individual inserts might see a micro-delay, but the overall workload finishes faster because you avoid chaos at the hot page.
Starting in SQL Server 2019, Microsoft gave us a way to tame this chaos. With a single switch, you can tell SQL Server to coordinate concurrent inserts into a sequential key index using an internal backoff algorithm.
<pre class="prettyprint lang-mssql">ALTER INDEX [PK_SalesOrders] ON dbo.SalesOrders
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
</pre>
Here’s what changes. Without this option, every insert thread charges straight at the last page, all trying to grab the exclusive latch at once. One session gets it, and the rest either spin aggressively or wait in line, racking up PAGELATCH_EX waits. CPU time is wasted, and overall runtime drags.
With OPTIMIZE_FOR_SEQUENTIAL_KEY enabled, SQL Server steps in as a quiet traffic cop. It notices the contention and starts spacing out latch requests. Internally, it applies a smart backoff: “You go now, you wait a microsecond, you wait a little longer.” Instead of 50 sessions slamming the same door, they’re queued more gracefully.
The magic is that it doesn’t require schema changes, application rewrites, or fancy hacks. It works at the index level (not server-wide), so you can enable it only on the tables that actually suffer from this problem. You can even check if it’s enabled:
<pre class="prettyprint lang-mssql">SELECT name, optimize_for_sequential_key
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.SalesOrders');
</pre>
Under the hood, you’re not “removing” contention — you’re teaching SQL Server to manage it better. The result is less wasted CPU, smoother throughput, and fewer latch storms.
So What Does It Actually Do? Internally, SQL Server builds a “smart queue” when it sees intense concurrent inserts into the same index hot spot. Instead of letting every thread rush the latch at once (and cause massive spinning and blocking), it gently delays new insert attempts when it detects contention.
<ul>
<li>No schema rewrite.</li>
<li>No index rebuild.</li>
<li>No guesswork.</li>
</ul>
It’s like a mini traffic controller sitting on top of your latch.
<h2>Simulation: Proving the Bottleneck</h2>
Let’s build a test environment. First, create a table with a sequential clustered index:
<pre class="prettyprint lang-mssql">DROP TABLE IF EXISTS dbo.TestInserts;
GO
CREATE TABLE dbo.TestInserts (
ID INT IDENTITY(1,1) PRIMARY KEY,
Data CHAR(100)
);</pre>
Insert a million rows first to fill the B-tree structure:
<pre class="prettyprint lang-mssql">INSERT INTO dbo.TestInserts (Data)
SELECT TOP 1000000 REPLICATE('A', 100)
FROM sys.all_objects a, sys.all_objects b;
</pre>
Instead of opening a bunch of SSMS windows, I used PowerShell to fire up 50 parallel sessions. Each session runs a loop of 20,000 inserts and logs how long it took. I ran this script several times with OPTIMIZE_FOR_SEQUENTIAL_KEY both ON and OFF, and each time the wait stats lined up closely with the corresponding setting.
<pre class="prettyprint lang-powershell">$connectionString = "Server=localhost;Database=simple_talk;Integrated Security=True;"
$query = @"
SET NOCOUNT ON;
DECLARE @i INT = 0;
WHILE @i < 20000
BEGIN
INSERT INTO dbo.TestInserts (Data) VALUES (REPLICATE('B',100));
SET @i += 1;
END
"@
$totalSw = [System.Diagnostics.Stopwatch]::StartNew()
$jobs = @()
for ($i=1; $i -le 200; $i++) {
$jobs += Start-Job -ScriptBlock {
param($conn,$q,$n)
$sw = [System.Diagnostics.Stopwatch]::StartNew()
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $conn
$sqlConn.Open()
$cmd = $sqlConn.CreateCommand()
$cmd.CommandTimeout = 0
$cmd.CommandText = $q
$cmd.ExecuteNonQuery() | Out-Null
$sqlConn.Close()
$sw.Stop()
[PSCustomObject]@{
Session = $n
ElapsedMs = $sw.ElapsedMilliseconds
}
} -ArgumentList $connectionString,$query,$i
}
$results = $jobs | Receive-Job -Wait -AutoRemoveJob
$totalSw.Stop()
$results | Sort-Object Session | Format-Table -AutoSize
Write-Host "Total runtime: $($totalSw.Elapsed.ToString())"
</pre>
This way, you capture per-session runtimes and the aggregate totals.
In our tests, the overall wall-clock runtime came out very close whether OPTIMIZE_FOR_SEQUENTIAL_KEY was ON or OFF. The difference was only about 10 seconds across the entire run — not dramatic. But the wait stats told a different story: with the option OFF, latch waits stacked almost linearly with runtime, showing raw contention. With it ON, we saw about ~10s of extra accumulated wait time, but that wasn’t “slowness” — it was SQL Server deliberately spacing out inserts with micro-delays. The payoff is that concurrency is smoothed out, threads don’t spin wildly, and CPU stays calmer, even though the total batch window barely changes.
<ul>
<li data-start="300" data-end="959">When OFSK was OFF, total runtime for all 200 sessions: 00:02:17.4372205</li>
<li data-start="300" data-end="959">When OFSK was ON, Total runtime for all 200 sessions: 00:02:05.9792700</li>
</ul>
We can track different wait types for PAGELATCH by running below query while both runs i.e OFSK was off and .on.
<pre class="prettyprint lang-mssql">SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH_%';
</pre>
When OFSK was OFF--->
<img loading="lazy" class="alignnone size-full wp-image-4637212" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.54.30 PM.png" data-lazy-load alt="" width="1400" height="572" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.54.30 PM.png 1400w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.54.30 PM-300x123.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.54.30 PM-1024x418.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.54.30 PM-768x314.png 768w" sizes="(max-width: 1400px) 100vw, 1400px" />
Now enable optimization by turning on OPTIMIZE_FOR_SEQUENTIAL_KEY(OFSK) the index:
<pre class="prettyprint lang-mssql">ALTER INDEX [PK_TestInserts] ON dbo.TestInserts
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
</pre>
Run the test again — same workload, same data, same hardware — but now with throttling applied.
<img loading="lazy" class="alignnone size-full wp-image-4637211" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.49.49 PM.png" data-lazy-load alt="" width="1134" height="336" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.49.49 PM.png 1134w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.49.49 PM-300x89.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.49.49 PM-1024x303.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/08/Screenshot-2025-08-18-at-8.49.49 PM-768x228.png 768w" sizes="(max-width: 1134px) 100vw, 1134px" />
When OPTIMIZE_FOR_SEQUENTIAL_KEY was OFF, we saw around 904k PAGELATCH_EX waits. Total wait time stacked up to about 354k ms, and the longest single wait went up to 66 ms. That’s SQL Server showing classic latch contention — lots of sessions hammering the same last page, queuing up behind each other, and some unlucky threads waiting longer than others. The workload still finishes, but it feels uneven, with certain sessions stalling more than they should.
When we flipped it ON, the latch waits actually dropped slightly — to around 877k. More importantly, the total wait time came down too, roughly 206k ms, and the longest single wait shrank to 33 ms. What’s happening is SQL Server is no longer letting all threads fight at once. Instead, it introduces micro-pauses that spread the inserts more evenly. That’s why you don’t just see fewer waits, but also smoother throughput: sessions don’t pile up behind one hot page the way they did before.
So the comparison is clear: OFF = higher waits, longer stalls, uneven performance. ON = fewer waits, shorter stalls, more balanced flow. The actual wall-clock runtime of the whole batch may look similar, but under the hood the ON setting delivers a calmer, fairer ride for every session.
<h2 data-start="4737" data-end="4761">Real-World Use Cases</h2>
This feature is made for:
<ol>
<li data-start="4790" data-end="5005">High-insert OLTP systems</li>
<li data-start="4790" data-end="5005"> Tables with clustered index on IDENTITY</li>
<li data-start="4790" data-end="5005"> Systems with intense concurrency</li>
<li data-start="4790" data-end="5005"> Logging, auditing, telemetry pipelines</li>
<li data-start="4790" data-end="5005"> Warehousing workloads with <code data-start="4972" data-end="4996">insert into fact_table</code> patterns</li>
</ol>
<h2 data-start="5012" data-end="5035">What It Doesn’t Fix</h2>
This setting doesn’t help if:
<ul>
<li data-start="5060" data-end="5095">You don’t have concurrent inserts</li>
<li data-start="5098" data-end="5142">Your keys are already random (GUIDs, hash)</li>
<li data-start="5145" data-end="5187">You’ve got contention on non-leaf levels</li>
<li data-start="5190" data-end="5234">You’re bottlenecked on disk I/O, not latches</li>
</ul>
Also, this is not a performance booster in all scenarios— it helps specifically when the root cause is latch contention from sequential inserts.
<h2 data-start="5381" data-end="5407">Can It Cause Problems?</h2>
Though it looks safe from documentation and tests, it’s not a magic bullet. In some cases, workload profiling tools may show slightly increased latency per insert — because SQL Server adds deliberate microsecond-scale waits to smooth contention. But the overall system becomes more stable and scalable. Refer this blog (<a class="" href="https://techcommunity.microsoft.com/blog/sqlserver/behind-the-scenes-on-optimize-for-sequential-key/806888?utm_source=chatgpt.com" target="_new" rel="noopener" data-start="418" data-end="574">Microsoft TechCommunity</a>).
<h2 data-start="6088" data-end="6102">Summary</h2>
The best performance features are the ones you don’t have to think about. And OPTIMIZE_FOR_SEQUENTIAL_KEY is one of those rare features: you flip it on, and SQL Server gets smarter about concurrency.
For tables suffering from last-page insert issues, this simple index option might save you hours of pain, code rewrites, or scaling discussions. It’s surgical, targeted, and non-invasive , exactly what a moderate fix should be.
If you’re running SQL Server 2019 or later and haven’t checked your hot OLTP tables yet… maybe it’s safe to flip that switch.
The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/unlocking-high-concurrency-inserts-in-sql-server-with-optimize_for_sequential_key">Unlocking High-Concurrency Inserts in SQL Server with OPTIMIZE_FOR_SEQUENTIAL_KEY</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.
]]></content:encoded>
</item>
</channel>
</rss>
<!--
Performance optimized by W3 Total Cache. Learn more: https://www.boldgrid.com/w3-total-cache/
Page Caching using Redis (Page is feed)
Served from: sqlservercentral.com @ 2025-10-14 11:08:56 by W3 Total Cache
-->

If you would like to create a banner that links to this page (i.e. this validation result), do the following:

Download the "valid RSS" banner.
Upload the image to your own server. (This step is important. Please do not link directly to the image on this server.)
Add this HTML to your page (change the image src attribute if necessary):

<a href="http://www.feedvalidator.org/check.cgi?url=https%3A//www.sqlservercentral.com/feed"><img src="valid-rss-rogers.png" alt="[Valid RSS]" title="Validate my RSS feed" /></a>

If you would like to create a text link instead, here is the URL you can use:

http://www.feedvalidator.org/check.cgi?url=https%3A//www.sqlservercentral.com/feed

Home · About · News · Docs · Terms