August 3, 2017 at 4:26 pm
OK, I'm trying to modify a query I've used to generate a simple graph showing the transactions/sec for the various databases on my servers, to one that will let me show multiple graph lines (multi-select several different databases)
My problem is, I've spent most of the day puttering around trying to get my query to work, to feed the report dataset, with no luck. It works fine with a single database, but when I set it up to use multiple databases, one of the databases in the results always winds up with 0 for the time difference. Whichever one comes up first in the results is fine.
OK, doing some putzing just now, I think I know *why* it isn't working, but I'm not sure how to fix it.
Here's the various scripts to reproduce:
Problem query:--Why the heck does it not work???
declare @startdate datetime
, @enddate datetime
set @startdate = (select convert(date, getdate()))
set @enddate = (select convert(date, dateadd(dd, 1, getdate())))
select src.[timestamp]
, src.[instance_name]
, src.cntr_value
, ((cntr_value - (case
when (lag(cntr_value) over(order by id)) is null then 0
else (lag(cntr_value) over(order by id))
end))
/ convert(decimal(16,4), (datediff(ss, [timestamp], lag([timestamp]) over(order by id)))) * -1) as [Transactions_per_sec]
from dbo.transactsec as src
where [timestamp] >= @startdate
and [timestamp] < @enddate
and [instance_name] in ('master', 'dbatools') --Put one DB name here, works. Put two, 2nd results for the time diff are ALL 0
order by [instance_name], [timestamp]
And this shows the 0 results, which it looks like the LAG is simply grabbing the preceding value regardless of the instance_name:declare @startdate datetime
, @enddate datetime
set @startdate = (select convert(date, getdate()))
set @enddate = (select convert(date, dateadd(dd, 1, getdate())))
select src.[timestamp]
, src.[instance_name]
, src.cntr_value
, cntr_value - (case
when (lag(cntr_value) over(order by id)) is null then 0
else (lag(cntr_value) over(order by id))
end)
, convert(decimal(16,4), (datediff(ss, [timestamp], lag([timestamp]) over(order by id)))) * -1 as [Transactions_per_sec]
from dbo.transactsec as src
where [timestamp] >= @startdate
and [timestamp] < @enddate
and [instance_name] in ('master', 'dbatools') --Put one DB name here, works. Put two, 2nd results for the time diff are ALL 0
order by [instance_name], [timestamp]
Create table and populate:--Data table
CREATE TABLE [dbo].[TransactSec](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Timestamp] [datetime] NULL,
[Object_name] [char](25) NULL,
[counter_name] [char](25) NULL,
[instance_name] [char](25) NULL,
[cntr_value] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Sample data
--Please note, the forum software turned all the colonDs' into smileys, so I added spaces.
insert into dbo.transactsec values ([timestamp], [object_name], [counter_name], [instance_name], [cntr_value])
('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','tempdb',288)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','model',164)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','DBATools',202)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','mssqlsystemresource',12)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','msdb',208)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','_Total',1092)
,('2017-08-03 07:05:21.487','SQLServer: Databases','Transactions/sec','master',218)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','tempdb',492)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','DBATools',205)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','msdb',273)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','_Total',1392)
,('2017-08-03 07:10:01.053','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','tempdb',496)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','DBATools',206)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','msdb',285)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','_Total',1409)
,('2017-08-03 07:10:10.523','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','tempdb',499)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','DBATools',207)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','msdb',297)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','_Total',1425)
,('2017-08-03 07:10:20.617','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','tempdb',502)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','DBATools',208)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','msdb',309)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','_Total',1441)
,('2017-08-03 07:10:30.673','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','tempdb',505)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','DBATools',209)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','msdb',321)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','_Total',1457)
,('2017-08-03 07:10:40.750','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','tempdb',508)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','DBATools',210)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','msdb',333)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','_Total',1473)
,('2017-08-03 07:10:50.853','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','tempdb',511)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','model',165)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','DBATools',211)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','mssqlsystemresource',24)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','msdb',345)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','_Total',1489)
,('2017-08-03 07:11:00.923','SQLServer: Databases','Transactions/sec','master',233)
,('2017-08-03 07:11:10.993','SQLServer: Databases','Transactions/sec','tempdb',514)
,('2017-08-03 07:11:10.993','SQLServer : Databases','Transactions/sec','model',165)
,('2017-08-03 07:11:10.993','SQLServer: Databases','Transactions/sec','DBATools',212)
August 3, 2017 at 4:37 pm
Biggest thing is the lack of a PARTITION BY in the OVER clause.
With multiple instance_names you'll want to do that; otherwise you're just getting whatever the previous counter was in order of ID, no matter the instance_name with which it was associated.
Also, for some housekeeping on the sample data, the VALUES keyword is in the wrong spot in the script, so it doesn't run as written, and you seem to have two rows for each timestamp/instance_name combination.
Cheers!
EDIT: Also, I'd probably order by timestamp, and not by ID. Seems more clear about the intent, and it's at least possible in principle for the order of ID not to match the order of timestamp.
August 3, 2017 at 5:23 pm
Jacob Wilkins - Thursday, August 3, 2017 4:37 PMBiggest thing is the lack of a PARTITION BY in the OVER clause.With multiple instance_names you'll want to do that; otherwise you're just getting whatever the previous counter was in order of ID, no matter the instance_name with which it was associated.
Also, for some housekeeping on the sample data, the VALUES keyword is in the wrong spot in the script, so it doesn't run as written, and you seem to have two rows for each timestamp/instance_name combination.
Cheers!
EDIT: Also, I'd probably order by timestamp, and not by ID. Seems more clear about the intent, and it's at least possible in principle for the order of ID not to match the order of timestamp.
First up, thank you!
It looks like PARTITION BY did the trick, it now looks like this will work the way I'm hoping it will. Tomorrow, to putter around with getting it into the report.
As for the sorting, I first sort by the instance_name (database name, actually, from sys.dm_os_performance_counters) then the timestamp. The duplicate values are actually for different DBs.
As for the broken insert, well, mea culpa.
Once again, thanks!
August 3, 2017 at 6:17 pm
Glad to help!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply