Tag: Maintenance

Find (and Safely Drop) Unused Indexes in PostgreSQL

A quick query to find PostgreSQL indexes nobody uses, the four checks to run before dropping one, and how to drop it without blocking writes.

Indexes speed up reads, but they aren’t free. Every INSERT, UPDATE and DELETE has to maintain them, they take up disk, and they compete for memory in shared buffers. On a database that’s been around a few years, it’s common to find indexes that nobody has used in months. Here’s how to find them, and what to check before you drop one.

The query

PostgreSQL tracks how often each index is scanned in pg_stat_user_indexes. This query lists indexes with zero scans. It skips primary keys, unique indexes, and indexes that back a constraint, because those enforce rules even if no query reads them.

SELECT s.schemaname,
       s.relname        AS table_name,
       s.indexrelname   AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT EXISTS (
        SELECT 1 FROM pg_constraint c
        WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

The biggest unused indexes come first, so the top of the list is where the payoff is.

Before you drop anything, check these four things

  1. When were stats last reset? A zero only means something if the counters have been collecting for a while. Run SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); and aim for at least a few weeks of history, ideally covering a month-end.
  2. Do you have read replicas? Scan counts are tracked per server. An index used only by reporting queries on a replica will show zero on the primary. Run the query on every replica too.
  3. Are there rare jobs? Quarterly reports and yearly archive jobs may depend on an index that looks idle most of the time.
  4. Is the index new? A recently created index will naturally show low counts.

On PostgreSQL 16 and later, pg_stat_user_indexes also has a last_idx_scan column, which tells you when the index was last used, not just how many times.

Dropping safely

First save the definition, so you can recreate the index if something slows down:

SELECT pg_get_indexdef('schema_name.index_name'::regclass);

Then drop it without blocking writes to the table:

DROP INDEX CONCURRENTLY schema_name.index_name;

CONCURRENTLY can’t run inside a transaction block. If it fails partway through, check for a leftover invalid index and drop it again.

Wrap-up

Run the query once a quarter, verify across all replicas, and keep the saved definitions somewhere handy. Removing dead indexes is one of the cheapest write-performance wins you’ll find.

How to run native SQL backups across multiple servers and multiple databases

Follow the below process to run SQL backups across one or more SQL servers, one or more databases using a simple powershell command. This uses the dbatools, open source powershell modules. You would use this in a scenario when Commvault server is down.

Instructions

Run the backups using the following powershell script:

$servers = 'SQLSERVER01','SQLSERVER02'
$filepath = '\\NetworkPath\Backup\servername\dbname'
$dbnamelist = 'DB01','DB02'
foreach ($server in $servers)
{
Backup-DbaDatabase -SqlInstance $server -database $dbnamelist -BackupDirectory $filepath -Type Full -CompressBackup -ReplaceInName -BuildPath
}

In the example above, the script would backup databases named ‘DB01′ and ‘DB02’ on both servers listed in the $servers variable. In order to backup all the databases, remove the -database parameter.

Creating Directories Dynamically

In the previous examples we just wrote the backups to the root of a file share. But sometimes you’ll want to organize or separate your backups based off of server name or database name or backup type. This is something else that dbatools makes very simple to do dynamically. This is done by using the –ReplaceInName parameter and then specifying keywords in your FilePath and Path strings.

-ReplaceInName
If this switch is set, the following list of strings will be replaced in the FilePath and Path strings:
instancename – will be replaced with the instance Name
servername – will be replaced with the server name
dbname – will be replaced with the database name
timestamp – will be replaced with the timestamp (either the default, or the format provided)
backuptype – will be replaced with Full, Log or Differential as appropriate

The backup jobs have already been created for certain critical servers which will enable us to switch to an alternative way to keep running backups in case Commvault server goes down.

Run the below script to enable the backup jobs across multiple servers:

# Run the following to enable native SQL backups to a network path
#Backup Location = '\\NetworkPath\Backup\servername\dbname'
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Enabled
}

# Run the following to enable transaction log backups for selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Enabled

 Run the below script to disable the backup jobs across multiple servers:

# Run the following to disable native SQL backups 
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Disabled
}
# Run the following to disable transaction log backups on selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Disabled

Install SQL Server maintenance solution

This script will download and install the latest version of SQL Server Maintenance Solution created by Ola Hallengren

Install-DbaMaintenanceSolution -sqlinstance SQLSERVER01 -database master -BackupLocation "\\NetworkPath\Backup\SQLSERVER01" -InstallJobs  -CleanupTime 168

What do the parameters mean?

ParameterDescriptionExample
sqlinstanceServer NameSQLSERVER01
databaseDatabase Name (Defaults to master if none specified)master
BackupLocationPath where backups will be stored (defaults to native SQL path specified in the instance
InstallJobsThis switch will create the SQL agent jobsThis only works when you install the full solution
CleanupTimeHow many hours worth of backups should be retained

Following script will remove the unwanted SQL agent jobs created by the maintenance solution.

We delete the below four jobs since we handle the requirement through different jobs

Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'sp_purge_jobhistory' -Mode Lazy
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'sp_delete_backuphistory' -Mode Lazy
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'CommandLog Cleanup' -Mode Lazy 
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'Output File Cleanup' -Mode Lazy

Highlight important information in a panel like this one. To edit this panel’s color or style, select one of the options in the menu below.

Database maintenance best practices

UPDATE: INDEXES & STATISTICS 

  • default to weekly, but then escalating to daily for specific stats that need it
  • You will need to update Column stats separately. Column stats do not get updated when you rebuild indexes or index stats

side note to be aware: Each time you update indexes or statistics on a table clears out the plan cache for those tables. Having outdated statistics can cause all sorts of issues, but updating statistics can also cause issues like parameter sniffing, we are not looking for a perfect plan but more like a good plan, a balance between the issues of outdated statistics against the parameter sniffing.

BACKUP SCHEDULE

Full Backups – WEEKLY

Differential Backups – DAILY (EVERY NIGHT)

Transaction Log Backups – Every few hours (ONLY IF Databases need to meet RPO of every few hours, additionally Database needs to be in FULL recovery mode)