tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 147

Dự toán nén dữ liệu Bởi vì mỗi đối tượng có thể mang lại một tỷ lệ nén khác nhau, đó là hữu ích để có một số ý tưởng của bao nhiêu nén là có thể trước khi thực sự thực hiện nén. Hướng tới mục tiêu này, SQL Server 2008 bao gồm khả năng ước tính trước giảm dữ liệu tiềm năng nén dữ liệu sử dụng hệ thống sp_estimate_data_compression_savings thủ tục lưu trữ. | Part IX Performance Tuning and Optimization Abbreviated result when executed in the AdventureWorks database object_id schema Object ix_ _id ixName ixType P_No Comp 1509580416 Person Person 1 PK_ _Person_Busines. CLUSTERED 1 NONE 1509580416 Person Person 2 IX_ _Person_LastName. . NONCLUSTERED 1 NONE 1509580416 Person Person 3 AK_ _Person_rowguid NONCLUSTERED 1 NONE Estimating data compression Because every object can yield a different compression ratio it s useful to have some idea of how much compression is possible before actually performing the compression. Toward this end SQL Server 2008 includes the ability to pre-estimate the potential data reduction of data compression using the sp_estimate_data_compression_savings system stored procedure. Specifically this system stored procedure will copy 5 of the data to be compressed into tempdb and compress it. The 5 is not a random sample but every twentieth page so it should give consistent results EXEC sp_estimate_data_compression_savings @schema_name Production @object_name BillOfMaterials @index_id NULL @partition_number NULL @data_compression page The result displays the following columns for each object base table and index object_name schema_name index_id partition_number size_with_current_compression_setting KB size_with_requested_compression_setting KB sample_size_with_current_compression_setting KB sample_size_with_requested_compressi on_setting KB The Data Compression Wizard shown in Figure 67-4 uses this same system stored procedure to estimate the compression. Select the type of compression to estimate and press the Calculate button. Enabling data compression Data compression alters the structure of the data on the disk so it makes sense that data compression is enabled using a CREATE or ALTER statement. Using the UI the only way to adjust an object s data compression is by using the same Data Compression Wizard used previously to estimate the compression gain. 1422 Data Compression 67