How to view statistics for tables and indexes

These days require a server to do statistics, mainly for tables and indexes. Here I'll simply share a few ways to query the data tables and index statistics:

1. Use T-SQL statements to implement:

SelectSchema_name (t.schema_id) as'Architecture', T.name as'Table name', i.rows as'Number of rows',       8* Reserved AS'reserved Space (KB)',       8* Dpages AS'Used (KB)',       8* (Reserved-dpages) as'not Used (KB)' fromSys.tables asT, sysindexes asIwheret.object_id = i.id and I.indid <=1--Select different tables to query and t.name= separately'Cttextdata'

Execution results

The advantages of this approach are as follows:

• Fast running speed.
• Without access to the user table, performance is not affected and locks are not added.
• Flexible combinations, and can also be combined with other statements to query the gold results.

2. Using the system stored procedure sp_spaceused

' ctapplication '

The results of the implementation are as follows:

Note that when you do not join the table name parameter, the result is all the table synthesis information for that context (use xxx--library name) DB instance.

You can use sp_msforeachtable to traverse all tables in the current context

As follows:

It is important to note that when using sp_spaceused, if the schema is dbo, you can not enter the schema, otherwise you must enter your own schema name.

3. Using System reports

Using the process, right-click the DB instance, and then tap reports \ Standard reports \ Disk usage by Top Tables:

Final display:

The figure shows some of the information needed, each of which can be ordered in sequence and flashback, in kilobytes.

At the same time right button in this window, there are changes to page layout, export (excel\word\pdf), printing and other functions.

This approach has the following advantages:

• The visual interface is easy to operate.
• No code writing is required.
• Printing can be exported directly.

Summarize:

This paper briefly introduces several statistics of disk and index of querying database table. In peacetime work will often use this function, because to look at the pressure of the query table, index ratio, has been the use of various tables of space, for the design of the table, the use of indexes, etc. have a great help. We hope to help you.

How to view statistics for tables and indexes