Common metrics and cache clearing methods in SQL server Testing

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.

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 OPENTRAN

For example, the deep SQL script for viewing indexes is as follows:

Select INDEXPROPERTY (OBJECT_ID ('chargeheap '), 'chargeheap _ ncind', 'indexdepth ')
Here, 'chargeheap 'is the name of the table where the index is to be viewed, 'chargeheap _ ncind' is the name of the index to be viewed, and 'indexdepth' is the index attribute to be viewed.

For more attributes, see parameter descriptions on the following page:
Http://technet.microsoft.com/zh-cn/library/ms187729.aspx

Alternatively, select the index you want to view in SQL Server Management Studio, and then right-click the index to view its attributes. The Fragmentation tab contains many
Content of interest to this index.