# Pachadata, Rudi Bruchez > Articles and how-to guides on SQL Server (administration, performance, high availability, replication), written by Rudi Bruchez, independent SQL Server consultant since 2006. Each page has a Markdown version (index.md) next to its HTML page. Contact, audit or hands-on help : https://www.pachadata.com/en/contact/ ## Administration how-tos - [Compress tables and indexes](https://www.pachadata.com/en/docs/howtos/administration/compression/index.md): ROW and PAGE compression of tables and indexes, in every edition since SQL Server 2016 SP1: estimate the gain, compress, rebuild. - [Exporting a table with BCP](https://www.pachadata.com/en/docs/howtos/administration/bcp/index.md): An example of exporting a table with the bcp command-line utility: integrated authentication (-T) and native format (-n) for SQL Server. - [Get the size of your tables](https://www.pachadata.com/en/docs/howtos/administration/table-size/index.md): How to find the size of your tables, and the largest ones in particular. - [Managing the SQL Server Transaction Log](https://www.pachadata.com/en/docs/howtos/administration/log/index.md): A transaction log filling up the disk is a classic DBA problem. Learn how to diagnose blocking issues and properly configure your recovery models. - [Remove backup messages from the error log](https://www.pachadata.com/en/docs/howtos/administration/errorlog-backup/index.md): The error log records every successful backup, which is of no use. Here is how to turn those messages off. - [Several SQL Server instances on one server](https://www.pachadata.com/en/docs/howtos/administration/instances/index.md): Consolidating SQL Server instances is better than installing several of them on the same server. - [Updating SQL Server: CUs, GDRs and servicing branches](https://www.pachadata.com/en/docs/howtos/administration/sql-server-updates-cu-gdr/index.md): The SQL Server servicing model since 2017: choosing between the CU branch and the GDR branch, reading a build number, finding the right update and applying it. ## BI features - [Connect to MongoDB from a BI tool](https://www.pachadata.com/en/docs/howtos/analytics/mongodb-bi-connector/index.md): How to connect to MongoDB from a BI tool such as Reporting Services or Power BI, which needs an ODBC driver that accepts SQL queries. ## Data Modeling - [Choosing a clustered index](https://www.pachadata.com/en/docs/howtos/data-modeling/clustered-index/index.md): Which key to give the clustered index of a SQL Server table: small, unique and increasing, why to avoid a GUID, and the case for the primary key. - [Managing several auto-increments](https://www.pachadata.com/en/docs/howtos/data-modeling/autoincrement-by-parent/index.md): Managing several auto-increments on a single table, when one column has to be numbered within the scope of another. ## Database maintenance - [A complete guide to SHRINK in SQL Server](https://www.pachadata.com/en/docs/howtos/database-maintenance/shrink-database-file/index.md): How to reduce the size of a SQL Server database file properly: good practice, sizing, an incremental strategy, and automation with dbatools. - [Identify and fix transaction log problems](https://www.pachadata.com/en/docs/howtos/database-maintenance/transaction-logs/index.md): Transaction log growing or disk full: read log_reuse_wait, find the open transaction, change the recovery model and shrink the file. - [SQL Server transaction log backups: why and how](https://www.pachadata.com/en/docs/howtos/database-maintenance/transaction-log-backups/index.md): Why a database in the FULL recovery model needs log backups, what VM backups do not replace, and how to restore to a precise point in time. - [Taking backups on an availability group](https://www.pachadata.com/en/docs/howtos/database-maintenance/backup-ag/index.md): Schedule COPY_ONLY full backups and log backups on every replica of an availability group with Ola Hallengren, clean up with dbatools. ## Development - [Fix collation problems on a database](https://www.pachadata.com/en/docs/howtos/dev/collation-conflict/index.md): Error 468 collation conflicts with temporary tables: fix them with COLLATE DATABASE_DEFAULT or a partially contained database (CONTAINMENT = PARTIAL). ## High availability - [Always On: mastering the timeouts](https://www.pachadata.com/en/docs/howtos/hadr/failovers/index.md): Lease Timeout, HealthCheck, Quorum… Configure the SQL Server and WSFC thresholds to stabilize your high availability. - [Basic Availability Groups in Standard edition](https://www.pachadata.com/en/docs/howtos/hadr/bag/index.md): The Standard edition of SQL Server offers one high availability solution: Basic Availability Groups. Here are their benefits and their limits. - [Troubleshooting AlwaysOn availability groups](https://www.pachadata.com/en/docs/howtos/hadr/troubleshooting/index.md): Collect the SQL Server logs, the AlwaysOn_health files and the WSFC logs (Get-ClusterLog) to troubleshoot an availability group. - [Use dbatools for AlwaysOn availability groups](https://www.pachadata.com/en/docs/howtos/hadr/dbatools/index.md): Using dbatools to manage and troubleshoot AlwaysOn availability groups in SQL Server. ## Managing the system databases - [Move tempdb files](https://www.pachadata.com/en/docs/howtos/system-databases/move-tempdb-files/index.md): Generate and run the ALTER DATABASE script that moves the tempdb files to another drive, effective when the SQL Server service restarts. - [Remove tempdb files](https://www.pachadata.com/en/docs/howtos/system-databases/remove-tempdb-files/index.md): Why DBCC SHRINKFILE EMPTYFILE often fails on tempdb, and two ways to remove extra files: minimal configuration mode or a startup Agent job. ## Performance articles - [Analyzing the Query Store with an AI agent](https://www.pachadata.com/en/docs/articles/sql-server/performance/query-store-agent/index.md): Let Claude Code or Codex analyze the Query Store through sqlcmd, with a login that reads the Query Store but not the tables, or on a clone without data. - [Query Store capture modes](https://www.pachadata.com/en/docs/articles/sql-server/performance/query-store-capture-modes/index.md): What separates the ALL, AUTO, CUSTOM and NONE Query Store capture modes, with the AUTO thresholds measured on SQL Server 2019, 2022 and 2025. - [SARGability of WHERE clauses](https://www.pachadata.com/en/docs/articles/sql-server/performance/sargability/index.md): Why functions in your WHERE clauses stop SQL Server from using your indexes, and how to rewrite your queries to get performance back. - [SQL Server string statistics](https://www.pachadata.com/en/docs/articles/sql-server/performance/string-statistics/index.md): The string summary of a SQL Server statistics object, decoded: a sample of strings stored in a trie, how it estimates LIKE predicates, and what bypasses it. - [What the Query Store records](https://www.pachadata.com/en/docs/articles/sql-server/performance/query-store-collection/index.md): What the Query Store keeps about a query and what it leaves out: missing statements, interrupted executions, losses after a hard shutdown. ## Performance how-tos - [Enable and use the Query Store](https://www.pachadata.com/en/docs/howtos/performance/query-store/index.md): How to enable and use the Query Store to diagnose performance problems. - [Fix cardinality estimator problems](https://www.pachadata.com/en/docs/howtos/performance/cardinality-estimator/index.md): How to identify and fix performance problems caused by the cardinality estimator. - [Fix implicit conversions](https://www.pachadata.com/en/docs/howtos/performance/implicit-conversions/index.md): Find the implicit conversions that turn an index seek into a scan, from plan warnings and Extended Events, and fix them in ADO.NET and Entity Framework. - [Get an execution plan](https://www.pachadata.com/en/docs/howtos/performance/execution-plan/index.md): Display the estimated (Ctrl+L) or actual (Ctrl+M) execution plan of a SQL Server query in SSMS, then save it to a .sqlplan file. - [Identify blocking problems](https://www.pachadata.com/en/docs/howtos/performance/blocking/index.md): How to recognise a blocking problem, set up the collection of blocked process reports, and send the files for analysis. - [Optimize SQL queries](https://www.pachadata.com/en/docs/howtos/performance/queries/index.md): A method for optimizing SQL Server queries: IO and TIME statistics, Query Store, Extended Events, row-by-row processing, indexes, statistics. - [Read Committed Snapshot](https://www.pachadata.com/en/docs/howtos/performance/rcsi/index.md): How to enable the Read Committed Snapshot option in a SQL Server database - [Refusing a query with ABORT_QUERY_EXECUTION](https://www.pachadata.com/en/docs/howtos/performance/abort-query-execution/index.md): Using ABORT_QUERY_EXECUTION in SQL Server 2025 to block the execution of problem queries through Query Store Hints. - [SQL Server Parallelism: Configuring MAXDOP and Cost Threshold](https://www.pachadata.com/en/docs/howtos/performance/parallelism/index.md): SQL Server's default parallelism settings are often the cause of slowdowns. Configure MAXDOP and Cost Threshold (CTFP) to optimize your performance. - [Troubleshoot performance problems](https://www.pachadata.com/en/docs/howtos/performance/troubleshooting/index.md): Troubleshoot a slow SQL Server one hypothesis at a time: waits, disks, configuration, expensive queries, blocking, missing indexes, statistics. ## Reference - [Intelligent Query Processing in Standard edition](https://www.pachadata.com/en/docs/reference/iqp/index.md): Which SQL Server 2022 Intelligent Query Processing features are available in Standard edition, in a table based on the Microsoft documentation. ## Replication - [Adding columns to a replicated table](https://www.pachadata.com/en/docs/howtos/replication/alter-table/index.md): Let a user who is neither sysadmin nor db_owner add columns to a replicated table by altering the tr_MStran_altertable trigger. ## SQL Server - Administration - [Installing and configuring SQL Server 2025 on Windows Server](https://www.pachadata.com/en/docs/articles/sql-server/administration/installation/index.md): A complete step-by-step guide: installing SQL Server 2025 on Windows Server, then configuring the server properties and the Configuration Manager. ## SQL Server Monitoring - [Always On: Supervision of Availability Groups](https://www.pachadata.com/en/docs/howtos/administration/monitoring/alwayson-ag/index.md): Key counters and metrics to monitor for SQL Server Always On Availability Groups. - [Basic counters](https://www.pachadata.com/en/docs/howtos/administration/monitoring/basic-counters/index.md): Windows and SQL Server counters to monitor: CPU, disk latency, buffer cache hit ratio, page life expectancy, transactions and batches per second. - [Monitoring with PRTG](https://www.pachadata.com/en/docs/howtos/administration/monitoring/prtg/index.md): Configure PRTG to monitor SQL Server with the PerfCounter Custom sensor: counter syntax, units, and computing the buffer cache hit ratio. ## SQL Server articles - [Intelligent Query Processing (IQP)](https://www.pachadata.com/en/docs/articles/sql-server/iqp/index.md): How to use Intelligent Query Processing (IQP) in Microsoft SQL Server. - [SQL Server memory consumption](https://www.pachadata.com/en/docs/articles/sql-server/sql-server-ram/index.md): Understanding SQL Server's memory management behavior for system administrators accustomed to other platforms ## SQL Server on Linux - [Install SQL Server on Docker](https://www.pachadata.com/en/docs/howtos/linux/docker/index.md): Pull the SQL Server 2022 image and run the container with rootless Podman, choose the edition with MSSQL_PID, get a certificate with Certbot. - [Virtualize SQL Server with KVM](https://www.pachadata.com/en/docs/howtos/linux/kvm/index.md): Install KVM and libvirt on Debian or Ubuntu, enable the service and add your user to the libvirt and kvm groups, before virtualizing SQL Server. ## Troubleshooting - [Analyze queries](https://www.pachadata.com/en/docs/howtos/troubleshooting/analyze-queries/index.md): How to analyze queries to identify performance problems. A step-by-step method. - [Detect an active transaction](https://www.pachadata.com/en/docs/howtos/troubleshooting/active-transaction/index.md): Find the transaction that holds the log in SQL Server: DBCC OPENTRAN, log_reuse_wait_desc, session DMVs, and what to know before running KILL. - [Extract deadlock information](https://www.pachadata.com/en/docs/howtos/troubleshooting/deadlock/index.md): Extract deadlock graphs from the SQL Server system_health session with a query, then save them as .xdl files to open them in SSMS. - [Identify excessive memory grants](https://www.pachadata.com/en/docs/howtos/troubleshooting/memory-grants/index.md): Diagnose memory grants that are too large or too small in SQL Server: varchar(max) sizing, actual plans, the query_memory_grant_usage event, tempdb spills. - [Retrieve the error logs](https://www.pachadata.com/en/docs/howtos/troubleshooting/retrieve-error-log/index.md): Find the path of the SQL Server ERRORLOG files with SERVERPROPERTY, identify the rotated logs, and zip them to send them for analysis. - [Trace execution plans](https://www.pachadata.com/en/docs/howtos/troubleshooting/trace-execution-plans/index.md): Capture actual execution plans with an Extended Events session since SQL Server 2017 CU14, filtered by database, login or duration, and what it costs. - [Use sp_whoisactive to see the active queries](https://www.pachadata.com/en/docs/howtos/troubleshooting/sp-whoisactive/index.md): Useful sp_whoisactive parameters to see the queries running now: execution plans, blocking chain leaders, memory grant information, average duration.