I am using SQL Server 2008 Enterprise. I will issue a lot of query to SQL Server. And I think SQL Server itself will use some optimization internally, like pre-fetching data into SQL Server internal data cache or accessing frequent requesting data from cache before load from physical page file to improve performance.
Any solutions to see SQL Server internal cache hit rate? Or any best practices to tune cache to make query performance better?
thanks in advance,
George
Best Answer
Have a look at BOL: SQL Server, Buffer Manager Object.
The two areas that you should look at first are:
Procedure cache is the area of memory where SQL stores your query plans.
Buffer cache is the area of memory where data pages are stored.
Relevant perfmon counters:
Top SQL Server 2005 Performance Issues for OLTP Applications contains the following: