Memory usage has a direct effect on how fast queries are executed. Having a store of memory helps expedite a SQL query by keeping it from having to go to the disk to retrieve data. DBAs often add more memory to speed up SQL Server performance, but this doesn’t address the issue of poorly written queries that quickly consume available memory. There is a better way to effectively optimize memory usage to accelerate SQL Server performance.
SolarWinds Database Performance Analyzer lets you access collated, second-by-second data pulled from active sessions. SQL statements are ranked by response times to help developers, admins, and database analysts identify which SQL query to focus on.
DPA lets DBAs see different factors that influence memory usage, including:
SolarWinds Database Performance Analyzer lets you see the direct correlation between response time and system resources such as CPU, storage, and memory, from a single pane of glass. The Resource Tab correlates resources with response times.
Frequently check servers for signs of memory pressure by monitoring Page Life Expectancy
Page life expectancy (PLE) is the number of seconds a page stays in the buffer pool without any references. The longer the page stays in memory, the greater the chances the page will be reused. Drops in PLE indicate that pages aren’t staying in memory for other uses. This behavior is usually caused by SQL Server running low on buffer memory. Typically, poorly written SQL statements or missing indexes can cause SQL Server® to use too much memory because the query is reading far too much data to return a far smaller result set. Proper indexing and well-written queries can reduce memory stress, which optimizes memory usage.
DPA for SQL Server® shows DBAs the page life expectancy drops and helps them correlate the memory pressure with factors like large table scans, sorting, etc. DPA isolates the queries that are causing PLE drops and provides clues for optimization. Optimizing bad/expensive queries also requires visibility into other performance metrics such as average execution, query duration, CPU time, logical/physical reads, and more. DPA helps with gathering information, establishing baselines, and maintaining a history of the best execution methods for effective SQL query optimization.
Quick and easy access to key metrics for plan and memory usage
Plan cache looks at the buffer pool size and shows the memory size configured. Every time a SQL statement is executed, the engine searches to see if the plan for the SQL statement already exists. If it does, the engine makes use of it instead of wasting time compiling a new plan. This is how the plan cache improves the efficiency of the SQL Server engine. Storing a query plan for a statement that is executed only once is called a single-use plan. Too many single-use plans can exhaust SQL Server memory and result in poor SQL Server performance.
Database Performance Analyzer collects cached execution plans in real-time and links these with SQL statements, wait types, and other performance data. DPA stores this data’s history, allowing you to watch for and alert on any plan changes that may cause performance degradation. DPA also displays compilations and recompilations per second, which is the metric for seeing if execution plans are being reused.
Database Performance Analyzer for SQL Performance
Easy drill in, context setting, and consistent navigation
See what’s being blocked AND what’s doing the blocking—plus, detailed deadlock analysis
Identify your best index, query, and table tuning opportunities