| Optimization of tempdb |
By default, the tempdb database is placed on the master device with a capacity of 2 MB, temporary databases are the most common databases used for sorting, creating temporary tables, and re-formatting. Therefore, tempdb optimization should be paid special attention. Step 1: bind the temporary database to the cache.
Due to the creation and use of temporary tables, the temporary database frequently uses the data cache. Therefore, a high-speed cache should be created for the temporary database, so that it can be resident in the memory and help to distribute I/O:
1. Create a named Cache
Sp_cacheconfig "tempdb_cache", "10 m", "mixed"
2. Restart the server.
3. Bind a temporary database to the tempdb_cache Cache
Sp_bindcache "tempdb_cache", tempdb
4. If there is a large I/O, configure the memory pool. Step 2: optimize temporary tables
Most temporary tables are simple to use and seldom need to be optimized. However, complex access to temporary tables is required,
Table creation and indexing should be separated by multiple processes or batch processing. The following two technologies can improve the optimization of temporary tables:
1. Create an index on a temporary table
1) The temporary table must exist.
2) The statistics page must exist (that is, an index cannot be created on an empty table)
2. The complex use of temporary tables is distributed to multiple batches or processes to provide information for the optimizer.
The following process needs to be optimized:
Create proc base_proc
As
Select * into # huge_result from auths
Select * from article, # huge_result where article. author_code =
# Huge_result.author_code and sex = "0" Use two processes to achieve better performance
1)
Create proc base_proc
As
Select *
Into # huge_result
From auths
Exec select_proc 2)
Create proc select_proc
As
Select * from article, # huge_result
Where article. author_code = # huge_result.author_code and sex = "0" Note: When a table is created and used in the same stored procedure or batch processing, the query optimizer cannot determine the table size. |
|
Ensure sufficient TempDB Space
Directory
If the SQL server needs to processRecordsetObject error, you may need to increase the size of TempDB. (Some queries require temporary processing space. For example, a query with an order by clause requiresRecordsetAnd this requires some temporary space .)
Key PointsRead this step before performing the operation, because once the device object is expanded, it is difficult to fold.
Increase TempDB space on the SQL Server
- Start Microsoft SQL Server Enterprise Manager, open the Server tree, and then open the database Device Tree.
- Select the (physical) device to be expanded, such as the Master, and double-click the device to open the "edit database device" dialog box.
This dialog box displays the space used by the current database.
- In the "size" scroll box, add the device to the required volume (for example, 50 MB hard disk space ).
- Click "change now" to increase (logical) the size of space that TempDB can expand.
- Open the "Database" tree on the server, and double-click "TempDB" to open the "edit database" dialog box. The databases tab lists the space ("data size") currently allocated to TempDB "). The default value is 2 MB.
- Under the size group, click scale ". The image shows available and allocated space on each physical device. Maroon entries represent available space.
- Select "Log Device", such as Master, and display available space in the "size (MB)" box.
- Click expand now to allocate the space to the TempDB database.
The "edit database" dialog box displays the size of the newly allocated tempdb.
SeeFor more information about this topic, see "database extension dialog box" in the Mcirosoft SQL Server Enterprise Manager Help file ".
