Data Management - Author(s): Eli Weinstock-Herman (tarwn)

Deploying Database Changes with PowerShell

Eli Weinstock-Herman (tarwn) — Fri, 17 May 2013 07:36:00 +0000

Recently, while working on a personal project, I found myself needing a lightweight way to deploy database changes to multiple environments. In the past I have used a wide range of methods, ranging from applying the changes manually to applying changes via a diff tool (SQL Compare), to automatically applying manually created change scripts, to automatically applying diff scripts that were automatically generated, to working directly in production..er, pretend you didn't see that one.

Why not _________ tool?

There are a lot of tools out there to handle database deployments, but this is a small project that I am building incrementally as a minimum viable product. Rather than tie up a bunch of time researching and experimenting with database deployment tools early on, I decided to do something simple that would work for the time being and free me up to work on the actual product.

What I want from the deployment is to:

Spend as little time on this as possible
Work against SQL Azure and a local 2008 R2 Server
Call it from TeamCity for a local server or a remote one
Produce readable output for TeamCity logs
Create the databases and users from the ground up
Include randomly generated data
Manage scripts for 2 independent databases in the same build
Allow real SQL (I'm not scared of SQL and I don't want to learn a code abstraction just to deploy changes)
Not worry about rollbacks. I am deploying small changes and if something breaks I'll be charging forward
Not expose credentials, as the code will be visible to the public
Be replaceable. I might replace it with a tool one day, so keep the deployment logic separate from the application

Looking back at this list and what I eventually created, I probably could have used something like DBDeploy. The scripts I created ended up taking a very similar approach.

The Deployment Scripts

My deployment consists of 4 PowerShell scripts:

ApplyDatabaseUpdates.ps1 - Responsible for generically applying changes from a folder to a specified database
UpdateCoreDatabase.ps1 - Responsible for the application's Core Database, calls ApplyDatabaseUpdates
UpdateSampleDatabase.ps1 - Response for the application's Sample Database, calls ApplyDatabaseUpdates
RunLocally.ps1 - Executes the two Update scripts against the local database on my development machine(s)

This project has been spread out over 6 months, intermixed with life, other projects, blog posts, etc. Along the way I also upgraded my local development machine to SQL Server 2012 but my main test database server is on 2008 still and my release environment is Azure Database/SQL Azure/(whatever the name is this week).

ApplyDatabaseUpdates.ps1

The purpose of the ApplyDatabaseUpdates script is to apply all of the scripts in a specified folder to the specified server. To do this it creates a tracking table on the target database, then iterates through the contents of the folder, building a script containing any files that were not previously logged in the tracking table.

Core Database Scripts Folder

The deployment script wraps the contents of each script file in an EXECUTE statement, followed by an INSERT to add it to the tracking table for the database.

ApplyDatabaseUpdates.ps1

powershell

Line number Off | Hide | Select all

function ApplyDatabaseUpdates
{
param (
[parameter(Mandatory=$true)]
[string]
$UpdatesFolder,
[parameter(Mandatory=$true)]
[string]
$Server,
[parameter(Mandatory=$true)]
[string]
$Database,
[parameter(Mandatory=$true)]
[string]
$AdminUserName,
[parameter(Mandatory=$true)]
[string]
$AdminPassword
)
$path = (Get-Location).Path
# For SQL 2008 - load the modules
try{
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -ne $null){
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
}
}
catch{
Write-Error "Powershell Script error: $_" -EA Stop
}
#updates tracking
try{
Write-Host "Creating Update Tracking Table If Not Exists"
Invoke-Sqlcmd -Query "IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UpdateTracking') CREATE TABLE UpdateTracking (UpdateTrackingKey int IDENTITY(1,1) PRIMARY KEY, Name varchar(255) NOT NULL, Applied DateTime NOT NULL);" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "$Database" -ErrorAction Stop
Write-Host "Done"
}
catch{
Write-Error "Powershell Script error: $_" -EA Stop
}
#database updates
$outputPath = "$path\UpdatesBatch.sql"
$stream = [System.IO.StreamWriter] "$outputPath"
$fileUpdates = Get-ChildItem "$UpdatesFolder"
$datestamp = $(get-date -f "yyyy-MM-dd HH:mm")
$stream.WriteLine("/* SQL Core Updates - Updated $datestamp */")
$stream.WriteLine("BEGIN TRANSACTION")
foreach($file in $fileUpdates)
{
$name = ($file.Name)
$namewe = ([System.IO.Path]::GetFileNameWithoutExtension($name))
$stream.WriteLine("")
$stream.WriteLine("/* File: $name */")
$stream.WriteLine("IF NOT EXISTS (SELECT 1 FROM UpdateTracking WHERE Name = '$namewe')")
$stream.WriteLine("BEGIN")
$stream.WriteLine("`tPrint 'Applying Update: $namewe'")
$stream.WriteLine("`tEXEC('")
(Get-Content "$UpdatesFolder\$name") | % {$_ -replace "'", "''"} | % {$stream.WriteLine("`t`t$_")}
$stream.WriteLine("`t');")
$stream.WriteLine("`tINSERT INTO UpdateTracking(Name, Applied) SELECT '$namewe', GETUTCDATE();")
$stream.WriteLine("END")
}
$stream.WriteLine("COMMIT TRANSACTION")
$stream.Close()
Write-Host "Update Script Created."
Write-Host "Running updates..."
Invoke-SqlCmd -InputFile "$outputPath" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "$Database" -Verbose -ErrorAction Stop
Remove-Item "$outputPath"
Write-Host "Updates completed."
}

Note: this has only been run in the context of my personal project. That means don't copy, paste, and run it immediately against your production environment. Running stuff blindly from the internet is known as both a bad idea and a career limiting maneuver.

Update____Database.ps1

My application has two databases which it will access via different accounts. I want the ability to rebuild these databases from scratch as well as manage their credentials from an external system (in this case, TeamCity). If the worst should happen and these databases are compromised or overwritten in some fashion, I want to be able to recreate them with new credentials, account names, the works.

To make life more difficult, many of these commands have to be executed individually in order to work with Azure Databases.

Both scripts detect if their specified database exists and, if not, create them. The UpdateSampleDatabase is capable of recreating the database in Azure, provided some extra options are passed in to it (the Core Database script is missing this bit, unfortunately):

Excerpt from UpdateSampleDatabase.ps1:

powershell

Line number Off | Hide | Select all

# ...
#database
Write-Host "Checking database exists...";
$result = Invoke-Sqlcmd -Query "SELECT [name] FROM [sys].[databases] WHERE [name] = N'$database'" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "master" -ErrorAction Stop
if($result.name){
Write-Host "Database already exists";
}
else{
Write-Host "Creating Database: $database"
Invoke-Sqlcmd -Query "CREATE DATABASE $database" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "master" -ErrorAction Stop
Invoke-Sqlcmd -Query "ALTER DATABASE $database SET RECOVERY SIMPLE" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "master" -ErrorAction Stop
Write-Host "Created."
}
# ...

They also generate the users specified by the build server (which will also be dynamically added into the relevant web.config files for the website):

Excerpt from UpdateCoreDatabase.ps1:

powershell

Line number Off | Hide | Select all

# ...
#user
try{
Write-Host "Creating User: $NewUserName"
$result = Invoke-Sqlcmd -Query "SELECT [name] FROM sys.sql_logins WHERE name = '$NewUserName'" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "master" -ErrorAction Stop
if($result.name){
Write-Host "Login already exists"
}
else{
Write-Host "Creating login..."
Invoke-Sqlcmd -Query "CREATE LOGIN $NewUserName WITH PASSWORD = '$NewPassword'" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "master" -ErrorAction Stop
Write-Host "Login Created."
}
$result = Invoke-Sqlcmd -Query "SELECT [name] FROM sys.sysusers WHERE name = '$NewUserName'" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "$Database" -ErrorAction Stop
if($result.name){
Write-Host "User already exists"
}
else{
Write-Host "Creating user..."
Invoke-Sqlcmd -Query "CREATE USER $NewUserName FOR LOGIN $NewUserName WITH DEFAULT_SCHEMA = dbo" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "$Database" -ErrorAction Stop
Invoke-Sqlcmd -Query "EXEC sp_addrolemember 'db_datareader','$NewUserName'; EXEC sp_addrolemember 'db_datawriter','$NewUserName'" -ServerInstance "$Server" -Username "$AdminUserName" -Password "$AdminPassword" -Database "$Database" -ErrorAction Stop
Write-Host "User Created."
}
}
catch{
Write-Error "Powershell Script error: $_" -EA Stop
}
# ...

