The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_estimate_data_compression_savings(nvarchar @schema_name, nvarchar @object_name
, int @index_id
, int @partition_number
, nvarchar @data_compression)
MetaData:
create procedure sys.sp_estimate_data_compression_savings @schema_name sysname, @object_name sysname, @index_id int, @partition_number int, @data_compression nvarchar(60) as begin set nocount on; if (3 <> SERVERPROPERTY ('EngineEdition')) begin declare @procName nvarchar(max) = N'sp_estimate_data_compression_savings'; declare @procNameLen int = len(@procName); declare @instanceName nvarchar(max) = CONVERT(nvarchar(max), SERVERPROPERTY('InstanceName')); declare @instanceNameLen int = len(@instanceName); raiserror(534, -1, -1, @procNameLen, @procName, @instanceNameLen, @instanceName); end -- Check @schema_name parameter declare @schema_id int if (@schema_name is null) set @schema_id = schema_id() else set @schema_id = schema_id(@schema_name) if (@schema_id is null) begin raiserror(15659, -1, -1, @schema_name); return @@error; end -- Set the schema name to the default schema if (@schema_name is null) set @schema_name = schema_name(@schema_id); -- check object name if (@object_name is null) begin raiserror(15223, -1, -1, 'object_name'); return @@error; end -- Check if the object name is a temporary table if (substring(@object_name, 1, 1) = '#') begin raiserror(15661, -1, -1); return @@error; end -- Verify that the object exists and that the user has permission to see it. declare @object_id int = object_id(quotename(@schema_name) + '.' + quotename(@object_name)); declare @object_len int; if (@object_id is null) begin set @object_len = datalength(@object_name); raiserror(1088, -1, -1, @object_len, @object_name); return @@error; end -- Check object type. Must be user table or view. if (not exists (select * from sys.objects where object_id = @object_id and (type = 'U' or type = 'V'))) begin raiserror(15001, -1, -1, @object_name); return @@error; end -- Check SELECT permission on table. The check above fails if the user has no permissions -- on the table, so this check applies only if the user has some permission other than -- SELECT (e.g., INSERT) but not SELECT itself. if has_perms_by_name(quotename(@schema_name) + '.' + quotename(@object_name), 'object', 'select') = 0 begin declare @db_name sysname = db_name(); declare @db_len int = datalength(@db_name), @schema_len int = datalength(@schema_name); set @object_len = datalength(@object_name); raiserror(229, -1, -1, N'SELECT', @object_len, @object_name, @db_len, @db_name, @schema_len, @schema_name); return @@error; end -- Check for sparse columns or column sets. declare @sparse_columns_and_column_sets int = (select count(*) from sys.columns where object_id = @object_id and (is_sparse = 1 or is_column_set = 1)); if (@sparse_columns_and_column_sets > 0) begin raiserror(15662, -1, -1); return @@error; end -- check data compression if (@data_compression is null) begin raiserror(15223, -1, -1, 'datacompression'); return @@error; end set @data_compression = upper(@data_compression); if (@data_compression not in ('NONE', 'ROW', 'PAGE')) begin raiserror(3217, -1, -1, 'datacompression'); return @@error; end if (@index_id is not null) begin declare @index_type int = null; select @index_type = type from sys.indexes with (nolock) where object_id = @object_id and index_id = @index_id; if (@index_type is null) begin raiserror(15323, -1, -1, @object_name); return @@error; end if (@index_type not in (0, 1, 2)) begin -- Currently do not support XML and spatial indexes raiserror(15660, -1, -1); end end declare @desired_compression int = case @data_compression when 'NONE' then 0 when 'ROW' then 1 else 2 end; -- Hard coded sample table and indexes that we will use declare @sample_table nvarchar(256) = '#sample_tableDBA05385A6FF40F888204D05C7D56D2B'; declare @dummy_column nvarchar(256) = 'dummyDBA05385A6FF40F888204D05C7D56D2B'; declare @sample_index nvarchar(256) = 'sample_indexDBA05385A6FF40F888204D05C7D56D2B'; declare @pages_to_sample int = 5000; -- Find all the partitions and their partitioning info that we need select i.index_id, p.partition_number, p.data_compression, p.data_compression_desc, ic.column_id as [partition_column_id], f.function_id as [partition_function_id], case when exists (select * from sys.computed_columns c with (nolock) join sys.index_columns ic with (nolock) on ic.object_id = c.object_id and ic.column_id = c.column_id and c.is_persisted = 0 where ic.index_id = i.index_id) then 1 else 0 end as requires_computed, create_index_ddl, compress_current_ddl, compress_desired_ddl, is_primary into #index_partition_info from sys.partitions p with (nolock) join sys.indexes i with (nolock) on p.object_id = i.object_id and p.index_id = i.index_id left join (select * from sys.index_columns with (nolock) where partition_ordinal = 1) ic on p.object_id = ic.object_id and i.index_id = ic.index_id left join sys.partition_schemes ps with (nolock) on ps.data_space_id = i.data_space_id left join sys.partition_functions f with (nolock) on f.function_id = ps.function_id cross apply sys.generate_index_ddl(@object_id, i.index_id, p.data_compression, @sample_table, @sample_index, @desired_compression) where p.object_id = @object_id and i.is_disabled = 0 and i.is_hypothetical = 0 -- Filter on index and/or partition if these were provided - always include the clustered index if there is one and i.type <= 2 -- ignore XML, Extended, columnstore indexes for now and (i.index_id = case when @index_id is null then i.index_id else @index_id end or i.index_id = 1) and p.partition_number = case when @partition_number is null then p.partition_number else @partition_number end order by i.index_id -- If the user requested to estimate compression of a view that isn't indexed, we will not have anything in #index_partition_info if (0 = (select count(*) from #index_partition_info)) begin raiserror(15001, -1, -1, @object_name); return @@error; end -- Find all the xml schema collections used by the table select 'use tempdb; create xml schema collection ' + quotename(N'schema_' + convert(nvarchar(10), xml_collection_id)) + ' as N''' + replace(convert(nvarchar(max), xml_schema_namespace(schema_name, name)), N'''', N'''''') + '''' as create_ddl, 'use tempdb; drop xml schema collection ' + quotename(N'schema_' + convert(nvarchar(10), xml_collection_id)) as drop_ddl into #xml_schema_ddl from ( select distinct c.xml_collection_id, xsc.name, s.name as schema_name from sys.columns c with (nolock) join sys.xml_schema_collections xsc with (nolock) on c.xml_collection_id = xsc.xml_collection_id join sys.schemas s with (nolock) on xsc.schema_id = s.schema_id where c.object_id = @object_id and c.xml_collection_id <> 0 ) t -- create required xml schema collections declare c cursor local fast_forward for select create_ddl from #xml_schema_ddl open c; declare @create_ddl nvarchar(max) fetch next from c into @create_ddl; while @@fetch_status = 0 begin exec(@create_ddl); fetch next from c into @create_ddl; end; close c; deallocate c; -- Create results table create table #estimated_results ([object_name] sysname, [schema_name] sysname, [index_id] int, [partition_number] int, [size_with_current_compression_setting(KB)] bigint, [size_with_requested_compression_setting(KB)] bigint, [sample_size_with_current_compression_setting(KB)] bigint, [sample_size_with_requested_compression_setting(KB)] bigint); -- Outer Loop - Iterate through each unique partition sample -- Iteration does not have to be in any particular order, the results table will sort that out declare c cursor local fast_forward for select partition_column_id, partition_function_id, partition_number, requires_computed, alter_ddl, insert_ddl, table_option_ddl from (select distinct partition_column_id, partition_function_id, partition_number, requires_computed from #index_partition_info ) t cross apply (select case when used_page_count <= @pages_to_sample then 100 else 100. * @pages_to_sample / used_page_count end as sample_percent from sys.dm_db_partition_stats ps where ps.object_id = @object_id and index_id < 2 and ps.partition_number = t.partition_number) ps cross apply sys.generate_table_sample_ddl( @object_id, @schema_name, @object_name, partition_number, partition_column_id, partition_function_id, @sample_table, @dummy_column, requires_computed, sample_percent) open c; declare @curr_partition_column_id int, @curr_partition_function_id int, @curr_partition_number int, @requires_computed bit, @alter_ddl nvarchar(max), @insert_ddl nvarchar(max), @table_option_ddl nvarchar(max); fetch next from c into @curr_partition_column_id, @curr_partition_function_id, @curr_partition_number, @requires_computed, @alter_ddl, @insert_ddl, @table_option_ddl; while @@fetch_status = 0 begin -- Step 1. Create the sample table in current scope create table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B]([dummyDBA05385A6FF40F888204D05C7D56D2B] [int]); -- Step 2. Sample the table exec (@alter_ddl); alter table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B] rebuild exec (@table_option_ddl); exec (@insert_ddl); -- Step 3. Loop through the indexes that use this sampled partition -- declare index_partition_cursor cursor local fast_forward for select ipi.index_id, ipi.data_compression, ipi.create_index_ddl, ipi.compress_current_ddl, ipi.compress_desired_ddl, ipi.is_primary from #index_partition_info ipi where (ipi.partition_column_id = @curr_partition_column_id or (ipi.partition_column_id is null and @curr_partition_column_id is null)) and (partition_function_id = @curr_partition_function_id or (partition_function_id is null and @curr_partition_function_id is null)) and (ipi.partition_number = @curr_partition_number or (ipi.partition_number is null and @curr_partition_number is null)) and ipi.requires_computed = @requires_computed open index_partition_cursor; declare @sample_table_object_id int = object_id('tempdb.dbo.#sample_tableDBA05385A6FF40F888204D05C7D56D2B'); declare @curr_index_id int, @cur_data_compression int, @create_index_ddl nvarchar(max), @compress_current_ddl nvarchar(max), @compress_desired_ddl nvarchar(max), @is_primary bit; fetch next from index_partition_cursor into @curr_index_id, @cur_data_compression, @create_index_ddl, @compress_current_ddl, @compress_desired_ddl, @is_primary; while @@fetch_status = 0 begin declare @current_size bigint, @sample_compressed_current bigint, @sample_compressed_desired bigint; -- Get Partition's current size set @current_size = (select used_page_count from sys.dm_db_partition_stats where object_id = @object_id and index_id = @curr_index_id and partition_number = @curr_partition_number); -- Create the index if @create_index_ddl is not null begin exec (@create_index_ddl); end; declare @sample_index_id int = case when @curr_index_id = 0 then 0 -- heap when @curr_index_id = 1 then 1 -- cluster else (select index_id from tempdb.sys.indexes with (nolock) where object_id = @sample_table_object_id and index_id <> 0 and index_id <> 1) -- In all other cases, there should only be one index end; -- Compress to current compression level if @compress_current_ddl is not null begin exec (@compress_current_ddl); end; -- Get sample's size at current compression level select @sample_compressed_current = used_page_count from tempdb.sys.dm_db_partition_stats where object_id = @sample_table_object_id and index_id = @sample_index_id; -- Compress to target level if (@index_id is null or @curr_index_id = @index_id) begin exec (@compress_desired_ddl); end -- Get sample's size at desired compression level select @sample_compressed_desired = used_page_count from tempdb.sys.dm_db_partition_stats where object_id = @sample_table_object_id and index_id = @sample_index_id; -- Drop non-clustered and non-primary key indexes (this is based on name - pk has special name since it can be non-clustered) -- #tables can get created from either a contained db or a podb. In contained db context, the index names are mangled by adding #&$, so we have an additional LIKE clause to find such index names if (exists(select * from tempdb.sys.indexes with (nolock) where name = 'sample_indexDBA05385A6FF40F888204D05C7D56D2B' OR name like 'sample_indexDBA05385A6FF40F888204D05C7D56D2B#&$%' and object_id = @sample_table_object_id)) begin drop index [sample_indexDBA05385A6FF40F888204D05C7D56D2B] on [#sample_tableDBA05385A6FF40F888204D05C7D56D2B]; end else begin -- For a non-clustered primary key, drop the constraint to drop the index if (@is_primary = 1 and @sample_index_id <> 1) begin alter table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B] drop constraint [sample_indexDBA05385A6FF40F888204D05C7D56D2B_pk]; end end -- if the current setting and requested setting are the same, show how much we would save if we discount fragmentation and new -- compression schemes (like unicode compression). In these cases, we use the sample size or the current size of the table as -- starting point, instead of the temp table that was created -- if (@cur_data_compression = @desired_compression) begin if (@current_size > @pages_to_sample) begin set @sample_compressed_current = @pages_to_sample end else begin set @sample_compressed_current = @current_size end end declare @estimated_compressed_size bigint = case @sample_compressed_current when 0 then 0 else @current_size * ((1. * cast (@sample_compressed_desired as float)) / @sample_compressed_current) end; if (@index_id is null or @curr_index_id = @index_id) begin insert into #estimated_results values (@object_name, @schema_name, @curr_index_id, @curr_partition_number, @current_size * 8, @estimated_compressed_size * 8, @sample_compressed_current * 8, @sample_compressed_desired * 8); end fetch next from index_partition_cursor into @curr_index_id, @cur_data_compression, @create_index_ddl, @compress_current_ddl, @compress_desired_ddl, @is_primary; end; close index_partition_cursor; deallocate index_partition_cursor; -- Step 4. Drop the sample table drop table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B]; fetch next from c into @curr_partition_column_id, @curr_partition_function_id, @curr_partition_number, @requires_computed, @alter_ddl, @insert_ddl, @table_option_ddl; end close c; deallocate c; -- drop xml schema collection declare c cursor local fast_forward for select drop_ddl from #xml_schema_ddl open c; declare @drop_ddl nvarchar(max) fetch next from c into @drop_ddl; while @@fetch_status = 0 begin exec(@drop_ddl); fetch next from c into @drop_ddl; end; close c; deallocate c; select * from #estimated_results; drop table #estimated_results; drop table #xml_schema_ddl; end
No comments:
Post a Comment