Download the code iconThere are many types of tasks that involve handling date and time intervals and computing all sorts of counts related to those intervals. This article is the third in a series on the subject. This month's focus is computing the maximum count of concurrent sessions during every fixed interval (e.g., hourly) within a certain input period. (I'll explain what this actually means shortly.)

Related: Intervals and Counts, Part 1 andIntervals and Counts, Part 2

I'll use input data similar to what I used in the previous articles in the series. Listing 1 contains the code to create the Sessions table and populate it with a small set of sample data to check the validity of the solution.

  1. SET NOCOUNT ON;
  2. USE tempdb;
  3. IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL DROP TABLE dbo.Sessions;
  4. IF OBJECT_ID(N'dbo.Apps', N'U') IS NOT NULL DROP TABLE dbo.Apps;
  5. CREATE TABLE dbo.Apps
  6. (
  7. app VARCHAR(10) NOT NULL,
  8. CONSTRAINT PK_Apps PRIMARY KEY(app)
  9. );
  10. CREATE TABLE dbo.Sessions
  11. (
  12. keycol INT NOT NULL,
  13. app VARCHAR(10) NOT NULL,
  14. starttime DATETIME2(0) NOT NULL,
  15. endtime DATETIME2(0) NOT NULL,
  16. CONSTRAINT PK_Sessions PRIMARY KEY(keycol),
  17. CONSTRAINT CHK_Sessios_et_st CHECK(endtime > starttime)
  18. );
  19. CREATE UNIQUE INDEX idx_start ON dbo.Sessions(app, starttime, keycol) INCLUDE(endtime);
  20. CREATE UNIQUE INDEX idx_end ON dbo.Sessions(app, endtime, keycol) INCLUDE(starttime);
  21. -- Code to fill Sessions table with small set of sample data
  22. TRUNCATE TABLE dbo.Sessions;
  23. TRUNCATE TABLE dbo.Apps;
  24. INSERT INTO dbo.Apps(app) VALUES('app1'),('app2'),('app3');
  25. INSERT INTO dbo.Sessions(keycol, app, starttime, endtime) VALUES
  26. (2, 'app1', '20130212 08:30:00', '20130212 10:30:00'),
  27. (3, 'app1', '20130212 08:30:00', '20130212 08:45:00'),
  28. (5, 'app1', '20130212 09:00:00', '20130212 09:30:00'),
  29. (7, 'app1', '20130212 09:15:00', '20130212 10:30:00'),
  30. (11, 'app1', '20130212 09:15:00', '20130212 09:30:00'),
  31. (13, 'app1', '20130212 10:30:00', '20130212 14:30:00'),
  32. (17, 'app1', '20130212 10:45:00', '20130212 11:30:00'),
  33. (19, 'app1', '20130212 11:00:00', '20130212 12:30:00'),
  34. (23, 'app2', '20130212 08:30:00', '20130212 08:45:00'),
  35. (29, 'app2', '20130212 09:00:00', '20130212 09:30:00'),
  36. (31, 'app2', '20130212 11:45:00', '20130212 12:00:00'),
  37. (37, 'app2', '20130212 12:30:00', '20130212 14:00:00'),
  38. (41, 'app2', '20130212 12:45:00', '20130212 13:30:00'),
  39. (43, 'app2', '20130212 13:00:00', '20130212 14:00:00'),
  40. (47, 'app2', '20130212 14:00:00', '20130212 16:30:00'),
  41. (53, 'app2', '20130212 15:30:00', '20130212 17:00:00'),
  42. (61, 'app3', '20130212 08:00:00', '20130212 08:30:00'),
  43. (62, 'app3', '20130212 08:00:00', '20130212 09:00:00'),
  44. (63, 'app3', '20130212 09:00:00', '20130212 09:30:00'),
  45. (64, 'app3', '20130212 09:30:00', '20130212 10:00:00');

What's a bit different in this article is the definition of the indexes idx_start and idx_end. The keylists are the same as in the previous articles in this series, but this time the indexes have an INCLUDE clause. The index idx_start includes the endtime column, and the index idx_end includes the starttime column. The new indexes work better for the solutions covered in this article.

Listing 2 contains the code to create the helper function GetNums, which generates a sequence of integers in the requested range. Listing 2 also contains the code to fill the Sessions table with a large set of sample data for performance testing purposes.

  1. IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
  2. GO
  3. CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
  4. AS
  5. RETURN
  6. WITH
  7. L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
  8. L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  9. L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  10. L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  11. L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  12. L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  13. Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
  14. FROM L5)
  15. SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  16. FROM Nums
  17. ORDER BY rownum;
  18. GO
  19. -- Code to fill Sessions table with large set of sample data
  20. TRUNCATE TABLE dbo.Sessions;
  21. TRUNCATE TABLE dbo.Apps;
  22. DECLARE
  23. @numrows AS INT = 2000000, -- total number of rows
  24. @numapps AS INT = 100; -- number of applications
  25. INSERT INTO dbo.Apps WITH(TABLOCK) (app)
  26. SELECT 'app' + CAST(n AS VARCHAR(10)) AS app
  27. FROM dbo.GetNums(1, @numapps) AS Nums;
  28. INSERT INTO dbo.Sessions WITH(TABLOCK)
  29. (keycol, app, starttime, endtime)
  30. SELECT
  31. ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS keycol,
  32. D.*,
  33. DATEADD(
  34. second,
  35. 1 + ABS(CHECKSUM(NEWID())) % (20*60),
  36. starttime) AS endtime
  37. FROM
  38. (
  39. SELECT
  40. 'app' + CAST(1 + ABS(CHECKSUM(NEWID())) % @numapps AS VARCHAR(10)) AS app,
  41. DATEADD(
  42. second,
  43. 1 + ABS(CHECKSUM(NEWID())) % (30*24*60*60),
  44. '20130101') AS starttime
  45. FROM dbo.GetNums(1, @numrows) AS Nums
  46. ) AS D;

The Challenge

The challenge this month is to compute the maximum count of concurrent sessions during every fixed interval within an input period, for each application separately. For example, suppose that the input period starts at 8:00 a.m. February 12, 2013, and ends at 5:00 p.m. February 12, 2013. For each application and hour within the input period, you need to compute the maximum count of concurrent sessions. For the sake of this article, if one session ends exactly when another starts, the two aren't considered concurrent. Also suppose that a session starts at 3:00 p.m. and ends at 4:00 p.m. This session is considered active during the fixed hourly interval starting at 3:00 p.m. and ending at 4:00 p.m. but not active during the fixed interval starting at 4:00 p.m. and ending at 5:00 p.m. Figure 1 depicts graphically what you need to compute for the small set of sample data generated by the code in Listing 1.

Graph Showing Counts During Fixed Intervals

Figure 2 presents the result, sorted by app and startime for clarity—but you can assume that there's no presentation ordering requirement in the task. It's fine if your solution returns the result sorted differently, as long as you return the same result set.

  1. app starttime endtime cnt
  2. ----- -------------------- -------------------- ----
  3. app1 2013-02-12 08:00:00 2013-02-12 09:00:00 2
  4. app1 2013-02-12 09:00:00 2013-02-12 10:00:00 4
  5. app1 2013-02-12 10:00:00 2013-02-12 11:00:00 2
  6. app1 2013-02-12 11:00:00 2013-02-12 12:00:00 3
  7. app1 2013-02-12 12:00:00 2013-02-12 13:00:00 2
  8. app1 2013-02-12 13:00:00 2013-02-12 14:00:00 1
  9. app1 2013-02-12 14:00:00 2013-02-12 15:00:00 1
  10. app1 2013-02-12 15:00:00 2013-02-12 16:00:00 0
  11. app1 2013-02-12 16:00:00 2013-02-12 17:00:00 0
  12. app2 2013-02-12 08:00:00 2013-02-12 09:00:00 1
  13. app2 2013-02-12 09:00:00 2013-02-12 10:00:00 1
  14. app2 2013-02-12 10:00:00 2013-02-12 11:00:00 0
  15. app2 2013-02-12 11:00:00 2013-02-12 12:00:00 1
  16. app2 2013-02-12 12:00:00 2013-02-12 13:00:00 2
  17. app2 2013-02-12 13:00:00 2013-02-12 14:00:00 3
  18. app2 2013-02-12 14:00:00 2013-02-12 15:00:00 1
  19. app2 2013-02-12 15:00:00 2013-02-12 16:00:00 2
  20. app2 2013-02-12 16:00:00 2013-02-12 17:00:00 2
  21. app3 2013-02-12 08:00:00 2013-02-12 09:00:00 2
  22. app3 2013-02-12 09:00:00 2013-02-12 10:00:00 1
  23. app3 2013-02-12 10:00:00 2013-02-12 11:00:00 0
  24. app3 2013-02-12 11:00:00 2013-02-12 12:00:00 0
  25. app3 2013-02-12 12:00:00 2013-02-12 13:00:00 0
  26. app3 2013-02-12 13:00:00 2013-02-12 14:00:00 0
  27. app3 2013-02-12 14:00:00 2013-02-12 15:00:00 0
  28. app3 2013-02-12 15:00:00 2013-02-12 16:00:00 0
  29. app3 2013-02-12 16:00:00 2013-02-12 17:00:00 0

The Solution

The solution I describe in this article involves using a helper table called TimeStamps. You populate the table with the start times of all fixed intervals within the period that you need to support. The code in Listing 3 creates the TimeStamps table and populates it with the start times of all fixed hourly intervals within the year 2013, assuming that you need to support the period spanning the year 2013.

  1. -- DDL for TimeStamps table
  2. IF OBJECT_ID(N'dbo.TimeStamps', N'U') IS NOT NULL
  3. DROP TABLE dbo.TimeStamps;
  4. CREATE TABLE dbo.TimeStamps
  5. (
  6. ts DATETIME2(0) NOT NULL
  7. CONSTRAINT PK_TimeStamps PRIMARY KEY
  8. );
  9. GO
  10. -- Populate TimeStamps table
  11. DECLARE
  12. @s AS DATETIME2(0) = '20130101', -- inclusive
  13. @e AS DATETIME2(0) = '20140101'; -- exclusive
  14. INSERT INTO dbo.TimeStamps WITH (TABLOCK) (ts)
  15. SELECT DATEADD(hour, n-1, @s) AS ts
  16. FROM dbo.GetNums(1, DATEDIFF(hour, @s, @e)) AS Nums;
  17. GO

Listing 4 contains the solution code provided just for the application app3, for simplicity. After I explain the solution for a single application, I'll explain how to apply it to all applications. I'll explain the steps in the solution one CTE at a time, starting with the CTE C1.

  1. DECLARE
  2. @app AS VARCHAR(10) = 'app1',
  3. @starttime AS DATETIME2(0) = '20130212 08:00:00', -- inclusive
  4. @endtime AS DATETIME2(0) = '20130212 17:00:00'; -- exclusive
  5. WITH C1 AS
  6. (
  7. SELECT
  8. endtime AS ts,
  9. -1 AS increment,
  10. 1 AS ord
  11. FROM dbo.Sessions
  12. WHERE app = @app
  13. AND starttime < @endtime
  14. AND endtime >= @starttime
  15. UNION ALL
  16. SELECT
  17. starttime AS ts,
  18. 1 AS increment,
  19. 2 AS ord
  20. FROM dbo.Sessions
  21. WHERE app = @app
  22. AND starttime < @endtime
  23. AND endtime >= @starttime
  24. UNION ALL
  25. SELECT
  26. ts,
  27. 0 AS increment,
  28. 3 AS ord
  29. FROM dbo.TimeStamps
  30. WHERE ts >= @starttime AND ts < @endtime
  31. ),
  32. C2 AS
  33. (
  34. SELECT
  35. ts,
  36. increment,
  37. SUM(increment) OVER(ORDER BY ts, ord
  38. ROWS UNBOUNDED PRECEDING) AS cnt
  39. FROM C1
  40. ),
  41. C3 AS
  42. (
  43. SELECT
  44. DATEADD( hour, DATEDIFF(hour, @starttime, ts), @starttime ) AS starttime,
  45. cnt
  46. FROM C2
  47. WHERE increment <> -1
  48. )
  49. SELECT
  50. starttime,
  51. DATEADD(hour, 1, starttime) AS endtime,
  52. MAX(cnt) AS mx
  53. FROM C3
  54. GROUP BY starttime;

