5

The below script will query Ola's Maintenance Solution results table (CommandLog) and come up with a comparison on the bi-weekly reindex exec duration, for the last 4 weeks.

The way it looks right now, it's a quick hack to help me tune the maintenance windows. But I would like to remove the date hardcoding, so that I don't have to manually add every future weekend as a new JOIN.

Please provide a complete rewrite (dynamic SQL?) or just some hints on how to achieve this or some other useful features to be included. Ok to change the sproc and add extra output to the table. Using SQL 2016. If there's already a script out there that serves the purpose, happy to go with that one.

WITH t0 AS 
(
SELECT ObjectName, IndexName, IndexType
FROM Tools.dbo.CommandLog
WHERE 1=1
AND DatabaseName = 'testdb'
AND CommandType = 'ALTER_INDEX'
GROUP BY ObjectName, IndexName, IndexType
)

SELECT 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
,DATEDIFF(ss,t1.StartTime,t1.EndTime) as '20-40 01-06'
,DATEDIFF(ss,t2.StartTime,t2.EndTime) as '5-40 01-07'
,DATEDIFF(ss,t3.StartTime,t3.EndTime) as '20-40 01-13'
,DATEDIFF(ss,t4.StartTime,t4.EndTime) as '5-40 01-14'
,DATEDIFF(ss,t5.StartTime,t5.EndTime) as '20-40 01-20'
,DATEDIFF(ss,t6.StartTime,t6.EndTime) as '5-40 01-21'
,DATEDIFF(ss,t7.StartTime,t7.EndTime) as '20-40 01-27'
,DATEDIFF(ss,t8.StartTime,t8.EndTime) as '5-40 01-28'
FROM t0
LEFT JOIN Tools.dbo.CommandLog as t1 ON t0.ObjectName = t1.ObjectName AND t0.IndexName = t1.IndexName AND t1.StartTime BETWEEN '2018-01-06 00:30:00' AND '2018-01-06 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t2 ON t0.ObjectName = t2.ObjectName AND t0.IndexName = t2.IndexName AND t2.StartTime BETWEEN '2018-01-07 00:30:00' AND '2018-01-07 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t3 ON t0.ObjectName = t3.ObjectName AND t0.IndexName = t3.IndexName AND t3.StartTime BETWEEN '2018-01-13 00:30:00' AND '2018-01-13 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t4 ON t0.ObjectName = t4.ObjectName AND t0.IndexName = t4.IndexName AND t4.StartTime BETWEEN '2018-01-14 00:30:00' AND '2018-01-14 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t5 ON t0.ObjectName = t5.ObjectName AND t0.IndexName = t5.IndexName AND t5.StartTime BETWEEN '2018-01-20 00:30:00' AND '2018-01-20 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t6 ON t0.ObjectName = t6.ObjectName AND t0.IndexName = t6.IndexName AND t6.StartTime BETWEEN '2018-01-21 00:30:00' AND '2018-01-21 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t7 ON t0.ObjectName = t7.ObjectName AND t0.IndexName = t7.IndexName AND t7.StartTime BETWEEN '2018-01-27 00:30:00' AND '2018-01-27 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t8 ON t0.ObjectName = t8.ObjectName AND t0.IndexName = t8.IndexName AND t8.StartTime BETWEEN '2018-01-28 00:30:00' AND '2018-01-28 23:59:59'
WHERE 1=1
ORDER BY 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
  • 1
    Have you considered utilizing a calendar table? – LowlyDBA Jan 26 '18 at 18:54
  • I didn't, thank you for this, will look into it. Another idea is to query the execution times for the reindex job, should be faster than scanning the CommandLog. and more accurate, in case the weekend schedule changes or for unscheduled maintenance during the week. – Razvan Zoitanu Jan 26 '18 at 19:00
9

Calendar table

As LowlyDBA and Anti-weakpasswords have pointed out in their comments you can find more than one method to generate a calendar table:

