SQL Server performance problems are not always caused by complicated queries, missing indexes, or insufficient hardware. Some of the most disruptive issues begin with basic configuration settings that were never reviewed after the server was installed, virtualized, upgraded, or assigned additional business applications. A single overlooked setting can create memory pressure, excessive disk activity, unstable performance, or unnecessary competition between SQL Server, Windows, and other applications.
This guide provides a practical starting point for IT professionals who support Microsoft SQL Server but are not full-time database administrators. It focuses on safe inspections, commonly missed configuration items, and the questions that should be answered before making changes. The objective is not to make every SQL Server consume less memory or force every performance counter to zero. The objective is to create a stable, documented, and appropriately balanced server that supports its business workload.
Scope of this guide
This article focuses on commonly missed Windows, storage, virtualization, and SQL Server configuration items. It is not a substitute for query tuning, index analysis, blocking investigation, application-vendor guidance, capacity planning, or a complete database health assessment. A healthy server configuration cannot correct inefficient queries, poor application design, missing indexes, blocking, or damaged databases.
Production change warning
Do not change production SQL Server settings simply because a value differs from an example in this article.
Before making a change:
Some of the inspection queries in this article require elevated SQL Server permissions. Depending on the SQL Server version, the connected account may need permissions such as:
VIEW SERVER STATEVIEW SERVER PERFORMANCE STATEIf a query returns incomplete data, an access-denied error, or no rows, do not assume the feature is unused or healthy. Confirm whether the connected account has sufficient visibility.
Technician safety rule
Queries labeled read-only in this guide inspect configuration or system metadata. They do not intentionally modify databases. Commands containing ALTER, UPDATE, DELETE, DROP, SHRINK, RECONFIGURE, OFFLINE, or service-restart instructions must be treated as changes and require approval.
A Tier 1 technician may normally:
A Tier 1 technician should not independently:
A Tier 2 engineer may collect and interpret the same information, create a change plan, confirm vendor requirements, coordinate a maintenance window, and implement approved changes within the engineer’s authorization and skill level.
Escalate to a qualified database administrator when the issue involves:
SQL Server does not operate in isolation. A memory setting that may be appropriate for a dedicated SQL Server could be unsafe on a shared server also running a document-management platform, reporting services, web applications, backup software, security agents, integration tools, or another database engine.
Before changing SQL Server, identify everything that shares the machine:
A restart can temporarily improve performance because it:
A restart resets the environment. It does not identify the root cause.
Do not tune from a single screenshot. A screenshot represents one moment. SQL Server performance should be evaluated during a representative business cycle.
Avoid changing several unrelated settings at once
If SQL memory, the pagefile, LPIM, MAXDOP, parallelism threshold, storage layout, and application settings are changed simultaneously, it becomes difficult to determine which change helped or caused a regression. When emergency stability requires several coordinated changes, document every change separately and plan controlled follow-up validation.
Performance symptoms may be caused or amplified by SQL Server defects corrected in service packs or cumulative updates.
Run this read-only query:
SELECT
SERVERPROPERTY('MachineName') AS machine_name,
SERVERPROPERTY('ServerName') AS server_name,
SERVERPROPERTY('InstanceName') AS instance_name,
SERVERPROPERTY('Edition') AS edition,
SERVERPROPERTY('ProductVersion') AS product_version,
SERVERPROPERTY('ProductLevel') AS product_level,
SERVERPROPERTY('ProductUpdateLevel') AS update_level,
SERVERPROPERTY('ProductUpdateReference') AS update_reference;
SQL Server intentionally uses memory to cache data pages and execution plans. High SQL memory usage is not automatically a leak. However, SQL Server should normally have an appropriate maximum memory value so Windows and other applications retain sufficient resources.
SELECT
name,
value AS configured_value_mb,
value_in_use AS running_value_mb,
description
FROM sys.configurations
WHERE name IN
(
'min server memory (MB)',
'max server memory (MB)'
)
ORDER BY name;
This query is read-only.
Configured value is the value SQL Server has been instructed to use. Running value is the value currently active.
They may differ when:
The default value of 2147483647 MB effectively means that no practical SQL-specific maximum has been established. This does not mean SQL Server immediately consumes all available memory. SQL Server grows its memory use as workload requires it.
Guidance such as “give SQL Server 80% of the RAM” is only a rough starting point. It can be unsafe on shared servers.
A better planning model is:
Total physical memory
minus Windows operating-system requirements
minus other applications and services
minus other SQL Server instances
minus memory required outside the SQL memory manager
minus an appropriate safety margin
equals the initial SQL Server maximum-memory value
A one-terabyte database does not require one terabyte of RAM.
SQL Server uses memory for:
The active working set and workload are more important than the total database-file size.
Three measurements commonly confused
Allocated database size: Space reserved by SQL Server data and log files.
Used database space: The portion of those files currently occupied by database objects or active log records.
Active working set: The subset of data and plans used frequently enough to benefit from memory.
None of these numbers alone calculates the correct SQL Server memory limit.
The SQL Server process may use more memory than the configured max server memory value.
Some memory may exist outside the primary SQL memory-manager limit, including:
Do not set the SQL memory maximum equal to every megabyte not currently used by Windows.
SELECT
physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_mb,
locked_page_allocations_kb / 1024 AS locked_pages_mb,
large_page_allocations_kb / 1024 AS large_pages_mb,
virtual_address_space_committed_kb / 1024
AS committed_virtual_memory_mb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
This query is read-only.
1 means SQL Server detected low physical memory.1 means SQL Server detected low virtual-memory conditions.SELECT
total_physical_memory_kb / 1024 AS total_physical_memory_mb,
available_physical_memory_kb / 1024 AS available_physical_memory_mb,
total_page_file_kb / 1024 AS total_page_file_mb,
available_page_file_kb / 1024 AS available_page_file_mb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
This query is read-only.
SELECT
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND counter_name IN
(
'Total Server Memory (KB)',
'Target Server Memory (KB)',
'Memory Grants Pending',
'Memory Grants Outstanding',
'Granted Workspace Memory (KB)'
)
ORDER BY counter_name;
Total Server Memory represents memory currently committed by SQL Server’s memory manager.
Target Server Memory represents the amount SQL Server would currently like to use, subject to configuration and system conditions.
Memory Grants Pending represents queries waiting for workspace memory. Sustained values above zero during poor performance deserve investigation.
Do not make a decision from one sample. Compare these values over time and during an affected workload.
SELECT TOP (20)
type AS memory_clerk_type,
SUM(pages_kb) / 1024.0 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_mb DESC;
This query is read-only. Use it as a starting point when SQL Server memory consumption is higher than expected. Escalate unfamiliar or abnormal memory-clerk findings to a DBA.
Page faults are frequently misunderstood.
A page fault occurs when a process requests a memory page that is not currently mapped into its working set.
A soft fault can be resolved from physical memory or another in-memory location without retrieving the page from storage.
A hard fault requires Windows to retrieve the page from storage.
The storage source may be:
A hard fault does not automatically prove that the pagefile was used.
The Windows Memory\Page Faults/sec counter includes more than harmful pagefile reads. A high value by itself does not prove that Windows is thrashing.
Memory\Available MBytes
Memory\Pages Input/sec
Memory\Page Reads/sec
Memory\Page Faults/sec
Paging File\% Usage
Process\Working Set
PhysicalDisk\Avg. Disk sec/Read
PhysicalDisk\Avg. Disk sec/Write
Investigate when hard faults are:
There is no universal hard-fault threshold that proves a problem on every server.
Lock Pages in Memory, commonly abbreviated as LPIM, allows eligible SQL Server memory allocations to remain in physical RAM rather than being handled through ordinary Windows paging.
LPIM may help when there is evidence that Windows is trimming SQL Server’s working set.
Possible evidence includes:
Do not enable LPIM automatically on every SQL Server.
LPIM does not:
LPIM should be paired with a carefully documented SQL maximum-memory value that leaves adequate memory for Windows and every other workload.
SELECT
sql_memory_model_desc
FROM sys.dm_os_sys_info;
Common results include:
CONVENTIONALLOCK_PAGESLARGE_PAGESLOCK_PAGES indicates that SQL Server is using the locked-pages memory model.
Use SQL Server Configuration Manager whenever possible.
For a default SQL Server instance using a virtual service account, the account may resemble: NT SERVICE\MSSQLSERVER
For a named instance, it may resemble: NT SERVICE\MSSQL$InstanceName
When granting the policy to a virtual service account, Windows may initially search the Active Directory domain instead of the local server.
If the account cannot be found:
The SQL Server Database Engine service must be restarted before the new user right becomes active.
Do not restart production SQL Server without approval
A restart disconnects applications, interrupts active work, clears SQL caches, resets many diagnostic statistics, and may affect dependent services.
After enabling LPIM and restarting SQL Server, Windows Task Manager and Resource Monitor may appear to show unexpectedly low SQL Server memory use. This can be normal. SQL Server may allocate important memory through locked pages. Standard Windows working-set columns may not represent those allocations in the way administrators expect.
A Task Manager value of a few hundred megabytes does not prove SQL Server is using only that amount of memory.
Use SQL Server’s own views:
SELECT
physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_mb,
locked_page_allocations_kb / 1024 AS locked_pages_mb,
virtual_address_space_committed_kb / 1024
AS committed_virtual_memory_mb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
Resource Monitor may display a large Commit value and a much smaller Working Set value.
Commit represents memory for which Windows has provided committed backing.
Working Set represents memory currently attributed to the process’s standard resident working set.
With LPIM, the Windows working-set column may not clearly reflect all locked SQL memory.
A 48 GB maximum-memory setting does not mean SQL Server immediately reserves or consumes 48 GB.
SQL Server starts with what it needs to initialize and grows as workload requires it. It may:
Do not run an unnecessarily large production query simply to make SQL memory increase.
The Windows pagefile is a safety mechanism. It is not a substitute for adequate physical memory.
An extremely small fixed pagefile can:
A small pagefile does not, by itself, prove the cause of every hard fault.
There is no one pagefile configuration that is correct for every SQL Server.
A system-managed pagefile on the operating-system volume is a reasonable general default for many servers.
A custom fixed size may also be appropriate when it is intentionally calculated for:
A pagefile placed on an active SQL data volume can compete with database reads and writes.
Before disabling or reducing it, confirm:
Record:
Commonly missed high-risk setting
Priority Boost sounds beneficial, but it is not a normal SQL Server performance recommendation.
Priority Boost causes SQL Server to run at a higher Windows scheduling priority. Microsoft advises that it is unnecessary for normal tuning and may interfere with Windows, networking, security software, and other applications.
SELECT
name,
value AS configured_value,
value_in_use AS running_value
FROM sys.configurations
WHERE name = 'priority boost';
For ordinary SQL Server operation, the expected value is generally:0
A value of 1 should be investigated.
Parallelism allows SQL Server to divide portions of a query across multiple workers. This can help large queries finish faster, but unnecessary parallelism can increase CPU use and reduce resources available for other work.
Maximum Degree of Parallelism, or MAXDOP, limits how many processors a parallel query-plan operation can use.
SELECT
name,
value AS configured_value,
value_in_use AS running_value
FROM sys.configurations
WHERE name = 'max degree of parallelism';
0: SQL Server may use all available logical processors, subject to other limits.1: Parallel query plans are disabled.1: Parallel plan operations are limited to that degree.MAXDOP should be selected using:
Do not assume MAXDOP 4 is correct for every eight-processor VM. It may be a reasonable starting point in some environments, but it requires validation.
This setting determines when SQL Server begins considering a parallel plan based on a query’s estimated cost.
SELECT
name,
value AS configured_value,
value_in_use AS running_value
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';
The default value is 5. Microsoft describes it as a starting point rather than a universal modern recommendation. A value such as 50 is often evaluated as a starting point, but it is not universally correct.
A balanced CPU graph does not prove that MAXDOP or Cost Threshold for Parallelism caused the improvement.
SQL Server stores execution plans in memory. An application that generates many one-time, non-parameterized queries can fill the plan cache with plans that are never reused. Optimize for ad hoc workloads stores a small plan stub the first time an ad hoc query is compiled. A full plan is stored if the query is executed again.
SELECT
name,
value AS configured_value,
value_in_use AS running_value
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';
SELECT
objtype,
cacheobjtype,
COUNT_BIG(*) AS plan_count,
CAST(
SUM(CAST(size_in_bytes AS BIGINT)) / 1024.0 / 1024.0
AS DECIMAL(18, 2)
) AS cache_size_mb
FROM sys.dm_exec_cached_plans
GROUP BY
objtype,
cacheobjtype
ORDER BY
cache_size_mb DESC;
SELECT
COUNT_BIG(*) AS single_use_plan_count,
CAST(
SUM(CAST(size_in_bytes AS BIGINT)) / 1024.0 / 1024.0
AS DECIMAL(18, 2)
) AS single_use_plan_cache_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
AND objtype = 'Adhoc';
These queries are read-only.
A large amount of memory used by single-use ad hoc plans may support enabling this option. However, the underlying issue may be application query design or lack of parameterization. Treat the setting as a mitigation, not automatically as the permanent application fix.
Allocated database-file size is not the same as active data size or memory requirement.
SELECT
DB_NAME(database_id) AS database_name,
CAST(
SUM(CASE WHEN type = 0 THEN size ELSE 0 END)
* 8.0 / 1024
AS DECIMAL(18, 2)
) AS data_file_size_mb,
CAST(
SUM(CASE WHEN type = 1 THEN size ELSE 0 END)
* 8.0 / 1024
AS DECIMAL(18, 2)
) AS log_file_size_mb,
CAST(
SUM(size) * 8.0 / 1024 / 1024
AS DECIMAL(18, 2)
) AS total_allocated_gb
FROM sys.master_files
GROUP BY database_id
ORDER BY total_allocated_gb DESC;
This query is read-only.
In SQL Server Management Studio:
Alternatively, use Copy with Headers and paste the results into a spreadsheet.
SELECT
DB_NAME(database_id) AS database_name,
name AS logical_file_name,
type_desc,
physical_name,
CAST(size * 8.0 / 1024 AS DECIMAL(18, 2))
AS allocated_size_mb,
CASE
WHEN max_size = -1 THEN 'Unlimited'
ELSE CAST(
CAST(max_size * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS maximum_size,
CASE
WHEN is_percent_growth = 1
THEN CAST(growth AS VARCHAR(20)) + '%'
ELSE CAST(
CAST(growth * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS growth_setting
FROM sys.master_files
ORDER BY
database_name,
type_desc,
logical_file_name;
This report helps identify:
Old test databases and copied production databases are common.
An inactive database does not continuously consume memory equal to its allocated disk size. However, it still creates:
A database may still be processed by:
SELECT
d.name AS database_name,
MAX(activity.last_observed_activity) AS last_observed_activity
FROM sys.databases AS d
LEFT JOIN sys.dm_db_index_usage_stats AS ius
ON d.database_id = ius.database_id
OUTER APPLY
(
SELECT MAX(activity_date) AS last_observed_activity
FROM
(
VALUES
(ius.last_user_seek),
(ius.last_user_scan),
(ius.last_user_lookup),
(ius.last_user_update)
) AS dates(activity_date)
) AS activity
GROUP BY d.name
ORDER BY last_observed_activity;
This query is read-only.
Important limitation
Index-usage statistics reset after events such as a SQL Server restart, database detach or attach, database shutdown, and some maintenance or upgrade events. A blank date does not prove that a database is unused.
Deleting old databases is not a direct query-optimization technique. It is good technology bookkeeping, security management, and operational risk reduction.
Separating SQL files onto different drive letters does not automatically improve performance. The underlying storage matters.
Two virtual disks may use the same:
Separate drive letters may improve organization and capacity management without providing true performance isolation.
C: Windows and application binaries
D: SQL Server data files
L: SQL Server transaction logs
T: TempDB
B: SQL Server backups
The letters do not matter. The architecture does.
Data files commonly experience mixed or random read and write activity. Transaction logs primarily use sequential writes during normal transaction processing, although backup and recovery operations also read them. Separating them can improve performance and capacity control when the underlying storage is also meaningfully separated. Do not move SQL files solely because a checklist says they need different drive letters. Measure the workload and create a tested migration plan.
TempDB supports:
USE tempdb;
GO
SELECT
name,
type_desc,
physical_name,
CAST(size * 8.0 / 1024 AS DECIMAL(18, 2)) AS size_mb,
CASE
WHEN is_percent_growth = 1
THEN CAST(growth AS VARCHAR(20)) + '%'
ELSE CAST(
CAST(growth * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS growth_setting,
CASE
WHEN max_size = -1 THEN 'Unlimited'
ELSE CAST(
CAST(max_size * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS maximum_size
FROM sys.database_files
ORDER BY
type_desc,
name;
This query is read-only.
For SQL Server 2016 and later, Microsoft commonly recommends equally sized TempDB data files, using one data file per logical processor up to eight as an initial configuration when establishing a baseline or addressing allocation contention. More files are not automatically better. Additional files should be driven by evidence such as TempDB allocation contention and relevant PAGELATCH waits.
Repeated shrinking creates a cycle of shrink and regrowth. Correct the file sizing, free-space, or workload problem instead.
Disk Active Time and queue length can be useful, but neither should be interpreted alone.
PhysicalDisk\Avg. Disk sec/Read
PhysicalDisk\Avg. Disk sec/Write
PhysicalDisk\Avg. Disk sec/Transfer
PhysicalDisk\Current Disk Queue Length
PhysicalDisk\Avg. Disk Queue Length
PhysicalDisk\Disk Reads/sec
PhysicalDisk\Disk Writes/sec
Latency measures how long an I/O request takes to complete.
Microsoft notes that SQL Server disk transfers are typically expected below approximately 15 milliseconds, but the acceptable value depends on:
Old rules such as “a disk queue must always remain below two” are too simplistic for:
A queue becomes more concerning when it remains elevated while latency and user-facing delays also increase.
SELECT
DB_NAME(vfs.database_id) AS database_name,
mf.type_desc,
mf.physical_name,
vfs.num_of_reads,
CASE
WHEN vfs.num_of_reads = 0 THEN 0
ELSE CAST(
vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
AS DECIMAL(18, 2)
)
END AS average_read_latency_ms,
vfs.num_of_writes,
CASE
WHEN vfs.num_of_writes = 0 THEN 0
ELSE CAST(
vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
AS DECIMAL(18, 2)
)
END AS average_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY
average_read_latency_ms DESC,
average_write_latency_ms DESC;
This query is read-only.
Cumulative-statistics warning
These values are cumulative since SQL Server started or since the underlying statistics were reset. A long-running server can show historical latency that is no longer occurring. Capture values at two times and compare the difference, or collect them during a known slow period.
A large transaction log is not automatically a problem. The log may have been intentionally sized to support normal transactions, maintenance, reporting, or recovery objectives.
DBCC SQLPERF(LOGSPACE);
This command reports:
It does not shrink or modify the log.
Repeated shrinking can create a shrink-and-regrow cycle and may increase the number of virtual log files or cause avoidable storage activity. Understand why the log grew and determine its normal operating size before changing it.
Performance tuning is not successful if it weakens recoverability.
SELECT
d.name AS database_name,
d.recovery_model_desc,
MAX(CASE WHEN bs.type = 'D'
THEN bs.backup_finish_date END) AS last_full_backup,
MAX(CASE WHEN bs.type = 'I'
THEN bs.backup_finish_date END) AS last_differential_backup,
MAX(CASE WHEN bs.type = 'L'
THEN bs.backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
ON d.name = bs.database_name
GROUP BY
d.name,
d.recovery_model_desc
ORDER BY d.name;
This query reports backup history recorded in the local msdb database.
Backup history is not proof of recoverability
A backup record in msdb does not prove that the backup file still exists, is protected, or can be restored. A backup stored only on the same server or storage system as the production database does not provide sufficient protection against server failure, storage failure, ransomware, or administrative mistakes.
Backup checksums can help detect certain damaged pages or backup-media problems. They do not replace restore testing. Regularly test restoration according to the organization’s recovery objectives.
Before assuming that memory, CPU, or storage is the bottleneck, review SQL Server wait statistics.
Waits can point toward broad categories such as:
SELECT TOP (20)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
CAST(
100.0 * wait_time_ms
/ NULLIF(SUM(wait_time_ms) OVER (), 0)
AS DECIMAL(6, 2)
) AS percentage_of_observed_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;
This query is read-only.
Wait-statistics warning
Wait statistics are cumulative since the SQL Server service started or the statistics were cleared. Do not use the results as a list of settings to change. Wait types require interpretation in the context of the workload.
Escalate unclear, persistent, or business-impacting waits to a DBA.
A server can have healthy CPU, memory, and storage while one query performs poorly.
Where supported and approved by the application vendor, SQL Server Query Store can help identify:
Before enabling or changing Query Store:
Do not enable Query Store everywhere without understanding the environment.
When SQL Server runs inside a virtual machine, inspect both the guest operating system and the host platform.
Adding RAM to a VM may help when memory pressure is confirmed, but it does not correct:
If the VM’s assigned RAM is reduced, review the SQL maximum-memory setting before the change. A value selected for a 96 GB VM may become unsafe if the VM is reduced to 64 GB or 32 GB. Document LPIM and SQL memory together so future infrastructure changes do not create an avoidable outage.
The following script gathers common configuration and health information without intentionally changing SQL Server configuration, databases, files, or application data.
Read-only inspection script
Review the script before running it. Execute it using an account authorized to view server-level information. Some sections may return incomplete data when permissions are limited.
/* ============================================================
BASIC SQL SERVER HEALTH AND CONFIGURATION REVIEW
Read-only inspection script
============================================================ */
SET NOCOUNT ON;
/* ------------------------------------------------------------
1. SQL Server version
------------------------------------------------------------ */
SELECT
SERVERPROPERTY('MachineName') AS machine_name,
SERVERPROPERTY('ServerName') AS server_name,
SERVERPROPERTY('InstanceName') AS instance_name,
SERVERPROPERTY('Edition') AS edition,
SERVERPROPERTY('ProductVersion') AS product_version,
SERVERPROPERTY('ProductLevel') AS product_level,
SERVERPROPERTY('ProductUpdateLevel') AS update_level,
SERVERPROPERTY('ProductUpdateReference') AS update_reference;
/* ------------------------------------------------------------
2. Important server configuration values
------------------------------------------------------------ */
SELECT
name,
value AS configured_value,
value_in_use AS running_value,
description
FROM sys.configurations
WHERE name IN
(
'min server memory (MB)',
'max server memory (MB)',
'max degree of parallelism',
'cost threshold for parallelism',
'optimize for ad hoc workloads',
'priority boost',
'backup compression default',
'backup checksum default'
)
ORDER BY name;
/* ------------------------------------------------------------
3. SQL Server memory model
------------------------------------------------------------ */
SELECT
sql_memory_model_desc
FROM sys.dm_os_sys_info;
/* ------------------------------------------------------------
4. Windows memory state
------------------------------------------------------------ */
SELECT
total_physical_memory_kb / 1024
AS total_physical_memory_mb,
available_physical_memory_kb / 1024
AS available_physical_memory_mb,
total_page_file_kb / 1024
AS total_page_file_mb,
available_page_file_kb / 1024
AS available_page_file_mb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
/* ------------------------------------------------------------
5. SQL Server process memory
------------------------------------------------------------ */
SELECT
physical_memory_in_use_kb / 1024
AS sql_physical_memory_in_use_mb,
locked_page_allocations_kb / 1024
AS locked_pages_mb,
large_page_allocations_kb / 1024
AS large_pages_mb,
virtual_address_space_committed_kb / 1024
AS committed_virtual_memory_mb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
/* ------------------------------------------------------------
6. SQL Server Memory Manager counters
------------------------------------------------------------ */
SELECT
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND counter_name IN
(
'Total Server Memory (KB)',
'Target Server Memory (KB)',
'Memory Grants Pending',
'Memory Grants Outstanding',
'Granted Workspace Memory (KB)'
)
ORDER BY counter_name;
/* ------------------------------------------------------------
7. Largest SQL memory clerks
------------------------------------------------------------ */
SELECT TOP (20)
type AS memory_clerk_type,
CAST(SUM(pages_kb) / 1024.0 AS DECIMAL(18, 2))
AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_mb DESC;
/* ------------------------------------------------------------
8. Allocated database and log sizes
------------------------------------------------------------ */
SELECT
DB_NAME(database_id) AS database_name,
CAST(
SUM(CASE WHEN type = 0 THEN size ELSE 0 END)
* 8.0 / 1024
AS DECIMAL(18, 2)
) AS data_file_size_mb,
CAST(
SUM(CASE WHEN type = 1 THEN size ELSE 0 END)
* 8.0 / 1024
AS DECIMAL(18, 2)
) AS log_file_size_mb,
CAST(
SUM(size) * 8.0 / 1024 / 1024
AS DECIMAL(18, 2)
) AS total_allocated_gb
FROM sys.master_files
GROUP BY database_id
ORDER BY total_allocated_gb DESC;
/* ------------------------------------------------------------
9. Database file paths and growth configuration
------------------------------------------------------------ */
SELECT
DB_NAME(database_id) AS database_name,
name AS logical_file_name,
type_desc,
physical_name,
CAST(size * 8.0 / 1024 AS DECIMAL(18, 2))
AS allocated_size_mb,
CASE
WHEN max_size = -1 THEN 'Unlimited'
ELSE CAST(
CAST(max_size * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS maximum_size,
CASE
WHEN is_percent_growth = 1
THEN CAST(growth AS VARCHAR(20)) + '%'
ELSE CAST(
CAST(growth * 8.0 / 1024 AS DECIMAL(18, 2))
AS VARCHAR(40)
) + ' MB'
END AS growth_setting
FROM sys.master_files
ORDER BY
database_name,
type_desc,
logical_file_name;
/* ------------------------------------------------------------
10. SQL file latency
Cumulative since SQL Server startup or counter reset
------------------------------------------------------------ */
SELECT
DB_NAME(vfs.database_id) AS database_name,
mf.type_desc,
mf.physical_name,
vfs.num_of_reads,
CASE
WHEN vfs.num_of_reads = 0 THEN 0
ELSE CAST(
vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
AS DECIMAL(18, 2)
)
END AS average_read_latency_ms,
vfs.num_of_writes,
CASE
WHEN vfs.num_of_writes = 0 THEN 0
ELSE CAST(
vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
AS DECIMAL(18, 2)
)
END AS average_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY
average_read_latency_ms DESC,
average_write_latency_ms DESC;
/* ------------------------------------------------------------
11. Recent observed index activity
Statistics reset after SQL restart and other events
------------------------------------------------------------ */
SELECT
d.name AS database_name,
MAX(activity.last_observed_activity)
AS last_observed_activity
FROM sys.databases AS d
LEFT JOIN sys.dm_db_index_usage_stats AS ius
ON d.database_id = ius.database_id
OUTER APPLY
(
SELECT MAX(activity_date) AS last_observed_activity
FROM
(
VALUES
(ius.last_user_seek),
(ius.last_user_scan),
(ius.last_user_lookup),
(ius.last_user_update)
) AS dates(activity_date)
) AS activity
GROUP BY d.name
ORDER BY last_observed_activity;
/* ------------------------------------------------------------
12. Backup summary recorded in local msdb
------------------------------------------------------------ */
SELECT
d.name AS database_name,
d.recovery_model_desc,
MAX(CASE WHEN bs.type = 'D'
THEN bs.backup_finish_date END)
AS last_full_backup,
MAX(CASE WHEN bs.type = 'I'
THEN bs.backup_finish_date END)
AS last_differential_backup,
MAX(CASE WHEN bs.type = 'L'
THEN bs.backup_finish_date END)
AS last_log_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
ON d.name = bs.database_name
GROUP BY
d.name,
d.recovery_model_desc
ORDER BY d.name;
/* ------------------------------------------------------------
13. Transaction-log allocation and use
Informational only
------------------------------------------------------------ */
DBCC SQLPERF(LOGSPACE);
/* ------------------------------------------------------------
14. Top observed SQL wait types
Cumulative since SQL startup or statistics reset
------------------------------------------------------------ */
SELECT TOP (20)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
CAST(
100.0 * wait_time_ms
/ NULLIF(SUM(wait_time_ms) OVER (), 0)
AS DECIMAL(6, 2)
) AS percentage_of_observed_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;
SQL Server grows its cache while Windows and other applications are left with inadequate headroom.
SQL Server repeatedly evicts useful cache pages and performs more physical database reads than the workload should require.
Reporting, document management, backups, security tools, integrations, and web services compete for the same resources.
Total page faults, hard faults, pagefile use, and database-file reads are treated as the same measurement.
SQL memory is protected, but Windows and other applications may be left with inadequate physical memory.
SQL Server receives elevated scheduling priority even though Microsoft does not recommend it for ordinary tuning.
A large query may use more parallel workers than appropriate for the workload.
Relatively small queries may be considered for parallel plans unnecessarily.
Tiny files, repeated growth, unequal files, or allocation contention create unnecessary overhead.
Different workloads compete for the same underlying storage.
Backups, integrity checks, monitoring, and maintenance continue processing databases that may no longer be needed.
Growth events become increasingly unpredictable as files become larger.
The server has inadequate commit headroom or crash-dump capability.
A server or storage failure can affect the database and its only backup simultaneously.
No one can determine which change helped, which caused a regression, or how to roll back safely.
Every non-read-only change should create a durable record.
Use a template such as:
Date and time:
Server and SQL instance:
Business owner:
Application owner:
Business reason:
Observed symptom:
Baseline measurements:
Previous configured value:
Previous running value:
New value:
Application-vendor guidance reviewed:
Backup status confirmed:
Restore capability confirmed:
Maintenance window:
Service restart required:
Expected impact:
Validation procedure:
Rollback procedure:
Validation result:
Engineer responsible:
Approver:
Follow-up date:
This documentation is technology bookkeeping. It helps future administrators understand why a non-default setting exists and prevents an unrelated infrastructure change from recreating the problem.
Not necessarily. An occasional value such as one hard fault per second is generally not concerning by itself. Hard faults are a normal part of Windows memory management.
Investigate when hard faults are sustained and correlate with:
A value of one hard fault per second with substantial available memory is encouraging, but it does not prove the entire server is healthy or permanently fixed.
A page fault means a requested memory page was not immediately available in the process’s current working set. A soft fault is resolved from memory. A hard fault requires storage access.
There is no universal number. The pattern, duration, storage effect, available memory, and user impact are more important than one isolated value.
No. Low memory use immediately after a restart is expected because SQL Server and other applications have not rebuilt their caches or completed a normal workload cycle.
SQL Server grows memory use as queries execute and data is loaded into cache. The maximum-memory value is a ceiling, not a reservation.
Task Manager may not clearly display memory SQL Server allocated through locked pages. Use sys.dm_os_process_memory, Total Server Memory, Target Server Memory, and the SQL memory model before drawing a conclusion.
Commit represents memory with committed backing. Working Set represents memory attributed to the process’s standard resident working set. LPIM can make the ordinary Windows working-set number appear much smaller than expected.
No. LPIM protects eligible SQL Server allocations. SQL Server can still access executables, DLLs, memory-mapped files, direct allocations, and data not currently in cache.
LPIM requires intentional capacity planning. Windows has less flexibility to reclaim eligible locked SQL memory during pressure. The SQL maximum-memory setting, other applications, and future VM resource changes must be documented and managed together.
It can be, but it requires additional caution. Measure peak memory use for Windows, the business application, reporting, backup, security, and integration services before selecting the SQL memory maximum.
A previously safe SQL memory limit may become unsafe. Review and adjust the SQL memory design before reducing the VM’s assigned memory.
It may have contributed, but the pagefile size alone does not prove the root cause. The overall condition may involve high total memory use, SQL working-set trimming, competing applications, limited commit headroom, and workload spikes.
Not directly. A correctly sized pagefile improves resilience and commit capacity. Heavy dependence on the pagefile remains much slower than physical RAM.
No. Database size is only one contextual measurement. Active data, query patterns, concurrency, reporting, memory grants, and cache effectiveness matter more.
An unused database does not continuously consume memory equal to its disk size. It may still consume resources indirectly through backups, integrity checks, monitoring, maintenance, reporting, or accidental access.
Not automatically. It may reduce maintenance, backup, monitoring, and governance overhead. The performance benefit depends on whether anything was actively processing those databases.
No. Separate drive letters help organization and capacity management. A performance benefit requires meaningful separation at the underlying storage layer.
Not as routine maintenance. Determine why the file grew and what its normal operating size should be. Shrink operations can create fragmentation and repeated regrowth.
No. Review queue activity together with latency, throughput, SQL waits, and the workload occurring at the time.
No. First identify why each setting exists, confirm the current workload, review vendor guidance, and document a tested change plan.
SQL Server optimization is not about finding one hidden setting that makes every database faster. It is about balance. SQL Server needs enough memory to cache active data efficiently. Windows needs enough memory to remain stable. Other applications need clearly understood resources. Storage must complete database requests with consistent latency. Backups must remain trustworthy. Changes must be documented and measurable.
The most valuable first step is often not advanced query tuning. It is confirming that the server’s basic architecture and configuration still match the workload it is expected to support today.
Good SQL Server management is also good technology bookkeeping:
The goal is not a server that looks perfect for five minutes after a restart. The goal is a server that performs predictably, remains recoverable, supports the business, and can be understood by the next person responsible for it.
Continue learning: After making an approved change, review our companion guide, How to Validate SQL Server Performance After Optimization, to understand CPU, memory, hard faults, IOPS, latency, disk queues, and post-change monitoring.