Similar to the techniques I described in "Intervals and Counts, Part 1" and "Intervals and Counts, Part 2," the solution starts by generating a chronological sequence of events. This happens in the body of the CTE C1. Previously, the chronological sequence of events was constructed by unifying only start and end events. But this time there's a twist—you need to return information about every fixed hourly interval even if no events (start or end) took place during the interval. Recall that you mark start events with a +1 increment, because such events increase the count of active sessions, and end events with a -1 increment, because such events decrease the count. To address the twist in our task, the solution adds dummy entries with the start times of all fixed hourly intervals, marked with a 0 increment so that the event won't affect the count. This way, the solution guarantees that there will be at least one event during every fixed interval.

To correctly handle cases in which different types of events happen at the same point in time, the queries in C1 assign a different ordering value (call it ord) to each type of event. End events are assigned with the ord value 1 because they need to be considered first, start events with 2, and dummy events with 3.

Remember that you're supposed to consider only intervals that fall in the specified input period; therefore, the first two queries in the body of C1 include the filter starttime < @endtime AND endtime >= @starttime (inclusive of @starttime and exclusive of @endtime), and the third query includes the filter ts >= @starttime AND ts < @endtime.

The second step in the solution is implemented by the CTE C2. The query in the body of the CTE queries C1 and computes a running total of the increment column (remember, 1s, -1s, and 0s) based on the order of ts and ord, naming the resulting column cnt:

  1. SELECT
  2. ts,
  3. increment,
  4. SUM(increment) OVER(ORDER BY ts, ord
  5. ROWS UNBOUNDED PRECEDING) AS cnt
  6. FROM C1

For all events—including dummy events—the query computes the current count after the event. By assigning a lower ord value for end events compared to start events, you ensure that if a session ends exactly when another starts, you don't consider them as concurrent.

The next step is implemented by the CTE C3. Here's the query in the body of the CTE:

  1. SELECT
  2. DATEADD( hour, DATEDIFF(hour, @starttime, ts), @starttime )
  3. AS starttime,
  4. cnt
  5. FROM C2
  6. WHERE increment <> -1

Because the maximum count during each hour will necessarily fall either after a start event or after a dummy event (in case no start event happens during the hour), the query filters only events that aren't end events. The query also computes for each timestamp the respective start of the hour using the expression DATEADD( hour, DATEDIFF(hour, @starttime, ts), @starttime ), naming the resulting column starttime.

Finally, the outer query groups the rows from C3 by startime (the start of the hour), returning the start of the hour, end of the hour, and maximum count per group, like so:

  1. SELECT
  2. starttime,
  3. DATEADD(hour, 1, starttime) AS endtime,
  4. MAX(cnt) AS mx
  5. FROM C3
  6. GROUP BY starttime;

The solution in Listing 4 is applied to a single input application. Next, you encapsulate this logic in an inline table function that accepts the application and period as inputs. Listing 5 provides the definition of such a function, called IntervalCounts.

  1. IF OBJECT_ID(N'dbo.IntervalCounts', N'IF') IS NOT NULL DROP FUNCTION dbo.IntervalCounts;
  2. GO
  3. CREATE FUNCTION dbo.IntervalCounts
  4. (
  5. @app AS VARCHAR(10),
  6. @starttime AS DATETIME2(0),
  7. @endtime AS DATETIME2(0)
  8. ) RETURNS TABLE
  9. AS
  10. RETURN
  11. WITH C1 AS
  12. (
  13. SELECT
  14. endtime AS ts,
  15. -1 AS increment,
  16. 1 AS ord
  17. FROM dbo.Sessions
  18. WHERE app = @app
  19. AND starttime < @endtime
  20. AND endtime >= @starttime
  21. UNION ALL
  22. SELECT
  23. starttime AS ts,
  24. 1 AS increment,
  25. 2 AS ord
  26. FROM dbo.Sessions
  27. WHERE app = @app
  28. AND starttime < @endtime
  29. AND endtime >= @starttime
  30. UNION ALL
  31. SELECT
  32. ts,
  33. 0 AS increment,
  34. 3 AS ord
  35. FROM dbo.TimeStamps
  36. WHERE ts >= @starttime AND ts < @endtime
  37. ),
  38. C2 AS
  39. (
  40. SELECT
  41. ts,
  42. increment,
  43. SUM(increment) OVER(ORDER BY ts, ord
  44. ROWS UNBOUNDED PRECEDING) AS cnt
  45. FROM C1
  46. ),
  47. C3 AS
  48. (
  49. SELECT
  50. DATEADD( hour, DATEDIFF(hour, @starttime, ts), @starttime ) AS starttime,
  51. cnt
  52. FROM C2
  53. WHERE increment <> -1
  54. )
  55. SELECT
  56. starttime,
  57. DATEADD(hour, 1, starttime) AS endtime,
  58. MAX(cnt) AS mx
  59. FROM C3
  60. GROUP BY starttime;
  61. GO

