cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

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.

Labels (1)
0 Kudos
14 Replies
Level 9

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

0 Kudos

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?

0 Kudos

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.

0 Kudos

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.

0 Kudos

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.

0 Kudos

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?

0 Kudos

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.

0 Kudos

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.

0 Kudos

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.

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'

0 Kudos

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

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?

0 Kudos

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.

0 Kudos
Level 14

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.

0 Kudos