Database Performance Testing Tip - Clear Cache and Buffers

Prior to testing each query set, call dbcc freeproccache and dbcc dropcleanbuffers.

Estimating Data Integration Projects

Not an original idea, more of a whole sale rip off of a post we found.

T = ((E * D * 5)+(S * C * 6)) * A + F

where

T = total no. of days to allow for implementation

E = no. of entities in physical model

D = data quality (D = 0.5 very clean, D = 1 medium cleanliness, D = 1.5 dirty, a lot of work)

S = no. of source systems

C = complexity of transformations (C = 1 relatively simple, C = 0.5 1 to 1 mapping, C = 2 complex, lookups required, derived data, calculations)

A = automation factor (A = 1 ETL tool used, A = 1.6 manual coding)

F = first time implementation, use of ETL (F = 0 many implementations done, F = 20 small & flexible, F = 50 large development)

this estimates have been tried and proven from my project management experience in projects implementing with Informatica as ETL tool.

try it and refine it at the end of each project.

http://datawarehouse.ittoolbox.com/groups/strategy-planning/dw-projectmanagement/estimation-effort-template-1663954

Test a Percentage Of Rows

There are times when working with a large data set that you may not have space or time to test every row you have processed. I use a limiting table that contains a sleect set of key values you will inner join your test query to.This throttles the test to just that set of key values, as a percentage of the total data.

DECLARE
--set the percent of the rows, as a whole number, you want to run these tests against
    @PercentRows INT = 10,
    @RowsToProcess INT,
    @SQL NVARCHAR(MAX)

DECLARE @LoanTbl TABLE (Loan_ID INT)

SET @RowsToProcess = (SELECT COUNT (DISTINCT Loan_ID)FROM TableOne (NOLOCK))*(@PercentRows*.01)
SET @SQL = 'SELECT DISTINCT TOP '+CONVERT(VARCHAR(20),@RowsToProcess)+' Loan_ID FROM TableOne (NOLOCK) ORDER BY Loan_ID DESC';

INSERT INTO @LoanTbl
EXECUTE sp_executesql @SQL

Find A Date Range Gap In A Daily Loaded Table

:SETVAR DatabaseName "NAME"
:SETVAR SourceTable "dbo.TABLE"
:SETVAR ControlColumn "etl_date"
USE [$(DatabaseName)]

DECLARE @Tbl1 TABLE (ID INT IDENTITY(1,1) , Dater1 DateTime)DECLARE @Tbl2 TABLE (ID INT IDENTITY(1,1) , Dater2 DateTime)DECLARE @DateFilter DATETIME = '2013-01-05 00:00:00.000'
INSERT INTO @Tbl1SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
INSERT INTO @Tbl2SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
SELECT '$(SourceTable)' SourceTable,t.Dater2,CASE
WHEN (CAST(o.Dater1 AS DATE) <> CAST(DATEADD(D,-1,t.Dater2) AS DATE)) THEN 'FAIL'
ELSE 'PASS' END AS 'Skipped Day',CASEWHEN (DATEPART(weekday,t.Dater2) = 2) THEN 'Monday'WHEN (DATEPART(weekday,t.Dater2) = 3) THEN 'Tuesday'
WHEN (DATEPART(weekday,t.Dater2) = 4) THEN 'Wednesday'
WHEN (DATEPART(weekday,t.Dater2) = 5) THEN 'Thurday'WHEN (DATEPART(weekday,t.Dater2) = 6) THEN 'Friday'WHEN (DATEPART(weekday,t.Dater2) = 7) THEN 'Saturday'WHEN (DATEPART(weekday,t.Dater2) = 1) THEN 'Sunday'ELSE 'Nomads!' END DayOfWeeker FROM @Tbl1 o JOIN @Tbl2 t ON o.ID = (t.ID-1)WHERE o.ID <> 1ORDER BY Dater1 DESC

Sample Regex Patterns

Used for test data generation

US Phone [0-9]{3}(-)[0-9]{3}(-)[0-9]{4}
US Phone Optional dash [0-9]{3}(-|)[0-9]{3}(-|)[0-9]{4}
US Phone Extension [X][ ][0-9]{3,4}
US SSN [0-9]{3}-[0-9]{2}-[0-9]{4}
US SSN no dash [0-9]{3}[0-9]{2}[0-9]{4}
Basic Email [a-z]{4,8}([0-9]{0,3}|\.[a-z]{4,10})@([a-z]{4,9}\.)?([a-z-]{6})\.(org|com|net|biz|cx)

Company Name
(Re|Ad|Par|Tru|Thru|In|Bar|Cip|Dop|End|Em|Fro|Gro|Hap|Kli|Lom|Mon|Qwi|Rap|Sup|Sur|Tip|Tup|Un|Up|Var|Win|Zee)(ban|cad|dud|dim|er|frop|glib|hup|jub|kil|mun|nip|peb|pick|quest|rob|sap|sip|tan|tin|tum|ven|wer|werp|zap)(il|ic|im|in|up|ad|ack|am|on|ep|ed|ef|eg|aqu|ef|edg|op|oll|omm|ew|an|ex|pl)?(icator|or|ar|ax|an|ex|istor|entor|antor|in|over|ower|azz)(([ -]?Pro|Duplex|Multi){0}) (Direct|WorldWide|Holdings|International||) (|Inc|Company|Group|Corp.)?

Clear Log Files In SQL Server 2008 and Beyond

Sometimes your recovery mode prevents you from clearing your log files. Change the recovery mode and then do SHRINKFILE


:SETVAR DB Datamart
:SETVAR __IsSqlCmdEnabled "True"GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'

BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';SET NOEXEC ON;END
GO
USE $(DB)
ALTER DATABASE $(DB) SET RECOVERY SIMPLE WITH NO_WAIT--SELECT name ,size/128.0 - CAST(FILEPROPERTY($(DB), 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
--FROM sys.database_files;DBCC SHRINKFILE ($(DB)_log,TRUNCATEONLY)--SELECT name ,size/128.0 - CAST(FILEPROPERTY([$(DB)], 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
--FROM sys.database_files;ALTER DATABASE [$(DB)] SET RECOVERY FULL WITH NO_WAIT

Dynmic SQL - List Space Allocation Across Dbs

DECLARE @Tbl Table
(ID INT IDENTITY(1,1),name    VARCHAR(100))


INSERT INTO @Tbl
(name)
VALUES
('model'),
('master')

DECLARE
@StartCnt INT = 1,
@MaxCnt INT = (SELECT COUNT (1) FROM @tbl),
@Db nVarchar(100),
@sql nVarchar(MAX)

WHILE @StartCnt <= @MaxCnt

BEGIN
SET @Db = (SELECT Name FROM @Tbl WHERE ID = @StartCnt)
PRINT @db
SET @sql = '
USE '
+@Db
+' SELECT '''
+ @DB
+''' DB, SUM(Size)/128 Used_MB, SUM(maxsize)/128 Allocated_MB
FROM sysfiles'


  exec sp_executesql @sql OUTPUT
  SET @StartCnt = @StartCnt+1
  END