SQL Server Grouping – Grouping Sessions Using DATETIME Stamp Across Days

group bysql serversql server 2014t-sql

I am working with data which has a session id value which gets recycled over time (asp session ids from IIS to be exact).

I am trying to give them a sequence so that each instance of an ASP_SESSION_ID don't get grouped together.

For example, the data would look like this. Same session id, used in Aug 2016, then again in March 2017.

DTTM                        SESSION_ID
2016-08-29 14:24:28.450     297692378
2017-04-13 23:54:53.760     297692378
2017-04-13 23:59:53.477     297692378
2017-04-14 00:04:52.897     297692378
2017-04-14 00:04:53.790     297692378

At first I thought to just group on the date (at the DAY level), but for the example above, notice how the 2nd instance of the session id spans across midnight. That would cause a 3rd group, when really it's the same session.

So if I could rank them properly, it would be:

DTTM                        SESSION_ID      RANK
2016-08-29 14:24:28.450     297692378       1
2017-04-13 23:54:53.760     297692378       2
2017-04-13 23:59:53.477     297692378       2
2017-04-14 00:04:52.897     297692378       2
2017-04-14 00:04:53.790     297692378       2

Here, ASP_SESSION_ID should be considered a new instance of a session when > 20 minutes passed since the last request.

So, how can I group by or rank the same ASP_SESSION_ID differently as they get re-used over time? e.g. if the next request by that ASP_SESSION_ID is > 20 minutes from the last, group it/rank it differently?

I am just not sure how to attack the problem.

Here are some statements to generate the data above:

CREATE TABLE #TEST
(
DTTM DATETIME, 
SESSION_ID INT
)

INSERT INTO #TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378

Best Answer

CREATE TABLE TEST
(
DTTM DATETIME, 
SESSION_ID INT
)

INSERT INTO TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378 union
select '2017-04-14 00:44:53.790', 297692378
GO

First I've added a new record just to check it runs for a more than 20 min.

select '2017-04-14 00:44:53.790', 297692378

Then I've added a new column called RANK to store the final result.

ALTER TABLE TEST ADD [RANK] int;

GO

I've used LAG() window function to calculate DATEDIFF between current and next row.

SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST
GO

DTTM                | SESSION_ID | DIF_MIN
:------------------ | ---------: | ------:
29/08/2016 14:24:28 |  297692378 |       0
13/04/2017 23:54:53 |  297692378 |  327450
13/04/2017 23:59:53 |  297692378 |       5
14/04/2017 00:04:52 |  297692378 |       5
14/04/2017 00:04:53 |  297692378 |       0
14/04/2017 00:44:53 |  297692378 |      40

Then I've used a CURSOR just to calculate RANK field. Basically it acumulates minutes between records, until it hits 20 or more minutes.

DECLARE @dttm datetime,
        @session_id int,
        @diff_min int,
        @acm_diff int,
        @rank int,
        @last_dttm datetime;

SET @diff_min = 0;
SET @acm_diff = 0;
SET @rank = 0;
SET @last_dttm = NULL;

DECLARE curMin CURSOR FAST_FORWARD  FOR
SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST

OPEN curMin;
FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @last_dttm IS NULL OR @acm_diff + @diff_min > 20
    BEGIN
        SET @rank = @rank + 1;
        SET @acm_diff = 0;
    END
    ELSE
    BEGIN
        SET @acm_diff = @acm_diff + @diff_min;
    END
    
    UPDATE TEST
    SET    [RANK] = @rank
    WHERE  DTTM = @dttm
    AND    SESSION_ID = @session_id;
    
    SET @last_dttm = @dttm;
    
    FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;
END

CLOSE curMin;

SELECT   DTTM, SESSION_ID,
         DATEDIFF(minute, DTTM, COALESCE(LEAD(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM)) DIF_MIN,
         [RANK]
FROM     TEST 
ORDER BY DTTM, SESSION_ID;
GO

DTTM                | SESSION_ID | DIF_MIN | RANK
:------------------ | ---------: | ------: | ---:
29/08/2016 14:24:28 |  297692378 |  327450 |    1
13/04/2017 23:54:53 |  297692378 |       5 |    2
13/04/2017 23:59:53 |  297692378 |       5 |    2
14/04/2017 00:04:52 |  297692378 |       0 |    2
14/04/2017 00:04:53 |  297692378 |      40 |    2
14/04/2017 00:44:53 |  297692378 |       0 |    3

dbfiddle here

Related Solutions

SQL Server – Can a Temporary Table Be Shared Across Sessions?

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them.

Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as global.

As for putting a suffix on the table name when creating it, you're wasting your time, as SQL Server does that anyway.

CREATE TABLE #temp (Name VARCHAR(20));

USE tempdb;
GO

SELECT name FROM sys.tables WHERE name LIKE '#temp%';

For me, this returns

#temp_______________________________________________________________________________________________________________00000000004C

However, as Aaron Bertrand pointed out in this answer:

if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure.

In this case, you could be sharing the temp table between a top-level process and a number of different procedures that it calls within that process. This of course could lead to data corruption, depending on how you defined and constructed your temporary table.

Sql-server – What does the “reads” column in sys.dm_exec_sessions actually indicate

My understanding has always been that reads is only physical (i.e. from disk) and logical_reads is only from the Buffer Pool (i.e. from memory). I did a quick test with a smaller table that only has 2 data pages and 3 pages total, and what I am seeing seems to confirm those two definitions.

One thing that is probably giving you bad results is that you are not clearing out the memory. You should run the following between tests to force it to reload from disk:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

My test setup was just the following:

CREATE TABLE dbo.ReadTest (Col1 CHAR(7500) DEFAULT (' '));
INSERT INTO dbo.ReadTest (Col1) VALUES (DEFAULT), (DEFAULT);

I then ran the following:

SELECT reads, logical_reads FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
SELECT * FROM dbo.ReadTest;

(Yes, I was testing in the same session that I was running the DMV in, but that didn't skew the results for the reads field, and if nothing else, was at least consistent if it did contribute to the logical_reads field.)

For testing I would run the DBCC command and then the two SELECT queries. Then I would see a jump in both the reads and logical_reads fields. I would run the SELECT queries again and sometimes I would see an additional jump in reads.

After that, I would run the two SELECT queries many times and the reads would remain the same while the logical_reads went up by 4 every single time.

I would then start over with running the DBCC and see that same pattern. I did this quite a few times and the numbers reported were consistent across all of the test runs.

More info:

I am also testing on SQL Server 2012, SP2 - 64 bit (11.0.5343).

The following DBCC commands we have both tried and seen no effect:

DBCC FREESYSTEMCACHE('ALL');
DBCC FREEPROCCACHE;
DBCC FREESESSIONCACHE;

Most of the time DBCC DROPCLEANBUFFERS does work, but I occasionally see that it is still in the Buffer Pool. Odd.

When I:

DBCC DROPCLEANBUFFERS: The reads go up by 24 and logical_reads go up by 52.
Run SELECT [Col1] FROM dbo.ReadTest; again: The reads do not go up, but logical_reads go up by 6.
Add a space to the query text and re-run: The reads do not go up, but the logical_reads go up by 52 (just like right after the DBCC DROPCLEANBUFFERS).

It would appear that the 52 logical reads accounts for plan generation and the results, which implies that the plan generation caused the additional 46 logical reads. But the physical reads do not go up again and yet it is the same 52 logical reads as it was when it did need to also do the physical reads, hence logical_reads does not include the physical reads. I am just making this point clear, whether or not it was being stated or implied in the Question.

BUT, one behavior I did notice that throws off (at least a little) using the existence of the table's data pages in sys.dm_os_buffer_descriptors: it gets reloaded by some other process. If you DROPCLEANBUFFERS and check immediately, then it should be gone. But wait a few minutes and it shows up again, but this time without all of the data pages. In my test, the table has 1 IAM page and 4 data pages. All 5 pages are in the buffer pool after I do the SELECT. But when it gets reloaded by some other process, it is just the IAM page and 1 data page. I thought it might be SSMS IntelliSense, but I removed all references to that object name in my query tab and it still gets reloaded.

Best Answer

Related Solutions

SQL Server – Can a Temporary Table Be Shared Across Sessions?

Sql-server – What does the “reads” column in sys.dm_exec_sessions actually indicate

Related Question