SQL Server – How to Select 1 Row Per Day for Each Database

sql serversql-server-2012t-sql

I have a table with over 60 million rows. Snapshots taken at multiple times a day for all databases across all instances. I am trying to reduce the rows in my select by only returning 1 snapshot per database/instance per day.

My current query using (SQL Server 2012):

SELECT  instance_name ,
        DU.database_name ,
        DU.collection_time ,
        DU.dbsize ,
        DU.logsize ,
        DU.reservedpages ,
        DU.usedpages
FROM    [core].[snapshots_internal] AS [SI]
LEFT JOIN [core].[source_info_internal] AS [SII] ON [SI].[source_id] = [SII].[source_id]
INNER JOIN [core].[snapshot_timetable_internal] AS [STI] ON [SI].[snapshot_time_id] = [STI].[snapshot_time_id]
LEFT JOIN [snapshots].[disk_usage] AS [DU] ON [SI].[snapshot_id] = [DU].[snapshot_id]
WHERE   instance_name LIKE '%\DEV'
        AND DU.[database_name] NOT LIKE 'tempdb'
        AND DU.[database_name] NOT LIKE 'master'
        AND DU.[database_name] NOT LIKE 'model'
        AND DU.[database_name] NOT LIKE 'msdb'
GROUP BY SII.instance_name
ORDER BY SII.instance_name ,
         DU.database_name ,
         DU.collection_time;

Returns the following:

2015-08-09 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 05:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 11:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 17:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 23:30:04.0000000 +00:00  665600  64960   484170
2015-08-11 05:30:03.0000000 +00:00  665600  64960   484170
2015-08-11 11:30:02.0000000 +00:00  665600  64960   484170
2015-08-11 12:30:28.0000000 +00:00  665600  64960   484170
2015-08-11 17:30:03.0000000 +00:00  665600  64960   484170
2015-08-11 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 05:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 11:30:02.0000000 +00:00  665600  64960   484170

I would like to obtain:

2015-08-09 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 23:30:04.0000000 +00:00  665600  64960   484170
2015-08-11 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 11:30:02.0000000 +00:00  665600  64960   484170

After some research on other stack posts I was able to find similar questions but the responses still had me returning multiple rows per day. So any suggestions is greatly appreciated.

EX of something i tried:

select collection_time, du.snapshot_id, du.database_name 
from   snapshots.disk_usage du 
where du.collection_time in 
(
   select max(du.collection_time) 
   from snapshots.disk_usage du 
   group by collection_time
)

Best Answer

Quite often you can use row_number() to select only certain rows of the data. so maybe something like this would work:

select * from (
  SELECT  
    instance_name ,
    DU.database_name ,
    DU.collection_time ,
    DU.dbsize ,
    DU.logsize ,
    DU.reservedpages ,
    DU.usedpages,
    row_number() over (
      partition by instance_name, DU.database_name, convert(date, DU.collection_time)
      order by DU.collection_time desc) as RN
    ...
) X
WHERE RN = 1
ORDER BY instance_name ,
         database_name ,
         collection_time;

Related Solutions

Sql-server – SQL Server 2012 Performance – Reads per second

The question itself is too nebulous. You asked how many Selects per second the database can handle? Well, can handle before what happens? What level of performance is acceptable? Do you need results returned in milliseconds, seconds, minutes? What is acceptable? How many rows are being returned from these Selects? What does the query plan look like?

Ultimately no one here is going to be able to tell you what you want to know. There are too many details you didn't tell us. But you can certainly find out for yourself. It's not at all difficult to stress test your database, to find this out for yourself. If you are a developer, you could easily write a C# program that creates threads that performs these Selects. It would be quite easy to measure performance as you gradually increase the number of threads. If you are not a developer, I have to believe there are stress test apps you can buy, or find open source, that will enable you to measure this. If determining a rough idea of how many users your database can accept is that important, you need to be doing this anyway.

SQL Server – How to Cache Statistic Data

How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT 
    UserID, 
    LoginDate = CONVERT(DATE, LoginDate), 
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd' 
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;

Best Answer

Related Solutions

Sql-server – SQL Server 2012 Performance – Reads per second

SQL Server – How to Cache Statistic Data

Related Question