SQL Server Performance Forum – Threads Archive

Indexed Views

I just upgraded my SQL version from SQL 2000 to 2005. In the maintenence plan, there’s is an option to index views which I did not have under 2000. Anyways, thinking that it couldn’t hurt anything, I decided to index my views. Well, this caused my database to grow from 11 GBs to 51 GBs. Now, I want to drop the indexes that are related to my views. Any advice as to the best way I can identify which index to drop and how to do that?

1) Really grows from 11 to 51 or becasue index creation log grows to much?
2) DROP INDEX ‘table.index | view.index’ [ ,…n ] to drop any index you want.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Are there any clustered in those indexes? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Luis, I’m not sure. How would I tell? Here are the Space #’s DatabaseSize= 51602.88 MB; UnallocatedSpace= 6445.55 MB; Reserved= 45192520 KB
Data= 4580872 KB; Index_size= 37972168 KB; Unused= 2639480 KB. My problem that I have is that I ran: select object_id,name,index_id
from sys.indexes This returned a list of object id’s, index names, and index id’s. However, when I look up th object id’s they all seem to correspond to actual table in the Dbase. So….while I know the statement that drops indexes I don’t know how to determine and specify which of the indexes correspond to the views in my database.
Satya, as best I can tell, all of the views are non-clustered. I mean when I’m in Enterprise Mgr and open a dbase table and then open the indexes folder underneath they all seem to say non-clustered.

Did you use same common name as index name? I mean: CREATE INDEX IXC……something. I’m looking for a way to find those indexes.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Unfortunately, this is a 3rd party Dbase so I didn’t really create the index’s myself. But here are some examples of index names in the Dbase. clust
NULL
nc1
nc2
PK_
USIX__ There are many examples of all of these. I believe that the PK_ and the USI_ are probably the ones created by the company that created our Dbase. So I see things like PK_ClientOID or USI_ClientLastName, etc.

Well the only way I can help is with this query: select ‘drop ‘ + case when left(si.Name,3) in (‘ixc’) then ‘index ‘ else ‘statistics ‘ end + so.name
+ ‘.’ + ‘[‘ + si.name + ‘]’
/* si.rows as ‘filas’, SO.Name as Tabla, SI.name as ‘Index’, SFG.groupname as ‘Filegroup’ */
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId
where left(SI.Name,4) not in (‘_WA_’) and left(si.Name,3) in (‘ixc’)
order by SO.Name , SI.name, SFG.GroupName You can change ‘ixc’ for any index name and with the output select those indexes you want to drop. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Not sure if this will help
SELECT ‘DROP INDEX ‘ + OBJECT_NAME(id)
FROM sysobjects
WHERE OBJECTPROPERTY(id, ‘IsView’) = 1
AND OBJECTPROPERTY(id, ‘IsIndexed’) = 1 This is for SQL Server 2000. You have to change sysobjects to its 2005er pendant —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>