SQL2005 performance Analysis Some detail functions are you helpful? Three

Original: SQL2005 performance analysis Some detail functions are you helpful? Three

Following previous: SQL2005 performance analysis Some detail functions do you have any useful? Two

First: SET STATISTICS profile on

When we compare the best of the query plan, in fact we prefer to use set STATISTICS, rather than set SHOWPLAN_TEXT on. It can tell you more or less the query consumption of each choice, and you can run two or more queries at the same time to see which one performs best.

After running SET STATISTICS profile on, a lot of information appears, here Stmttext to illustrate the following:

Stmttext:
SELECT * FROM
(
SELECT *,
Row_number () over (order by card_no Desc) as RowNum
From Tblname
) as TBL

where RowNum between 1 and 20

|--filter (WHERE: ([expr1003]>= (1) and [expr1003]<= (20)))
|--top (Top EXPRESSION: (Case if () is NULL OR (a) < (0) then (0) ELSE ()))
|--sequence Project (DEFINE: ([Expr1003]=row_number))
|--compute Scalar (DEFINE: ([expr1007]= (1)))
|--segment
|--nested Loops (Inner Join, OUTER REFERENCES: ([bdg_retail].[ DBO]. [Card_ext]. [Id], [Expr1005]) with ORDERED PREFETCH)
|--index Scan (OBJECT: ([bdg_retail].[ DBO]. [Card_ext]. [Ix_card_ext_card_no]), ORDERED backward)
|--clustered Index Seek (OBJECT: ([bdg_retail].[ DBO]. [Card_ext]. [Pk_card_ext]), SEEK: ([Bdg_retail]. [dbo]. [Card_ext].
[Id]=[bdg_retail]. [dbo]. [Card_ext]. [Id]) LOOKUP ORDERED FORWARD)

In addition to displaying the current SQL statement, it also gives details of the actual running situation, how to find the index, how to scan the table, and how to sort and so on.

Nested Loops: Nested query;

Index Scan: Indexing search;

Clustered Index Seek: Clustered indexes Lookup

Second: sp_spaceused

function: get statistics of table size for our analysis:

Case:

sp_spaceused Employees
Results:
Name rows reserved data index_size unused
-------------- -------- --------- ------- -------------- ---------

Employees 2977 2008KB 1504KB 448KB 56KB

:

return content Description:

                Name      The name of the table for which to request space usage information.
                rows  The number of existing rows in the table.
                reserved  Total amount of space reserved by the table. The amount of space used by the data in the
                database table.
                index_size The amount of space used by the index in the table. The amount of unused space in the
                Unused table.

          remarks: sp_spaceused Calculates the amount of disk space used by the data and index and the amount of disk space used by the tables in the current database. If no objname,sp_spaceused is given, the space used by the entire current database is reported.

Permissions: Execute permissions are granted by default to the public role.

Third: Ranking function in SQL2005 row_number ()

           Paging algorithm There are many kinds of, here I would like to say I have been using the paging method, SQL2005 new features: Row_number ()
row_number (Transact-SQL)

          in the ranking function   Definition: returns the serial number of the expert in the result set partition, starting at 1 for the first row of each partition.

           Syntax: row_number ()       over ([<partition_by_clause>] <order_by_clause>)

            parameters: 1:<partition_by_clause>: The result set generated by the FROM clause is applied Row_ The partition of the number function.

                  2:<order_by_clause>: Determines the order in which the Row_number values are assigned to the rows in the partition. For more information, see ORDER BY clause (Transact-SQL). When <order_by_clause> is used in a ranking function, columns cannot be represented by integers.

           return type: bigint 

remark: An ORDER BY clause determines the order in which the rows are assigned unique row_number in a particular partition.

This section intends to: I have always had a misunderstanding, that is, as long as the paging when the need to take (query the first few pages on the first page to take a few pages of data), the efficiency will be particularly high , and then use the IO analysis, only to know that is not as perfect as I imagined ( Take any page speed is the same fast).

Case:

--Take the first page
(s) affected)

Table ' Card_ext '. Scan count 1, logical reads, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB Read-ahead reads 0.

--Take page 100th
(s) affected)

Table ' Card_ext '. Scan count 1, logical reads 8157, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0 , LOB Read-ahead reads 0.

--Take page 10,000th
(s) affected)

Table ' Card_ext '. Scan count 1, logical reads 81322, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB read-ahead reads 0.

Solution: Big Data paging, no matter what you use the paging algorithm, there will be performance bottlenecks, so you can use the Top N method to compromise: when the actual query results are particularly long, only select the first n.

This section concludes that the above results are the result of running with the data cache, so only logical reads are seen, and no physical reads are recorded. The record shows that the number of logical reads is constantly changing, multiplied by the size of the pages the user fetches, That is, it is proportional to the number of pages. The reason is that row_number () is generated when the data is fully queried and then produced from the start in sort order, so it is necessary to load all the data before the page into memory before it can be generated.
This is a very straightforward answer to the reason why the data is getting to the last slower speed.

By the way, when you use the sp_help command, you cannot run the execution plan at the same time. Otherwise it will be reported this error: MSG 262, Level, state 4, Procedure sp_help, line SHOWPLAN Permission denied In database ' master '.

Summary: Performance tuning is a particularly fine work, often changing a small statement performance will change dramatically, to experience in the ongoing practice.

Note:

This article refers to: MSDN

SQL2005 performance Analysis Some detail functions are you helpful? Three