Hello,
I am experiencing some performance issues with the dbo.sysindexkeys.
I wonder if anyone can help me create an index on it?
Thanx!
SQL script:
set nocount on
set ansi_warnings off
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------------------------------------------------------------------'
print ' Create test database'
print
'--------------------------------------------------------------------------------------'
use master
go
if not db_id('test') is null
drop database test
go
create database test
go
use test
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------------------------------------------------------------------'
print ' Create 4000 tables in test database'
print
'--------------------------------------------------------------------------------------'
declare @i_iter int
set @i_iter = 0
while @i_iter < 4000
begin
if @i_iter % 100 = 0
print 'Table -> ' + cast(@i_iter as char(10))
exec ('
create table TABLE_' + @i_iter + '
( COLUMN_0 int not null,
COLUMN_1 int not null,
COLUMN_2 datetime not null,
COLUMN_3 datetime not null,
COLUMN_4 varchar(100) not null,
COLUMN_5 smallint not null,
COLUMN_6 bit not null,
constraint TABLE_' + @i_iter + '_PK primary key (COLUMN_0,
COLUMN_1, COLUMN_2)
)
create index TABLE_' + @i_iter + '_I1 on TABLE_' + @i_iter + '
(COLUMN_4, COLUMN_5)
')
set @i_iter = @i_iter + 1
end
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------------------------------------------------------------------'
print ' Dump dbo.sysindexkeys in temp. table and create a clustered
index on it'
print
'--------------------------------------------------------------------------------------'
if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
select * into dbo.t_sink from dbo.sysindexkeys
alter table dbo.t_sink add constraint t_sink_pk primary key ([id] ,
[indid], [colid])
go
print
'--------------------------------------------------------------------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using this'
print ' temp table'
print
'--------------------------------------------------------------------------------------'
if not object_id('tempdb..#dump') is null
drop table #dump
go
declare @time datetime
set @time = getdate()
select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #dump
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.t_sink sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name
print 'Rows: ' + cast(@@rowcount as char(10))
set @time = getdate() - @time
print 'Time: ' + convert(char(25), @time, 114)
go
if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------------------------------------------------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using the'
print ' system tables'
print
'--------------------------------------------------------------------------------------'
if not object_id('tempdb..#direct') is null
drop table #direct
go
declare @time datetime
set @time = getdate()
select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #direct
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.sysindexkeys sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name
print 'Rows: ' + cast(@@rowcount as char(10))
set @time = getdate() - @time
print 'Time: ' + convert(char(25), @time, 114)
go
