February 2007 - Posts

Everything Scales

The tune to a Bush song is running through my head as I type this... the band, not the president - although imagining the President singing the song is an interesting brain-stretch.

It's a fact of IT life that everything scales. Some successfully, even. Problems start when things do not scale successfully (or well). It happens in business. It happens with software systems.

When it happens with businesses, you hear things like "They grew too fast." When it happens with software systems, you browse to a website and receive an HTTP 500 or 404 error.

Can this be avoided (in business or software)? I think that's an excellent question - one well worth examining.


The answer, I believe, lies with how predictable the scalability is.

Consider a database application: If you know which tables are going to grow, how, and how much, you can plan for said growth. How would you plan? You could partition the tables using one or a combination of partitioning techniques. You could appropriate filegroups, snapshots, and a host of other functionality. If you only you knew where to apply these techniques.

That's the key.


Achieving scalability starts with capturing metrics. If you know how your database is growing from the beginning - if you can chart the growth of individual tables, access patterns, and internal performance data - you can predict growth and manage scalability.

So the key is measurement.

Measurement is an engineering discipline in its own right. The field of applied measurement is called Instrumentation. Applying measurement to a process is referred to as "instrumenting the process."

How do you instrument a database process? Iteration 1 would include creating an internal table to house and maintain process metadata:

CREATE TABLE dbo.ProcessData
(ProcessDataID int IDENTITY(1,1) NOT NULL,
ProcessDataDateTime datetime NULL CONSTRAINT DF_ProcessDataDateTime DEFAULT (getdate()),
ProcessDataIndicatorName varchar(50) NULL,
ProcessDataIndicatorValue varchar(50) NULL,
ProcessDataIndicatorStatus char(1) NULL CONSTRAINT DF_ProcessDataIndicatorStatus DEFAULT ('C'),
CONSTRAINT PK_ProcessData PRIMARY KEY CLUSTERED (ProcessDataID)

If your instrumented process is stored-procedure-based, you could add INSERT statements to your existing stored procedures. Consider instrumenting a parent stored procedure that calls child stored procedures. The instrumented proc could look like the following (instrumentation emphasized):

CREATE PROCEDURE dbo.SomeProcess
AS

begin

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc1','Starting');


EXEC dbo.ChildProc1

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc1','Ending');

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
('ChildProc2','Starting');


EXEC dbo.ChildProc2

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc2','Ending');

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
('ChildProc3','Starting');


EXEC dbo.ChildProc3

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc3','Ending');


end

Before moving forward, removing code duplication would be a worthwhile effort. In application development, this is one of many processes generally referred to as Refactoring.

The INSERT statements are a prime candidate for refactoring and we can address this with a stored procedure:

CREATE PROCEDURE dbo.AddProcessData
@ProcessDataIndicatorName varchar(50),
@ProcessDataIndicatorValue varchar(50)
AS

begin

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName, ProcessDataIndicatorValue)
VALUES(@ProcessDataIndicatorName, @ProcessDataIndicatorValue);

end

Now the parent stored procedure instrumentation above can be modified to look like this:

CREATE PROCEDURE dbo.SomeProcess
AS

begin

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc1', @ProcessDataIndicatorValue='Starting';

EXEC dbo.ChildProc1

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc1', @ProcessDataIndicatorValue='Ending';

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc2', @ProcessDataIndicatorValue='Starting';


EXEC dbo.ChildProc2

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc2', @ProcessDataIndicatorValue='Ending';

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc3', @ProcessDataIndicatorValue='Starting';


EXEC dbo.ChildProc3

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc3', @ProcessDataIndicatorValue='Ending';

end

Much better.

Measuring the current process provides a baseline - the first step in a continuous improvement process that will provides dynamic design changes, performance monitoring, and - eventually - a dynamically-scalable system. It also supplies the current performance status against which we can benchmark future improvements and modification.

:{> Andy

Technorati Tags: Bush Scalability Database Instrumentation

Roanoke Code Camp - 3 Mar 2007

Roanoke Code Camp is 3 March, 2007!

You could win an MP3 player! You could win an XBox 360! You could win a copy of Vista!

One thing for certain: you will have lots of fun and learn something. !

:{> Andy

Technorati Tags: Roanoke Code Camp MSDN Code Camp Roanoke Valley .Net Users Group rvnug svssug Roanoke Valley SQL Server Users Group

Note to self...

Note to self: Try to avoid getting locked out of your blog engine when you have so many cool things going on to blog about!

Goodness. Folks, please accept my apologies. I'm really not sure what happened. I couldn't log in and was really too busy to dig into it much. Suffice it to say I'm back!

I have some cool stuff coming up too:

  • SQL Server SP2 is live!
  • Roanoke Code Camp!!!
  • I'm starting a new series about SSIS Patterns on Applied Business Intelligence!
  • I'm upgrading to Community Server 2.1! Check out the partially migrated new Applied Business Intelligence.

And more - including more anecdotes from the road as the Freezing Redneck Tour 2007 resumes after a brief hiatus...

:{> Andy

Technorati Tags: Andy's back SQL Server SP2 Roanoke Code Camp SSIS Patterns Freezing Redneck Tour 2007

Digging my new Zune!

I wanted a personal player, did some research, and decided to give the Zune a try.

So far I like it a lot! It has 30G of storage and a nice big screen (important for us old guys...). I'm still learning all the features, but playing music is pretty straightforward on it. I can attest Van Zant, Brad Paisley, Johnny Cash, and Waylon sound just fine on it. ;)

Now all I have to do is figure out some way to load SQL Server Compact.

:{> Andy

Technorati Tags: Zune Van Zant Brad Paisley Johnny Cash Waylon Jennings SQL Server Compact

Vista and SQL Server Mixed-Mode

I've been traveling the continent the past few months warning people of the evils of SQL Server Mixed Mode authentication. I recently (this past weekend) procured a snappy new Gateway laptop with Vista installed.

It turns out SQL Server 2005 installed on Vista does not automatically add members of the local Administrators group to the SQL Server role BUILTIN\Administrators. Rather than waste lots of time reading the readme files, I discovered this when I tried to connect to freshly-installed SQL Server instance.

So after re-installing SQL Server in Mixed Mode and logging in as sa, I was able to add myself as a sysadmin. Afterwards, I changed the instance from Mixed Mode to Windows Authentication. Windows Authentication is still more secure.

Thank you, Slammer worm...

:{> Andy

Technorati Tags: SQL Server 2005 Vista Mixed Mode Windows Authentication security Slammer

Jim Gray Missing

Jim Gray, renowned computer scientist, has been missing for over a week now. You can follow the story here.

Itzik's blog contains a link to Amazon Mechanical Turk where you can scan satellite imagery from Gray's last known and projected locations for signs of a craft.

Our thoughts and prayers are with Mr. Gray and his family and friends.

:{> Andy

Technorati Tags: Jim Gray missing Amazon Mechanical Turk

Richmond .Net Users Group Meeting Postponed

Tonight's meeting of the Richmond .Net Users Group has been postponed until Wednesday, 7 Feb 2007.

:{> Andy

Technorati Tags: