When sp_depends is not dependable

by Posted on

A lot of DBAs including me used sp_depends several times to analyse stored procedures. I felt helpless when I understood, sp_depends is not always dependable. Scan through stored procedure script with 1000+ lines of code manually to find objects is practically impossible.

How do we analyse a stored procedure performance and healthcheck all underlying tables quickly when sp_depends is not dependable?

A quick look at the stored proc script gave more tables and views not listed in sp_depends output.

References

As a solution to this and mainly to run “UPDATE STATISTICS” for dependant objects as soon as we find a slow performing stored procedure, my manager Sundar gave me a task to build a script . The script had to follow below guidelines to achieve our goal.

  • Scan the stored proc for all nested stored procedures to infinite level
  • Find all actual dependent tables and views
  • Find all indexes
  • Generate scripts to check fragmentation for all identified indexes
  • Generate “UPDATE STATISTICS” and “ALTER INDEX” statements for all fragmented indexes
  • All by not using sp_depends

Finally, here is the script that works fine. It goes one step ahead after finding dependent objects, also it generates scripts to update statistics, check fragmentation and prints alter index statements.

/*
---------------------------------------------------------------------------------------------------------------------------------------
  Filename       : FindIndexesUsedByStoredProc_TableViews_NestedSPs.sql
  Purpose        : To troubleshoot a stored proc. Scans all dependent objects for indexes to check fragmentation
  Schedule       : NONE
  Date           : 08-May-2019
  Author         : Sundaresh Malikayil, Sherbaz Mohamed
  Version        : 1
  SQL Version    : 2008, 2008 R2, 2012, 2014, 2016, 2017

  Important --arks:	
  INPUT          : @spName
  VARIABLE       : @spName
  PARENT         : NONE
  CHILD          : NONE
  NOTE           : Set value for @spName

  Version History:
  7-May-2019	Sundar	Initial version
  7-May-2019	Sherbaz	Scan Views for Indexes
  8-May-2019	Sherbaz	Scan nested stored procs
  8-May-2019	Sundar	Identified a bug in sp_depends logic
  8-May-2019	Sherbaz	Replaced sp_depends logic with sql_expression_dependencies
-------------------------------------------------------------------------------------------------------------------------------------------
 Usage:
	set the stored proc name for variable @spName

-------------------------------------------------------------------------------------------------------------------------------------------
*/
SET NOCOUNT ON; 
SET ANSI_WARNINGS OFF

DECLARE @spName SYSNAME,
		@recordcount int

SET @spName = 'stored procname' -- Set stored proc name here to analyse


IF OBJECT_ID('tempdb..#Fragmentation', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #Fragmentation



IF OBJECT_ID('tempdb..#table', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #table
 
  
IF OBJECT_ID('tempdb..#Filtedtable', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #Filtedtable

IF OBJECT_ID('tempdb..#Procs', 'U') IS NOT NULL
/*Then it exists*/
drop table #Procs

CREATE TABLE #Fragmentation
(
SerialId int identity,
objectname sysname,
indexname  sysname,
avgFragmentation_in_percent float
)


CREATE TABLE #Filtedtable 
(
SerialID INT IDENTITY,
objectname sysname null,
objectype sysname null
)

create table #Procs
(
	id int identity,
	objectname sysname null,
	scanned bit default 0
)

CREATE TABLE #table 
(
id int identity,
objectname sysname null,
objectype  sysname null,
scanned bit default 0
)


insert into #Procs(objectname) values (@spname)

--select * from #Procs


-- Scan for all nested SPs
while (select count(objectname) from #Procs where scanned = 0) > 0
 begin
 
 select top 1 @spname = objectname from #Procs where scanned = 0

 insert into #Procs(objectname)
 SELECT referenced_entity_name
	FROM sys.sql_expression_dependencies AS sed
	INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
	join sys.objects o2 on o2.object_id=object_id(referenced_entity_name)
	WHERE referencing_id = OBJECT_ID(@spname) and o2.type = 'P'
	and o.object_id is not null
 --select   (s6.name+ '.' + o1.name)
 -- from  sys.objects  o1  
 --  ,master.dbo.spt_values v2  
 --  ,sysdepends  d3  
 --  ,master.dbo.spt_values u4  
 --  ,master.dbo.spt_values w5 --11667  
 --  ,sys.schemas  s6  
 -- where  o1.object_id = d3.depid  
 -- and  o1.type = substring(v2.name,1,2) collate catalog_default and v2.type = 'O9T'  
 -- and  u4.type = 'B' and u4.number = d3.resultobj  
 -- and  w5.type = 'B' and w5.number = d3.readobj|d3.selall  
 -- and  d3.id = object_id(@spname)  
 -- and  o1.schema_id = s6.schema_id
 -- and (s6.name+ '.' + o1.name) not in (select objectname from #Procs)
 -- and deptype < 2 and substring(v2.name, 5, 66) = 'stored procedure'

  update #Procs set scanned = 1 where objectname = @spname
 
 end

select * from #Procs
update #Procs set scanned = 0


-- Scan for all tables and views
while (select count(objectname) from #Procs where scanned = 0) > 0
begin

	select top 1 @spname = objectname from #Procs where scanned = 0

	INSERT #TABLE (objectname,objectype)
	SELECT referenced_entity_name,o2.type
	FROM sys.sql_expression_dependencies AS sed
	INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
	join sys.objects o2 on o2.object_id=object_id(referenced_entity_name)
	WHERE referencing_id = OBJECT_ID(@spname) and o2.type in ('U', 'V')
	and o.object_id is not null

	update #Procs set scanned = 1 where objectname = @spname
end


select * from #Table order by objectname

 
INSERT #Filtedtable (objectname, objectype)
SELECT DISTINCT objectname, objectype FROM #table

select * from #Filtedtable

select @recordcount=count(1) from #Filtedtable

--This will get the list of all Indexes used by a given stored proc
DECLARE @C INT, @objectname sysname, @objectype sysname, @cmdText varchar(8000) = NULL, @refDB varchar(500), @refSchema varchar(100), @viewName varchar(500)
SET @C= 1

print '/*'
WHILE @C <= @recordcount
BEGIN

	SELECT  @objectname=objectname, @objectype = objectype FROM #Filtedtable 
	WHERE SerialID = @C

	print ''
	if @objectype = 'V'
	begin
		SELECT @refDB=referenced_database_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(@objectname);
		print 'use ' + @refDB
	end
	else
	begin
		print 'use ' + db_name()
	end
	print 'GO'
	print 'sp_spaceused ' +  @objectname + ' '   
	print 'UPDATE STATISTICS ' +  @objectname + ' '
	print 'GO'
	print ''
	SET @C = @C + 1
END 

print '*/'

 --Find the fragmentation of the indexes

 SET @C = 1

print 'IF OBJECT_ID(''tempdb..#Fragmentation'', ''U'') IS NOT NULL
	/*Then it exists*/
	drop table #Fragmentation

	CREATE TABLE #Fragmentation
	(
	SerialId int identity,
	dbname sysname,
	objectname sysname,
	indexname  sysname null,
	avgFragmentation_in_percent float,
	page_count bigint
	)'
 
WHILE @C <= @recordcount
BEGIN

	SELECT  @objectname=objectname, @objectype=objectype FROM #Filtedtable 
	WHERE SerialID = @C
	
	if @objectype ='V'
	begin

		SELECT @refDB=referenced_database_name, @refSchema = referenced_schema_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(@objectname);
		--set @viewName=
		print '
		INSERT #Fragmentation (dbname, objectname,indexname,avgFragmentation_in_percent, page_count)
		SELECT '''+@refDB+''','''+@refSchema+'.'+@objectname+''',name, avg_fragmentation_in_percent, a.page_count
		FROM '+@refDB+'.sys.dm_db_index_physical_stats (DB_ID('''+@refDB+'''), object_id('''+@refDB+'.'+@refSchema+'.'+@objectname+'''), NULL, NULL, NULL) as a
		join '+@refDB+'.sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id and b.type <> 0'
	end
	else
	begin
		print '
		INSERT #Fragmentation (dbname, objectname,indexname,avgFragmentation_in_percent, page_count)
		SELECT db_name(),'''+@objectname+''',name, avg_fragmentation_in_percent, a.page_count
		FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('''+@objectname+'''), NULL, NULL, NULL) as a
		join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id and b.type <> 0'
	end
	SET @C = @C + 1
END 

print '

--select ''USE ''+dbname+''; ALTER INDEX [''+indexname+''] ON [''+objectname+''] REBUILD WITH (MAXDOP = 4, FILLFACTOR = 80, ONLINE = ON);'', avgFragmentation_in_percent, page_count FROM #Fragmentation where avgFragmentation_in_percent > 10

select ''USE ''+dbname+''; ALTER INDEX [''+indexname+''] ON [''+objectname+''] REBUILD WITH (MAXDOP = 4, FILLFACTOR = 80, ONLINE = ON(WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = SELF)));'', avgFragmentation_in_percent, page_count FROM #Fragmentation where avgFragmentation_in_percent > 10
'
select * from #Fragmentation

Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.