How to view disk I/O operation information The SET STATISTICS IO on command is a message that enables SQL Server to display information about the amount of disk activity generated by Transact-SQL statements.
We can be very useful when analyzing index performance.
With this property enabled, after executing the SQL statement, we receive information similar to the following, which helps us analyze SQL performance:
(3999 row (s) affected)
Table ' Chargecl '. Scan count 1, logical read 9,547 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
The three metrics for LOB logical reads, LOB physical reads, and LOB prefetching are indicators that read text, ntext, image, or large value type (varchar (max), nvarchar (max), varbinary (max).
and logical read, physical read, and prefetching are read to normal data pages.
Using SQL Server Management Studio Standard Reports
In SQL Server Management Studio, when you select a database server, or a specific database, or security-logins, or Management, Notification Services or SQL Ser Ver Agent object, you will see SQL Server for us to provide some of the out-of-the-box reports, the data of these reports, to help us analyze the state of the database.
For example, in the SQL Server Index Basics (1)---The basic format for recording data
Http://blog.joycode.com/ghj/archive/2008/01/02/113290.aspx
, we use the data table to occupy the space report
specific reports can 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
In the test, some ways to release the cache
Especially when query statement performance test, whether the data is cached, this is an important point in the test. Here are a few commands to help us clear the cache. Easy to test.
to clear caching-related commands: SQL 2000 In addition to DBCC UNPINTABLE seems to be gone and this operation will 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 the. needed to read in a new PA GE from disk.)
SQL 2005/2008 allows DBAs to be more free to handle the memory space occupied by SQL such as:
CHECKPOINT Writes all dirty pages of the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and have been modified but have not been written to disk. CHECKPOINT can create a checkpoint that guarantees that all dirty pages are written to disk, saving time later in the recovery process.
DBCC Dropcleanbuffers Removes all purge buffers from the buffer pool.
DBCC Freeproccache Deletes all elements from the procedure cache.
DBCC Freesystemcache Frees all unused cache entries from all caches. The SQL Server 2005 database engine cleans up unused cache entries in the background in advance so that memory can be used for the current entry. However, you can use this command to manually remove unused entries from all caches.
You can also view all cursors in sp_cursor_list
DBCC OpentranView database open transaction status, etc.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
