IT Solutions Technology Blog

Microsoft SQL Server – optimize index maintenance to improve query performance and reduce resource consumption

SQL Server Index and Statistics Maintenance – ‚IndexOptimize‘ is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics

Define thresholds for a common guideline is to:

Reorganize indexes with 5% to 30% fragmentation
Rebuild indexes with greater than 30% fragmentation

If using SQL Server Enterprise Edition use the ONLINE=ON option for index rebuilds to allow the database to remain available during the operation minimizing downtime

Microsoft SQL Server 2025 … die neue Version setzt auf bewährte Sicherheit Performance und hohe Verfügbarkeit und erweitert diese Basis erstmals um tief in die Datenbank integrierte KI Funktionen

Microsoft SQL Server – more rows are added to the backup and restore history tables after each backup or restore operation is performed therefore we recommend that you periodically execute sp_delete_backuphistory

The following example deletes all entries that are older than January 14, 2023, 12:00 A.M. in the backup and restore history tables

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = ‚2023-01-14‘;
GO

   Microsoft SQL Server – export XML column rows from SQL table into individual files

It is roughly formatted as such

+————–+——————+
| REFERENCE_ID | REFERENCE_XML |
+————–+——————+
| 1 | <xml>…</xml> |
| 2 | <xml>…</xml> |
| 3 | <xml>…</xml> |
| 4 | <xml>…</xml> |
| … | <xml>…</xml> |
| 70000 | <xml>…</xml> |
+————–+——————+

I would like to loop through every row and bulk export each REFERENCE_XML column and name it as the REFERENCE_ID

My output folder set would look as such

C:\References\1.xml
C:\References\2.xml
C:\References\3.xml
C:\References\4.xml
C:\References\…
C:\References\70000.xml

Microsoft SQL Server Profiler  – is an interface to create and manage traces and analyze and replay trace results events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when diagnosing a problem

Quest Foglight for SQL Server – you can quickly diagnose and resolve SQL Server performance problems and optimize your workloads to prevent future issues. This SQL Server tuning tool includes real-time and historical diagnostics, reporting and more, so you can confidently ensure the health of your entire database environment

Microsoft SQL Server 2019 – Licensing Guide