14 Replies Latest reply on Jul 24, 2014 9:19 AM by shaulis

    Custom report: Top SQL per machine

    glimbeek

      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.

        • Re: Custom report: Top SQL per machine
          mandevil

          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.

          • Re: Custom report: Top SQL per machine
            shaulis

            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

              • Re: Custom report: Top SQL per machine
                glimbeek

                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?

                  • Re: Custom report: Top SQL per machine
                    shaulis

                    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.

                      • Re: Custom report: Top SQL per machine
                        glimbeek

                        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.

                          • Re: Custom report: Top SQL per machine
                            shaulis

                            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.

                              • Re: Custom report: Top SQL per machine
                                glimbeek

                                Hi shaulis,

                                 

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

                                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?

                                  • Re: Custom report: Top SQL per machine
                                    shaulis

                                    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.

                                      • Re: Custom report: Top SQL per machine
                                        glimbeek

                                        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

                                         

                                        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.

                                          • Re: Custom report: Top SQL per machine
                                            shaulis

                                            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.

                                            1 of 1 people found this helpful
                                              • Re: Custom report: Top SQL per machine
                                                glimbeek

                                                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'

                                                  • Re: Custom report: Top SQL per machine
                                                    shaulis

                                                    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

                                                    1 of 1 people found this helpful
                                                      • Re: Custom report: Top SQL per machine
                                                        glimbeek

                                                        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?

                                                          • Re: Re: Custom report: Top SQL per machine
                                                            shaulis

                                                            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.