Memory usage during SQL statement execution by SQLServer

As we all know, the performance criterion for SQL statement execution by SQLServer is the large number of I/O reads.

As we all know, the performance criteria for SQL Server to execute SQL statements are mainly IO reads.

As we all know, SQL ServerRunSQLStatementThe performance criterion is the IO read size. This article analyzes some SQL statements without violating this principle.StatementRunSQL ServerMemory.

First, briefly describe SQL ServerMemoryOccupiedOfFeatures. SQL ServerOccupiedOfMemoryIn addition to programs (SQL Server engine), it mainly includes cached data (Buffer) andRunPlan (Cache ). SQL Server stores data on 8 KB pages. This is the same size as the storage page of SQL Server data on the disk. When SQL ServerRunSQLStatementIf the required data already exists inMemoryFromMemoryRead the buffer, perform necessary operations, and then outputRunResult. If the data is notMemoryFirst, read the data from the disk.MemoryBuffer. In general, the IO logic reads in SQL Performance Indicators correspondMemoryThe number of pages read by the buffer, while the number of physical reads by I/O corresponds to the number of pages read from the disk.

Note: The following tests can also be performed on development and test servers shared by multiple users, because you can see a tableOccupiedOfMemoryStatus. However, for convenience, the author conducts this experiment on a separate database that confirms that there are no other concurrent tasks.MemoryEach change is exactlyRunSQLStatement.

Let's first look at a simple example. Create the following table:

The following is a reference clip: Create Table P_User (UserMobileStatus int not null, Inclueno int not null, LastOpTime DateTime Not NULL )

Insert certain data to the table:

The following is a reference clip: Declare @ I int Set @ I = 28000 WHILE @ I <29000 BEGIN Insert Into P_User Select @ I % 2, @ I, GetUTCDate () Set @ I = @ I + 1 END

Then, in the query analyzer, we firstRun:

The following is a reference clip: Set Statistics IO ON

Press Ctrl + M to display the actualRunPlan.

Now, we can start our experiment. To accurately observe each SQL statementStatementInRunThe first SQLStatementPreviously, we first cleared the SQL ServerOccupiedDataMemory:

The following is a reference clip: CHECKPOINT GO DBCC DROPCLEANBUFFERS

This will clear the SQL ServerOccupiedData Buffer (thisStatementUse it with caution on the production server, because it will lead to subsequent SQLStatementRunSlow ).