Once the database and users are created, the SampleDatabase script produces a replacement for one of it's script files that will contain some randomized data. The original file is a placeholder and produces an error if it hasn't been replaced.

Excerpt from UpdateSampleDatabase.ps1:

powershell

Line number Off | Hide | Select all

# ...
# ---------------------------------- Content Generation ---------------------------------------------
# Scripts to generate content dynamically and update the appropriate update script
#generate customers table content
$CustomersContentPath = "$UpdatesFolder\0002_CustomersData.sql"
try{
Write-Host "Generating content script for dbo.Customers"
$girlsnames = ("" + [string]::Join("",(Get-Content "$path\Data\girlsforenames.txt")) + "").Replace("'","''")
$boysnames = ("" + [string]::Join("",(Get-Content "$path\Data\boysforenames.txt")) + "").Replace("'","''")
$lastnames = ("" + [string]::Join("",(Get-Content "$path\Data\surnames.txt")) + "").Replace("'","''")
(Get-Content "$path\Data\BulkImportNames.AzureFriendly.sql") `
| % {$_ -replace "{{GIRLSNAMES}}", $girlsnames} `
| % {$_ -replace "{{BOYSNAMES}}", $boysnames} `
| % {$_ -replace "{{LASTNAMES}}", $lastnames} `
| Set-Content -path "$CustomersContentPath"
}
catch{
Write-Error "Powershell Script error: $_" -EA Stop
}
# ...

This generated data is a necessary part of the application that I initially replaced on each deployment, but now only use on new database deployments. Keeping it random and replaceable prevents the application code from making any assumptions about the data in this table.

The final step for both scripts is to run the ApplyDatabaseUpdates function on their respective folders and databases.

RunLocally.sample.ps1

The RunLocally.sample.ps1 script will bring a local development environment all the way up to the latest version without requiring me to type out a long series of arguments and credentials. It is basically just a list of hardcoded variables and then calls to the two Update______Database.ps1 scripts.

RunLocally.sample.ps1:

powershell

Line number Off | Hide | Select all

# 1) Copy this file to RunLocally.ps1
# 2) Open RunLocally.ps1 + substitute meaningful values for the variables below (update web.config connection strings also)
# 3) [Cross your fingers and] Run it
$DbServer = "localhost"
$DbAdminUsername = "admin"
$DbAdminPassword = "password"
$DbSampleDatabase = "SampleDB"
$DbSampleReadUsername = "readuser"
$DbSampleReadPassword = "password"
$DbCoreDatabase = "CoreDB"
$DbCoreUsername = "coreuser"
$DbCorePassword = "password"
.\UpdateSampleDatabase.ps1 -s $DbServer -d $DbSampleDatabase -nu $DbSampleReadUsername -np $DbSampleReadPassword -au $DbAdminUsername -ap $DbAdminPassword -DeleteGeneratedContentAfter $true
.\UpdateCoreDatabase.ps1 -s $DbServer -d $DbCoreDatabase -nu $DbCoreUsername -np $DbCorePassword -au $DbAdminUsername -ap $DbAdminPassword

The reason it is a sample file is because the real one is going to be different for my desktop and laptop and I knew if they were under source control I would constantly be accidentally committing them and having to change back and forth as I switched systems. To use it, I create a copy of the sample file, rename it to RunLocally.ps1 (which is ignored via the .gitignore for the project) and fill in the real values.

Future Plans

I'm not a fan of the libraries that try to abstract away the SQL in SQL deployments. I know SQL and don't need to spend the time learning a library abstraction that, at best, can offer me no better control and ease of use then raw SQL. Many developers feel less than confident in their SQL skills and an abstracted library seems to reduce the need to learn SQL, but in reality the less you know about SQL the harder it will be to determine if the abstraction is doing what you think it is doing, and doing it in a safe and performant manner (and frequently the answer is "it's not").

This set of scripts evolved as a I built the project. In the future I'll probably switch over to something like DbDeploy, as I mentioned earlier. Having the scripts in file system folders makes it easy to glance over the list to see what has changed, makes them easily accessible in my git repositories, can be copied and pasted (or opened and saved) directly from SSMS, and requires no extra tooling.

The scripts above are part of the deployment process for a personal project I have been working on called SQLisHard.com. Launching in the next few weeks (quietly), the site is designed to help people interactively learn or improve their SQL skills and help build the knowledge and confidence that comes from writing and solving real SQL problems.

Original post blogged on LessThanDot.

T-SQL Tuesday #31 - Logging Simple Things

Eli Weinstock-Herman (tarwn) — Tue, 12 Jun 2012 12:14:00 +0000

How big are your core databases right now? Do you know how they got that way? Is that normal? These questions are impossible to answer just by looking at the database options dialog in SSMS. They are also questions I've had to try to answer in several different environments, because without logging we not only didn't know what normal growth looked like for our system, we didn't know what tables and indexes were driving that growth.

The answer lies in a very simple query and a little logging.

