1. How to check the status of whether a table has its automatic statistics turned off or not?
Ans. We can use this command
EXEC sp_autostats 'TableName';
2. What is role of AUTO_CLOSE?
Ans. AUTO_CLOSE will shuts down a database and frees all its resources when the last user connection is closed.Also all the data and queries in the cache are automatically flushed.
3. What is the significance of AUTO_SHRINK?
Ans. AUTO_SHRINK periodically shrinks the size of the database. It can shrink the data files and, when in Simple Recovery mode, the log files. While doing this, it can block other processes, seriously slowing down your system.More often than not, file growth is also set to occur automatically on systems with AUTO_SHRINK enabled, so your system will be slowed down yet again when the data or log files have to grow.
4. Explain about Page/Faults?
Ans. A page fault occurs when a process requires code or data that is not in its working set (or its space in physical memory). There are two types of page faults.Soft page fault : If the faulted page is found elsewhere in physical memory, then it is called a soft page fault.Hard page fault : A hard page fault occurs when a process requires code or data that is not in its working set or elsewhere in physical memory and must be retrieved from disk.
5. Explain about Buffer Cache in sql server?
Ans. The buffer cache is the pool of buffer pages into which data pages are read, and it is often the biggest part of the SQL Server memory pool.
6.What is a dirty page?
Ans. A dirty page is one that is modified while in the buffer.When it is modified, it’s marked as dirty and will get written back to the disk during the next checkpoint.