I have a SQL Server 2000 database.
This database has indexes with numerous duplicate columns and these
columns are not used for covered indexes.
A lot of the indexes were created initially.
What is the best way to show database performance analysis before and after
deleting these indexes with duplicate columns?
Please help me with this performance issue.
Thank You,
I use set statistics IO on and compare the io before and after.
I also run timings like this
declare @.counter int
declare @.getdate datetime
set @.getdate=getdate()
set @.counter=1
while @.counter < 100
begin
EXEC MY_Proc
select @.counter=@.counter+1
end
select datediff(ms, @.getdate, getdate())
so I have an idea of how long 100 iterations took
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8E832B5C-FB88-4E97-8288-443AEA0A9E8D@.microsoft.com...
>I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after
> deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
|||Joe K. wrote:
> I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
You can use the profiler to record a typical workload before changing
indexes and to get timings (and execution plans). You can then use this
workload to get suggestions from the ITW. You can then implement them or
implement different changes. Profile again and compare timings and
execution plans.
Kind regards
robert
Showing posts with label covered. Show all posts
Showing posts with label covered. Show all posts
Friday, March 9, 2012
Indexes with Duplicate Columns
I have a SQL Server 2000 database.
This database has indexes with numerous duplicate columns and these
columns are not used for covered indexes.
A lot of the indexes were created initially.
What is the best way to show database performance analysis before and after
deleting these indexes with duplicate columns?
Please help me with this performance issue.
Thank You,I use set statistics IO on and compare the io before and after.
I also run timings like this
declare @.counter int
declare @.getdate datetime
set @.getdate=getdate()
set @.counter=1
while @.counter < 100
begin
EXEC MY_Proc
select @.counter=@.counter+1
end
select datediff(ms, @.getdate, getdate())
so I have an idea of how long 100 iterations took
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8E832B5C-FB88-4E97-8288-443AEA0A9E8D@.microsoft.com...
>I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after
> deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,|||Joe K. wrote:
> I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
You can use the profiler to record a typical workload before changing
indexes and to get timings (and execution plans). You can then use this
workload to get suggestions from the ITW. You can then implement them or
implement different changes. Profile again and compare timings and
execution plans.
Kind regards
robert
This database has indexes with numerous duplicate columns and these
columns are not used for covered indexes.
A lot of the indexes were created initially.
What is the best way to show database performance analysis before and after
deleting these indexes with duplicate columns?
Please help me with this performance issue.
Thank You,I use set statistics IO on and compare the io before and after.
I also run timings like this
declare @.counter int
declare @.getdate datetime
set @.getdate=getdate()
set @.counter=1
while @.counter < 100
begin
EXEC MY_Proc
select @.counter=@.counter+1
end
select datediff(ms, @.getdate, getdate())
so I have an idea of how long 100 iterations took
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8E832B5C-FB88-4E97-8288-443AEA0A9E8D@.microsoft.com...
>I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after
> deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,|||Joe K. wrote:
> I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
You can use the profiler to record a typical workload before changing
indexes and to get timings (and execution plans). You can then use this
workload to get suggestions from the ITW. You can then implement them or
implement different changes. Profile again and compare timings and
execution plans.
Kind regards
robert
Indexes with Duplicate Columns
I have a SQL Server 2000 database.
This database has indexes with numerous duplicate columns and these
columns are not used for covered indexes.
A lot of the indexes were created initially.
What is the best way to show database performance analysis before and after
deleting these indexes with duplicate columns?
Please help me with this performance issue.
Thank You,I use set statistics IO on and compare the io before and after.
I also run timings like this
declare @.counter int
declare @.getdate datetime
set @.getdate=getdate()
set @.counter=1
while @.counter < 100
begin
EXEC MY_Proc
select @.counter=@.counter+1
end
select datediff(ms, @.getdate, getdate())
so I have an idea of how long 100 iterations took
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8E832B5C-FB88-4E97-8288-443AEA0A9E8D@.microsoft.com...
>I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after
> deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,|||Joe K. wrote:
> I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
You can use the profiler to record a typical workload before changing
indexes and to get timings (and execution plans). You can then use this
workload to get suggestions from the ITW. You can then implement them or
implement different changes. Profile again and compare timings and
execution plans.
Kind regards
robert
This database has indexes with numerous duplicate columns and these
columns are not used for covered indexes.
A lot of the indexes were created initially.
What is the best way to show database performance analysis before and after
deleting these indexes with duplicate columns?
Please help me with this performance issue.
Thank You,I use set statistics IO on and compare the io before and after.
I also run timings like this
declare @.counter int
declare @.getdate datetime
set @.getdate=getdate()
set @.counter=1
while @.counter < 100
begin
EXEC MY_Proc
select @.counter=@.counter+1
end
select datediff(ms, @.getdate, getdate())
so I have an idea of how long 100 iterations took
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8E832B5C-FB88-4E97-8288-443AEA0A9E8D@.microsoft.com...
>I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after
> deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,|||Joe K. wrote:
> I have a SQL Server 2000 database.
> This database has indexes with numerous duplicate columns and these
> columns are not used for covered indexes.
> A lot of the indexes were created initially.
> What is the best way to show database performance analysis before and
> after deleting these indexes with duplicate columns?
> Please help me with this performance issue.
> Thank You,
You can use the profiler to record a typical workload before changing
indexes and to get timings (and execution plans). You can then use this
workload to get suggestions from the ITW. You can then implement them or
implement different changes. Profile again and compare timings and
execution plans.
Kind regards
robert
Friday, February 24, 2012
Indexes
Say you have a table with 10 fields, 50,000-100,000 records, and 1 primary key field.
Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.
It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?
I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.
It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?
If we speak read, then it's all coming about what queries are going to be used.
If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.
As for the write operation I am not that sure that there'll be any differences anyways.
I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.
In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.
Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.
It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?
I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.
It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?
If we speak read, then it's all coming about what queries are going to be used.
If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.
As for the write operation I am not that sure that there'll be any differences anyways.
I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.
In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.
Subscribe to:
Posts (Atom)