August 7, 2013 at 5:25 am
Hi guys,
I was gonna put this in the TSQL sub-section but the little intro says it's for discussing "changes" to TSL in 10.0~
I'm looking at sys.dm_os_performance_counters and attempting to derive values from their accumulating columns (e.g. transactions/sec). My plan is as follows:
2 tables (main & temp) with columns TRANSACTIONS_BASE and TRANSACTIONS_DIFF.
Data is inserted into the BASE then an update statement should update the DIFF value (from null). The problem is the main table may have hundreds of records for each database & I only want the most recent base value for each database. Having seen that guy's (sorry forgot your name) RBAR signature repeatedly I wanted to try & avoid doing a looping update but my SQL attempts arent getting close :f
It will update the columns but it looks at every row in the main table so in the example below it would do 4 updates instead of the 2 it should (overwriting values):
UPDATE dbo.PERF_COUNTERS_TEMP SET
TRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE)/60/10
FROM PERF_COUNTERS_TEMP a INNER JOIN (
SELECT instance, db_name, COLL_DATE, TRANSACTIONS_BASE
from dbo.PERF_COUNTERS c) b
on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME
Anyone know how to only take into account the most recent entries in perf_counters for comparisson?
Below is some code if anyone is willing to help & wants to replicate the scenario
CREATE TABLE perf_counters (
instance varchar(50), db_name varchar(50), coll_date smalldatetime,
transactions_base bigint, transactions_diff smallint);
GO
CREATE TABLE perf_counters_temp (
instance varchar(50), db_name varchar(50), coll_date smalldatetime,
transactions_base bigint, transactions_diff smallint);
GO
INSERT INTO perf_counters values ('SRV01','DB01',getdate(),1000,null);
GO
INSERT INTO perf_counters values ('SRV01','DB02',getdate(),4000,null);
GO
-- wait a while
INSERT INTO perf_counters values ('SRV01','DB01',getdate(),1200,null);
GO
INSERT INTO perf_counters values ('SRV01','DB02',getdate(),4150,null);
GO
-- wait a while
INSERT INTO perf_counters_temp values ('SRV01','DB01',getdate(),1250,null);
GO
INSERT INTO perf_counters_temp values ('SRV01','DB02',getdate(),4270,null);
GO
Edit: It's easiest to remove the /60/10 for testing since the time intervals won't be there (so it's not always 0).
So with the example above the DML would update the perf_counters temp rows to be:
SRV01, DB01, *date*, 1250, 50
SRV01, DB02, *date*, 4270, 120
Since it would ignore the 1000/4000 rows as they are older.
August 7, 2013 at 7:14 am
OK you can ignore this thread. I fixed it by using row_number() in the subquery like:
row_number() over (partition by instance, db_name order by coll_date desc) rnk
Should have remembered it =/ Used similar in the past =(
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply