Custom report: Top SQL per machine

FormerMember
FormerMember

Hi,

New to Thwack, new to Ignite (now know as Database Performance Analyzer).

I'm looking for a way to create a custom report that shows me the top SQL over a period of time for a single machine.

Something like Custom Report - Top SQL for a Machine | thwack, only that is an example for Oracle SQL and I'm looking for Microsoft SQL.

As I have no idea where to begin, I'm looking for some help.

Top Replies

  • FormerMember
    FormerMember over 6 years ago in reply to FormerMember +1

    Oh ok glimbeek, so you're looking for any queries that execute from AP001 onto DB001 which we are monitoring, but AP001 does not house the DB itself and is not being individually monitored either.

  • FormerMember
    FormerMember over 6 years ago in reply to FormerMember +1

    Hi glimbeek,

    I edited the post above yours to contain the fixed syntax, thanks! I've since then got SQL installed on my local instance and did a little more querying. Is this closer to what you were looking…

  • FormerMember
    0 FormerMember over 6 years ago in reply to FormerMember

    I had to change your code a bit into the following:

    SET @sql =

    N'SELECT ' +

      @id + N',

      ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME,

      SUM(SW.QP/100) TIMESECS,

      ST.ST SQLTEXT ' +

    'FROM ignite.CONSW_' + @id + ' SW LEFT OUTER JOIN ignite.CON_SQL_NAME N ON SW.IZHO = N.HASH ' +

    'LEFT OUTER JOIN ignite.CONM_' + @ID + ' MM ON SW.PWMY = MM.ID ' +

    'LEFT OUTER JOIN ignite.CONST_' + @id + ' ST ON SW.IZHO = ST.H ' +

    'INNER JOIN ignite.CONU_' + @id + ' U ON SW.XCUW = U.ID ' +

    'WHERE SW.D BETWEEN ' +

      'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

      'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

      ' AND MM.NAME =  ''' + @APPLICATIONSERV + ''''

    I ended up executing the following query to use the procedure:

    USE [ignite_repository]

    GO

    DECLARE    @return_value int

    EXEC    @return_value = [ignite].[TopSQLforMSSQLMachine]

            @FROM_DATE = N'2013-01-01',

            @TO_DATE = N'2014-08-10',

            @APPLICATIONSERV = N'AP001',

            @MACHINENAME = N'DB001'

    SELECT    'Return Value' = @return_value

    GO

    Which results in the following error:

    Msg 8120, Level 16, State 1, Line 2

    Column 'ignite.CON_SQL_NAME.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    If I print the @sql in the stored procedure it returns the following SQL:

    SELECT 4,

      ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME,

      SUM(SW.QP/100) TIMESECS,

      ST.ST SQLTEXT FROM ignite.CONSW_4 SW LEFT OUTER JOIN ignite.CON_SQL_NAME N ON SW.IZHO = N.HASH LEFT OUTER JOIN ignite.CONM_4 MM ON SW.PWMY = MM.ID LEFT OUTER JOIN ignite.CONST_4 ST ON SW.IZHO = ST.H INNER JOIN ignite.CONU_4 U ON SW.XCUW = U.ID WHERE SW.D BETWEEN CONVERT(DATETIME,'2013-01-01', 101) AND CONVERT(DATETIME,'2014-08-10', 101)  AND MM.NAME =  'AP001'

  • FormerMember
    0 FormerMember over 6 years ago in reply to FormerMember

    Hi glimbeek,

    I edited the post above yours to contain the fixed syntax, thanks! I've since then got SQL installed on my local instance and did a little more querying. Is this closer to what you were looking for?

    IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopSQLforMSSQLMachine') = 1

    DROP PROC TopSQLforMSSQLMachine

    GO

    -- TopSQLforMSSQLMachine PROC

    --

    -- RUN AS THE IGNITE REPO OWNER AND STORE UNDER THE IGNITE REPOS PROGRAMMABILITY STORED PROCS

    -- PARMS '@FROM_DATE', '@TO_DATE', '@APPLICATIONSERV, '@MACHINENAME'

    -- The @MACHINENAME can be found from the following query: Select CONN_HOST from cond.

    -- The @APPLICATIONSERV can also be found from the following query: SELECT * FROM CONM_XX WHERE XX = ID of DBID you are investigating. (DBID = SELECT ID FROM COND)

    --

    -- EXAMPLE:

    --

    -- EXEC TopSQLforMSSQLMachine '2014-12-23 00:00','2014-12-30 00:00', 'APPLICATIONSERVERHERE', 'MACHINENAMEHERE'

    -- EXEC TopSQLforMSSQLMachine '2014-07-01 00:00','2014-07-19 00:00', 'JSHAULIS-LT', 'JSHAULIS-LT\THEDB'

    CREATE PROC [TopSQLforMSSQLMachine]

    (@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @APPLICATIONSERV VARCHAR(50), @MACHINENAME VARCHAR(50))

    AS

    BEGIN

    DECLARE @HostGatheredCursorData TABLE(

    Tablename nvarchar(100),

    Hash_or_Name nvarchar(100),

    Timesecs nvarchar(100),

    Sqltext nvarchar(max)

    );

    DECLARE @ID nvarchar(1000);

    DECLARE MachineGatherCursor CURSOR FAST_FORWARD FOR

    SELECT ID FROM COND WITH (NOLOCK)

    WHERE CONN_HOST = @MACHINENAME

    OPEN MachineGatherCursor;

    FETCH NEXT FROM MachineGatherCursor INTO @ID;

    DECLARE @SQL NVARCHAR(MAX);

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQL =

    N'SELECT ' +

      @ID + N',

      ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME,

      SUM(SW.QP/100) TIMESECS,

      ST.ST SQLTEXT ' +

    'FROM CONSW_' + @ID + ' SW LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +

    'LEFT OUTER JOIN CONM_' + @ID + ' MM ON SW.PWMY = MM.ID ' +

    'LEFT OUTER JOIN CONST_' + @ID + ' ST ON SW.IZHO = ST.H ' +

    'INNER JOIN CONU_' + @ID + ' U ON SW.XCUW = U.ID ' +

    'WHERE SW.D BETWEEN ' +

      'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

      'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

      ' AND MM.NAME =  ''' + @APPLICATIONSERV + '''' +

      ' GROUP BY N.NAME, SW.IZHO,  ST.ST

      ORDER BY SUM(SW.QP/100) DESC'

    INSERT INTO @HostGatheredCursorData

    EXEC (@SQL)

    FETCH NEXT FROM MachineGatherCursor INTO @ID;

    END

    CLOSE MachineGatherCursor;

    DEALLOCATE MachineGatherCursor;

    SELECT

      COND.NAME AS "SQLInstance",

      HGCD.Hash_Or_Name,

      HGCD.TimeSecs,

      HGCD.SQLText

    FROM @HostGatheredCursorData AS HGCD

    INNER JOIN COND ON COND.ID = HGCD.TABLENAME;

    END

  • FormerMember
    0 FormerMember over 6 years ago in reply to FormerMember

    Hi Shaulis,

    I think so...

    But now what? How do I, for instance, get the result of the stored procedure in my Ignite Report section?

  • FormerMember
    0 FormerMember over 6 years ago in reply to FormerMember

    Hi glimbeek,

    The DPA software itself won't house the custom reports but does have a lot of stock reports you can utilize that were commonly requested, we tried to focus on performance analysis and tuning but we are always open to suggestions! If allowing customizable reporting in the application itself is something you'd like to see, similar to our alerting structure, you can place a feature request here where other customers can vote and discuss the feature as well. This allows our community to help us shape and guide the tool to meet the needs of the ever changing IT industry.

    Database Performance Analyzer Feature Requests

    For setting up the custom report, I would recommend to create a stored procedure while scheduling a job to utilize SQL Mail for the report. I've seen other customers use SSRS or SSIS as well, but if you're more into System Administration, I've also seen people use Powershell and Blat. It all just depends on how you want the report ran or what the end goal of is. Whether you'd like to run it manually or perhaps you'd like to create an ETL job to load the results of the procedure and store / create your own analytics on the metrics collected. It would be pretty cool to hear what you decide to do with it though as I'm sure it could help give other customers ideas on how to utilize this procedure in their environment.