Clear-DbaPlanCache View Source Tracy Boggiano, databasesuperhero.com Windows, Linux, macOS Synopsis Clears SQL Server plan cache when single-use adhoc and prepared plans exceed memory threshold
Description Monitors your SQL Server’s plan cache for single-use adhoc and prepared plans that consume excessive memory. When these plans exceed the specified threshold (default 100MB), the function clears the entire plan cache using DBCC FREESYSTEMCACHE(‘SQL Plans’).
Single-use plans are a common cause of memory pressure in SQL Server environments with dynamic SQL or applications that don’t use parameterized queries.
Get-DbaDbccMemoryStatus View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Executes DBCC MEMORYSTATUS and returns memory usage details in a structured format
Description Runs DBCC MEMORYSTATUS against SQL Server instances and parses the output into a structured PowerShell object for analysis. This replaces the need to manually execute DBCC MEMORYSTATUS and interpret its raw text output, making memory troubleshooting and monitoring much easier. The function organizes memory statistics by type (like Memory Manager, Buffer Manager, Resource Pool, etc.
Get-DbaDbMemoryUsage View Source Shawn Melton (@wsmelton), wsmelton.github.io Windows, Linux, macOS Synopsis Retrieves detailed buffer pool memory consumption by database and page type for performance analysis.
Description Analyzes SQL Server buffer pool memory usage by querying sys.dm_os_buffer_descriptors to show exactly how much memory each database consumes, broken down by page type (data pages, index pages, etc.). This helps DBAs identify memory-hungry databases that may be impacting instance performance and guides decisions about memory allocation, database optimization, or server capacity planning.
Get-DbaMaxMemory View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Retrieves SQL Server max memory configuration and compares it to total physical server memory
Description This command retrieves the SQL Server ‘Max Server Memory’ configuration setting alongside the total physical memory installed on the server. This comparison helps identify potential memory configuration issues that can impact SQL Server performance.
Use this function to audit memory settings across your environment, troubleshoot performance issues related to memory pressure, or verify that SQL Server isn’t configured to use more memory than physically available.
Get-DbaMemoryUsage View Source Klaas Vandenberghe (@PowerDBAKlaas) Windows, Linux, macOS Synopsis Collects memory usage statistics from all SQL Server services using Windows performance counters
Description Collects detailed memory usage from SQL Server Database Engine, Analysis Services (SSAS), and Integration Services (SSIS) using Windows performance counters. This helps you troubleshoot memory pressure issues and understand how memory is allocated across different SQL Server components on the same server.
Gathers counters from Memory Manager (server memory, connection memory, lock memory), Plan Cache (procedure plans, ad-hoc plans), Buffer Manager (total pages, free pages, stolen pages), and service-specific memory usage.
Get-DbaPlanCache View Source Tracy Boggiano, databasesuperhero.com Windows, Linux, macOS Synopsis Retrieves single-use plan cache usage to identify memory waste from adhoc and prepared statements
Description Analyzes the plan cache to identify memory consumed by single-use adhoc and prepared statements that are unlikely to be reused. These plans accumulate over time and can consume significant memory without providing performance benefits.
When applications generate dynamic SQL without proper parameterization, each unique statement creates its own execution plan.
Get-DbaStartupParameter View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Retrieves SQL Server startup parameters from the Windows service configuration
Description Extracts and parses SQL Server startup parameters directly from the Windows service configuration using WMI. Returns detailed information about file paths (master database, transaction log, error log), trace flags, debug flags, and special startup modes like single-user or minimal start.
Useful for troubleshooting startup issues, documenting server configurations, and verifying trace flag settings without connecting to SQL Server itself.
Set-DbaMaxMemory View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Configures SQL Server ‘Max Server Memory’ setting using calculated recommendations or explicit values
Description Modifies the SQL Server ‘Max Server Memory’ configuration to prevent SQL Server from consuming all available system memory.
This setting controls how much memory SQL Server can allocate for its buffer pool and other memory consumers, leaving
adequate memory for the operating system and other applications.
Test-DbaMaxMemory View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Calculates recommended SQL Server max memory settings to prevent OS memory pressure and optimize performance.
Description Analyzes server memory and SQL Server instances to calculate optimal max memory configuration settings. Uses a tiered algorithm that reserves appropriate memory for the operating system based on total server memory, accounting for multiple SQL instances and other SQL services like SSAS, SSRS, or SSIS.
dbatools