SQL Server focuses on optimization of Stored Procedure performance, data compression, and page compression to improve IO performance (I ).

SQL Server focuses on optimization of Stored Procedure performance, data compression, and page compression to improve IO performance (I ).

Preface

The SQL Server basic series is not over yet, And the last point is not written, and will continue later. Some school friends asked me when I began to write the SQL Server performance series. It may take some time to wait. I have been busy recently, but I will update the SQL Server performance series in succession, this article serves as the basic guide for the performance series, so that you can try it. When it comes to SQL Server performance optimization, I have seen some articles listing the performance optimization regulations of SQL Server. I have not figured out why I did this. Of course, I may have understood it myself, it is just a memorandum, but here I am, I will follow not only the memorandum, but also make it easy for all park friends to understand, so that they will not only know the result during the interview, I do not know the cause.

Storage Process Performance Optimization

Disable affected functions by setting SET NOCOUNT ON

For example, when we perform a query, the affected number of rows will always be returned. This message is only helpful for us to debug SQL statements, and there is no other help. We can disable this feature by setting SET NOCOUNT ON, this will significantly improve the performance and reduce the transmission of network traffic. In the stored procedure, we set it as follows.

CREATE PROC dbo.ProcNameASSET NOCOUNT ON;--Your Procedure codeSELECT [address], city, companyname FROM Sales.Customers-- Reset SET NOCOUNT to OFFSET NOCOUNT OFF;GO

Use schema name + Object Name

We have already made it clear at the beginning that the object name by setting the architecture name is the most qualified, in this case, the compilation plan will be executed directly, instead of searching for objects in other possible architectures when using the cache plan. Therefore, we recommend that you use it as follows.

SELECT * FROM Sales. MERs -- Recommendation -- instead of SELECT * FROM Customers -- avoid -- call the Stored Procedure EXEC dbo. MyProc -- Recommendation -- instead of EXEC MyProc -- avoid

The stored procedure name cannot start with sp

If the name of a stored procedure starts with sp, the database query engine first searches for the stored procedure in the master database and then searches for the stored procedure in the database of the current session.

Use if exists (SELECT 1) instead of (SELECT *)

You can view the following SQL statement when you search for it on the Internet.

Declare @ message varchar (200), @ name varchar (200) if exists (select * from students where student ID = '000000') beginset @ message = 'the following persons meet the conditions: 'print @ messageset @ name = (select name from students where student ID = '000000') print @ nameendelse begin set @ message = 'No one meets the condition' print @ message endgo

If exisis is used to determine whether a record EXISTS in the table, TRUE is returned IF there is any value in the if exists internal statement. For example

If exists (select * from students where student ID = '201312 ')

In this case, the row with the student ID = '123' is returned. If the row is replaced by 1, this row of records meeting the condition is not returned. During the query, we should minimize the processing data for network transmission, therefore, we should return the single value 1 as follows.

IF EXISTS (SELECT 1 FROM Sales.CustomersWHERE [address] = 'Obere Str. 0123')

Use sp_executesql instead of EXECUTE

Sp_executesql supports using parameters instead of using EXECUTE to improve code reuse. The query execution plan of dynamic statements can only be reused for statements with the same case, space, parameters, and comments for each character. EXECUTE the following dynamic SQL statement.

DECLARE @Query VARCHAR(100)DECLARE @contactname VARCHAR(50)SET @contactname = 'Allen, Michael'SET @Query = 'SELECT * FROM Sales.Customers WHERE contactname = ' + CONVERT(VARCHAR(3),@contactname)EXEC (@Query)

The execution of the query plan is as follows. If different @ contactname values are used again, the query execution plan will be created again and the @ contactname will not be reused.

If we use sp_executesql as the following query, if different @ contactname values are used, the query execution plan will be reused to improve the performance.

DECLARE @Query VARCHAR(100)SET @Query = 'SELECT * FROM Sales.Customers WHERE contactname = @contactname'EXECUTE sp_executesql @Query,N'@contactname VARCHAR(50)',@contactname = 'Allen, Michael'

TRY-CATCH for Exception Handling

After SQL Server 2005, exception handling is supported. If exception statements are checked and processed, exceptions will not cause more code to consume more resources and time.

Make transactions as short as possible

The length of the transaction will affect the blocking and deadlock. The exclusive lock will not be released until the transaction ends, and the lifecycle of the shared lock at the high isolation level is longer. Therefore, a lengthy transaction means a longer lock time, and the longer the lock time, the more blocking will eventually occur, in some cases, blocking may turn into deadlocks, so we should keep the transaction length as short as possible to achieve faster execution and reduce blocking.

Data Compression and page compression improve I/O

The main performance of SQL Server depends on the disk I/O efficiency. Improving I/O means improving performance. SQL Server 2008 provides data and backup compression functions. Let's take a look.

Data Compression

Data Compression reduces disk space reserved. Data Compression can be configured for clustered indexes, non-clustered indexes, index views, or partitioned tables or partition indexes. Data Compression can be achieved in two levels: one is row compression, the other is page compression, and even page compression will automatically implement row compression, when using the create table and create index statements, tables and indexes are compressed. To change the compression status of a TABLE, INDEX, and partition .. rebuild with or alter index .. rebuild with statement implementation. When the compression status of a stack changes, non-clustered indexes are rebuilt. In row compression, the following four methods are used to eliminate unused space.

1. Reduce the metadata overhead in the record.

2. all NUMERIC types (INT, NUMERIC, etc.) and NUMERIC-based (such as DATETIME and MONEY) are converted to variable length values, for example, all unused space of the INT type will be recycled after compression. For example, we know that 0-100 can store one byte. If our value is, INT Is 4 bytes in the disk, but after compression, the remaining 3 bytes will be recycled.

3. CHAR and NCHAR are converted to variable-length storage,After compression, there will be no space for the actual stored data. For example, we define CHAR (10). At this time, the data we store is Jeffcky, and 10 bytes will be reserved by default, at this time, three bytes will be added as spaces, but after compression, the three bytes will be recycled and only seven bytes will be reserved.

4. All NULL and 0 have been optimized without bytes.

Page Compression

Page compression is implemented in the following three methods.

1. All mentioned above.

2. prefix compression: for each column on each page, the public values of all identified rows, and each row stored under the title, after compression, the public value is replaced with the reference of the title line.

3. dictionary Compression: In dictionary compression, each column on each page identifies the public value, which is stored in the second row of the title row, then these public values are replaced with the reference of the values in the new line.

After talking about this, how is it actually used? To continue, We can insert 748 data records using a temporary database, as shown below:

USE tempdbGOCREATE TABLE TestCompression (col1 INT, col2 CHAR (50) goinsert into TestCompression VALUES (10, 'Compression test') GO 748

Next, we will perform row compression and page compression to compare with the original ones that are not compressed.

-- Original Value EXEC sp_spaceused TestCompressionGO -- DATA_COMPRESSION = set ROW compression alter table TestCompressionREBUILD WITH (DATA_COMPRESSION = ROW ); GOEXEC sp_spaceused TestCompressionGO -- compression = set PAGE compression alter table compression WITH (DATA_COMPRESSION = PAGE); GOEXEC sp_spaceused TestCompressionGO -- compression = no compression of alter table compression WITH (compression = NONE); GOEXEC sp_spaceused testcompresgo

The result is as follows:

After compression, the data is obviously reduced. If the data size is large enough, the page compression ratio will be less, which reduces IO and improves performance, I don't know if you have applied this article in the production service. You can try it next time.

The above is a small series of SQL Server focuses on the performance optimization of the stored procedure, data compression and page compression to improve IO performance (I), I hope to help you, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!