Aaron Nelson (twitter|blog) is hosting T-SQL Tuesday this month with the topic of "Logging". Check out his post for a list of other T-SQL Tuesday posts this month and congratulate him on speaking at TechEd on Tuesday morning (he's denied being the loud powershell fan in the keynote, btw).

The Setup

Monitoring the sizes at the index and table level provides raw material for looking several different statistics. Luckily this is fairly easy to do for on premises and SQL Azure databases.

For the purpose of these examples I'm going to be logging the database growth in the same database. Obviously this won't work for every environment and adds an extra variable to our growth statistics.

First up, let's create a table to store the raw data in:

Code: tsql

CREATE TABLE dbo.DatabaseSizeLog(
Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
LogTime DateTime2 NOT NULL,
DatabaseId int NOT NULL,
ObjectId int NOT NULL,
TableName varchar(200) NOT NULL,
IndexId int NULL,
IndexName varchar(200) NULL,
AllocatedBytes int NOT NULL,
UsedBytes int NOT NULL,
UnusedBytes int NOT NULL,
[RowCount] int NOT NULL
);

Note that I am capturing both ids and names for the table and index. We could JOIN to get the names, but being able to run a quick one line statement to look at key information is worth the slightly higher storage cost. I've also used an integer IDENTITY column as the required clustered index for a SQL Azure database. A better option would be LogTime, DatabaseId, and IndexId in the order that makes the most sense for how you intend to look at the data.

Once we have the raw table, we need the query to capture the data:

Code: tsql

INSERT INTO dbo.DatabaseSizeLog(LogTime, DatabaseId, ObjectId,
TableName, IndexId, IndexName, AllocatedBytes,
UsedBytes, UnusedBytes, [RowCount])
SELECT GetDate()
, DB_ID()
, o.object_id AS TableId
, o.name
, ps.index_id as [IndexId]
, CASE WHEN ps.index_id > 1 THEN i.name ELSE NULL END AS IndexName
, CAST(ps.reserved_page_count * 8192 AS DECIMAL(18,0)) AS Allocated
, CAST(ps.used_page_count * 8192 AS DECIMAL(18,0)) AS Used
, CAST((ps.reserved_page_count - used_page_count) * 8192 AS DECIMAL(18,0)) AS Unused
, ps.row_count AS "RowCount"
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id
INNER JOIN sys.indexes i on i.index_id = ps.index_id AND i.object_id = ps.object_id
WHERE o.type NOT IN ('S','IT');

This should be placed in a scheduled job in SQL Agent for an on-premises database, or tied into a scheduled task system (perhaps via a worker role) for a SQL Azure database.

Now that we have some data and we're logging it at some regular interval, lets start getting some use out of it.

Basic Information

Once we have some data in the table, we can build some information gathering queries to learn more about our data growth rates.

What are our largest tables right now?

Code: tsql

DECLARE @TargetTime DateTime;
SELECT TOP 1 @TargetTime = LogTime FROM dbo.DatabaseSizeLog ORDER BY LogTime DESC;

SELECT TOP 10
TableName,
"Allocated (MB)" = CAST(SUM(AllocatedBytes) as DECIMAL(18,2)) / 1048576,
"Used in MB" = CAST(SUM(UsedBytes) as DECIMAL(18,2)) / 1048576,
"Unused in MB" = CAST(SUM(UnusedBytes) as DECIMAL(18,2)) / 1048576,
"Row Count" = MIN([RowCount])
FROM dbo.DatabaseSizeLog
WHERE LogTime = @TargetTime
GROUP BY TableName
ORDER BY SUM(AllocatedBytes) DESC;

What percentage of our largest tables is indexes?

Code: tsql

DECLARE @TargetTime DateTime;
SELECT TOP 1 @TargetTime = LogTime FROM dbo.DatabaseSizeLog ORDER BY LogTime DESC;

SELECT TOP 10
TableName,
"Total Allocated (MB)" = CAST(SUM(AllocatedBytes) as DECIMAL(18,2)) / 1048576,
"Percent For Indexes" = CAST(SUM(CASE WHEN IndexName IS NOT NULL THEN AllocatedBytes ELSE 0 END) as DECIMAL(18,2)) / CAST(SUM(AllocatedBytes) as DECIMAL(18,2)),
"Row Count" = MIN([RowCount])
FROM dbo.DatabaseSizeLog
WHERE LogTime = @TargetTime
GROUP BY TableName
ORDER BY SUM(AllocatedBytes) DESC;

Which tables are growing the quickest?

Code: tsql

WITH LogHistory AS (
SELECT Id, LogTime, DatabaseId, ObjectId,
TableName, IndexId, IndexName, AllocatedBytes,
UsedBytes, UnusedBytes, [RowCount],
ROW_NUMBER() OVER (PARTITION BY ObjectId, IndexId ORDER BY LogTime DESC) AS PastTimeNumber
FROM dbo.DatabaseSizeLog
)
SELECT
LH_NOW.TableName,
PercentGrowth = CASE
WHEN SUM(LH_THEN.AllocatedBytes) = 0 AND SUM(LH_NOW.AllocatedBytes) = 0 THEN 0
WHEN SUM(LH_THEN.AllocatedBytes) = 0 THEN 1
ELSE 1 - SUM(LH_NOW.AllocatedBytes) / CAST(SUM(LH_THEN.AllocatedBytes) AS DECIMAL(18,2))
END,
BytesPerHour = (SUM(LH_NOW.AllocatedBytes) - SUM(LH_THEN.AllocatedBytes)) / (DateDiff(minute, LH_THEN.LogTime, LH_NOW.LogTime) / 60.0)
FROM LogHistory LH_NOW
INNER JOIN LogHistory LH_THEN ON LH_NOW.ObjectId = LH_THEN.ObjectId AND LH_NOW.IndexId = LH_THEN.IndexId
WHERE LH_NOW.PastTimeNumber = 1
AND LH_THEN.PastTimeNumber = 3 -- tweak this to widen or narrow the comparison time
GROUP BY LH_Now.TableName, LH_NOW.LogTime, LH_THEN.LogTime
ORDER BY SUM(LH_NOW.AllocatedBytes) - SUM(LH_THEN.AllocatedBytes) DESC

Monitoring

Besides being able to look at some general statistics, if we have a system that can monitor the result of a SQL query, we have a number of options we can start monitoring against:

Database size
Most recent total allocated size of the database (in M.

Code: tsql

SELECT TOP 1 SUM(AllocatedBytes) / 1048576.0
FROM dbo.DatabaseSizeLog
GROUP BY LogTime
ORDER BY LogTime DESC;

Hourly database growth for the last two entries
Hourly growth in MB/hour for the last two entries

Code: tsql

WITH LastTwoEntries AS (
SELECT TOP 2
SizeInMB = SUM(AllocatedBytes) / 1048576.0,
LogTime
FROM dbo.DatabaseSizeLog
GROUP BY LogTime
ORDER BY LogTime DESC
)
SELECT (LTE1.SizeInMB - LTE2.SizeInMB) / (DATEDIFF(minute, LTE1.LogTime, LTE2.LogTime) / 60.0)
FROM LastTwoEntries LTE1
INNER JOIN LastTwoEntries LTE2 ON LTE1.LogTime > LTE2.LogTime

Wait, you deleted how many records?!?
This lists all tables that have shrunk by more than 10% since the previous log entry.

Code: tsql

WITH Entries AS (
SELECT ObjectId,
TableName,
SizeInMB = AllocatedBytes / 1048576.0,
LogTime,
ROW_NUMBER() OVER (PARTITION BY ObjectId ORDER BY LogTime DESC) AS Num
FROM dbo.DatabaseSizeLog
WHERE IndexName IS NULL
)
SELECT E1.TableName
FROM Entries E1
INNER JOIN Entries E2 ON E1.ObjectId = E2.ObjectId
WHERE E1.Num = 1
AND E2.num = 2
AND E2.SizeInMb <> 0
AND E1.SizeInMb < E2.SizeInMb - (.10 * E2.SizeInMb);

And more

By now I hope you have even more ideas for ways to slice this simple data set. Logging is a powerful tool in our inventory and often even a simple query can provide a great deal of information if we log it over time. The basic query that started this post probably didn't look like much, but capturing the values over time allowed us to expose a lot of new information about our database. Having visibility into how our systems are running can be the difference between finding out our database hit a size limit after the fact and knowing that it is growing at an abnormal pace long before the problem occurs.

Original post blogged on LessThanDot.

Performance Impacts of Unicode, Equals vs LIKE, and Partially Filled Fixed Width

Eli Weinstock-Herman (tarwn) — Wed, 28 Dec 2011 11:45:00 +0000

Several weeks ago I was refreshing my memory on some nvarchar/varchar tradeoffs when I ran into a post by Michael J Swart (blog|twitter) where he shared the results of investigating a performance problem in one of his live environments. After changing many columns from varchar to nvarchar, he had several procedures that were taking much longer to execute without any changes in I/O profile. This led him to compare the execution times of searching a varchar vs an nvarchar column, where he found that searching an nvarchar took 800% as long as a varchar.

After reading this, I was curious. Does that follow with char and nchar also? How about looking for exact matches (equals) instead of a search (LIKE)? And partially filled char fields are supposed to be poorer performing too, right?

Results

The results are based on 4 runs of each query against 1,000,000 rows with explicitly limited parallelism. The name identifies the type, which operation was used (equals or LIKE), and the size of the field. All data was 36 characters, so "(36)" tests are fully populated values and "(72)" tests are half-full values.

All Results, Normalized to Shortest Value (Char 32 - Equals)

This graph shows the average for each type as a percentage of the smallest value, 'Char(72) - Equals'. What's immediately obvious is the level of difference between an equals operation and a LIKE, especially when we get to the trailing, partially filed NCHAR field (wow!).

The data was pretty consistent across the 4 runs used for these graphs:

Test	Average (ms)	StdDev (ms)
Char(36) - Equals	113	0
Char(36) - Like	596	0
Char(72) - Equals	117	2
Char(72) - Like	1043	1.5
NChar(36) - Equals	123	0
NChar(36) - Like	3951	13.08
NChar(72) - Equals	121	1.5
NChar(72) - Like	7350	0
NVarchar(36) - Equals	153	0
NVarchar(36) - Like	3997	8.85
NVarchar(72) - Equals	147	2
NVarchar(72) - Like	3972	3
Varchar(36) - Equals	136	2.87
Varchar(36) - Like	623	2.45
Varchar(72) - Equals	141	1.5
Varchar(72) - Like	630	0

Note: I originally had 5 runs, but removed the first since it had consistently higher values, with 2 cases far outside several standard devitations.

Comparing EQUALS statements

There is limited statistical significance in the fixed-width EQUALs, but the values do group together by type.

Equals Results, Normalized to Shortest Value (Char 32 - Equals)

It is interesting to note that there is a pretty consistent 20% gap between fixed and variable widths for the same types (char to varchar, for instance), regardless of whether the column is partially or fully populated.

Comparing LIKE statements

There is a much broader impact when we start looking at the LIKE statement comparisons. Like Michael's post above, there is a noticeable difference (on my system, ~650%) between searching a varchar and an nvarchar column.

LIKE Results, Normalized to Shortest Value (Char 32 - Like)

The conclusions I see are:

The previously mentioned difference between unicode and non-unicode fields is present
Fixed width fields suffer the same performance impact as variable length
Partially populated fixed width fields ran 75-90% longer than the associated fully populated column
Unicode fields were consistently 6x slower (on my system) than their non-unicode variants

Poorly sized nchar seems to be the real winner.

Scripts

The script for these results was heavily based on the one in Michael's post above. The prep script assumes you have a numbers table (here is a script for one).

Setup Sample Data Table

Code: tsql

-----------------------------------
-- Prep - Generate Sample Data

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CharTypeTest')
DROP TABLE dbo.CharTypeTest;

CREATE TABLE dbo.CharTypeTest(
CharTypeTestId int identity(1,1) NOT NULL,
VarcharField36 varchar(36) NOT NULL,
VarcharField72 varchar(72) NOT NULL,
CharField36 char(36) NOT NULL,
CharField72 char(72) NOT NULL,
NVarcharField36 nvarchar(36) NOT NULL,
NVarcharField72 nvarchar(72) NOT NULL,
NCharField36 nchar(36) NOT NULL,
NCharField72 nchar(72) NOT NULL );

WITH SampleData AS(
SELECT CAST(NEWID() as CHAR(36)) AS SampleText
FROM Numbers N1, Numbers N2 -- 50,000 * 20 = 1,000,000
WHERE N1.Number < 50000 AND N2.Number < 20
)

INSERT INTO dbo.CharTypeTest(VarcharField36, CharField36, NvarcharField36, NCharField36,
VarcharField72, CharField72, NvarcharField72, NCharField72)
SELECT SampleText, SampleText, SampleText, SampleText, SampleText, SampleText, SampleText, SampleText
FROM SampleData;

And here is the test code:

Setup Sample Data Table

Code: tsql

-----------------------------
-- Test

CREATE TABLE #Results (name varchar(30), elapsed int, run int);
DECLARE @StartTime DateTime, @run int;
SELECT @run = 1;

WHILE (SELECT COUNT(1) FROM #Results WHERE name = 'Char(36) - Equals') < 2
BEGIN

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE CharField36 = 'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Char(36) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE VarcharField36 = 'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Varchar(36) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NCharField36 = N'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NChar(36) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NVarcharField36 = N'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NVarchar(36) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE CharField36 LIKE '%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Char(36) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE VarcharField36 LIKE '%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Varchar(36) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NCharField36 LIKE N'%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NChar(36) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NVarcharField36 LIKE N'%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NVarchar(36) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE CharField72 = 'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Char(72) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE VarcharField72 = 'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Varchar(72) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NCharField72 = N'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NChar(72) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NVarcharField72 = N'abcd' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NVarchar(72) - Equals', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE CharField72 LIKE '%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Char(72) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE VarcharField72 LIKE '%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('Varchar(72) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NCharField72 LIKE N'%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NChar(72) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SET @StartTime = GETDATE();
SELECT COUNT(1) FROM CharTypeTest WHERE NVarcharField72 LIKE N'%abcd%' OPTION (MAXDOP 1);
INSERT INTO #Results(name,elapsed,run) VALUES('NVarchar(72) - Like', DateDiff(ms, @StartTime, GETDATE()),@run);

SELECT @run = @run + 1;
END;

SELECT * FROM #Results;

DROP TABLE #Results;

I realize this is not a groundbreaking post, but after finding the results out for myself I thought it would be interesting to share. It also underlines and italicizes the need to fit your data definitions to the data you will be storing.

Original post blogged on LessThanDot.

Using T-SQL OUTPUT and MERGE To Link Old and New Keys

Eli Weinstock-Herman (tarwn) — Tue, 13 Sep 2011 11:44:00 +0000

You don't catch me doing SQL posts that often, probably because I spend far more time working in a development world that seems to be moving further and further away from SQL. That being said, the database server is optimized towards handling large sets of data and data manipulations, so rather than second-guessing developers that know far more (and have far larger budgets), I like to take advantage of database-side solutions when I can. The time saved not dragging data back and forth on the network is an extra bonus.

Recently I needed the ability to transfer records (and control of the data) between two data structures on demand. The original data structure has grown up over the course of 10+ years as part of a larger system, while the newer, purpose-built data structures exist specifically to support this data and support newer capabilities. The tricky part is that the end user gets to pick and chose which records will be managed by the newer system and which will not, on demand.

Sample Table Schemas

To get us started, I've created some simplified, substitute table for the post. Using simpler tables that are far fewer in count will let us focus more on the problem at hand and less on my data definition skills.

Code: tsql

use SampleStuff;
Go

-- "Original" tables with columns for new keys

CREATE TABLE dbo.Orig_User(
Orig_User_Key int IDENTITY(1,1) NOT NULL,
Username varchar(20) NOT NULL,
New_User_Key int NULL
);

CREATE TABLE dbo.Orig_Work_Phone(
Orig_Work_Phone_Key int IDENTITY(1,1) NOT NULL,
Orig_User_Key    int NOT NULL,
Phone    varchar(20),
New_Phone_Key    int NULL
);

CREATE TABLE dbo.Orig_Home_Phone(
Orig_Home_Phone_Key int IDENTITY(1,1) NOT NULL,
Orig_User_Key    int NOT NULL,
Phone    varchar(20),
New_Phone_Key    int NULL
);

-- And the "New" tables
CREATE TABLE dbo.New_User(
New_User_Key int IDENTITY(1,1) NOT NULL,
Username varchar(20) NOT NULL
)

CREATE TABLE dbo.New_Phone(
New_Phone_Key int IDENTITY(1,1) NOT NULL,
New_User_Key int NOT NULL,
Phone    varchar(20) NOT NULL,
Is_Work   bit NOT NULL
);

Note: For the sake of shorter examples, I'm going to provide examples on the User tables. We don't actually need anything fancier than SCOPE_IDENTITY() until we get to the step where we transfer Phone records since we are doing one user at a time, but that's why examples are so much easier than the real world.

The tricky part of this transform is the ownership of the data. Due to some internal restrictions, the original data structure is going to be the master. This means the original record is responsible for holding both its own key and the key in the newer system. The transform needs to get the record from the original system, push it into the newer system (generating the new key), then update the new key back into the original record.

If this was a one-shot ETL load, I would probably add a temporary column to the target tables to track the source keys, then update the new keys back into the source table and drop the temporary column. As you can imagine, this is an even less graceful solution when we put control into the users hands and operate at the individual record level (c'mon, it's only a few hundred thousand table locks during peak hours).

The OUTPUT Statement

Added in SQL Server 2005 (6 years ago already?), OUTPUT can be used to return data from inside INSERT, UPDATE, DELETE, and MERGE statements. This solves the first part, giving us access to the new key when it's generated. Well, almost.

Code: tsql

CREATE TABLE #ID_Transfer(new_key int, old_key int);

INSERT INTO dbo.New_User(Username)
OUTPUT Inserted.New_User_Key, ??? INTO #ID_Transfer
SELECT OU.Username FROM dbo.Orig_User OU
WHERE OU.Orig_User_Key = @TargetUserKey;

While we can easily create a temporary table to capture the resulting New_User_Key that is generated on insert, we don't have access to the table the data is coming from, so we don't have the original key to populate next to that new key.

Which brings us to MERGE.

The MERGE Statement

MERGE was added in SQL 2008 and allows us to define a source dataset, target data set, and rules to insert or update data from that source to the target. While we don't really need the rules and update capability, what it also provides is simultaneous access to both datasets AND support for OUTPUT. Aha, I hear you saying.

By forcing the merge to always perform an INSERT, we will satisfy our needs for inserting the data into the "New" tables as well as have the capability to capture both the original and new key in a single OUTPUT statement.

Code: tsql

CREATE TABLE #ID_Transfer(new_key int, old_key int);

MERGE INTO dbo.New_User AS [Target]
USING (SELECT OU.Orig_User_Key, OU.Username FROM dbo.Orig_User OU WHERE OU.Orig_User_Key = @TargetUserKey) AS Source
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (Username)
VALUES(source.Username)
OUTPUT INSERTED.New_User_Key, source.Orig_USer_Key INTO #ID_Transfer;

Full Roundtrip

With the critical pieces out of the way, it's easy now to create the full round-trip update.

Code: tsql

CREATE TABLE #ID_Transfer(new_key int, old_key int);

DECLARE @TargetUserKey int;
SELECT @TargetUserKey = 1;

MERGE INTO dbo.New_User AS [Target]
USING (SELECT OU.Orig_User_Key, OU.Username FROM dbo.Orig_User OU WHERE OU.Orig_User_Key = @TargetUserKey) AS Source
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (Username)
VALUES(source.Username)
OUTPUT INSERTED.New_User_Key, source.Orig_USer_Key INTO #ID_Transfer;

-- and back-update original record with new key
UPDATE dbo.Orig_User
SET New_User_Key = IT.new_key
FROM dbo.Orig_User OU
INNER JOIN #ID_Transfer IT ON IT.old_key = OU.Orig_User_Key;

DROP TABLE #ID_Transfer;

And we can see the updated values like so:

Code: tsql

SELECT TOP 20 *
FROM dbo.Orig_User O
LEFT JOIN dbo.New_User N ON N.New_User_Key = O.New_User_Key

So that was the first step, next is the phone numbers.

Homework/Practice

Given the sample tables above, it would now be fairly straightforward to apply this idea to the phone number tables, populating the data from the original two into the single new table and back-populating the new keys back into the original records. Rather than give you the final script, I'll instead give you the pieces needed to get this far.

Setup script:

Code: tsql

use SampleStuff;
Go

/*
DROP TABLE dbo.New_Phone;
DROP TABLE dbo.New_User;
DROP TABLE dbo.Orig_Home_Phone;
DROP TABLE dbo.Orig_Work_Phone;
DROP TABLE dbo.Orig_User;
DROP TABLE dbo.Number;
*/

CREATE TABLE dbo.Orig_User(
Orig_User_Key int IDENTITY(1,1) NOT NULL,
Username varchar(20) NOT NULL,
New_User_Key int NULL
);

CREATE TABLE dbo.Orig_Work_Phone(
Orig_Work_Phone_Key int IDENTITY(1,1) NOT NULL,
Orig_User_Key    int NOT NULL,
Phone    varchar(20),
New_Phone_Key    int NULL
);

CREATE TABLE dbo.Orig_Home_Phone(
Orig_Home_Phone_Key int IDENTITY(1,1) NOT NULL,
Orig_User_Key    int NOT NULL,
Phone    varchar(20),
New_Phone_Key    int NULL
);

CREATE TABLE dbo.New_User(
New_User_Key int IDENTITY(1,1) NOT NULL,
Username varchar(20) NOT NULL
)

CREATE TABLE dbo.New_Phone(
New_Phone_Key int IDENTITY(1,1) NOT NULL,
New_User_Key int NOT NULL,
Phone    varchar(20) NOT NULL,
Is_Work   bit NOT NULL
);
Go

CREATE TABLE dbo.Number(num int IDENTITY(1,1) NOT NULL);
GO

SET NOCOUNT ON;

INSERT dbo.Number DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Number DEFAULT VALUES ;

INSERT INTO dbo.Orig_User(Username)
SELECT 'User #' + CAST(num as varchar)
FROM dbo.Number;

INSERT INTO dbo.Orig_Work_Phone(Orig_User_Key, Phone)
SELECT Orig_User_Key, 'Phone ' + CAST(Orig_User_Key as varchar)
FROM dbo.Orig_User
WHERE Orig_User_Key % 2 = 0;

INSERT INTO dbo.Orig_Work_Phone(Orig_User_Key, Phone)
SELECT Orig_User_Key, 'Phone ' + CAST(Orig_User_Key as varchar)
FROM dbo.Orig_User
WHERE Orig_User_Key % 3 = 0;

INSERT INTO dbo.Orig_Home_Phone(Orig_User_Key, Phone)
SELECT Orig_User_Key, 'Phone ' + CAST(Orig_User_Key as varchar)
FROM dbo.Orig_User
WHERE Orig_User_Key % 3 = 0;

INSERT INTO dbo.Orig_Home_Phone(Orig_User_Key, Phone)
SELECT Orig_User_Key, 'Phone ' + CAST(Orig_User_Key as varchar)
FROM dbo.Orig_User
WHERE Orig_User_Key % 4 = 0;

Given those pieces above and some additional scripts, finishing the script should only take 5-10 minutes. Consider it a free practice problem

Original post blogged on LessThanDot.

SQLAzure - My First Cloud

Eli Weinstock-Herman (tarwn) — Thu, 12 May 2011 13:30:00 +0000

This past weekend I made my first foray into SQL Azure. SQL Azure has been on my list of "to learn" technologies and lately it made it closer to the top. While I had picked up some knowledge from random links in the twitter-sphere, I hadn't made a concerted effort to learn about it yet.

I started with a site named Microsoft Virtual Academy. This site (despite some usability issues with the dialog boxes prior to signup) offers a number of virtual "courses" that consist of links to external resources grouped into modules, each of which finishes with a short assessment. As I started working my way through the modules for SQL Azure it so happened that Brian Harry (blog) posted a link to a free Azure trial, which was handy since I no longer have an MSDN account (MSDN accounts are also offered a free trial, check out your subscription page for more information).

On top of this information it happened that Buck Woody (blog|twitter) posted up an even more extensive list of information on Azure technologies, so it looks like my to-learn list is going to be full for a while.

As if that flurry of links wasn't enough, DeepFriedBytes posted a podcast with Buck Woody on the importance of cloud (ahem, distributed) technologies. If there is one link you follow-up on, make it that one.

My First Azure Database

I followed the instructions provided by the resources above to create my account, configure the firewall rules, and create my first database. But then I was stumped, what was I going to put in it?

It so happens that I've been working on an idea for a pet project for some time (well, one of many). In fact I had already used this idea to practice prototyping, requirements elicitation, and creation of an Agile-y product backlog. Some people knit, it never really caught on for me.

Prototype image 3 of 8

Not only that, but the act of coming up with user stories (potential features) forced me to invent a common terminology and recognize which terms were coming up the most frequently.

70+ User Stories/Features

So I might have enough information to put together a normalized database to support the core functionality, which is about as close as I am going to get to a realistic data model on a Sunday evening.

Creating the Database

Creating the first database was fairly straightforward (video) and consisted of setting up my subscription, creating a new server, and then creating the database. The main interface uses Silverlight, so unfortunately I wasn't able to use my [insert tablet here] (the future is here?). After the initial database 'server' was setup, I switched over to SSMS to actually build out my data model.

I Also Drew A Data Model...with Color (Oooo)

The first step was to add my database to SSMS. As I already have 2008 RS on my local machine, this was fairly straightforward. I clicked the "Connect" button in SSMS, entered the full server name from the Azure interface (right toolbar after creating "Server") and the credentials I had created as part of setting up the database "Server". Voila, it's that easy. The database server is connected, but instead of the usual yellow cylinder I get this nifty new icon.

Now that I was connected I right-clicked to wizard my way through database creation only to find myself staring at a new script window. Unfortunately the GUI capabilities for Azure databases are limited and many times in mousing my way around the GUI I found myself staring at unexpected script windows, which was a little disconcerting and showed me how dependent I had grown towards the interface.

Creating my database, I found that nearly all of the work was done through scripts instead of the usual GUI windows. Attempting to create the database or tables in the GUI generated a script with a SQL snippet to help me out. Luckily I knew well enough to include indexes on each of my tables (Azure requires each table have a clustered index), so this part went smoothly.

What I hadn't counted on was how ingrained the usage of the IDE had become. Twice I accidentally left off IDENTITY's that I had intended to write, and both times I found it easier to drop and recreate the table from scratch. Attempting to script the table from SSMS (Right click, Script Table As, aww no "Alter", OK then "Create") resulted in the usual CREATE TABLE command being scripted, including some options that aren't available in Azure.

Despite some hiccups, however, I was able to build out my entire data model in an hour. Considering I was having to polish some rusty skills along the way and had never touched Azure before, this seems fair.

What's Next

Now that I have a database, I'm tempted to use it. Continuing plans include building a sample application to talk to this database, playing with some fail over options, and generally trying out this whole SQL (and Windows) Azure technology. So far I've found the technology to be remarkably easy to start using, lets see if that continues to be true.

I suspect having missed the distributed computing course in college, this will all get much trickier at some random point in the future.

And now go click that DeepFriedBytes link up top and listen to Buck, you'll thank me.

Original post blogged on LessThanDot.

SQL Saturday #46, Raleigh NC

Eli Weinstock-Herman (tarwn) — Wed, 22 Sep 2010 10:32:50 +0000

It's been a few days since SQL Saturday 46 and I have finally found some time to jot down my recap. This was the first Raleigh SQL Saturday and it lived up to all of my expectations.

The Event

The excellent group of volunteers
that pulled it all together

There were 6 tracks of events, which were balanced more towards the BI and SSIS side of things than the previous events I've attended. Registration was hiccup free (though I was early and missed the rush), rooms were all located in close proximity to one another, and the lunch room flowed pretty quickly despite an early line. The signs and schedule were excellent, with 8.5x11 placards posted for all of the sessions on each room's door as well as large grids posted on the wall in two locations, displaying the whole schedule. Once I figured out the room numbering, it was easy to locate the sessions or speakers I was interested in. The event was organized by members of TriPASS, the local SQL Server User Group in Raleigh, and I'm looking forward to getting involved with the next one they put together. If I had to pick one element of the event that characterized it the most, that element would be how smoothly the execution went.

Sessions

Grant Fritchey getting
started on his session

I started my morning off with Geoff Hiten's (blog | twitter) "Clustering for Mere Mortals" session, which gave me enough basic knowledge that there may be a clustering blog article in the near future. Geoff did an excellent job of communicating a complex task to his audience, balancing just enough background knowledge, direct examples, and good practices from his own experience.

I expanded my CDC knowledge in Chris Skorlinski's (blog) first session, but unfortunately wasn't able to make his SSIS + CDC session later in the afternoon (the disadvantage to being in my home town, had to run home to feed the cats). He provided an excellent overview of how CDC works, how to interact with it, and some of the internals behind the magic.

After lunch, I attended Grant Fritchey's (blog | twitter) "Identifying and Fixing Performance Problems using" presentation and had a blast (I'm using the SQL Saturday site-ized version of his presentation name). Despite some demo issues, Grant managed to balance delivery of his presentation with good audience feedback, even taking a 2 minute break in the middle to get everyone up on their feet and fight the post-lunch effects.

After Grant's session I caught Andy Leonard's (blog | twitter) "SSIS Design Patterns" session. I was completely out of my depth here, being far more of a beginner in SSIS (it doesn't get much more beginner), but I was still able to take away quite a bit of information from the session, including some ideas I will likely try to put into practice in an upcoming project.

The After-thing

I managed to run into quite a few of the local members of the user group, who did an excellent job preparing and executing this event. It seemed Janice Lee (blog | twitter) and I were constantly running into each other just long enough to share how great our last session was before heading off to two more completely different ones. It was one of these between-session breaks where I got to meet Eric Humphrey (blog | twitter) for the first time, another familiar face from the SQL community on twitter. There were many others I met along the way and at least as many people that I intended to meet but missed (or who snuck out early).

The day ended on a high note, around a table with Grant, Allen White (blog|twitter), Brent Kremer, and David Elvikis. Stories were traded, good food was eaten (not counting the fried pickles that showed up), and I think we all had a fun time. It was great to find out that Grant and Allen were just two more normal (well, close to normal) people like the rest of us. Somehow I managed to leave owing a round. So the next time we end up at the same event Grant, don't be surprised if a drink shows up

Unfortunately I fell down on the job where pictures and tweets were concerned, so there aren't as many as there have been for past events.

SQL Saturday 46 was an excellent event and ultimately both a lot of fun and a lot of information, what I have grown to expect from SQL Saturday events. The sessions were great, the event went smoothly, and the networking with the local and greater community was everything I had hoped for. I'm already looking for to my next one.

Original post blogged on LessThanDot.

SQL Server Types - Numeric vs Int

Eli Weinstock-Herman (tarwn) — Thu, 02 Sep 2010 12:20:43 +0000

Generally when we are defining tables, the more specific the column definition the better. Yesterday, however, I ran into a case where better definition actually has increased storage use for no appreciable benefit.

Integers - Using Numeric vs Int

As I was working on the database I came across a curious sight, multiple columns defined as numeric(7,0), numeric(9,0), and so on. It seemed like someone was trying to provide the database with the most specific definition possible for a number of different pieces of data. Having never run into this particular practice, I immediately started searching for a reason. Was it smaller? faster? better?

Storage

Using a very specific, well-defined numeric has actually cost us storage space, not reduced it. A numeric (or decimal) with a precision value of 1 to 9 requires 5 bytes and with 10-19 requires 9 bytes. Compare this to the many varieties of int:

Digits	Int Variety	Int Bytes	Numeric(*,0) Bytes	Difference
1 - 2	tinyint	1	5	4 bytes
3 - 4	smallint	2	5	3 bytes
5 - 9	int	4	5	1 byte
10 - 18	bigint	8	9	1 byte

So for every row and every index that includes this value, we lose storage space.

References: Int reference on MSDN and Numeric/Decimal reference on MSDN

Performance #1

When SQL Server is asked to execute a math function (+,-,*,/), it uses a defined set of rules to determine the output type, then implicitly converts the arguments to that type (see this article for a subset that relates to decimals). This means that in many cases there could be implicit conversions to numeric from int, so it's possible someone believed we could try and tweak our performance by defining the field as numeric instead of an int.

Let's test out implicit conversions:

Code: tsql

/* ****** Creation of some number tables ****** */
Create Table NumberIntTest(Num Int Identity(1,1) Primary Key)
go

Set NOCOUNT ON
Begin Tran
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Insert Into NumberIntTest Default Values
Commit Tran
go 100000

Create Table NumberNumericTest(Num numeric(7,0) Primary Key)
Go

Set NOCOUNT ON
Begin Tran
Insert Into NumberNumericTest(Num) Select Num From NumberIntTest WHERE Num > 10001
Commit Tran
Go

/* ****** Execute a variety of test scripts ****** */
DECLARE @Start DateTime;
DECLARE @Garbage int, @Junk numeric(7,0);

DECLARE @Int int; SET @Int = 1;
DECLARE @Num numeric(7,0); SET @Num = 1;

-- Divide an int by an int
SELECT @start = GETDATE();
SELECT @Garbage = Num/@Int FROM NumberIntTest n;
SELECT DateDiff(Millisecond, @Start, GetDate());

-- Divide a numeric(7,0) by an int
SELECT @start = GETDATE();
SELECT @Junk = Num/@Int FROM NumberNumericTest n;
SELECT DateDiff(Millisecond, @Start, GetDate());

-- Divide a numeric(7,0) by a numeric(7,0)
SELECT @start = GETDATE();
SELECT @Junk = Num/@Num FROM NumberNumericTest n;
SELECT DateDiff(Millisecond, @Start, GetDate());

-- Divide an int by an int w/ explicit casting to numeric
SELECT @start = GETDATE();
SELECT @Junk = Num/CAST(@Int as numeric(7,0)) FROM NumberIntTest n;
SELECT DateDiff(Millisecond, @Start, GetDate());

-- Divide an numeric(7,0) by an int w/ explicit casting
SELECT @start = GETDATE();
SELECT @Junk = Num/CAST(@Int as numeric(7,0)) FROM NumberNumericTest n;
SELECT DateDiff(Millisecond, @Start, GetDate());

Initially I compared the execution plans and didn't see much difference, but after some modifications (thanks George!) and additions we can see the differences between a number of different situations.

Sample Results:

Test	time (ms)
int/int - No Cast	170
numeric/int - Implicit Cast	313
numeric/numeric - Implicit Cast	296
int/CAST(int as numeric) - Implicit Cast	320
numeric/CAST(int as numeric)	290

In the second test's plan we can see an example of that implicit cast:

numeric/int w/ Implicit Cast:

  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(numeric(7,0),[utils].[dbo].[NumberNumericTest].[Num] as [n].[Num]/CONVERT_IMPLICIT(numeric(10,0),[@Int],0),0)))
       |--Clustered Index Scan(OBJECT:([utils].[dbo].[NumberNumericTest].[PK__NumberNu__C7D08B630AD2A005] AS [n]))

So if we did have an integer that we need to operate on with a float value (the last case), the addition of a simple cast on the integer argument will bring the execution performance in line with having two numerics, meaning there is no gain in storing the value as a numeric(7,0).

Performance #2

The other potential performance impact is with auto-parameterization. Auto-parameterization occurs when you provide SQL Server with a non-parameterized SQL statement. The server determines a type for those parameters and parameterizes them (part of the magic that makes plans reusable for non-parameterized queries). I couldn't find anything terribly recent, but as far back as SQL Server 6.5 and 7.0 the engine was documented as using the int type for any non-decimal value of 9 digits or less. This means that in the unlikely situation that you're executing inline, non-parameterized SQL statements and have used numeric(*,0) types in your table definitions, you will actually be taking a performance hit for the implicit conversion from auto-parameterized integer to the numeric(*,0) field.

And if that wasn't bad enough, the same SQL Server documentation says that SQL Server treats integers as more exact than numeric and decimal types. It doesn't specify why the document goes out of its way to share this information with us, but generally when someone goes out of their way to point out something like this in a document, I get a little nervous and tend to focus more heavily on their 'recommended' practice (use int).

More information on Parameterization and SQL 7 Comparison Optimization

The Wrap-up

So at the end of the day, using a numeric(*,0) requires more space, provides no appreciable benefit over explicit casting, and can actually harm you if you are executing non-parameterized SQL statements against your server.

There are two options for finding these columns, using a SQL query like the one below or downloading SQLCop to check for this and many other common situations.

Code: tsql

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.DATA_TYPE IN ('numeric','decimal') AND NUMERIC_SCALE = 0 AND NUMERIC_PRECISION <= 18

Original post blogged on LessThanDot.

SQL Saturday 28, Baton Rouge

Eli Weinstock-Herman (tarwn) — Mon, 16 Aug 2010 09:47:36 +0000

I'm in the air flying back from SQL Saturday 28 and wanted to capture my thoughts while the event was still fresh. Overall I would have to rate it as a resounding success. Last year the event attracted 200 professionals from Baton Rouge and the surrounding area, this year attendance doubled to 400, with additional interest from about 200 more that weren't able to make it.

Patrick LeBlanc (blog|twitter)
Speaking Friday night

The event featured 8 tracks, including SQL and .Net sessions. Several early-bird sessions kicked off around 7:30AM - I have to applaud those brave enough to speak in front of a crowd that early, as I was still getting appropriately caffeinated. In addition, INETA made it possible for Keith Elder (blog|twitter) to provide a full day's ".Net From Scratch" training session. Steve Jones (blog|twitter) delivered the keynote, starting the day off with commentary on professional development, technical skill, and the importance of enjoying our jobs and finding balance in life.

The dinner the night before was in a large enough venue that we were able to mingle and talk a good bit before the dinner appeared, and the food was both good and plentiful (which worked out well for those of us who had been eating airport fare). Patrick LeBlanc (blog|twitter) provided our mid-evening entertainment, welcoming everyone to the area and introducing us to the team that had worked so hard to put the event together. Somehow I managed to end up at the Sparkhound table, but they're a friendly group and it made for a fun dinner.

Chris Eargle (blog|twitter)
Documenting his Audience

The event took place at LSU in a lecture hall, which meant we had classrooms with integrated power and overheads, simplifying some of the setup. A full size classroom was set aside for the speakers and featured plenty of space for us to get off our feet, plug in, or just get a snack throughout the day.

My session was right after lunch (see the Conceptual Data Modeling post from last week). Despite speaking with no code examples in an after lunch session, I still managed to draw a number of inquisitive people. Despite ending a few minutes early, we still managed to almost run over due to the great conversation we had at the end. Thomas LeBlanc (blog|twitter) presented on normalization and data modeling after my session and I stuck around to watch his presentation as well, picking up some ideas for the next version of my presentation.

Of the three SQL Saturday events I attended this year, this one was probably the best represented on twitter during the day. William Assaf (blog|twitter) kept the media flowing, constantly uploading pictures and commentary throughout the day and keeping the @SQLSatBR account busy.

A great team of people (click for larger)

As is always the case with the events, the best part was the people. I met a number of new people, at varying places in their respective careers, and had interesting conversations that lasted until the restaurant hosting the after party finally asked the last four of us to leave. I wasn't able to work everyone's names into the post, but it was a great group and there a lot of names both familiar and new. From what I hear, the planning for next year is already in progress, so maybe I'll see you there.

For more pictures, check out William's full gallery on Picasa.

And the slide decks for my session are posted now.

Original post blogged on LessThanDot.

Creating a Conceptual Data Model

Eli Weinstock-Herman (tarwn) — Tue, 10 Aug 2010 11:20:20 +0000

There are three types of data models: Conceptual, Logical, and Physical. The conceptual model provides a high-level view of the data, defining the general entities and entity relationships using the language of the business or organization. The logical model adds attributes to these entities, providing a technology-agnostic foundation for a database design. The physical model assigns table names, column names, and data types to the entities and attributes defined in the prior models. Defining the data model in distinct layers helps us manage the complexity of design and focus as we refine our understanding of the problem space.

This topic is a sub-set of my presentation later this week at Baton Rouge, SQL Saturday #28. If you are planning on being in the area I urge you to sign up, there are going to be a wide range of speakers presenting on SQL and .Net development topics and so far it sounds like we have over 500 attendees (read: networking opportunities). My slides are posted up on my website.

Building the Conceptual Model

My approach is to start with the business drivers for the data and progress through external materials, technical inputs, and an analysis phase. Whether we are in a university environment, software development house, manufacturing company or non-profit organization, there is generally one key business driver. That driver has a business owner, technical or non-technical, and it is there that I prefer to start. Conversations with the business owner will often include less than specific terms and examples of competing services or existing data stores (think excel and MS Access) in the organization. Factors like maintainability, reliability, scalability, and so on will be mentioned in some cases, so we will want to document these if they come up. This provides the raw materials we need to build our data model.

What is the purpose of [fill in the blank]?

Context and expectations are key
Unnamed Picture on Picasa

Start with the purpose of the project or initiative. As the business owner describes the purpose of the project, we collect the nouns and specific terms they use and how those words are related. A notepad can be sufficient but a small recorder is probably the best tool (especially if your memory and handwriting are less than stellar). The terms we collect during this conversation generally fall into three buckets: initial entities to populate a rough model with, attributes that may need to be analyzed in later steps, and existing materials/systems/projects/people to follow up with. In this step I often find it easier to deal with non-technical people, as a technical person will sometimes communicate the design they have started in their head rather than the original business case.

What does the business do?

It may not always be necessary to ask the business this (hopefully if you work there you already know the answer), but at a minimum it should be written somewhere near your other notes. The business provides context for the data and drives the relative importance of different terms and entities. It's important to remember that one company's definition of "customer" could be entirely different from another company's. Knowing the context helps us determine how important the terms from the prior conversation are and the wide variety of contexts is the reason there isn't one world-wide standard for the "Customer" entity.

Outside influences can sneak up
Sneaky Cat on Flickr

About those other materials...

Any additional materials, products, or people referenced in that first conversation need to be tracked down. Materials will expose a much more detailed list of potential data fields then you will hear in a conversation, competing products or products priced out of the organizations range will provide additional terminology and purpose, while other people will provide both a sounding board for the assumptions so far and a source of secondary translations and assumptions. The important part is that we all started with the same base story (the initial business owner) before getting this far, as that frames these conversations and investigations. And yes, I really want to use the word context again.

What's sneaking up on us?

The last source of information we want to address is projects that are starting in the near future, strategic initiatives, and similar surprises waiting only a few weeks down the road. Going back to the business to ask about planned projects in the near future can often uncover additional information that will be critical to the model. Changes in business direction, assumptions of geographic growth or acquisitions, and even projects that are intended to tie into this data source can all mean the difference between a completed model and a redo of the modeling effort.

Put it all together...

So by now we should be able to put it all together, right? Well, maybe. Probably not.

Now that we have all of those raw inputs, all of those conversations, all of those identified terms and topics we need to start trying to fit it all into a cohesive structure. So as we start drawing pieces (and usually I am drawing all along) we are going to run into more questions, we are going to have moments where we stare dumbfounded at our whiteboards, and there will be a number of odd moments of clarity (generally in the shower or coffee line).

Turn the problem sideways
Cat in Basket on Flickr

This last step incorporates the "Analysis" phase that I mentioned above. This is where we look at all of the potential attributes we have collected (you kept a list to make the logical phase easier, right?) and consider them from multiple different directions.

As we look through our list of entities and potential attributes, we want to question them on the dimensions of time, availability, location/person, and categorization:

Do we need to track the value as it changes over time?
Do we need to provide a mechanism to enter future values?
Do these values get deleted, flagged as inactive, or ...?
Do different variations of the value exist based on Location? Language? Currency? Person viewing the data?
Can entities be re-categorized (ie, relationships changed)?
Can relationships/categorizations be duplicated? Many-to-Many? Time sensitive?

These questions can often spark the creation of new entities or extra information to consider in later modeling efforts. This extra analysis (and the rigor with which we gathered information) is what brings everything together and, in my mind, is where our expertise and experience should shine.

And Done-ish

There will be a time when you have to call it done (for now). I probably spend the most time on the conceptual model because the clearer and better a fit it is, the easier it is to come back and start attaching attributes and to communicate amongst the business and other technical personnel. But even so, perfect is the enemy of good, so we want to reach good and call it a day.

The Finished Conceptual Model

At the point we call it "Finished [for now]", we will have one or more diagrams that display the different entities and relationships, using business terms. We will also have a whole slew of notes on what those terms meant, other terms that were mentioned during the exploration (potential attributes, technical requirements, etc), and a partially worn out whiteboard. These our our deliverables from the conceptual modeling process and provide the foundation for our next steps.

Original post blogged on LessThanDot.

Virtual Lab: Setting up Database Mail on SQL Server 2008 R2

Eli Weinstock-Herman (tarwn) — Tue, 06 Jul 2010 10:02:26 +0000

After our installation of SQL Server 2008 R2 a few weeks ago, I mentioned that we still had some additional setup tasks before we could consider the server to be done. This article covers setting up DatabaseMail, the internal mail client/management system that will allow us to use built-in alerts (among other things).

Technical Area: Accidental Database Administrator

Level of Difficulty:
Additional Articles:Virtual Lab entry on the LTD Wiki

Database Mail is a subsystem that acts like a SMTP client, allowing us to send emails from SQL Server processes and scripts. It replaces SQL Mail and doesn't require a local installation of additional software (like Outlook, the quick fix) to function.

Account, Profile, Service Broker, what?

Database Mail expands on the concept of sending mail to include all of the features we wanted in SQLMail. This grown up version of mail uses SMTP to communicate with mail servers, no longer requiring a MAPI component to communicate messages (the reason we used to install products like Outlook to make SQLMail work). Behind the scenes, Database Mail uses Service Broker to manage email in queues, rather than trying an immediate send. Profiles allow us to define a chain of fail-over accounts to send from, so that messages are never left undelivered due to a primary email server being unavailable. And accounts are exactly what they sound like, individual, unique accounts that we can setup to use a variety of authentication and SMTP options.

From the GUI

Database Mail is something we want to setup immediately on creating a new server. When we are working with a relatively small number of servers, the GUI is not a bad option for setting up some accounts.

We'll start by setting up an administrator profile with then intent to send critical notifications and alerts to this profile.

In SSMS we can right click the Database Mail entry under Management to begin the wizard.

DatabaseMail Menu Item in SSMS

The Intro screen is fairly boring, so lets press "Next" and head to the main configuration page. This being the first time we are configuring DatabaseMail on our new server, we want to leave the first option selected and continue.

DatabaseMail Wizard in SSMS

A quick check by the wizard and it determines that components for Database Mail aren't available on the server yet, so it asks us if we want to go ahead and install them (yes).

How did the server know Database Mail wasn't configured, and what did it do when we pressed 'Yes'?

There is an advanced option in the system configurations called 'Database Mail XPs'. When the option is set to '0' (which is the default), then the Database Mail process doesn't run. So the dialog simply checked the current value and, when we selected 'Yes', updated the configuration to a value of '1'.

Profiles are used to represent a set of email addresses which allows us to represent a single person (or system) with multiple fail-over accounts. If an error occurs when the system is attempting to send mail from the first account in a profile, it fails to the next and retries, continuing until it either runs out of accounts or successfully sends it's message.

Lets enter a Profile Name of "The Accidental Admin".

DatabaseMail Wizard in SSMS

Now we'll add the first two accounts to this profile, each on a different network. Many admins will choose a non-local account with an external provider as their primary to reduce the chances that emails will not get through when other issues are happening. As many of you know, when a server room overheats the exchange server is always the first to go, so we will start with an external provider and fail-over to an internal one. Keep in mind that these are the accounts the database will send emails from when using the specified profile, not to.

Creating an account is fairly straightforward, simply enter the details for where you would like the email to be sent and any authentication options that are necessary. In this case we want to create a minimum of two accounts so the failover can work properly.

DatabaseMail Wizard in SSMS - Account Entry

Now that we have two accounts, we can move on.

DatabaseMail Wizard in SSMS - Account View

The next step is to decide whether our profile is going to be public or private. A public profile will be accessible to other people working on the system, whereas a private one will not. In this case we will make the profile public but be aware that this means anyone will be able to us the database server to send mail from this particular profile.

DatabaseMail Wizard in SSMS - Profile Security

The last interactive step is to confirm or modify some additional system parameters. This step is not specific to configuring an email profile and accounts, it is displayed as the final step of setting up DatabaseMail (when setting up for the first time). I suggest reviewing the settings to ensure your comfortable with each of them.

DatabaseMail Wizard in SSMS - System Parameters

After a final review screen, press the 'Finish' button to implement the changes.

DatabaseMail Wizard in SSMS - Finished

The last step is to test our new setup. Right-click the Database Mail menu option in SSMS and select "Send Test Email...".

SSMS Menu - Sending a Test Email

After entering an email address for our test message, we can hit the "Send" button and wait for confirmation of our ability to hit "Next, Next, Finish".

SSMS DatabaseMail - Test Email Dialog

or From a Script

Doing this on one server is pretty quick. Banging through the dialog on 20 (or with multiple profiles) is not only going to be a bit slower but probably guarantees at least one bad entry along the way. Luckily there is nothing magical about what SSMS is doing to configure these accounts and profiles, just a wizard driving system procedures behind the scenes. We can follow the same process programmatically and create a setup script. Once we have a setup script, of course, we can then use it against multiple systems and if we save it then we not only can set up future servers the same way, but we have detailed documentation on exactly how our servers were setup when we need it 3 years later.

Here are the commands we will be using:

sp_configure: This procedure is used to change SQL Server configurations
sysmail_add_profile_sp: Used to create a Database Mail Profile
sysmail_add_account_sp: Used to create a Database Mail Account
sysmail_add_profileaccount_sp: Used to tie a Database Mail Account to a Profile (Tab A in Slot
sysmail_add_principalprofile_sp: Assign access rights to a Database Mail profile
sp_send_dbmail: And sending an email, of course

And if we put it all together then we can duplicate the wizardy approach above like so:

Code: tsql

---- Enable the XPs for Database Mail
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs',1;
RECONFIGURE;
EXECUTE sp_configure 'show advanced', 0;
RECONFIGURE;
GO

---- Create our Default Profile
DECLARE @newProfileId int;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'The Accidental Admin v2',
@description = 'A second version of the DB Admin email profile',
@profile_id = @newProfileId OUTPUT;
print 'Profile Created';

---- Create our accounts (You did plan on a minimum of two, right?)
DECLARE @newPriAccountId int,
@newSecAccountId int;
DECLARE @sender varchar(50);
SELECT @sender = 'DB SERVER - ' + @@SERVERNAME;

-- External account using username/password
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'External DBAdmin',
@email_address = 'AnAccount@ExternalService.com',
@display_name = @sender,
@description = 'Primary account for DB Admin profile',
@mailserver_name = 'mail.ExternalService.com',
@username = 'username',
@password = 'password',
@account_id = @newPriAccountId OUTPUT;

-- Internal account using credentials from the account DB server is running under
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Internal DBAdmin',
@email_address = 'YourAccount@YourDomain.com',
@display_name = @sender,
@description = 'Secondary account for DB Admin profile',
@mailserver_name = 'mail.YourDomain.com',
@use_default_credentials = 1,
@account_id = @newSecAccountId OUTPUT;

print 'Accounts Created: ' + @sender;

---- Add Accounts to Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @newProfileId,
@account_id = @newPriAccountId,
@sequence_number = 1;

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @newProfileId,
@account_id = @newSecAccountId,
@sequence_number = 2;

print 'Accounts assigned to Profile';

Go

---- And Then We Test
-- Make sure you update the profile name with the value from above
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'The Accidental Admin v2',
@subject = 'Test Database Mail Message',
@recipients = 'You@YourDomain.Com',
@body = 'Test Message';
print 'Test Email Away!';

GO

Running this script creates a setup similar to what we created in the first section, though some of the names have been changed (and I have removed my email address from the list so I don't get server updates when someone comes along and forgets to swap out the addresses).

And Now, Uses

So now that we have Database Mail setup, what are we going to use it for? Well, besides creating our own monitoring and email scripts, the built in Alerts component in SQL Server uses Database mail to tell us when critical parts of our server have decided to misbehave. SQL Agent jobs have the option to notify us when they fail and they do so using, yep, Database Mail. Setting up Database Mail and using it in each of these situations won't prevent problems from occurring, but it is the difference between finding out when the problem is occurring and finding out after the server has melted down.

Next week will be something interesting, but I haven't determined what that will be yet, so we'll figure it out as we go.

Original post blogged on LessThanDot.