query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIA
Hi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

Query Timeouts--Indexing Questions

I am helping a client with query timeout issues. Their tables have an
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s the most efficient?
My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:

> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s of the nonclustered index on [PhoneNumber] with a
bookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:

> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s of the nonclustered index on [PhoneNumber] with a
> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s* on a non-clusted index, with a
bookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>

Query timeout when rows returned < TOP (n)?

SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".

Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.

The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.

Obviously there's something I'm missing, but I'm completely clueless as to what it is.

Thanks - James

Query 1:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

37759 ms

66 rows

IO Cost: 188.225

Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds

Query 2:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')

ORDER BY date DESC

334 ms

100 rows

IO Cost: 188.225

It really depends on how many records there are of Attenna 1 and Antenna 2.

Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.

On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.

|||

Query plans are your friend.

Look at the query plan and you will see the difference between both queries.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Yep query plans are the same for both. I've got indices for the other fields also.

The odd thing is with a if I give it a point name that doesn't exist like

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')

ORDER BY date DESC

It uses the index for point then hits the clustered index, but the same query with a valid point name

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.

Thanks again - James

|||

Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.

In the second query, it found 100 records very quickly so there was no need to continue.

If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).

Query Sysindexes

i hav a table, Test1, with a clustered index and two non clustered index defined on it.

When i use sp_help Test1, the CI and non clustered index are listed along with the column names.

when i query the sysindex table (for id = object_id(Test1))

i can see entries like _WA_Sys_<<ColumnName>>_3D5EEB29.

what are these entries? are they indexes? if yes, how these entries are created and what is the significance of these entries.

Pl discuss.

Thanks.Hi

Given the info - why would you not expect to see this entry? Did you explicitly name your indexes? If not the SQL Server generates names for you. I admit I can't remember the conventions it uses (I name my indexes).

Also, SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason.|||Hello.

Thanks for the reply.

i hav named my indexes. the entries i mentioned are displayed in addition to the indexes i created.

"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.

Thanks|||Entries like _WA_Sys... are for Statics.|||Entries like _WA_Sys... are for Statics.Thanks mihirclarion. http://www.google.com/custom?cx=013269018370076798483:gg7jrrhpsy4&cof=GFNT%3A%23666666%3BGALT%3A%23666666%3BLH%3A100 %3BCX%3ABlackle%3BVLC%3A%23999999%3BDIV%3A%2300000 0%3BFORID%3A1%3BT%3A%23999999%3BALC%3A%23cccccc%3B LC%3A%23cccccc%3BS%3Ahttp%3A%2F%2Fwww%2Eblackle%2E com%2F%3BL%3Ahttp%3A%2F%2Fwww%2Eheapmedia%2Ecom%2F blackle%2Flogo%2Ejpg%3BGIMP%3A%23666666%3BLP%3A1%3 BBGC%3A%23000000%3BAH%3Aleft&q=_WA_Sys&hl=en&client=pub-8993703457585266

Coo - that's a long URL.


"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.
BoL is the source of all knowledge:
http://msdn2.microsoft.com/en-us/library/ms177420.aspx|||Thank you :)|||_WA_Sys is auto generated statistics. If you execute a query and one (or more) of the columns in the WHERE clause does not have an index or an appropriate statistics, the SQL Server will generate statistics on the column for you. The reason it is named _WA_Sys is because it is system generated and programmed by the SQL Server developers sitting in Washington.

Example:

create table test(id int)
insert into test values(1)

-- SQL Server 2005: This query will now return 0 rows because no
-- statistics has been created for any column in the table
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

select id from test where id=1

-- This time this query will return one row because a statistics
-- was auto generated on the id column.
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

If you're using SQL Server 2000, use this statistics query instead:

select s.name,o.name from sysindexes s
inner join sysobjects o on s.id=o.id
where s.name like '_WA%' and o.name='test'