INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Does anyone know where I can see the source SQL for this View?
OR
Does anyone know which fields in which system tables would allow me to
determine whether updates and deletes are cascaded?> Does anyone know where I can see the source SQL for this View?
EXEC master..sp_helptext 'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'
> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?
You can also get the cascade options using SQL Server OBJECTPROPERTY
functions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:3C2F64CC-DA03-4FE2-B198-E76DDB203BE1@.microsoft.com...
> Does anyone know where I can see the source SQL for this View?
> OR
> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?

Infinity

I have a calculation to determine percentage. When the two values that are
evaluated are the same the report displays Infinity as the answer rather than
100%. Anyone have any ideas how to have it read 100%?Please show your code which does the calculation
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jvjones" <jvjones@.discussions.microsoft.com> wrote in message
news:9E50B2E2-0B94-4F3A-B65C-224950FAB597@.microsoft.com...
>I have a calculation to determine percentage. When the two values that are
> evaluated are the same the report displays Infinity as the answer rather
> than
> 100%. Anyone have any ideas how to have it read 100%?

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
>

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]

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
>

Indexes Enabled or Disabled?

Hey folks,
SQL Server 2000 SP3/4
How can I determine if an index is enabled or disabled? The information must
be kept somewhere, but I can't seem to find it. Not in sysindexes,
information_schema, sp_helpindex.
Easy to get in SQL 2005 mind you.
--
Thanks,
Scott H.Well, it's been a while, but I don't think you can disable an index on SQL
Server 2000, can you?
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
> Hey folks,
> SQL Server 2000 SP3/4
> How can I determine if an index is enabled or disabled? The information
> must
> be kept somewhere, but I can't seem to find it. Not in sysindexes,
> information_schema, sp_helpindex.
> Easy to get in SQL 2005 mind you.
>
> --
> Thanks,
> Scott H.|||> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
Nope. That ability was introduced in SQL Server 2005.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFrZOVhqIHA.552@.TK2MSFTNGP06.phx.gbl...
> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
>
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
>> Hey folks,
>> SQL Server 2000 SP3/4
>> How can I determine if an index is enabled or disabled? The information
>> must
>> be kept somewhere, but I can't seem to find it. Not in sysindexes,
>> information_schema, sp_helpindex.
>> Easy to get in SQL 2005 mind you.
>>
>> --
>> Thanks,
>> Scott H.
>|||Ok, that's what I thought. So that makes it easy.
SELECT *, Enabled = 1
FROM sysindexes;
:-)
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on
>> SQL Server 2000, can you?
> Nope. That ability was introduced in SQL Server 2005.
>|||On Tue, 29 Apr 2008 12:21:43 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>Well, it's been a while, but I don't think you can disable an index on SQL
>Server 2000, can you?
Doesn't look that way from the docs. ALTER INDEX had not been
introduced yet.
Roy Harvey
Beacon Falls, CT|||Good thinking, Aaron!
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZZRWhhqIHA.4476@.TK2MSFTNGP04.phx.gbl...
> Ok, that's what I thought. So that makes it easy.
> SELECT *, Enabled = 1
> FROM sysindexes;
> :-)
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on SQL Server 2000, can you?
>> Nope. That ability was introduced in SQL Server 2005.
>>
>

Indexes

Hi, is there any type of monitor I can use to determine what affect an index
(or indexes) has?
We recently changed/added some indexes to a table, and since then,
performance has gone down hill...but I need proof that it was the indexes
before removing.
Thanks.You can run profiler to look at your worst performing queries. It can
capture the query text and execution plan. Then, look at the execution
plans for the queries. If the plans are less efficient, i.e. higher cost,
than they were before and use the new indexes, there is a good chance that
the indexes are at fault.
Christian Smith
"SQL" <nospam@.asdfadsf.com> wrote in message
news:OZBDsOP7DHA.3804@.tk2msftngp13.phx.gbl...
> Hi, is there any type of monitor I can use to determine what affect an
index
> (or indexes) has?
> We recently changed/added some indexes to a table, and since then,
> performance has gone down hill...but I need proof that it was the indexes
> before removing.
> Thanks.
>|||execution plan is key.
look for table scans (or index scans)
apply new index and hopefull the scans turn into SEEKS.
that is generally a good thing.
obviously this post is over-simplified, but that's it in a nutshell.
cheers,
Greg Jackson
PDX, Oregon

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such infor
mation, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the
information using some
of the new dynamic management views (dm_db_missing_indexes_%).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl..
.
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and r
elease some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>|||You are in for a lot more work, then and the question is whether you want to
do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse t
he text for the
execution plans to see what indexes were used, compare that to the indexes i
n the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for
2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl..
.
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>

Indexes

Hi, is there any type of monitor I can use to determine what affect an index
(or indexes) has?
We recently changed/added some indexes to a table, and since then,
performance has gone down hill...but I need proof that it was the indexes
before removing.
Thanks.You can run profiler to look at your worst performing queries. It can
capture the query text and execution plan. Then, look at the execution
plans for the queries. If the plans are less efficient, i.e. higher cost,
than they were before and use the new indexes, there is a good chance that
the indexes are at fault.
Christian Smith
"SQL" <nospam@.asdfadsf.com> wrote in message
news:OZBDsOP7DHA.3804@.tk2msftngp13.phx.gbl...
> Hi, is there any type of monitor I can use to determine what affect an
index
> (or indexes) has?
> We recently changed/added some indexes to a table, and since then,
> performance has gone down hill...but I need proof that it was the indexes
> before removing.
> Thanks.
>|||execution plan is key.
look for table scans (or index scans)
apply new index and hopefull the scans turn into SEEKS.
that is generally a good thing.
obviously this post is over-simplified, but that's it in a nutshell.
cheers,
Greg Jackson
PDX, Oregon

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the information using some
of the new dynamic management views (dm_db_missing_indexes_%).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and release some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and
>> release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>|||You are in for a lot more work, then and the question is whether you want to do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse the text for the
execution plans to see what indexes were used, compare that to the indexes in the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for 2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have
>> to do it using a Profiler trace or similar. 2005 does, and you can look at the information using
>> some of the new dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> I'm sorry...
>> I'm using SQL Server 2000...
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> escribió en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such
>> information, so you'd have to do it using a Profiler trace or similar.
>> 2005 does, and you can look at the information using some of the new
>> dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them
>> and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>>
>