Query DPA database for daily Avg CPU per database?

jsql

Hello!

We're doing some research to migrate to Azure and would like to have the Avg CPU per Database per day for sizing requirements.  

Any idea on how to query the DPA database to pull this information?  

Instancename, Databasename, Avg CPU 

If not per database, per instance would suffice.  

THANKS! 

jaminsql

Per Instances is about all you would be able to get. I would look to the log term Metric tables CON_METRIC_DAY_X  of CON_METRIC_HOUR_X Where X would be some number or ID of the instance that you find in COND. 

This should get you a start. 

SELECT (SELECT NAME
        FROM   ignite.cond
        WHERE  id = 1) instance_name,
       mn.NAME,
       mh.d,
       mh.v_avg,
       mh.v_sum,
       mh.v_min,
       mh.v_max
FROM   ignite.con_metrics_names_1 mn
       INNER JOIN ignite.con_metrics_1 m
               ON m.metric_name_id = mn.id
       INNER JOIN ignite.con_metrics_day_1 mh
               ON m.id = mh.metrics_id
WHERE  mn.NAME = 'CPU Utilization' 

This is also a great resource with info on the tables in use for this data. 

https://support.solarwinds.com/SuccessCenter/s/article/DPA-Database-Metrics-Architecture?language=en_US

Note: I ran this on a SQL server repository where the schema was the some common "ignite" but, that isn't always the case and can also often be dbo 

You can also run something like this in a loop of dynamic SQL so you can fill in the ID's for the table names or even another metric. Note this isn't super fast running code but, should work. 

USE dpa_repo
GO
DECLARE @DBID NVARCHAR(3)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @METRIC_NAME NVARCHAR(255)
DECLARE @RESULTS TABLE 
( 
       instance_name VARCHAR(255)
       ,metric_name VARCHAR(255)
       ,[date] datetime
       ,metric_AVG int
	   ,metric_SUM int
	   ,metric_MIN int
	   ,metric_MAX int
)
Declare @name table 
(
       metric_name VARCHAR(255)
) 

--insert @name (metric_name) values('CustomMetric-7');
--insert @name (metric_name) values('Active Sessions');
--insert @name (metric_name) values('Batch Requests Per Sec');
--insert @name (metric_name) values('Blocked Sessions');
--insert @name (metric_name) values('Buffer Cache Hit Ratio');
--insert @name (metric_name) values('Buffer Cache Size');
--insert @name (metric_name) values('CPU Count');
--insert @name (metric_name) values('CPU Queue Length');
insert @name (metric_name) values('CPU Utilization');
--insert @name (metric_name) values('Disk Queue Length');
--insert @name (metric_name) values('Distinct Machines Total');
--insert @name (metric_name) values('Distinct Users Total');
--insert @name (metric_name) values('Instance CPU Utilization');
--insert @name (metric_name) values('Instance Wait Time');
--insert @name (metric_name) values('Log Bytes Flushed Per Sec');
--insert @name (metric_name) values('Log Flushes Per Sec');
--insert @name (metric_name) values('Memory Paging Rate');
--insert @name (metric_name) values('Memory Utilization');
--insert @name (metric_name) values('Page Life Expectancy');
--insert @name (metric_name) values('Physical I/O Rate');
--insert @name (metric_name) values('Physical Read Rate');
--insert @name (metric_name) values('Physical Write Rate');
--insert @name (metric_name) values('Plan Cache Size');
--insert @name (metric_name) values('Procedure Cache Hit Ratio');
--insert @name (metric_name) values('Response Time');
--insert @name (metric_name) values('Signal Waits Percent');
--insert @name (metric_name) values('SQL Compilations Per Sec');
--insert @name (metric_name) values('SQL Disk Read Latency');
--insert @name (metric_name) values('SQL Disk Write Latency');
--insert @name (metric_name) values('SQL Re-Compilations Per Sec');
--insert @name (metric_name) values('Total IO Wait');
--insert @name (metric_name) values('Total Read IO Wait');
--insert @name (metric_name) values('Total User Sessions');
--insert @name (metric_name) values('Total Write IO Wait');
--insert @name (metric_name) values('Transaction Rate');

DECLARE mCURSOR CURSOR 
       FOR select metric_name from @name
OPEN mCURSOR
FETCH NEXT FROM mCURSOR INTO @METRIC_NAME
@FETCH_STATUS = 0
BEGIN 
                     DECLARE dbCURSOR CURSOR
                     FOR SELECT ID from ignite.COND where DB_TYPE = 'SQL SERVER'

                     OPEN dbCursor
                           FETCH NEXT FROM dbCursor INTO @DBID
@FETCH_STATUS = 0
                     BEGIN
                     SET @SQL = 'select
                                                (Select name from ignite.cond where id = '+ @DBID + ') instance_name 
                                                , mn.name
												, mh.D
                                                , mh.V_AVG
												, mh.V_SUM
												, mh.V_MIN
												, mh.V_MAX
                                         from ignite.con_metrics_names_'+ @DBID +' mn
                                         inner join ignite.con_metrics_' + @DBID + ' m
                                         on m.metric_name_id = mn.id
										 inner join ignite.con_metrics_day_'+ @DBID + ' mh
                                         on m.id = mh.metrics_id
										 where mn.name = '''+ @METRIC_NAME + '''
                                         '
                                         insert into @RESULTS (instance_name ,metric_name, [date], metric_AVG, metric_SUM, metric_MIN, metric_MAX) 
                                         exec sp_executesql @SQL
                                         --SELECT @SQL
                                  FETCH NEXT FROM dbCursor INTO @DBID
                     END
                     CLOSE dbCURSOR
                     DEALLOCATE dbCURSOR
       FETCH NEXT FROM mCURSOR INTO @METRIC_NAME
END 
CLOSE mCURSOR
DEALLOCATE mCURSOR
select * from @RESULTS ORDER BY instance_name 




----- 

--Other fast code I did to build the inserts above 

--select 
--'insert @name (metric_name) values(''' + name + ''');'  as name from ignite.CON_METRICS_NAMES_27 where name not like 'custom%' and name not like 'USER'