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 numerous. Show all posts
Showing posts with label numerous. 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
Indexes onTable
I have a table (Denver) with Sales data that is sampled hourly.
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_YearWhat is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very efficient.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
> >I have a table (Denver) with Sales data that is sampled hourly.
> > This table numerous samples (50 million samples).
> >
> > Please help me determine the appropriate indexes that should be applied
to
> > the table with the select statement listed below for quick retrieval. I
> > create a monthly report for each month and each category.
> >
> > Thanks,
> >
> >
> >
> > Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> > From Denver
> > Where DC_Year = 2005 AND DC_Month = 5
> > order by DC_Day, DC_Hour
> >
> >
> > Denver
> > DC_Date
> > DC_Value
> > DC_Name
> > DC_Category
> > DC_Hour
> > DC_Day
> > DC_Month
> > DC_Year
>|||I doubt adding day and hour at the end of the index will affect the query plan. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following rows, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found because the sort operation
didn't include the high order elements in the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provide
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
>> What is the expected selectivity (number of rows to be typically
> returned)?
>> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
> very efficient.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>> >I have a table (Denver) with Sales data that is sampled hourly.
>> > This table numerous samples (50 million samples).
>> >
>> > Please help me determine the appropriate indexes that should be applied
> to
>> > the table with the select statement listed below for quick retrieval. I
>> > create a monthly report for each month and each category.
>> >
>> > Thanks,
>> >
>> >
>> >
>> > Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
>> > From Denver
>> > Where DC_Year = 2005 AND DC_Month = 5
>> > order by DC_Day, DC_Hour
>> >
>> >
>> > Denver
>> > DC_Date
>> > DC_Value
>> > DC_Name
>> > DC_Category
>> > DC_Hour
>> > DC_Day
>> > DC_Month
>> > DC_Year
>
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_YearWhat is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very efficient.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
> >I have a table (Denver) with Sales data that is sampled hourly.
> > This table numerous samples (50 million samples).
> >
> > Please help me determine the appropriate indexes that should be applied
to
> > the table with the select statement listed below for quick retrieval. I
> > create a monthly report for each month and each category.
> >
> > Thanks,
> >
> >
> >
> > Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> > From Denver
> > Where DC_Year = 2005 AND DC_Month = 5
> > order by DC_Day, DC_Hour
> >
> >
> > Denver
> > DC_Date
> > DC_Value
> > DC_Name
> > DC_Category
> > DC_Hour
> > DC_Day
> > DC_Month
> > DC_Year
>|||I doubt adding day and hour at the end of the index will affect the query plan. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following rows, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found because the sort operation
didn't include the high order elements in the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provide
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
>> What is the expected selectivity (number of rows to be typically
> returned)?
>> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
> very efficient.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>> >I have a table (Denver) with Sales data that is sampled hourly.
>> > This table numerous samples (50 million samples).
>> >
>> > Please help me determine the appropriate indexes that should be applied
> to
>> > the table with the select statement listed below for quick retrieval. I
>> > create a monthly report for each month and each category.
>> >
>> > Thanks,
>> >
>> >
>> >
>> > Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
>> > From Denver
>> > Where DC_Year = 2005 AND DC_Month = 5
>> > order by DC_Day, DC_Hour
>> >
>> >
>> > Denver
>> > DC_Date
>> > DC_Value
>> > DC_Name
>> > DC_Category
>> > DC_Hour
>> > DC_Day
>> > DC_Month
>> > DC_Year
>
Indexes onTable
I have a table (Denver) with Sales data that is sampled hourly.
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_YearWhat is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very
efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
to[vbcol=seagreen]
>|||I doubt adding day and hour at the end of the index will affect the query pl
an. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following row
s, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found bec
ause the sort operation
didn't include the high order elements in the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...[vbco
l=seagreen]
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provi
de
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> returned)?
> very efficient.
> to
>[/vbcol]
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_YearWhat is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very
efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
to[vbcol=seagreen]
>|||I doubt adding day and hour at the end of the index will affect the query pl
an. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following row
s, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found bec
ause the sort operation
didn't include the high order elements in the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...[vbco
l=seagreen]
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provi
de
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> returned)?
> very efficient.
> to
>[/vbcol]
Indexes onTable
I have a table (Denver) with Sales data that is sampled hourly.
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_Year
What is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year
|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
to
>
|||I doubt adding day and hour at the end of the index will affect the query plan. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following rows, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found because the sort operation
didn't include the high order elements in the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provide
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> returned)?
> very efficient.
> to
>
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_Year
What is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year
|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
to
>
|||I doubt adding day and hour at the end of the index will affect the query plan. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following rows, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found because the sort operation
didn't include the high order elements in the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provide
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> returned)?
> very efficient.
> to
>
Friday, February 24, 2012
Indexes
I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index
.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index
.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
Indexes
I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
Sunday, February 19, 2012
Indexes
I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-Cluster
There is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-Cluster
There is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>
Subscribe to:
Posts (Atom)