To apply the function to all applications from the Apps table, you use the APPLY operator. Here's an example using the small set of sample data, with an input period that starts at 8:00 a.m. February 12, 2013, and ends at 5:00 p.m.February 12, 2013:

  1. SELECT A.app, IC.*
  2. FROM dbo.Apps AS A
  3. CROSS APPLY dbo.IntervalCounts(A.app, '20130212 08:00:00',
  4. '20130212 17:00:00') AS IC;

This code generates the desired result set shown earlier in Figure 2.

After the tables are populated with the large set of sample data, here's an example for using the function with an input period that starts January 1, 2013 (inclusive), and ends February 1, 2013 (exclusive):

  1. SELECT A.app, IC.*
  2. FROM dbo.Apps AS A
  3. CROSS APPLY dbo.IntervalCounts(A.app, '20130101', '20130201')
  4. AS IC;

Figure 3 shows the plan for this query (using SQL Sentry's Plan Explorer).

Serial Plan for Solution in Listing 5

This query took 13 seconds to complete on my machine. This plan isn't bad, but there's room for improvement in a couple of areas. First, as you can see, SQL Server decided to use a serial plan. Without a doubt, parallelism can help here to shorten the run time. Second, because the grouping is based on a computation, the optimizer doesn't rely on index order to compute the aggregate; instead, it uses a hash match aggregate. Hashing requires a memory grant for the query execution (as does sorting, when the optimizer sorts before using a stream aggregate).

In "Intervals and Counts, Part 2," I showed a trick that causes the optimizer to use a parallel plan. You add an artificial cross join to the query, like so:

  1. DECLARE @n AS BIGINT = 1;
  2. SELECT A.app, IC.*
  3. FROM dbo.Apps AS A
  4. CROSS APPLY dbo.IntervalCounts(A.app, '20130101', '20130201')
  5. AS IC
  6. CROSS JOIN (SELECT TOP (@n) * FROM dbo.Apps) AS B
  7. OPTION (OPTIMIZE FOR (@n = 100));

This time I got the parallel query plan that Figure 4 shows.

Parallel Plan for Solution in Listing 5

With the parallel plan, the query finished in 7 seconds on my system. As for avoiding sorting and hashing, there's a way to achieve this as well—which I discuss in the next section.

Avoiding Sorting and Hashing

To avoid sorting and hashing for the aggregate computation, you need three things:

  1. Computed columns called fstartime and fendtime holding the floored (to the beginning of the hour) starttime and endtime values, respectively:
    1. ALTER TABLE dbo.Sessions ADD
    2. fstarttime AS
    3. DATEADD( hour,
    4. DATEDIFF(hour, CONVERT(DATETIME2(0),
    5. '19000101', 112), starttime),
    6. CONVERT(DATETIME2(0), '19000101', 112) ),
    7. fendtime AS
    8. DATEADD( hour,
    9. DATEDIFF(hour, CONVERT(DATETIME2(0),
    10. '19000101', 112), endtime),
    11. CONVERT(DATETIME2(0), '19000101', 112) );
  2. Indexes similar to idx_start and idx_end, but with the column holding the floored time preceding the column holding the original time. Namely, fstarttime before startime, and fendtime before endtime:
    1. CREATE UNIQUE INDEX idx_fstart
    2. ON dbo.Sessions(app, fstarttime, starttime, keycol)
    3. INCLUDE(endtime);
    4. CREATE UNIQUE INDEX idx_fend
    5. ON dbo.Sessions(app, fendtime, endtime, keycol)
    6. INCLUDE(starttime);
  3. Adjust the implementation of the IntervalCounts function using the new fstarttime and fendtime columns, as Listing 6 shows.
  1. IF OBJECT_ID(N'dbo.IntervalCounts', N'IF') IS NOT NULL DROP FUNCTION dbo.IntervalCounts;
  2. GO
  3. CREATE FUNCTION dbo.IntervalCounts
  4. (
  5. @app AS VARCHAR(10),
  6. @starttime AS DATETIME2(0),
  7. @endtime AS DATETIME2(0)
  8. ) RETURNS TABLE
  9. AS
  10. RETURN
  11. WITH C1 AS
  12. (
  13. SELECT
  14. fendtime AS fts,
  15. endtime AS ts,
  16. -1 AS increment,
  17. 1 AS ord
  18. FROM dbo.Sessions
  19. WHERE app = @app
  20. AND fstarttime < @endtime
  21. AND fendtime >= @starttime
  22. UNION ALL
  23. SELECT
  24. fstarttime AS fts,
  25. starttime AS ts,
  26. 1 AS increment,
  27. 2 AS ord
  28. FROM dbo.Sessions
  29. WHERE app = @app
  30. AND fstarttime < @endtime
  31. AND fendtime >= @starttime
  32. UNION ALL
  33. SELECT
  34. ts AS fts,
  35. ts,
  36. 0 AS increment,
  37. 3 AS ord
  38. FROM dbo.TimeStamps
  39. WHERE ts >= @starttime AND ts < @endtime
  40. ),
  41. C2 AS
  42. (
  43. SELECT
  44. fts,
  45. increment,
  46. SUM(increment) OVER(ORDER BY fts, ts, ord
  47. ROWS UNBOUNDED PRECEDING) AS cnt
  48. FROM C1
  49. )
  50. SELECT
  51. fts AS starttime,
  52. DATEADD(hour, 1, fts) AS endtime,
  53. MAX(cnt) AS mx
  54. FROM C2
  55. WHERE increment <> -1
  56. GROUP BY fts;
  57. GO

You add the floored columns to the SELECT lists of the first two queries in the body of C1, naming the resulting column fts (for floored timestamp). As for the third query, which returns the dummy events by querying the TimeStamps table, the ts column is already the beginning of the hour, so you simply add it as the fts column.

Then, you entirely skip the step that computes the floored timestamps in the previous solution in Listing 5, because the fts column in the new solution in Listing 6 already contains the floored timestamps. The CTE C2 in the new solution handles the computation of the running total increment as the current count, only this time preceding ts by fts in the window order clause. This allows the optimizer to perform ordered scans of the indexes idx_fstart and idx_fend and rely on that order in the last step implemented by the outer query computing the maximum count for each hourly group.

Run the following code to test the new solution against the large set of sample data:

  1. SELECT A.app, IC.*
  2. FROM dbo.Apps AS A
  3. CROSS APPLY dbo.IntervalCounts(A.app, '20130101', '20130201') AS IC;

I got the plan that Figure 5 shows.

Plan for Solution in Listing 6

There are two interesting things to observe about this plan. First, there's no sorting or hashing used to compute the aggregate; instead, the optimizer uses a stream aggregate based on the existing order of the data from the indexes. Second, SQL Server chose a parallel plan without you needing to resort to any awkward tricks. The run time I got for this solution was 7 seconds on my system—slightly disappointing, since it's the same run time that I got for the previous solution after applying the trick that resulted in a parallel plan. However, this plan still has advantages: You don't need any tricks to obtain a parallel plan, and the query doesn't need a special memory grant for sorting and hashing.

Part 4 Still To Come

In this article, I continued my coverage of querying tasks involving intervals and counts. This time, the task was to compute the maximum count of concurrent sessions within each fixed hourly interval. I demonstrated a solution that generates a chronological sequence of events, including dummy events ensuring that the result will contain a slot for every hour in the input period. Then, I computed the counts using a window function that applies a running total aggregate. The first solution generated a serial plan. Using a trick that applies an artificial cross join, I managed to obtain a parallel plan. Finally, I showed a solution in which you add computed columns holding floored timestamps, indexes that include those columns, and a revision of the solution query to involve those columns. This allowed for a parallel plan without resorting to any awkward tricks and without any sorting and hashing to compute the maximum count aggregate for each hourly group.

Next month, I'll continue my coverage of tasks involving intervals and counts. Stay tuned for Part 4!