I‌ wanted to run a stored proc over 500 databases in production.

Question

I‌ wanted to run a stored proc over 500 databases in production.

Ed7

SSCrazy Eights

Points: 8519
More actions
March 11, 2019 at 2:44 pm

#377671

Hello Room,
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 :
SELECT
p.name AS [SP Name],
qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_writes AS [TotalLogicalWrites],
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_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;
GO
Questions:
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.
Best regards,
Edwin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

procedure stats is server wide, but your JOIN and WHERE statement limits it to the current database context.
a minor tweak to use built in finctions and you can get all the results from all databases

SELECT db_name(qs.database_id) AS DatabaseName, OBJECT_SCHEMA_NAME(qs.object_id,qs.database_id) AS [SP Schema], OBJECT_NAME(qs.object_id,qs.database_id) AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_writes AS [TotalLogicalWrites], qs.execution_count, qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.dm_exec_procedure_stats AS qs WHERE 1=1 --AND qs.database_id = DB_ID() AND qs.total_logical_reads > 0 ORDER BY qs.total_logical_reads DESC; GO GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 2

I know the question has been answered, and the solution in this case did not need to run a statement on every database. However, that functionality is sometimes needed, and there are several ways to get that done. I'm linking to an article on one person's solution because I thought it was good:
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Lowell's fix is awesome (as usual).

However, if you do find that you want to run something across all databases, or across all servers, I strongly recommend learning Powershell. Best of all, you don't have to learn everything because there are the DBA Tools that make all this easier.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning