How to fix System.OutOfMemoryException when using SSMS

Question: When executing a statement through SSMS returning a large recordset , I’m getting a System.OutOfMemoryException error. The error occurs before the recordset fully returns. Why is this error occurring and what is the workaround? I’m using SQL Server 2012 Enterprise Edition . On a 64 bit Windows 2008 server. The full error message is : An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown. Answer: SSMS is a 32 bit process. It is limited to 2GB memory.When using Grid Mode there is a 64 kb limit per database field. If the resultset is... Read more →

VM Memory balloon performance counters

In acceptance tests for migrating database servers from Physical Servers to Virtual Servers, one of the grey areas is the ESX host and the memory balloon. Read more on Virtualization – and database servers - Sqlserver-dba.com A typical list of performance counters I use is found below. For now , I’d like to focus on the Memory Balloon CPU Usage in MHz CPU Usage % Disk Command Latency Disk Write Latency Disk Read Latency Disk Commands Issued Memory Consumed Memory Balloon Memory Swapped Memory Swapped In Memory Swapped Out To access these performance counters , use Perfmon. Open perfmon and... Read more →

How to Generate a Perfmon System Diagnostics Report

Perfmon has a useful feature of generating a Systems Diagnostic Report. The Report checks for various categories : Software Configuration, Hardware Configuration, CPU,Network,Disk ,Memory To create the report use the cmd line. Run As administrator type perfmon /report. Perfmon will collect data for 60 seconds The two immediate areas to check are : Warnings and Performance. You can drill down into the details by following the links. This is a great feature and is usefull all-round diagnostics tool. The key is to interpret the data to resolve any issues. If you need to analyse other correlations over a greater period... Read more →

Memory grants and execution plans

Question: When reviewing some executions plans for slow running queries, I’ve noticed Memory Grant appearing. What is the Memory grant message? Answer: As an example: When the SQL Optimizer predicts it will operate on 15 rows based on the latest statistics for those tables , a chunk of memory is granted to fulfil the operation. But, if the statistics are inaccurate or not up to date, and the operation returns 10000 rows , the operation doesn’t automatically grant the extra memory. The operation will spill to disk – in the tempdb. Spilling to disk will allow the sorting and joining... Read more →

SQL Server Install Checklist

Troubleshooting is essential for SQL Server issues – but focusing on a solid architecture and sql tuning large amounts of troubleshooting can be avoided. Part of a solid SQL Server architecture is to install on an infrastructure supporting the workload using SQL Server test data generation testing tools and other workload analysis. The infrastructure should also include redundancy. Redundancy levels should reflect the agreements between the DBA and customer. Redundancy is a tricky issue and there is a cost of having extra levels of redundancy. For example, server hardware redundancy requires extra servers. I use this basic checklist to review... Read more →