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

  • Ed7

    SSCrazy Eights

    Points: 8352

    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


  • Lowell

    SSC Guru

    Points: 323377

    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: 3562

    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: 395449

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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