October 12, 2010 at 11:23 am
I have a sproc that im running, and it runs fine 9 times out of 10, but then it will throw this error:
Could not find table or object ID 825398117. Check system catalog.
(object ID varies from error to error).
The sproc is doing the following:
1.Getting a list of databases.
2.in a while statement, it builds a query that gathers index usage stats for each database.
3. executes the query using sp_executeSQL
4. goes back around and does the same for each database until the query has run for them all.
I cannot see what is going wrong here. Each time the error is thrown, the query can be running against any database. its not always the same database.
I have the sproc spit out the query being executed and when i run it it runs perfectly.
Any idea what im doing wrong here?
October 12, 2010 at 11:43 am
Post the code? Near-impossible to say anything useful without being able to see and/or run it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2010 at 11:51 am
Cool. There are a few parts to this. First is the overall sproc that does the work:
create Procedure Get_index_usage_for_All_DB
@database_list varchar(1000)
AS
BEGIN
--table to store databases for monitoring
DECLARE @tabDbId TABLE ( dbname VARCHAR(128))
declare @DBCount INT
declare @DBName VARCHAR(128)
declare @query NVARCHAR(MAX)
declare @ErrorMessage NVARCHAR(4000)
declare @ErrorNumber INT
declare @ErrorSeverity INT
declare @ErrorState INT
declare @ErrorLine INT
declare @ErrorProcedure NVARCHAR(200)
INSERT @tabDbId
select val from [ufnSplitString](@database_list)
select @DBCount = count(*) from @tabDbId
while (@DBCount > 0)
BEGIN
select top 1 @DBName = dbname from @tabDbId
Begin Try
set @query += 'USE ' + @DBName + ';'
set @query += ' insert into AdminDB..IndexStats
select getdate() as Date, OBJECT_NAME ( stats.[object_id] ) as [Table],
ind.[name],ind.index_id, user_seeks, user_scans, user_lookups,
user_updates, last_user_seek, last_user_scan,last_user_lookup, last_user_update,
system_seeks, system_scans, system_lookups, system_updates, last_system_seek,
last_system_scan, last_system_lookup, last_system_update, stats.database_id,db.name
, os.range_scan_count,os.singleton_lookup_count, stats.database_id,stats.object_ID,stats.index_id
from sys.dm_db_index_usage_stats stats
join sys.indexes ind on (stats.[object_id] = ind.[object_id] and stats.index_id = ind.index_id)
join sys.databases db on db.database_id = stats.database_id
join sys.objects sysobj on ind.[object_id] = sysobj.[object_id]
cross apply AdminDB..udf_Index_Operational_Stats(stats.database_id,stats.object_ID,stats.index_id) as os
where ind.index_id > 0 and sysobj.type = ''u'''
select @query
EXEC sp_executesql @query
delete from @tabDbId where dbname = @DBName
select @DBCount = count(*) from @tabDBId
set @Query = ''
End Try
Begin Catch
set @ErrorNumber = ERROR_NUMBER()
set @ErrorSeverity = ERROR_SEVERITY()
set @ErrorState = ERROR_STATE()
set @ErrorLine = ERROR_LINE()
set @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
SET @ErrorMessage ='JobName: Index_Monitoring' + ', Error Source: Get_index_usage_for_All_DB' + '-'+ERROR_MESSAGE();
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- original error number.
@ErrorSeverity, -- original error severity.
@ErrorState, -- original error state.
@ErrorProcedure, -- original error source name.
@ErrorLine -- original error line number.
);
BREAK;
End Catch
End
END
sproc is called as so:
exec AdminDB..Get_index_usage_for_All_DB'Database1,Database2,Database3'
Next is the function that parses the list of databases. it takes a comma delimited list of databases and splits out the database names into a list:
CREATE FUNCTION [dbo].[ufnSplit_String_List]( @List varchar(1000))
RETURNS TABLE
/*
i: position of 1st char of the value extracted from the string list
j: position of the comma or specified charachter
pos: position of the list entry in the provided list
*/
AS RETURN
(
WITH cteList(Pos,i,j, Val)
AS(
SELECT 0, i=1, val=CHARINDEX(',',@List+','),
convert(varchar(1000),SUBSTRING(@List, 1, CHARINDEX(',',@List+',')-1) )
UNION ALL
SELECT
pos=pos + 1,i=j+1, j=CHARINDEX(',',@List+',',j+1),
convert(varchar(1000),SUBSTRING(@List, j+1, CHARINDEX(',',@List+',',j+1)-(j+1)))
FROM
cteList
WHERE CHARINDEX(',',@List+',',j+1) <> 0
)
SELECT pos=pos+1,i,j=j-1,val FROM cteList where i<j
)
Next is the function that wraps up the sys.dm_db_index_operational_stats function, and returns it in table format so that i can do a cross join on it:
create function udf_Index_Operational_Stats( @database_ID int, @obj_ID int ,@index_ID int)
returns @Index_operational_Stats TABLE (database_id int, obj_ID int, index_id int,range_scan_count bigint,singleton_lookup_count bigint)
as BEGIN
insert into @Index_operational_Stats
select database_ID, object_ID,index_id,range_scan_Count, singleton_lookup_count
from sys.dm_db_index_operational_stats(@database_ID,@obj_ID,@index_ID,null);
Return
END
Finally is the table i am inserting the data into:
CREATE TABLE [dbo].[IndexStats](
[Date] [datetime] NOT NULL,
[Table] [nvarchar](128) NULL,
[name] [sysname] NULL,
[index_id] [int] NOT NULL,
[user_seeks] [bigint] NOT NULL,
[user_scans] [bigint] NOT NULL,
[user_lookups] [bigint] NOT NULL,
[user_updates] [bigint] NOT NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL,
[system_seeks] [bigint] NOT NULL,
[system_scans] [bigint] NOT NULL,
[system_lookups] [bigint] NOT NULL,
[system_updates] [bigint] NOT NULL,
[last_system_seek] [datetime] NULL,
[last_system_scan] [datetime] NULL,
[last_system_lookup] [datetime] NULL,
[last_system_update] [datetime] NULL,
[database_id] [smallint] NOT NULL,
[database_name] [varchar](100) NOT NULL,
[range_scan_count] [bigint] NULL,
[singleton_lookup_count] [bigint] NULL
) ON [PRIMARY]
GO
That should be everything required to set this up in any database. I feel the issue is in udf_Index_Operational_Stats, but i cannot identify exactly what it is just yet.
October 12, 2010 at 12:36 pm
I "think" ive fixed it. In the main sproc i altered the select statement that gets the index usage data:
declare @Database_ID int
select @Database_ID = database_id from sys.databases where name = @DBName
set @query += ' insert into AdminDB..IndexStats
select getdate() as Date, OBJECT_NAME ( stats.[object_id] ) as [Table],
ind.[name],ind.index_id, user_seeks, user_scans, user_lookups,
user_updates, last_user_seek, last_user_scan,last_user_lookup, last_user_update,
system_seeks, system_scans, system_lookups, system_updates, last_system_seek,
last_system_scan, last_system_lookup, last_system_update, stats.database_id,db.name
, os.range_scan_count,os.singleton_lookup_count, stats.database_id,stats.object_ID,stats.index_id
from sys.dm_db_index_usage_stats stats
join sys.indexes ind on (stats.[object_id] = ind.[object_id] and stats.index_id = ind.index_id)
join sys.databases db on db.database_id = stats.database_id
join sys.objects sysobj on ind.[object_id] = sysobj.[object_id]
cross apply AdminDB..udf_Index_Operational_Stats(stats.database_id,stats.object_ID,stats.index_id) as os
where ind.index_id > 0 and sysobj.type = ''u'' AND stats.database_id = ' + cast(@Database_ID as nvarchar(4))
i think the fact that the udf_Index_Operational_stats (which is really just a wrapper for sys.dm_db_index_operational_stats) does a cross join, and as thsi function returns results regardless of the current database context, it was getting a object_ID's that were not in the database currently being examined.
Adding the where clause seems to have fixed it. does this seem plausable. I have run the sproc about 30 times now with no reoccourence of the issue, but im still not 100% sold that its resolved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply