I'm not hundred percent sure on how to program in T-SQL.
I wanted to run a stored proc over 500 databases in production.
and, the stored procedures will detect the expensive stored proc on each user databases :
p.name AS [SP Name],
qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_writes AS [TotalLogicalWrites],
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
AND qs.total_logical_reads > 0
ORDER BY qs.total_logical_reads DESC;
How can I can an automation to run or execute my stored proc on all 500 databases and store the output into a table?
I created a table called TopExpensiveSPs.
create table dbo.TopExpensiveSPs (
ID INT IDENTITY(1,1),
SP_Name VARCHAR(150) NOT NULL,
TotalLoficalReads INT NOT NULL,
TotalLogicalWrite smallINT NOT NULL,
Execution_Count smallInt NOT NULL,
Total_ElapseTime INT NOT NULL,
AvgElapseTime INT NOT NULL,
Date Date NOT NULL
Would someone please help.
Thank you in advance for all your kind helps.