January 22, 2008 at 8:51 pm
Hi all,
I am a grad-student and had a question about schema-bound Views ... I am currently working with a legacy system that was designed and admin-ed in Access ... it is currently ported to 2005, but is facing a lot of latency, because of poorly designed and normalized table structure with 30 column tables and no PKs ... its too heavy and costly to change the structure ... The OLAP portion is especially hard hit when yearly reports are run ...
Since there a lot of rows and columns in these tables, and each report needs a different indexing scheme to perform better, would it be a good idea to create multiple Schema-Bound Views of the tables and index each View differently?
January 23, 2008 at 11:40 am
How big is the database? If it is not that big, you can have data exported to the secondary database for reporting and index that second database.
Regards,Yelena Varsha
January 23, 2008 at 9:07 pm
Yelena ... thank you for the response ... the database can actually get quite heavy ... 36+ gigs ... and what more, they are not running on hot boxes ... I was wondering how much of performance hit you would take by having multiple indexes for the same table ... would this be a better idea than inserting into a another more efficiently indexed temp table? ... of course space can expensive ... and to your point, maybe the answer would be a replicated reports database ...
January 23, 2008 at 9:46 pm
Seems there are a couple things to work on here. For multiple indexes, you may use "Index Tuning Wizard" to find out whether all the indexes are needed or not.
http://www.sql-server-performance.com/articles/per/index_elimination_english_p1.aspx.
January 24, 2008 at 2:27 pm
Having been in the same boat many times before I know how to improve poorly written and designed application databases with a min. of effort.
So the first thing you should do is to force auto-parameterization for the database this will improve query performance on the database side becasue I assume that with the poor design there are also no stored procedures in use (read more about that here http://www.sql-server-performance.com/articles/per/forced_parameterization_p1.aspx)
Right click on database> properties> on the left hand side choose Options > scroll down to the Miscellaneous section and change parameterization to Forced.
Second thing to do is figure out which indexes the database really needs. Personally the index tuning wizard is not the best thing to use in this situation what you want to do is pull the data from the missing index dmv and the script below will do this for you by creating a database called AUTOINDEXRECS that polls the missing index dmv and sucks in the info which leaves you to come back at a later time and look at the table to determine what indexes you need to create and on what tables. You need to have sa permission to do this. This comes from the query team at microsoft and you can find the .zip download here http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx.
When you query the recommendation table the results will look like the following:
CREATE INDEX _MS_Sys_1 ON [Database_name].[dbo].[tbl_name]([ResponseID]) INCLUDE ([ResponseText])
This is without a doubt the best tuning tool for a database server and I am baffled why this is not more widely known.
--Dave
/****************************************************************************
//Copyright (c) 2005 Microsoft Corporation.
//
// @File: AutoIndex.sql
//
// @test-2:
//
// Purpose:
//Auto create or drop indexes
//
// Notes:
//
//
// @EndHeader@
*****************************************************************************/
CREATE DATABASE AUTOINDEXRECS
go
USE AUTOINDEXRECS
go
-- Table to store recommendations
IF object_id(N'dbo.recommendations', N'U') IS NOT NULL
DROP table [dbo].[recommendations]
GO
create table [dbo].[recommendations]
(
id int IDENTITY primary key,
recommendation nvarchar(400),
type char(2),
initial_time datetime,
latest_time datetime,
[count]int,
status nvarchar(20)
)
GO
-- Table to store recommendation history
IF object_id(N'dbo.recommendations_history', N'U') IS NOT NULL
DROP table [dbo].[recommendations_history]
GO
create table [dbo].[recommendations_history]
(
id int,
operation nvarchar(20),
time datetime,
db_user_name sysname,
login_name sysname
)
GO
-- Table to store index recommendations details
IF object_id(N'dbo.recommendations_details_index', N'U') IS NOT NULL
DROP table [dbo].[recommendations_details_index]
GO
create table [dbo].[recommendations_details_index]
(
id int,
database_id int,
table_id int,
table_modify_time datetime
)
GO
------------------------- add_recommendation_history ----------------------------------------------------
------ SP for adding a recommendation into the recommendations_history table.
IF OBJECT_ID (N'dbo.add_recommendation_history', N'P') IS NOT NULL
DROP PROC [dbo].[add_recommendation_history];
GO
create procedure [dbo].[add_recommendation_history]
@id int,
@operation nvarchar(20),
@time datetime
AS
BEGIN
declare @db_user_name sysname
select @db_user_name = CURRENT_USER
declare @login_name sysname
select @login_name = SUSER_SNAME()
insert into recommendations_history values (@id, @operation, @time, @db_user_name, @login_name)
END
go
------------------------- add_recommendation----------------------------------------------------
------ SP for inserting a new recommendation into the dbo.RECOMMENDATIONS table.
------ If the same entry already exists, it just changes latest_create_date to the latest time
------ and increase the count by one
IF OBJECT_ID (N'dbo.add_recommendation', N'P') IS NOT NULL
DROP PROC [dbo].[add_recommendation];
GO
create procedure [dbo].[add_recommendation]
@recommendation nvarchar(max),
@type_desc char(2),
@id int OUTPUT
AS
BEGIN
declare @create_date datetime
set @create_date = getdate()
IF ( @recommendation not in
(select recommendation from dbo.recommendations))
BEGIN
insert into dbo.recommendations values
(@recommendation, @type_desc, @create_date, @create_date, 1, N'Active')
select @id = @@identity
-- add it into the recommendation history
exec [dbo].[add_recommendation_history] @id, N'ADD', @create_date
return 0
END
ELSE
BEGIN
select @id = id
from dbo.recommendations
where @recommendation = recommendation
update dbo.recommendations
set latest_time = @create_date,
[count] = [count] +1
where id = @id
-- add it into the recommendation history
exec [dbo].[add_recommendation_history] @id, N'UPDATE', @create_date
return 10
END
END
go
------------------------- disable_recommendation----------------------------------------------------
------ SP for disabling a recommendation in the RECOMMENDATIONS table.
IF OBJECT_ID (N'dbo.disable_recommendation', N'P') IS NOT NULL
DROP PROC [dbo].[disable_recommendation];
GO
create procedure [dbo].[disable_recommendation]
@id int
AS
BEGIN
BEGIN TRANSACTION xDisableRecommendation
declare @create_date datetime
set @create_date = getdate()
update recommendations
set status = N'Inactive'
where id = @id
-- add it into the recommendation history
exec [dbo].[add_recommendation_history] @id, N'DISABLE', @create_date
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION xDisableRecommendation
RETURN @Error
END
COMMIT TRANSACTION xDisableRecommendation
END
go
------------------------- enable_recommendation----------------------------------------------------
------ SP for enabling a recommendation in the RECOMMENDATIONS table.
IF OBJECT_ID (N'dbo.enable_recommendation', N'P') IS NOT NULL
DROP PROC [dbo].[enable_recommendation];
GO
create procedure [dbo].[enable_recommendation]
@id int
AS
BEGIN
BEGIN TRANSACTION xEnableRecommendation
declare @create_date datetime
set @create_date = getdate()
update recommendations
set status = N'Active'
where id = @id
-- add it into the recommendation history
exec [dbo].[add_recommendation_history] @id, N'ENABLE', @create_date
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION xEnableRecommendation
RETURN @Error
END
COMMIT TRANSACTION xEnableRecommendation
END
go
------------------------- execute_recommendation----------------------------------------------------
------ SP for executing a recommendation in the RECOMMENDATIONS table.
IF OBJECT_ID (N'dbo.execute_recommendation', N'P') IS NOT NULL
DROP PROC [dbo].[execute_recommendation];
GO
create procedure [dbo].[execute_recommendation]
@id int
AS
BEGIN
declare @recommendation nvarchar(max)
declare @status nvarchar(20)
-- exec the recommendation
select @recommendation = recommendation, @status = status
from [recommendations]
where id = @id
-- check recommendation status
if (@status = 'Inactive')
begin
print N'Error: Recommendation ' + cast ( @id as nvarchar(10)) + ' is Inactive. Change the status to Active before execution'
return 1
end
-- check whether the schema has changed for the table
declare @database_id int
declare @object_id int
declare @stored_modify_date datetime
select @database_id = database_id, @object_id = table_id, @stored_modify_date = table_modify_time
from [dbo].[recommendations_details_index]
where id = @id
declare @database_name sysname
select @database_name = db_name(@database_id)
-- create temporary table to store the current table schema version
create table [#tabSchema] ( modify_date datetime)
truncate table [#tabSchema]
declare @exec_stmt nvarchar(4000)
select @exec_stmt =
'use '+ @database_name +
'; insert [#tabSchema] select modify_date from sys.objects where object_id = ' + cast ( @object_id as nvarchar(10))
--print @exec_stmt
EXEC (@exec_stmt)
declare @modify_date datetime
select @modify_date = modify_date from #tabSchema
if (object_id('[#tabSchema]') is not null)
begin
drop table [#tabSchema]
end
if (@modify_date > @stored_modify_date)
begin
print N'Error: Recommendation ' + cast ( @id as nvarchar(10)) + ' might be invalid since the schema on the table has changed since the recommendation was made'
return 1
end
declare @create_date datetime
set @create_date = getdate()
BEGIN TRANSACTION xExecuteRecommendation
exec (@recommendation)
-- add it into the recommendation history
exec [dbo].[add_recommendation_history] @id, N'EXECUTE', @create_date
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION xExecuteRecommendation
RETURN @Error
END
COMMIT TRANSACTION xExecuteRecommendation
END
go
------------------------- add_recommendation_details_index ----------------------------------------------------
------ SP for adding index recommendation details into the recommendations_details_index table.
IF OBJECT_ID (N'dbo.add_recommendation_details_index', N'P') IS NOT NULL
DROP PROC [dbo].[add_recommendation_details_index];
GO
create procedure [dbo].[add_recommendation_details_index]
@id int,
@database_id int,
@table_id int
AS
BEGIN
declare @database_name sysname
select @database_name = db_name(@database_id)
-- create temporary table to store the current table schema version
create table [#tabSchemaVer] ( modify_date datetime)
truncate table [#tabSchemaVer]
declare @exec_stmt nvarchar(4000)
select @exec_stmt =
'use '+ @database_name +
'; insert [#tabSchemaVer] select modify_date from sys.objects where object_id = ' + cast ( @table_id as nvarchar(10))
--print @exec_stmt
EXEC (@exec_stmt)
declare @tabVer datetime
select @tabVer = modify_date from #tabSchemaVer
insert into recommendations_details_index values (@id,@database_id, @table_id, @tabVer)
if (object_id('[#tabSchemaVer]') is not null)
begin
drop table [#tabSchemaVer]
end
END
go
---------------------------- auto_create_index ------------------------------
IF OBJECT_ID (N'dbo.auto_create_index', N'P') IS NOT NULL
DROP PROC [dbo].[auto_create_index];
GO
create procedure [dbo].[auto_create_index]
as
--NOTE: This sp will create indexes recommended by the Missing Index DMVs.
--
set nocount on
-- required for creating index on ICC/IVs
set ansi_warnings on
set ansi_padding on
set arithabort on
set concat_null_yields_null on
set numeric_roundabort off
declare @exec_stmt nvarchar(4000)
declare @table_name nvarchar(521)
declare @column_name sysname
declare @column_usage varchar(20)
declare @column_id smallint
declare @index_handle int
declare @database_id int
declare @object_id int
-- find the top 5 indexes with maximum total improvent
declare ms_cri_tnames cursor local static for
Select Top 5 mid.database_id, mid.object_id, mid.statement as table_name, mig.index_handle as index_handle
from
(
select
(user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs
) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
where
migs_adv.group_handle = mig.index_group_handle and
mig.index_handle = mid.index_handle
and migs_adv.index_advantage > 10
order by migs_adv.index_advantage DESC
-- create temporary table to store the table names on which we just auto created indexes
create table #tablenametab
(table_name nvarchar(521) collate database_default
)
truncate table #tablenametab
open ms_cri_tnames
fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle
--print @table_name
while (@@fetch_status <> -1)
begin
-- don't auto create index on same table again
-- UNDONE: we may try to filter out local temp table in the future
if (@table_name not in (select table_name from #tablenametab ))
begin
-- these are all columns on which we are going to auto create indexes
declare ms_cri_cnames cursor local for
select column_id, quotename(column_name,'['), column_usage
from sys.dm_db_missing_index_columns(@index_handle)
-- now go over all columns for the index to-be-created and
-- construct the create index statement
open ms_cri_cnames
fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage
declare @index_name sysname
declare @include_column_list nvarchar(517)
declare @key_list nvarchar(517)
select @index_name = '_MS_Sys'
select @key_list = ''
select @include_column_list = ''
declare @num_keys smallint
declare @num_include_columns smallint
select @num_keys = 0
select @num_include_columns = 0
while @@fetch_status >= 0
begin
-- construct index name, key list and include column list during the loop
-- Index Name in the format: _MS_Sys_colid1_colid2_..._colidn
if (@column_usage = 'INCLUDE')
begin
if (@num_include_columns = 0)
select @include_column_list = @column_name
else
select @include_column_list = @include_column_list + ', ' +@column_name
select @num_include_columns = @num_include_columns + 1
end
else
begin
if (@num_keys = 0)
select @key_list = @column_name
else
select @key_list = @key_list + ', ' +@column_name
select @num_keys = @num_keys + 1
select @index_name = @index_name + '_'+cast ( @column_id as nvarchar(10))
end
fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage
end
close ms_cri_cnames
deallocate ms_cri_cnames
--print @index_name
--print @table_name
--print @key_list
--print @include_column_list
-- construct create index statement
-- "CREATE INDEX @INDEX_NAME ON @TABLE_NAME (KEY_NAME1, KEY_NAME2, ...) INCLUDE (INCLUDE_COL_NAME1, INCLUDE_COL_NAME2, ...) WITH (ONLINE = ON)" (Note: for recommendation mode, we don't use online option)
if (@num_include_columns > 0)
select @exec_stmt = 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @key_list + ') INCLUDE ('+ @include_column_list + ')'-- WITH (ONLINE = ON)'
else
select @exec_stmt = 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @key_list + ')'-- WITH (ONLINE = ON)'
--print @exec_stmt
declare @id int
declare @create_date datetime
BEGIN TRANSACTION xAddCreateIdxRecommendation
DECLARE @result int;
EXEC @result = dbo.add_recommendation @exec_stmt, 'CI', @id OUT
if (@result <> 10)
EXEC dbo.add_recommendation_details_index @id, @database_id, @object_id
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION xAddCreateIdxRecommendation
RETURN @Error
END
COMMIT TRANSACTION xAddCreateIdxRecommendation
--EXEC (@exec_stmt)
-- insert the table name into #tablenametab
insert into #tablenametab values (@table_name)
end
fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle
end
deallocate ms_cri_tnames
return(0) -- auto_create_index
go
---------------------------- sp_autodropindex ------------------------------
IF OBJECT_ID (N'dbo.auto_drop_index', N'P') IS NOT NULL
DROP PROC [dbo].[auto_drop_index];
GO
create procedure [dbo].[auto_drop_index]
as
--NOTE: This sp will drop indexes that are automatically created and
-- are no longer very useful in a cost efficient manner based on feedbacks
-- from index usage DMVs.
set nocount on
declare @database_id int
declare @object_id int
declare @index_id int
declare ms_drpi_iids cursor local static for
Select Top 3 database_id, object_id, index_id
from sys.dm_db_index_usage_stats
where user_updates > 10 * (user_seeks+user_scans)
and index_id > 1
order by user_updates / (user_seeks+user_scans+1) DESC
open ms_drpi_iids
fetch next from ms_drpi_iids into @database_id, @object_id, @index_id
-- create temporary table to store the table name and index name
create table #tabIdxnametab
(
table_name nvarchar(1000) collate database_default,
index_name nvarchar(521) collate database_default
)
while (@@fetch_status >= 0)
begin
declare @exec_stmt nvarchar(4000)
declare @database_name sysname
select @database_name = db_name(@database_id)
truncate table #tabIdxnametab
-- insert the table name and index name into the temp table
select @exec_stmt =
'use '+ @database_name + ';'+
'insert #tabIdxnametab select quotename(''' + @database_name+''', ''['')+ ''.'' +quotename(schema_name(o.schema_id), ''['')+''.''+quotename(o.name,''['') , i.name
from sys.objects o, sys.indexes i where o.type = ''U'' and o.is_ms_shipped = 0 and i.is_primary_key = 0 and i.is_unique_constraint = 0 and o.object_id =' + cast ( @object_id as nvarchar(10))+' and o.object_id = i.object_id and index_id = '+ cast ( @index_id as nvarchar(10))
--print @exec_stmt
EXEC (@exec_stmt)
-- get the table_name and index_name
declare @table_name nvarchar(1000)
declare @index_name sysname
select @table_name = table_name, @index_name = index_name from #tabIdxnametab
--use name convention to recognize auto-created indexes for now
--in the future, we will add a special bit inside metadata to distinguish
--if (substring(@index_name, 1, 8) = '_MS_Sys_')
--begin
-- construct drop index statement
-- "DROP INDEX @TABLE_NAME.@INDEX_NAME"
--select @exec_stmt = 'drop index '+@index_name+' on '+@table_name
--print @exec_stmt
--EXEC (@exec_stmt)
--end
--else
--print 'User Index: '+@table_name + '.'+ @index_name
IF (@index_name IS NOT NULL)
begin
select @exec_stmt = 'drop index '+@index_name+' on '+@table_name
declare @id int
declare @create_date datetime
BEGIN TRANSACTION xAddDropIdxRecommendation
DECLARE @result int;
EXEC @result = dbo.add_recommendation @exec_stmt, 'DI', @id out
if (@result <> 10)
EXEC dbo.add_recommendation_details_index @id, @database_id, @object_id
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION xAddDropIdxRecommendation
RETURN @Error
END
COMMIT TRANSACTION xAddDropIdxRecommendation
end
fetch next from ms_drpi_iids into @database_id, @object_id, @index_id
end
if (object_id('[#tabIdxnametab]') is not null)
begin
drop table [#tabIdxnametab]
end
deallocate ms_drpi_iids
return(0) -- auto_drop_index
go
--
-- JOBs for Executing [auto_create_index] and [auto_drop_index]
--
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job
@job_name=N'SQL MDW: Auto Index Management',
@job_id = @jobId OUTPUT
GO
EXEC msdb.dbo.sp_add_jobstep
@job_name=N'SQL MDW: Auto Index Management',
@step_name=N'Auto Create Index',
@step_id=1,
@subsystem=N'TSQL',
@command=N'EXECUTE [dbo].[auto_create_index]',
@on_success_action = 3,-- on success, go to next step
@database_name=N'AUTOINDEXRECS'
GO
EXEC msdb.dbo.sp_add_jobstep
@job_name=N'SQL MDW: Auto Index Management',
@step_name=N'Auto Drop Index',
@step_id=2,
@subsystem=N'TSQL',
@command=N'EXECUTE [dbo].[auto_drop_index]',
@database_name=N'AUTOINDEXRECS'
GO
EXEC msdb.dbo.sp_add_jobserver
@job_name=N'SQL MDW: Auto Index Management'
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'SQL MDW: Auto Index Management' ,
@freq_type = 4, -- daily
@freq_interval = 1, -- every day
@freq_subday_type = 4, -- subday interval in minutes
@freq_subday_interval = 30, -- every 30 minutes
@schedule_id = @schedule_id OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_name=N'SQL MDW: Auto Index Management',
@schedule_id = @schedule_id
go
January 24, 2008 at 8:31 pm
Dave ... this sounds really cool ... I have never heard of this technique ... thank you ... I'm going to give a try ... I will let you know ...
January 25, 2008 at 3:46 am
watch out for auto-parameterization. it can rebound
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply