SQL Server database optimization practice (III), SQL Server

SQL Server database optimization practice (III), SQL Server

Preface:

This chapter describes how to compress logs and tables.

Shrink log files

-- Exploitation

Exec sp_spaceused

Statement to view the database size

-- Right-click Database Properties

-- View options

-- Set the recovery mode to simple

-- Right-click Database-task-shrink-File

-- Select log for file type

-- View the database size after shrinking

-- Right-click Database properties-Option

-- Set the recovery mode to complete

-- Note: a complete database backup is required.

Table Compression

-- SQL Server 2005 and later support Table Partitioning

For more information about Table Partitioning, see the following website:

Http://blog.csdn.net/yole_grise/article/details/18658949

-- SQL Server 2008 and later versions support table Compression

(Table compression cannot be performed in the Standard Edition)

-- For our mainstream customers, with the accumulation of time and the expansion of business scale, the database at the headquarters is getting bigger and bigger, which has a lot of negative effects.

For example, a chain company with 100 stores may have databases of 200 GB to GB in three years.

The main impact of database enlargement is as follows:

1. As a result, the cost of disk storage is increased. If a database of GB needs to be backed up, at least 1 TB of disk is enough.

2. database performance is degraded.

3. potential risks of database faults increase. (Although there is no direct evidence, this conclusion can be drawn through simple reasoning)

4. It takes too long to back up a database. Copying and restoring backup files is troublesome.

For example, we now have many customer databases of over GB. In this context, "Table compression" is coming soon.

Operation Method:

-- Right-click table-storage-manage Compression

-- Size of the table before compression:

-- Check the table size:

-- The table size is compressed from 81344KB to 28kb.

The size of the compressed table is usually about 1/4 of the original table size.

-- The same is true for index compression.

-- Right-click index-storage-manage Compression

Currently, the bottleneck for database applications is mainly on disk I/O. That is, the efficiency of reading and writing disks.

Table compression can reduce the burden on the disk. Therefore, table compression is very effective in practical applications.

When the database exceeds GB, the database can be reduced to about 30 GB by compressing the largest tables (and indexes,

It is faster and easier to manage.

However, compression also has a negative effect.

Compression will increase the CPU overhead, because the compression algorithm and decompression algorithm must be continuously carried out.

Question:

What types of tables need to be compressed?

-- View the table size IF OBJECT_ID ('tempdb .. # TB_TEMP_SPACE ') is not null drop table # TB_TEMP_SPACEGOCREATE TABLE # TB_TEMP_SPACE (name varchar (500), rows int, reserved varchar (50), data varchar (50 ), INDEX_SIZE VARCHAR (50), unused varchar (50) GOSP_MSFOREACHTABLE 'insert INTO # TB_TEMP_SPACE exec sp_spaceused ''? '''Goselect *, 'alter TABLE [dbo]. ['+ NAME +'] rebuild partition = ALLWITH (DATA_COMPRESSION = PAGE) 'as sqlFROM # TB_TEMP_SPACEORDER by replace (DATA, 'kb', '') + 0 DESC

Answer:

1. the query frequency is small.

2. Space-consuming

Such a table needs to be compressed.

Typical table: u_sale_c

Generally, tables larger than 1g or indexes larger than 1g must be compressed.

Note: clustered indexes do not need to be compressed. Because clustered index itself does not occupy space.

Difference between compression and contraction:

Compression refers to reducing the size of database data through algorithms and rules.

Contraction refers to releasing the available space of the database into the available space of the operating system.

Example:

When a database has 100 GB, we compress the table and compress it with only 40 Gb. However, the mdf file is still 100 GB.

Execute sp_spaceused and you will find that the unallocated space contains 60 GB, which is the unallocated space.

Therefore, we must shrink the database to release the 60 GB space.

SO, table compression operations and database shrinking are closely linked; only in this way can we achieve the expected results.

--------------------------- Gorgeous split line -----------------------------------

This chapter ends