Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

quick question about all indexes for my DB Expand / Collapse
Author
Message
Posted Monday, March 30, 2009 8:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 537, Visits: 1,921
hello guys, i am trying to get a list of all my indexes in my databases,
i followed this article http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-list-all-the-indexes-in-a-database.html

and got my list of all indexes, however i am getting some listed as :

_dta_index_DS_distrib_V_8_772718351__K48_K1_3_12
_dta_index_DS_Orders_6_1101611363__K1
_dta_index_DS_Orders_6_1101611363__K1_2
_dta_index_DS_Orders_8_1101611363__K1_2_21
idx_sysarticlecolumns
c1sysarticles
unc1sysarticleupdates
nc3syspublications

and i don't find them in the indexes for the table, are these statistics?
Post #686163
Posted Monday, March 30, 2009 8:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:31 PM
Points: 387, Visits: 586
Hi

How exactly did you retrieve your index list?

Using sp_helpindex or sys.sysindexes?

You will find stats in sys.sysindexes but sp_helpindex returns only indexes.
Post #686172
Posted Monday, March 30, 2009 9:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 537, Visits: 1,921
I use a modification of sp_helpindex

I found in this site:

http://www.mssqltips.com/tipimages/1003_sp_helpindex3.txt
Post #686209
Posted Monday, March 30, 2009 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
Did you run the Database Tuning Advisor against that system? That looks like artifacts from there. Statistics usually have look something like _WA_SYS_0000003_0425A276.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #686231
Posted Monday, March 30, 2009 9:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 537, Visits: 1,921
hello, i ran the script in the link I mentioned above:
http://www.mssqltips.com/tipimages/1003_sp_helpindex3.txt


-- Modified sp_helpindex SP to show all indexes for all tables
-- this was modified to handle object owned by dbo and other users

CREATE proc sp_helpindex3
--@objname nvarchar(776) -- the table to check for indexes
as
-- PRELIM
set nocount on

declare @objname nvarchar(776),
@objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@dbname sysname,
@usrname sysname

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end


-- create temp table
create table #spindtab
(

usr_name sysname,
table_name sysname,
index_name sysname collate database_default,
stats int,
groupname sysname collate database_default,
index_keys nvarchar(2126) collate database_default -- see @keys above for length descr
)


-- OPEN CURSOR OVER TABLES (skip stats: bug shiloh_51196)
declare ms_crs_tab cursor local static for
select sysobjects.id, sysobjects.name, sysusers.name from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid where type = 'U'

open ms_crs_tab
fetch ms_crs_tab into @objid, @objname, @usrname

while @@fetch_status >= 0
begin


-- Check to see the the table exists and initialize @objid.
/*
select @objid = object_id(@objname)
if @objid is NULL
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
*/
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select indid, groupid, name, status from sysindexes
where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status

-- IF NO INDEX, QUIT
--if @@fetch_status < 0
--begin
--deallocate ms_crs_ind
--raiserror(15472,-1,-1) --'Object does not have any indexes.'
--return (0)
--end

-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3

select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'

select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end

select @groupname = groupname from sysfilegroups where groupid = @groupid

-- INSERT ROW FOR INDEX
insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys)

-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind

fetch ms_crs_tab into @objid, @objname, @usrname
end
deallocate ms_crs_tab

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

-- DISPLAY THE RESULTS
select
'usr_name'=usr_name,
'table_name'=table_name,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by table_name, index_name


return (0) -- sp_helpindex

GO


Post #686252
Posted Monday, March 30, 2009 10:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 537, Visits: 1,921
ok after doing some research i found out my indexes that start with _dta_index are "hypothetical indexes" meaning they are not real indexes only recommendations from the DTA, so should be deleted. am i correct?

what about the other ones ?
i.e:
c1sysarticles
unc1sysarticleupdates
nc3syspublications
uc1syspublications... this is a publisher DB, could that be it? i am making a comparison of all the indexes between my publisher and subscriber DB, so those indexes don't come out in my subscriber DB.
Do i need them? where do those indexes come from?


Post #686290
Posted Monday, April 8, 2013 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 27, 2014 2:05 AM
Points: 4, Visits: 102
Yes, DTA creates those Indexes and statistics. But do not suggest how to remove those.
You may follow the step here to clean those up:
[url=http://www.codeproject.com/Articles/123750/SQL-Cleaning-Up-After-the-Database-Tuning-Engine-A][/url]

Thanks,
Shoeb
Post #1439813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse