Not enough storage is available to complete this operation

This message appears in the TDP SQL dsmsched file.  It’s usually associated with low levels of contiguous memory

BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-00001BDC-0000'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.). A nonrecoverable I/O error occurred on file "TDPSQL-00001BDC-0000:" 995(The I/O operation has been aborted because of either a thread exit or an application request.). BACKUP DATABASE is terminating abnormally.

 

Notes

 1) Likely SQL Server has run out of large memory blocks to allocate to the backup/restore process

 2) Monitor the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values

3) When a SQL Server backup fails due to the lack of a large enough contiguous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes. 

 4) Immediate but drastic action may include restart of the SQL Server Instance. Proceed with caution and double check there aren’t open transactions .

 See Also

SQL Server Performance ,memory pressure and memory usage

SQL Cachestore flush

For each cleared cachestore in the plan cache SQL Server reports a message in the error logs  , such as :

 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

 Notes:

1)       When the plan cache clears ,  a recompilation is forced , this can cause a temporary performance decrease

2)       Using DBCC FREEPROCCACHE  clears the cache .

3)       Restoring a database and attaching\detaching a database  causes a plan cache clear for the SQL Server instance.

4)       Using DBCC FREESYSTEMCACHE clears the cache.

5)       AUTOCLOSE = ON which causes the database to shutdown forces a clear down of cache.

6)     Buffer manager sql counters and sys.dm_os_memoryclerks  are good starting points to identify pressure

 See Also

SQL Server page life expectancy and memory bottleneck

Sys.dm_os_memory_clerks and AWE memory allocation

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Performance Tuning - optimizing for response time

Different  queries are optimized for different objectives

Response Times and Throughput are two standard objectives.

Response Time is about retrieving the  hits as quickly as possible. In a search engine , that would mean returning the first few rows immediately

Throughput is about returning ALL the hits (rows) in the smallest time frame

Factors to consider in improving Response Time

1)       Parallelism

2)       Partitioning

3)       Data types – such as CLOBs in the table

4)       Sorting

5)       Memory

6)       SQL Statistics

See Also

Waitstats CXPACKET wait type and how to reduce it

SQL Server Performance ,memory pressure and memory usage

SQL Server Buffer Cache Hit Ratio and memory pressure

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk.  The purpose of  asynchronous IO is to release resources and  for other tasks to progress.

i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur.

 When this occurs , you’ll see an error message IO Requests taking longer than 15 seconds to complete

IO  writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server memory region

When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.

Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging

The write-ahead logging method ensure the transaction log is written to disk  and is fundamental to the Recovery Manager.

How is the dirty page written to disk?

Eager writing   - Nonlogged bcp,  SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.

Checkpoint  writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.

Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

a)       net stop mssqlserver

b)       SHUTDOWN

c)       ALTER DATABASE adding a file

 

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint

Author: Jack Vamvas (http://www.sqlserver-dba.com)

sys.dm_os_loaded_modules

sys.dm_os_loaded_modules  is a SQL Server OS Dynamic Management View (DMV).

When executing the t-sql statement below – a recordset is returned . I’ve included some output further down the page :

select file_version,product_version,company,[description] from sys.dm_os_loaded_modules  

 

I find this dmv useful when troubleshooting corruption .

An example , is to  check if there is any anti-virus module which is getting loaded in the SQL memory area. Normally , I’m looking for non-Microsoft Corporation modules .

Returns a row for each module loaded into the server address space.

 

2005.90:4340.0	9.0:4340.0	Microsoft Corporation	SQL Server Windows NT
5.2:3790.4789	5.2:3790.4789	Microsoft Corporation	NT Layer DLL
5.2:3790.4480	5.2:3790.4480	Microsoft Corporation	Windows NT BASE API Client DLL
8.0:50727.3053	8.0:50727.3053	Microsoft Corporation	Microsoft® C Runtime Library
7.0:3790.3959	6.1:8638.3959	Microsoft Corporation	Windows NT CRT DLL
8.0:50727.3053	8.0:50727.3053	Microsoft Corporation	Microsoft® C++ Runtime Library
5.2:3790.4455	5.2:3790.4455	Microsoft Corporation	Advanced Windows 32 Base API
5.2:3790.4759	5.2:3790.4759	Microsoft Corporation	Remote Procedure Call Runtime
5.2:3790.4530	5.2:3790.4530	Microsoft Corporation	Security Support Provider Interface
2005.90:4035.0	9.0:4035.0	Microsoft Corporation	SQLOS Hosting DLL

Author: Jack Vamvas (http://www.sqlserver-dba.com)