Go Summary of SQL Server execution efficiency and performance test methods

This article transferred from: http://www.zhixing123.cn/net/27495.html

For programmers who do management systems and analysis systems, complex SQL statements are unavoidable, and in the face of huge amounts of data, sometimes an optimized statement can improve execution efficiency and overall operational performance.

How to select SQL statements, this article provides two methods , respectively, to quantify the analysis of multiple SQL.

In the process of testing SQL performance.

One is to view the system situation when executing SQL by setting statistics.

The options are Profile,io, time. SET STATISTICS profile on: Displays the time, in milliseconds, that is required to parse, compile, and execute the query.

SET STATISTICS IO on: Reports information about the number of scans per table referenced in the statement, the number of logical reads (pages accessed in the cache), and the number of physical reads (the number of times the disk was accessed).

Set STATISTICS time on: Displays the result set after each query execution, representing the configuration file that the query executes.

Examples are as follows:

SET STATISTICS profile on

SET STATISTICS IO on

SET STATISTICS time on

GO

– Your SQL script starts

SELECT [TestCase] from [Testcaseselect]

– Your SQL script is over

GO

SET STATISTICS Profile OFF

SET STATISTICS IO OFF

SET STATISTICS Time OFF

Alternatively, you can determine how efficient the SQL statement is by adding statements manually to calculate the execution time to see how much time is spent executing the statement:

DECLARE @d datetime set @d=getdate ()

– Your SQL script starts

SELECT [TestCase] from [Testcaseselect]

– Your SQL script is over

Select [Statement execution takes time (milliseconds)]=datediff (Ms,@d,getdate ())

The second is the " include actual execution plan" and "include client Statistics" under SQL 2008 's "Query" button.

Here is a comparison of the performance of the two examples I wrote with the manager:

It's mine:

SQL Server Execution Time:

CPU time = 32 milliseconds, elapsed time = 762 milliseconds.

CPU time = 16 milliseconds, elapsed time = 475 milliseconds.

CPU time = 32 milliseconds, elapsed time = 671 milliseconds.

CPU time = 31 milliseconds, elapsed time = 615 milliseconds.

Manager's:

SQL Server Execution Time:

CPU time = 16 milliseconds, elapsed time = 547 milliseconds.

CPU time = 47 milliseconds, elapsed time = 491 milliseconds.

CPU time = 32 milliseconds, elapsed time = 436 milliseconds.

CPU time = 47 milliseconds, elapsed time = 416 milliseconds.

PS: In doing a topic today, the manager gives a difficult problem, although made, but performance is not optimized.

Three layers of nesting are used.

Now in particular to summarize: large table with the small table when the association query, involves nested query, first query the small table, and then Union large table.

I started by getting the size table to be associated and then nesting. And the manager wrote the first to let the small table nested, finally in the association. This way, the efficiency of execution is naturally higher than mine.

Go Summary of SQL Server execution efficiency and performance test methods