Bad index information in one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bad index information in one table

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-04-10 : 10:41:04

Hi All,

I have written a code in which I am trying to get the bad indexes information of each database into one single table that is SSIS..BADINDEXES but the problem is after executing this code it does not insert the records from any of the database althoough it should populate SSIS..BADINDEXES because their are one or two databses in which bad indexes exist..
If I print the dynamic query and run this entire query on the diffent window in this case my table SSIS..BADINDEXES gets the records in it .

I dont undestand what is the problem with my code ..Please suggest me if any one can ...

SELECT NAME INTO #DATABASES FROM SYS.DATABASES WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL')

CREATE TABLE SSIS..BADINDEXES([DatabaseName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[IndexId] [int] NOT NULL,
[RowCnt] [bigint] NULL,
[TotalWrites] [bigint] NOT NULL,
[TotalReads] [bigint] NULL,
[Difference] [bigint] NULL
)
DECLARE @DB VARCHAR(200),@SQL VARCHAR(2000)
DECLARE CUR CURSOR LOCAL FOR SELECT NAME FROM #Databases
OPEN CUR
FETCH NEXT FROM CUR INTO @db
WHILE(@@FETCH_STATUS = 0)
BEGIN

SET @SQL = 'INSERT INTO SSIS..BADINDEXES(DatabaseName,TableName,IndexName,IndexId,
RowCnt,TotalWrites,TotalReads,Difference)
select
DB_name(database_id)DatabaseName,
object_name(s.[object_id],database_id) as [table name],
i.name as [index name],
i.index_id,
si.rowcnt,
user_updates as [total writes],
user_seeks + user_scans + user_lookups as [total reads],
user_updates - (user_seeks + user_scans + user_lookups) as [difference]
from '+@db+'.'+'sys.dm_db_index_usage_stats as s with (nolock)
inner join '+@db+'.'+'sys.indexes as i with (nolock)
on s.[object_id] = i.[object_id]
and i.index_id = s.index_id
inner join '+@db+'..'+'sysindexes as si
on s.[object_id] = si.[id]
and s.index_id = si.indid
where
objectproperty(s.[object_id],''isusertable'') = 1
and s.database_id = db_id()
and user_updates > (user_seeks + user_scans + user_lookups)
and i.index_id > 1'

EXEC (@SQL)

FETCH NEXT FROM CUR INTO @db
END
CLOSE CUR
DEALLOCATE CUR

select * from SSIS..BADINDEXES

drop table SSIS..BADINDEXES
drop table #DATABASES

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-10 : 11:08:16

[code]SELECT NAME INTO #DATABASES FROM SYS.DATABASES WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL')

CREATE TABLE SSIS..BADINDEXES([DatabaseName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[IndexId] [int] NOT NULL,
[RowCnt] [bigint] NULL,
[TotalWrites] [bigint] NOT NULL,
[TotalReads] [bigint] NULL,
[Difference] [bigint] NULL
)
DECLARE @DB VARCHAR(200),@SQL VARCHAR(2000)
DECLARE CUR CURSOR LOCAL FOR SELECT NAME FROM #Databases
OPEN CUR
FETCH NEXT FROM CUR INTO @db
WHILE(@@FETCH_STATUS = 0)
BEGIN

SET @SQL = 'INSERT INTO SSIS..BADINDEXES(DatabaseName,TableName,IndexName,IndexId,
RowCnt,TotalWrites,TotalReads,Difference)
select
DB_name(database_id)DatabaseName,
object_name(s.[object_id],database_id) as [table name],
i.name as [index name],
i.index_id,
si.rowcnt,
user_updates as [total writes],
user_seeks + user_scans + user_lookups as [total reads],
user_updates - (user_seeks + user_scans + user_lookups) as [difference]
from '+@db+'.'+'sys.dm_db_index_usage_stats as s with (nolock)
inner join '+@db+'.'+'sys.indexes as i with (nolock)
on s.[object_id] = i.[object_id]
and i.index_id = s.index_id
inner join '+@db+'..'+'sysindexes as si
on s.[object_id] = si.[id]
and s.index_id = si.indid
where
objectproperty(s.[object_id],''isusertable'') = 1
and s.database_id = ' + cast(db_id(@db) as varchar) + '
and user_updates > (user_seeks + user_scans + user_lookups)
and i.index_id > 1'

EXEC (@SQL)

FETCH NEXT FROM CUR INTO @db
END
CLOSE CUR
DEALLOCATE CUR

select * from SSIS..BADINDEXES

drop table SSIS..BADINDEXES
drop table #DATABASES[/code]See corrected section in red. Your original version called db_id() without parameters, which returns the current database id. Since you aren't calling "USE database" in your code, the current database never changes.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-04-10 : 11:22:47

Hi Rob ,

Thanks for your reply abut the issue is not yet resolve after applying it

select * from SSIS..BADINDEXES

this table is still not populating after executing the entire code ..
again if a print the dynamic query and execute the generated query it works fine and populate the table SSIS..BADINDEXES
one by one ,but whats wrong with the entire code ..I cant understand ..any suggestion on this

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-10 : 11:40:10

The only thing I can think of is that you don't have permissions to execute dynamic SQL (which doesn't make sense). Are you running this code as-is, or is it in a stored procedure?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-04-10 : 11:43:59

Hi Rob

I am running this code as it is and running on the development box .

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-10 : 11:50:34

Try this:

SELECT NAME INTO #DATABASES FROM SYS.DATABASES WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL')

CREATE TABLE SSIS..BADINDEXES([DatabaseName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[IndexId] [int] NOT NULL,
[RowCnt] [bigint] NULL,
[TotalWrites] [bigint] NOT NULL,
[TotalReads] [bigint] NULL,
[Difference] [bigint] NULL
)
DECLARE @DB VARCHAR(200),@SQL VARCHAR(2000)
DECLARE CUR CURSOR LOCAL FOR SELECT NAME FROM #Databases
OPEN CUR
FETCH NEXT FROM CUR INTO @db
WHILE(@@FETCH_STATUS = 0)
BEGIN

SET @SQL = 'select
DB_name(database_id)DatabaseName,
object_name(s.[object_id],database_id) as [table name],
i.name as [index name],
i.index_id,
si.rowcnt,
user_updates as [total writes],
user_seeks + user_scans + user_lookups as [total reads],
user_updates - (user_seeks + user_scans + user_lookups) as [difference]
from '+@db+'.'+'sys.dm_db_index_usage_stats as s with (nolock)
inner join '+@db+'.'+'sys.indexes as i with (nolock)
on s.[object_id] = i.[object_id]
and i.index_id = s.index_id
inner join '+@db+'..'+'sysindexes as si
on s.[object_id] = si.[id]
and s.index_id = si.indid
where
objectproperty(s.[object_id],''isusertable'') = 1
and s.database_id = ' + cast(db_id(@db) as varchar) + '
and user_updates > (user_seeks + user_scans + user_lookups)
and i.index_id > 1'

INSERT INTO SSIS..BADINDEXES(DatabaseName,TableName,IndexName,IndexId,
RowCnt,TotalWrites,TotalReads,Difference) EXEC (@SQL)

FETCH NEXT FROM CUR INTO @db
END
CLOSE CUR
DEALLOCATE CUR

select * from SSIS..BADINDEXES

drop table SSIS..BADINDEXES
drop table #DATABASES

If that doesn't work, can you do a simplified version that only inserts object names for each database? The query should be very simple, no joins or where clauses. If that works, try adding pieces back to the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources