T-SQL

Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

My Favourite SSIS Log Setup

After playing around with BIML for the last week, I have finally found a Log Setup for SSIS that I really like.

First, the SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE TABLE [dbo].[ETL_Log]
(
  [Log_ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  [StartTime] DATETIME2(7) NOT NULL,
  [PackageName] VARCHAR(255) NOT NULL,
  [LogText] VARCHAR(255) NOT NULL,
  [RowCount] INT NULL,
  [ErrorText] VARCHAR(MAX) NULL,
  [EndTime] DATETIME2(7)
)
 
--Stored procedure for logging the start of a package execution
 
CREATE PROCEDURE [dbo].[s_Log_Start]
  @PackageName VARCHAR(255),
  @LogText VARCHAR(255)
AS
  BEGIN
 
    INSERT ETL_Log (PackageName,StartTime,LogText) 
    VALUES (@PackageName,SYSDATETIME(),@LogText)
 
    RETURN @@IDENTITY
 
  END
 
--Stored procedure for logging the end of a package execution
 
CREATE PROCEDURE [dbo].[s_Log_End]
  @Log_ID INT,
  @LogText VARCHAR(255),
  @RowCount INT = NULL,
  @ErrorSource VARCHAR(MAX) = NULL,
  @ErrorText VARCHAR(MAX) = NULL
AS
  BEGIN
 
  UPDATE ETL_Log 
    SET 
      EndTime = SYSDATETIME(), 
      LogText = @LogText, 
      [RowCount] = @RowCount,
      ErrorText = CASE WHEN ErrorText IS NULL THEN @ErrorSource + ': ' + @ErrorText 
        ELSE ErrorText + CHAR(13) + CHAR(10) + @ErrorSource + ': ' + @ErrorText END 
    WHERE Log_ID = @Log_ID
 
  END
 
--Finally, a view for easy inspection of the log
 
CREATE VIEW [dbo].[v_ETL_Log]
  AS 
    SELECT 
      StartTime,
      EndTime,
      CASE 
        WHEN (SELECT Max(StartTime) FROM ETL_Log)>StartTime 
        THEN DATEDIFF(ss,StartTime,EndTime)
        ELSE DATEDIFF(ss,Starttime,ISNULL(EndTime,SYSDATETIME()))
      END AS DurationSeconds,
      PackageName,
      LogText,
      [RowCount],
      CAST(ISNULL([RowCount],0)*1000.0/
        NULLIF(DATEDIFF(ms,StartTime,EndTime),0) AS INT) AS RowsPerSec,
      ErrorText FROM ETL_Log

Now, with BIML, it is finally easy to share SSIS code snippets on my blog, so here is the BIML part.

First, the variables:

1
2
3
4
<Variables>
  <Variable Name="Log_ID" DataType="Int32">0</Variable>
  <Variable Name="Log_RowCount" DataType="Int32">0</Variable>
</Variables>

For the error handling, I use an OnError Event task on the package level:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<Events>
  <Event Name="EH_OnError" EventType="OnError" ConstraintMode="Linear">
    <Tasks>
      <ExecuteSQL Name="SQL_OnErrorLog" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
      <DirectInput>EXEC s_Log_End ?, 'Package Failed', ?, ?, ?</DirectInput>
        <Parameters>
          <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="Input"></Parameter>
          <Parameter Name="1" VariableName="User.Log_RowCount" DataType="Int32" Direction="Input"></Parameter>                
          <Parameter Name="2" VariableName="System.SourceName" DataType="String" Direction="Input"></Parameter>
          <Parameter Name="3" VariableName="System.ErrorDescription" DataType="String" Direction="Input"></Parameter>
        </Parameters>
      </ExecuteSQL>
    </Tasks>
  </Event>
</Events>

At the beginning of the control flow of the package i use an Execute SQL task:

1
2
3
4
5
6
7
<ExecuteSQL Name="SQL_StartLog" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
  <DirectInput>EXEC ? = s_Log_Start ?,'Package Started'</DirectInput>
    <Parameters>
      <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="ReturnValue"></Parameter>
      <Parameter Name="1" VariableName="System.PackageName" DataType="String" Direction="Input"></Parameter>
    </Parameters>
</ExecuteSQL>

In the data flow, use this transformation as the last step before your final destination:

1
<RowCount VariableName="User.Log_RowCount" Name="ROWCOUNT_Task" />

And finally, as the last step of the control flow:

1
2
3
4
5
6
7
<ExecuteSQL Name="SQL_EndLog_Success" ConnectionName="YOUR_OLEDB_CONNECTION" IsStoredProcedure="true">
  <DirectInput>EXEC s_Log_End ?,'Package Completed', ?</DirectInput>
    <Parameters>
      <Parameter Name="0" VariableName="User.Log_ID" DataType="Int32" Direction="Input"></Parameter>
      <Parameter Name="1" VariableName="User.Log_RowCount" DataType="Int32" Direction="Input"></Parameter>
    </Parameters>
</ExecuteSQL>

What I like about this setup:

  • It’s super fast to log to
  • It’s super fast to query (If you have a gigantic number of package executions, add an index to StartTime)
  • The table stays small (1 row per package execution)
  • You can see if a package is running, and for how long it has been running (via the view)
  • It logs ALL error messages from the package, and logs them only once. Basically all the “red dots” in the progress window in BIDs
  • The log is easy to query later to see for which packages the row count and/or the performance changes
  • By using BIML, it’s easy to add information to the log later for all packages if requirements change
  • It works for all versions of SSIS and SQL Server without any changes

Finished LogTable

User defined function (T-SQL) for finding work days and holidays – Norwegian Edition

In my last blog post I published a handy stored procedure for populating a time dimension in SQL Server 2008. The stored procedure makes use of a UDF called dbo.fIsWorkDay(@Date). This function returns a boolean value saying if the date parameter is a work day or not. The following two functions are specific for Norwegian holidays:

T-SQL procedure for finding easter sunday (Found here: http://www.mssqltips.com/tip.asp?tip=1537):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
IF OBJECT_ID('fEasterByYear',N'FN') IS NOT NULL DROP FUNCTION fEasterByYear
 
GO
 
CREATE FUNCTION [dbo].[fEasterByYear] (@Year SMALLINT) RETURNS DATE AS
BEGIN 
	DECLARE
		@c INT,
		@n INT,
		@k INT, 
		@i INT, 
		@j INT, 
		@l INT, 
		@m INT, 
		@d INT, 
		@Easter DATE 
 
	SET @c = (@Year / 100) 
	SET @n = @Year - 19 * (@Year / 19) 
	SET @k = (@c - 17) / 25 
	SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15 
	SET @i = @i - 30 * ( @i / 30 ) 
	SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)) 
	SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4 
	SET @j = @j - 7 * (@j / 7) 
	SET @l = @i - @j 
	SET @m = 3 + (@l + 40) / 44 
	SET @d = @l + 28 - 31 * ( @m / 4 ) 
 
	SET @Easter = CAST((SELECT CONVERT(CHAR(4),@Year) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + 'T00:00:00') AS DATE)
 
	RETURN @Easter 
END

Calculating work day:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
IF OBJECT_ID('fIsWorkday',N'FN') IS NOT NULL DROP FUNCTION fIsWorkDay
 
GO
 
CREATE FUNCTION [dbo].[fIsWorkDay] (@Date DATE) RETURNS BIT AS
BEGIN
	DECLARE @IsWorkDay BIT = 1
 
	SELECT @IsWorkDay = 
	CASE
		--Exclude Saturday and Sunday (Datefirst-independent)
		WHEN ((@@DATEFIRST + DATEPART(dw,@Date)) % 7) IN (0,1) THEN 0 
		--Exclude National Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=17 THEN 0
		--Exclude Workers' Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=1 THEN 0
		--Exclude Christmas
		WHEN MONTH(@Date)=12 AND DAY(@Date) IN (25,26) THEN 0
		--Exclude New Years
		WHEN MONTH(@Date)=1 AND DAY(@Date)=1 THEN 0
		--Exclude Easter, Pentecost and Ascension of Jesus
		WHEN @Date IN (
			DATEADD(DAY,-3,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,-2,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,1,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,39,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,50,dbo.fEasterByYear(YEAR(@Date)))
			) THEN 0
		ELSE 1
	END
 
	RETURN @IsWorkDay
END