Common metrics and cache clearing methods in sqlserver

How to view disk I/O operation information
The set statistics io on command enables SQL Server to display information about the disk activity volume generated by the Transact-SQL statement.
We can analyze the index performance very effectively.
After this attribute is enabled, we will receive information similar to the following after executing the SQL statement, which is conducive to the analysis of SQL Performance:

(3999 row (s) affected)
Table 'chargecl '. Scan count 1, logical reads 9547, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Among them, the lob logic reading, lob physical reading, and lob pre-reading indicators are reading text, ntext, image, or 2D (varchar (max), nvarchar (max) and varbinary (max.
Logical reading, physical reading, and pre-reading are the reads of common data pages.
Use SQL Server Management Studio Standard Reports
In SQL Server Management Studio, when selecting a database Server, a specific database, or Security-Logins, or Management, the Notification Services or SQL Server Agent object, SQL Server provides some ready-made reports for us. The data of these reports is helpful for us to analyze the database status.
For example, basic SQL Server indexing knowledge (1) --- basic data format record
Http://blog.joycode.com/ghj/archive/2008/01/02/113290.aspx
.
For specific reports, refer to the following links:
SQL Server Management Studio Standard Reports-Overview
Http://blogs.msdn.com/buckwoody/archive/2007/10/09/sql-server-management-studio-standard-reports-overview.aspx
Some methods to release the Cache during testing
In particular, whether the data is cached during the query statement performance test is an important aspect of the test. The following commands help us clear the cache. Easy to test.
Commands related to clearing the cache:
Apart from dbcc unpintable in SQL 2000, it seems that this operation does not immediately release the table memory Buffer.
(Dbcc unpintable does not cause the table to be immediately flushed from the data cache. it specifies that all of the pages for the table in the buffer cache can be flushed if space is needed to read in a new page from disk .)
SQL 2005/2008 allows DBA to process the memory space occupied by SQL more freely, for example:
CHECKPOINT
Write all dirty pages of the current database to the disk. "Dirty page" is a data page that has been input to the cache and modified but not written to the disk. A checkpoint can be created to ensure that all dirty pages are written to the disk, saving time in the future recovery process.
DBCC DROPCLEANBUFFERS
Delete all cleared buffers from the buffer pool.
DBCC FREEPROCCACHE
Deletes all elements from the process cache.
DBCC FREESYSTEMCACHE
Release all unused cache entries from all caches. SQL Server 2005 database engine clears unused cache entries in the background in advance so that the memory can be used for the current entry. However, you can use this command to manually delete unused entries from all caches.
You can also use sp_cursor_list to view all cursors.
DBCC OPENTRANView the database opening transaction status, etc.