0

I have few questions about SQL Server 2008.

How can I check is Memory Caching feature in SQL Server 2008 is enabled? Is there a variable to turn memory caching on or off? "I just want to make sure it is on"

Also, when does SQL Server decide that this cached data is outdated so it dumps it and perform a hard disk read again?

Finally, assuming I have this query SELECT * FROM table1 WHERE id = 10 After the record is cached in memory and a process is trying to read it, does SQL Server place a Shared lock on that record in memory or there is no locks in memory?

Mark
  • 2,041
  • 2
  • 18
  • 35
Junior
  • 11,602
  • 27
  • 106
  • 212

1 Answers1

0

The short answer is no, you can't turn off Memory Caching at the server level. The engine takes care of memory Caching for you and it is very aggressive in how it caches, you basically want all queries returned from memory and not from disk. The buffer is orders of magnitude faster than disk access.

Check out these articles which explain how the caching works - known as buffer pool in SQL Server speak.

https://dba.stackexchange.com/questions/43572/how-to-see-what-is-cached-in-memory-in-sql-server-2008

Also anything by Paul Randal is pretty definitive - http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-whats-in-the-buffer-pool/

In terms of locking, this is a commonly asked question on SO. In simple terms the default behaviour is that SQL Server uses Shared locks for readers. So multiple readers can access the data at the same time. SQL Server uses a dynamic locking strategy which escalates locks as needed typically from page level to table level but may de-escalate to row level locks if it sees fit. However this is done dynamically and is automatically handled by the SQL Server engine. here is an article on this topic - https://technet.microsoft.com/en-us/library/ms189286(v=sql.105).aspx

Also this question has been asked a lot at SO before so check out this link What are row, page and table locks? And when they are acquired? and others

Community
  • 1
  • 1
cameront
  • 690
  • 5
  • 9
  • Thank you for that. I am still unclear on when will SQL Server decide that the data is aged. Also, when multiple processes are trying to access the same record from the cache will SQL Server place lock on the records in the memory? – Junior Mar 01 '16 at 00:18