Results 1 to 4 of 4

Thread: Performance Analysis Script

  1. #1
    Join Date
    Feb 2004
    Location
    Massachusetts
    Posts
    32

    Thumbs up Performance Analysis Script

    The first step in preparing and maintaining a OLTP database for query and better performance is diagnosis. Maintaining statistics on a regular basis is important but a heavily updated database needs more emaphasis on Statistics.

    The following script (stored procedure) is useful in getting the information what tables are updated and how many rows are updated since last stats update. The script also generates script to run on the databases to update statistics for the tables which have out-of-date statistics.

    /**********Begin SQL Script************/

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO



    ALTER PROCEDURE sp_dba_ShowMe_TableStats @dbname sysname=NULL, @option char(1) = NULL
    AS
    --- Author: Sravan Kasarla
    --- Created: 10/01/2003
    BEGIN
    Declare @what char(1),
    @qry varchar(2000)
    set @what = @option


    IF @what = 'I'
    Begin
    set @qry = ' Use ' + @dbName +' SELECT substring(o.name,1,50) AS [table name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows Modified]
    FROM SYSOBJECTS o JOIN SYSINDEXES i
    ON o.id = i.id
    WHERE i.rowmodctr > 0 and o.xtype = ''U''
    ORDER BY i.rowmodctr DESC'
    exec (@qry)
    End

    ELSE IF @what = 'A'
    Begin
    Print space(10)+' Run the Update Statistics on the following Tables'
    SET @qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @dbName + ' SELECT Distinct ''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
    JOIN SYSINDEXES i ON o.id = i.id
    WHERE i.rowmodctr > 0 and o.xtype = ''U''
    ---ORDER BY O.NAME'
    exec (@qry)
    End

    ELSE
    Begin
    Print space(10)+'Please pass in the right parameters : DBName and option "I" for Information or "A" Action"'
    PRINT '-------------------------------------------------------------------------------------------------------------------------------------'
    set @qry = ' Use ' + @dbName +' SELECT substring(o.name,1,50) AS [table name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows Modified]
    FROM SYSOBJECTS o JOIN SYSINDEXES i
    ON o.id = i.id
    WHERE i.rowmodctr > 0 and o.xtype = ''U''
    ORDER BY i.rowmodctr DESC'
    exec (@qry)
    End
    END
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    /*************End of Script************/



    **************************
    Usage of the Procedure: **
    **************************

    Run the following script on master database to catalog this stored procedure. The usage of the procedure is

    The sp has 2 input paramaters '@dbname' and @option.

    @dbname - Name of the database for which you want to update stats
    @option - Information - "I" OR Action "A"

    Exec sp_dba_ShowMe_TableStats '@dbname','@action'

    Example:

    EXEC sp_dba_ShowMe_TableStats 'pubs','I'

    Results:
    ---------------------------------------
    Table Name Index Name Rows Modified
    PJR_Sales PJR_Sales 1704364
    PJR_Sales PJR_Sales 1704364
    PJR_Sales PJR_Sales 1704364
    EMPSTAT_DIM EMPSTAT_DIM 177522
    EMPSTAT_DIM EMPSTAT_DIM 177233
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    PJR_Sales PJR_Sales 147092
    EMPSTAT_DIM EMPSTAT_DIM 30795
    PJR_Sales PJR_Sales 29444
    newTitles newTitles 18

    Executing the proc with "A" will fetch the below results

    EXEC sp_dba_ShowMe_TableStats 'pubs','A'

    Results
    ---------------------------------
    UPDATE STATISTICS EMPSTAT_DIM GO
    UPDATE STATISTICS newTitles GO
    UPDATE STATISTICS PJR_Sales GO
    ..

    Use it carefully and I hope it helps tuning and maintaining databases.

    Good Luck

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Hi sravan,
    Please contribute your script here,
    http://www.databasejournal.com/scripts/

  3. #3
    Join Date
    Feb 2004
    Posts
    1

    arggh..

    1) the script that is offered has syntactcal problems in it.. probably from the upload process. This is very unprofessional to allow scripts to be 'published' without checking them out at least once. I **** realize ***** you have a disclaimer, but don't use that disclaimer for a shoddy 'product' or lack of time on anyone's part. Check the script out. It doesn't run.

    2) Even the author didn't do much error checking.. the columns reported aren't correct. He is reporting the object name twice instead of the object name and index name. When small problems like this are so obvious by simply using it, what other logic errors are possibly hidden?

    This comes down to.. I have no faith in the code if there is obvious misses in simple things like column names.

    Sure.. I can fix this myself, but newbies are reading/using this stuff and are getting even more confused.

    DBA-type code needs to have a LOT more 'attention to detail' that this..

  4. #4
    Join Date
    Feb 2004
    Location
    Massachusetts
    Posts
    32
    Sorry for the typo if it happended while error checking. I appreciate your input and your point is well taken. To clarify this SP is intended to provide Table name, all the Indexes which have been updated since the last stats/index update. The corrected script is pasted here under. I will work with DB Journal staff to fix the same in the "Scripts" section. I am using this in production and as I use this for generating script of the "tables" to be updated. That comes out accurate with parameter option "A".

    ****************************************
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    CREATE PROCEDURE sp_dba_ShowMe_TableStats @dbname sysname=NULL, @option char(1) = NULL
    AS
    --- Author: Sravan Kasarla
    BEGIN
    Declare @what char(1),
    @qry varchar(2000)
    set @what = @option


    IF @what = 'I'
    Begin
    set @qry = ' Use ' + @dbName +' SELECT substring(o.name,1,50) AS [table name], substring(i.name,1,50) AS [Index Name], i.rowmodctr AS [Rows Modified]
    FROM SYSOBJECTS o JOIN SYSINDEXES i
    ON o.id = i.id
    WHERE i.rowmodctr > 0 and o.xtype = ''U''
    ORDER BY i.rowmodctr DESC'
    exec (@qry)
    End

    ELSE IF @what = 'A'
    Begin
    Print space(10)+' Run the Update Statistics on the following Tables'
    SET @qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @dbName + ' SELECT Distinct ''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
    JOIN SYSINDEXES i ON o.id = i.id
    WHERE i.rowmodctr > 0 and o.xtype = ''U''
    ---ORDER BY O.NAME'
    exec (@qry)
    End

    ELSE
    Begin
    Print space(10)+'Please pass in the right parameters : DBName and option "I" for Information or "A" Action"'
    PRINT '-------------------------------------------------------------------------------------------------------------------------------------'
    End
    END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    ***************************************
    Last edited by skasarla; 02-17-2004 at 02:46 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •