Determine Index Size

Can anyone help me figure out the size used by my indexes?
TIA
RobEXEC sp_spaceused
optional:
EXEC sp_spaceused '<table name>'
--
Jacco Schalkwijk
SQL Server MVP
"Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> Can anyone help me figure out the size used by my indexes?
> TIA
> Rob
>|||Use can query the "used" column in sysindexes table for Indid = 1 , indid
between 2 and 249
select object_name(id) as Table_name,sum(used) from sysindexes
where indid between 1 and 249
group by object_name(id)
Thanks
Hari
MCDBA
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#b4gKg0uDHA.1756@.TK2MSFTNGP09.phx.gbl...
> EXEC sp_spaceused
> optional:
> EXEC sp_spaceused '<table name>'
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
> news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> > Can anyone help me figure out the size used by my indexes?
> >
> > TIA
> >
> > Rob
> >
> >
>sql

Determine if a column is indexed (and the index name)

Is there a slick way to determine if all the indexes (if any) for a particular field?

let's say:

tbPeople

ID (bigint)

FirstName (varchar(50))

LastName (varchar(50))

I need to determine if [FirstName] in indexed anywhere, and if so, what are the name(s) of the indexes.

thanks ahead of time.

Which version of SS are you using?

To select all indexes where the column is part of the key, in 2005, you can use:

select

object_name(si.[object_id])as table_name,

si.name as index_name,

index_col(object_name(sic.[object_id]), sic.index_id, sic.index_column_id)as column_name,

sic.key_ordinal,

sic.is_descending_key,

sic.is_included_column

from

sys.indexesas si

innerjoin

sys.index_columnsas sic

on si.[object_id] = sic.[object_id]

and si.index_id = sic.index_id

where

si.object_id=object_id('dbo.Employees')

andcolumnproperty(object_id('dbo.Employees'),'LastName','ColumnId')= sic.column_id

orderby

sic.[object_id],

sic.index_id

go

AMB

|||

Brilliant. Thanks. Was not aware of sys.index_columns.

Any cute tricks for the equivalent query on SS 2K?

|||

Try:

select

object_name(si.[id])as table_name,

si.name as index_name,

col_name(sic.[id], sic.colid)as column_name,

sic.keyno

from

sysindexesas si

innerjoin

sysindexkeysas sic

on si.[id] = sic.[id]

and si.indid = sic.indid

where

si.[id] =object_id('dbo.Employees')

andcol_name(sic.[id], sic.colid)='LastName'

orderby

sic.[id],

sic.indid

go

AMB

Detect changes on indexes in a published table

Hi,

Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.

Cheers,
Justin

One thing you may be able to use is DDL trigger. DDL trigger are fired in response to a variety of DDL statements, including create/alter/drop index. Just look for DDL trigger in BOL for more information.

Peng

|||

Yes, you may use special tools for compare structure changes

link http://www.red-gate.com/products/index.htm

sql

Design question - Foreign key indexes

A rather elementary design question - is it a good idea to index foreign keys in fact tables? I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time? Does the cube generate all dimension members then query fact tables for them? Does it query fact tables with group by's on participating dimensions? I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed.

Thanks in advance,

John Hennesey

It is an interesting question.

General assumption is Analysis Services cubes are being built based on the relational data warehouse which data is coming from OLTP system. The relationa data warehouse is not the place where you expecting lots of individual atomic updates. That is in theory. In practice depending on the size I have seen implmentations where cubes are built based on raw OLTP shemas.

It is dependant on your case and it is really tradeoff you will be making. If you would like to speed up processind of cubes , you should go and run, for example index tuning wizard and build your relational database indexes that match the queries Analysis Server sends during processing. I wouldnt go and try analyzing every query by yourself. Index tuning wizard makes work easier for you.

But if you see indexed built slowing down your ETL update process, you can always drop these indexes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi:

I think there is another consideration and it is performance -not on insertion of data - but at ETL time, when you want to determine if a given row is already present in a dimension (locating a row).

Most of the times, I just index for ETL speed. OLAP Processing , because of the amount of rows read, may easily go to be a table/partition scan, making indexes almost useless.However, that also depends on the cardinality of your attributes, and what kind of processing you use in your OLAP structures

Alejandro Leguizamo

SQL Server MVP

Colombia

|||

Alejandro / Edward -

Thank you very much for your quick responses - both very good points. Edward - to clarify, we are using service broker which receives xml messages from our source system, which uses stored procs to update our data warehouse. During peak times we receive hundreds of thousands of messages each day, and each one will cause updates / inserts / deletes in our dimension and fact tables. Hence my consideration about index changes slowing things down. When we process, we are doing a full process, so Alejandro's response about doing full table scans is a very realistic possibility. The only way to truly tell what is going on and if there can be some performance boost is to run the index tuning wizard - a very good suggestion. I will check it out!

Thanks again,

John

p.s. Alejandro - you would be suprised what the Enterprise Reporting solution has evolved into - I believe Robert Skoglund gave you guys a nice presentation a few months back...