Basic SQL Server Index knowledge (3) -- test common metrics and cache clearing methods

There are two examples in the previous two blogs to demonstrate the content to be told. Some methods for viewing the database status are mentioned, which is not very comprehensive. This blog lists several of the statuses and data that we will use in subsequent blogs. And how to clear the cache in the test.
The link addresses of the previous two blogs are as follows:

Basic SQL Server Index knowledge (1) --- basic format of Record Data
Http://blog.joycode.com/ghj/archive/2008/01/02/113290.aspx

Basic SQL Server Index knowledge (2) ---- clustered index, non-clustered Index
Http://blog.joycode.com/ghj/archive/2008/01/02/113291.aspx

How to obtain index information

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, such:

Here we can see the depth of the index, the number of subnodes, the number of data pages, and so on. This information is very helpful for our analysis and query statement performance.

 

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 opentran

References:
Reply to rouqu (Shi Lin # Huang guoshu) in the post of csdn
Http://topic.csdn.net/u/20070404/14/6ee765a4-fc32-4d2c-a62c-6d51291e6007.html