Custom report: Top SQL per machine

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

  • 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.

  • 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…

  • glimbeek, we'll look into this and see what we can come up with.

    You're right that the code in that query is for Oracle - should have been under custom Oracle reports...

    We'll get that moved over also.

  • Hi glimbeek,

    I believe this code should do what you're requesting. The structure for our repository when registering new instances follows the syntax of TABLENAME_DBID. You can potentially script a COALESCE function to do a similar job, but I found the most luck with a cursor and temp table to store the values. You could also play with the type of temp table depending on your environment and alter it from variable to local or global, but may have to add in some drops or truncates depending on how you want to set up the report. Please let us know if this didn't answer your question.

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

    DROP PROC TopSQLforMSSQLMachine

    GO

    -- TopSQLforMSSQLMachine PROC

    --

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

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

    -- EXAMPLE:

    --

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

    ALTER PROC [dbo].[TopSQLforMSSQLMachine]

    (@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @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 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) ' +

    'GROUP BY ISNULL(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

  • Hi shaulis,

    I finally got around to try this out.

    I executed to above statement(s) on the Ignite repository and it ran successfully. Then I tried to  execute the stored procedure per example:

    EXEC TopSQLforMSSQLMachine '2014-12-23 00:00','2014-12-30 00:00','MyMachineName'

    and I tried:

    EXEC TopSQLforMSSQLMachine '2014-01-01 00:00','2014-05-14 00:00','MyMachineName'

    I run into the following issue:

    Msg 208, Level 16, State 1, Procedure TopSQLforMSSQLMachine, Line 31

    Invalid object name 'cond'.

    What am I doing wrong?

  • Hi Glimbeek,

    Could you please confirm the stored procedure was created in your repository under your Ignite database? I was able to re-create the same error by executing the total script as shown in my first post in the master or my dev DB instance. The script is looking to run in the local Ignite DB instance since it's not specifying the schema so it can remain dynamic for anyone. Example: The stored procedure is running and stored under my DB Ignite_Jonathan.Shaulis. (My laptop has an ignite instance installed and I'm running the stored procedure there.) Let me know if that works for you.

  • Its located in: SQL Server Management Studio, in the Object Explorer:

    - ServerName

         - Databases

              - ignite_repository

                   - Programmability

                        - Stored Procedures

                             - dbo.TopSQLforMSSQLMachine

    The EXEC statement is also run on the ignite_repository database.

  • Glimbeek,

    You could try modifying the stored procedure and adding the dbo schema to the table names in the query. If that still doesn't work, would you mind if I converted this thread into a ticket for you and we could hop on a go to meeting to get the report working? It may help if I could see what you're seeing and troubleshoot in real time.

  • Hi shaulis,

    Got around to giving this another go and changing the dbo schema did the trick. Thanks for that emoticons_happy.png

    Now it works, I'm not sure what I'm looking at...

    I ran the following query: EXEC TopSQLforMSSQLMachine '2014-01-01 01:01','2014-05-14 01:01','MACHINENAMEHERE'

    I was hoping I could swap out the 'MACHINENAMEHERE' part for the machine that a user is working on. But If I do that I get no results. It only seems to work when I swap out the 'MACHINENAMEHERE' for my SQLInstance. Not really what I was looking for.

    It looks like we miss understood each other when I posted what I was looking for. Or am I using the procedure incorrectly?

  • Hi Glimbeek,

    The query should be returning results based on the CONN_HOST field in COND which should be your machine's name. In the event you want to modify this, take a look at this part of the stored procedure:

    select id from cond with (nolock)

    where CONN_HOST = @MACHINENAME

    That's where the magic is for delimiting the data pulled. If you change the <where CONN_HOST> portion, you can set it to whatever field you would prefer in COND. Do a SELECT * FROM COND sometime and look at the columns. Changing it to MACHINE_NAME or IP_ADDRESS are also good choices. Here's a few examples:

    select id from cond with (nolock)

    where MACHINE_NAME = @MACHINENAME

    select id from cond with (nolock)

    where IP_ADDRESS = @MACHINENAME

    Depending how you set up your environment, results could vary a little bit. Play with those adjustments and see if that works out better.

  • Hi shaulis,

    If I do a select * from [ignite].[COND] with (nolock)

    It returns the list of my database servers (4 results) which are listed in the "Database Instance" part of the "Ignite - Main" screen. Which unfortunately is not what I'm looking for.

    Apologies for not being clear enough, but I'm trying to get all the (or a top 15) SQL queries executed from a host. If that makes any sense...

    I'll try and paint a picture emoticons_happy.png

    Database server: DB001

    Several Citrix servers: CT001, CT002, CT003 on which users start a client which connects to the database server DB001.

    And a application server: AP001, same client runs on this server and also connects to the database server DB001. De AP001 runs batch jobs.

    What I'm looking for is a way to get a report of the top 15 most executed/heavy queries that are being run on the AP001 server.

  • 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.

    I have a question for you then, if you do a select * from COND to find the DBID (column ID) of DB001, what do you see when you do a select * from conm_XX (where XX is the ID of DB001 from COND)? I'm hoping you can see the name of AP001 in that list. If you can, than you can alter the query this way:

    ALTER PROC [dbo].[TopSQLforMSSQLMachine]

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

    AS

    BEGIN

    SET @sql =

    N'SELECT ' +

      @id + N',

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

      SUM(SW.QP/100) TIMESECS,

      ST.ST SQLTEXT ' +

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

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

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

    'INNER JOIN dbo.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)

    You may have to adjust the quotes a bit or possibly fully qualify the MM.NAME = portion of the where clause, I ended up typing those additions in Thwack vs SQL while my VM's are getting fixed up. Adding the CONM table and adding the computer name into the where clause should give you what you're looking for as long as the above statement about checking those tables rings true. Is that closer to what you were looking for? If not, would it be ok to get a go to meeting scheduled and then I can see your screen / instance and we can talk out what you're looking for in case I'm missing something? My email should be in my bio but we can be reached by support@confio.com too.