querying indexes 1

[jazerr]

I have a query that gets me the table.column and id of indexes. The trouble is that I need to add a where statement to filter out all the ones that are not clustered indexes. SQL help doesnt tell me what to lookup to tell which ones are clustered.

Anyone know?

Here is my query if you need it:
select sysindexkeys.id,
sysobjects.name as tname,
syscolumns.name as cname
from sysindexkeys
inner join syscolumns on (sysindexkeys.id = syscolumns.id and sysindexkeys.colid = syscolumns.colid)
inner join sysobjects on sysobjects.id = sysindexkeys.id

 

[JamesLean]

Clustered indexes have an indid of 1 in the sysindexes table. Non-clustered have an indid of > 1.

If the table doesn't contain a clustered index then there will be an entry for indid = 0 in sysindexes - this represents the "heap" of unordered data pages. A table will have either an index with indid = 0 or indid = 1 but never both.

--James

 

[JamesLean]

Also, indid = 255 is for tables with text/image data.

--James

 

[jazerr]

Excellent. Perfect. A thousand points to you. Thank you so much.

Incidentally, I have another question if you wouldn't mind.

We have a DB that isnt very large. 400k records in the main tables. It runs on a quad 700mhz box with 4gb of memory.

Basically its stoopid-slow. Simple selects from those tables are taking upwards of 2 minutes. Hence the indexing. The DB wasn't designed well to begin well, but we are trying to recover. So, the question is, other than some well-placed indexes what can be done to speed things up?

 

[TitleistDBA]

Here are few helpful tips that might help.
1. make sure your mdf and ldf are not on the same disk.
2. make sure you are not sending your bak files to the same drive as the mdf file.
3. Run DBCC reindex on each table once a week. (this will clean up your index.)
4 Try update statistics on your slower queries.

As a technical reference you should get SQL Server 2000 Performance and Tuning by Microsoft press. It is very helpfull.

http://www.acushnetcompany.com

 

[jazerr]

Regarding 1 & 2...Should they be on separate physical disks? Or will separate partitions do?

 

[JFoushee]

jazerr, I'm looking at the original query

select     sysindexkeys.id,
    sysobjects.name as tname,
    syscolumns.name as cname
from sysindexkeys
    inner join syscolumns on (sysindexkeys.id = syscolumns.id and sysindexkeys.colid = syscolumns.colid)
    inner join sysobjects on sysobjects.id = sysindexkeys.id

I was hoping to see similar info as when I use Enterprise Manager to design the table and view the indexes. The query above gives back more than that.

I'd like to filter this to say: the table name, the indexes on the table, and the columns within the index. Any thoughts?

 

[JFoushee]

I got it by borrowing sp_help index. It's kinda crude, but it gets what I need...

select so.name, si.name
, index_col(so.name, si.indid, 1)
+ IsNull(', ' + index_col(so.name, si.indid, 2),'')
+ IsNull(', ' + index_col(so.name, si.indid, 3),'')
+ IsNull(', ' + index_col(so.name, si.indid, 4),'')
+ IsNull(', ' + index_col(so.name, si.indid, 5),'')
from sysindexes si
  INNER JOIN sysobjects so on so.id = si.id
Where indid > 0 and indid < 255
  AND so.xtype <> 'S'
  AND Left(si.name,8) <> '_WA_Sys_'
  AND so.name <> 'dtproperties'
order by so.id, indid