For the sake of the question I'll generate a (basic) calendar table just to show you how to use it with your data, you'll find examples on-line that use more detailed fields (seconds, day of year, quarters, etc)

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, 
                                 DAY(@date), 
                                 DATEPART(weekday, @date), 
                                 DATENAME(weekday, @date));

    SET @date = DATEADD(day, 1, @date);
END

Accordingly with your query, it seems that you're interested on get results for Saturday and Sunday only. In this case you should filter Calendar table by day name or by day of week. Have a look at SET DATEFIRST if you want to filter by day of week.

SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday');
GO
cDate               | cDay | cDayOfWeek | cDayName
:------------------ | ---: | ---------: | :-------
06/01/2018 00:00:00 |    6 |          7 | Saturday
07/01/2018 00:00:00 |    7 |          1 | Sunday  
13/01/2018 00:00:00 |   13 |          7 | Saturday
14/01/2018 00:00:00 |   14 |          1 | Sunday  
20/01/2018 00:00:00 |   20 |          7 | Saturday
21/01/2018 00:00:00 |   21 |          1 | Sunday  
27/01/2018 00:00:00 |   27 |          7 | Saturday
28/01/2018 00:00:00 |   28 |          1 | Sunday  

Populating CommandLog table with random data

Now let me simulate a CommandLog table and populate it with 1000 random dates between Jan-01 and Jan-31.

CREATE TABLE CommandLog 
(
    ObjectName varchar(10), 
    IndexName varchar(10), 
    IndexType int, 
    StartTime datetime,
    EndTime datetime
);

DECLARE @step int = 1;
DECLARE @startDate datetime;

WHILE @step <= 1000
BEGIN
    SET @startDate = DATEADD(minute, RAND() * 59, 
                             DATEADD(hour, RAND() * 23, 
                                     DATEADD(day, RAND() * 31, '20180101')))

    INSERT INTO CommandLog VALUES('OBJ1', 
                                  'INDEX1', 
                                  1, 
                                  @startDate, 
                                  DATEADD(second, RAND() * 59,@startDate));
    SET @step += 1;
END

/* just to check first 10 records */
SELECT TOP 10 * FROM CommandLog;
GO
ObjectName | IndexName | IndexType | StartTime           | EndTime            
:--------- | :-------- | --------: | :------------------ | :------------------
OBJ1       | INDEX1    |         1 | 16/01/2018 04:36:00 | 16/01/2018 04:36:43
OBJ1       | INDEX1    |         1 | 06/01/2018 09:11:00 | 06/01/2018 09:11:33
OBJ1       | INDEX1    |         1 | 05/01/2018 14:23:00 | 05/01/2018 14:23:43
OBJ1       | INDEX1    |         1 | 10/01/2018 19:53:00 | 10/01/2018 19:53:11
OBJ1       | INDEX1    |         1 | 14/01/2018 14:31:00 | 14/01/2018 14:31:53
OBJ1       | INDEX1    |         1 | 06/01/2018 18:43:00 | 06/01/2018 18:43:07
OBJ1       | INDEX1    |         1 | 21/01/2018 21:52:00 | 21/01/2018 21:52:41
OBJ1       | INDEX1    |         1 | 28/01/2018 06:51:00 | 28/01/2018 06:51:03
OBJ1       | INDEX1    |         1 | 19/01/2018 08:39:00 | 19/01/2018 08:39:58
OBJ1       | INDEX1    |         1 | 30/01/2018 19:57:00 | 30/01/2018 19:57:50

Formatting data

Ok, so far so good, I've chosen a PIVOT solution in this case, but first I need to format source data to make it possible. Records can be filtered or grouped using the Calendar table.

ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
/* check first 10 records again */
SELECT     TOP 10 
           cl.ObjectName, cl.IndexName, cl.IndexType, cDayName,
           CASE cDayName 
                WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
           END FormatDate,
           DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GO
ObjectName | IndexName | IndexType | cDayName | FormatDate    | DiffSeconds
:--------- | :-------- | --------: | :------- | :------------ | ----------:
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           7
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          25
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          40
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           0
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          13
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          52
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          50
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          15

PIVOT query

