This query shows: Amount of memory allocated to the buffer pool Amount of memory consumed by BPool Amount of memory used by AWE SELECT SUM(single_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] FROM sys.dm_os_memory_clerks WHERE type = ‘MEMORYCLERK_SQLBUFFERPOOL’ Popular search terms:SQL awe check amount of memory usedawe memorymemoryclerk_sqlbufferpoolsingle_pages_kb virtual_memory_committed_kbquery [...]
Archive of entries posted on May 2009
Transferring Large Amounts of Data using Batch Inserts
Below is a technique used to transfer a large amount of records from one table to another. This scales pretty well for a couple reasons. First, this will not fill up the entire log prior to committing the transaction. Rather, it will populate the table in chunks of 10,000 records. Second, it’s generally much quicker. [...]
SQL Server Pagination using CTE and RowNumber
This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) [...]
SQL Server Slow Performance
This post deals with a random hanging that sometimes happens with SQL Server 2005+. In order to troubleshoot SQL Server Slowness, go here. The introduction of the new SQL Server 2005 Query Optimization engine has brought great things (including statement-level caching and smarter execution plan generation). There is however a little more overhead with the [...]