Well, this is the PIVOT query I'm trying to simulate. Keep in mind that PIVOT queries require a well-know number of columns and you often must generate it dynamically.

SELECT ObjectName, IndexName, IndexType,
      [20-40 06-01],[5-40 07-01],[20-40 13-01],[5-40 14-01],
      [20-40 20-01],[5-40 21-01],[20-40 27-01],[5-40 28-01]
FROM
(
    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
               CASE cDayName WHEN 'Saturday' THEN '20-40 ' + FORMAT(cDate, 'dd-MM')
                             WHEN 'Sunday'   THEN '5-40 ' + FORMAT(cDate, 'dd-MM')
                             END FormatDate,
               DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
    FROM       CommandLog cl
    INNER JOIN Calendar c
    ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
    AND        cl.StartTime < DATEADD(day, 1, c.cDate)
    WHERE      cDayName IN ('Saturday', 'Sunday')
)src
PIVOT 
(
    SUM(DiffSeconds) FOR FormatDate IN ([20-40 06-01],[5-40 07-01],[20-40 13-01],
                                        [5-40 14-01],[20-40 20-01],[5-40 21-01],
                                        [20-40 27-01],[5-40 28-01])
) pvt;
GO
ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

Generating dynamic query

You can use STUFF function to dynamically generate columns names. And then simply concatenate strings.

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ',' + 
                   CASE cDayName 
                     WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                     WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                   END
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =     
    'SELECT ObjectName, IndexName, IndexType, ' + @cols
 + ' FROM
     (
       SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
                  CASE cDayName 
                       WHEN ''Saturday'' THEN ''20-40 '' + FORMAT(cDate, ''dd-MM'')
                       WHEN ''Sunday''   THEN ''5-40 '' + FORMAT(cDate, ''dd-MM'')
                  END FormatDate,
                  DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
       FROM       CommandLog cl
       INNER JOIN Calendar c
       ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
       AND        cl.StartTime < DATEADD(day, 1, c.cDate)
       WHERE      cDayName IN (''Saturday'', ''Sunday'')
     )src
    PIVOT 
    (
      SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')'
 + ') pvt';

EXEC sp_executesql @cmd;
GO

Final result

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

GROUP BY solution

Ok, it's Saturday afternoon and I have nothing else to do than read some interesting book. Having a look at your query I think you could replace all this table joins by a single GROUP BY + SUM(CASE... query using the Calendar table.

Similar to this one:

SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
           SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 06-01],
           SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 07-01],
           SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 13-01],
           SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 14-01],
           SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 20-01],
           SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 21-01],
           SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 27-01],
           SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 28-01]
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType;
GO
ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

This query can be generated dynamically too:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ', ' + ('SUM(CASE WHEN cDay = ' 
                                 + FORMAT(cDay, 'D')
                                 + ' THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS '
                                 + CASE cDayName 
                                        WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                                        WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                                   END
                                 + ' ')
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =   'SELECT     cl.ObjectName, cl.IndexName, cl.IndexType, '
           + @cols
           + 'FROM       CommandLog cl
              INNER JOIN Calendar c
              ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
              AND        cl.StartTime < DATEADD(day, 1, c.cDate)
              WHERE      cDayName IN (''Saturday'', ''Sunday'')
              GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType';

/* REMOVE Warning: Null value is eliminated by an aggregate or other SET operation. */
SET ANSI_WARNINGS OFF;

EXEC sp_executesql @cmd;
GO

dbfiddle here

  • 2
    Excellent example, though I would have used a Tally Table based date/calendar table creation method rather than a loop. – Anti-weakpasswords Jan 27 '18 at 2:51
  • 1
    Excellent,also please advice to use sp_executeSql so that index are also utilize. – KumarHarsh Jan 31 '18 at 6:29
  • @KumarHarsh: What do you mean? What does sp_executesql have to do with indices? – Andriy M Feb 2 '18 at 6:33
  • 1. sp_executesql allow to parameterized query so it is safe than Exec. 2. sp_executesql can utilize cache plan after first execution. – KumarHarsh Feb 2 '18 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.