Why you need DBA support

Almost every shop is short-handed these days; DBAs in particular, since we're often on call, tend to be a bit... stretched. I wrote this in an attempt to explain to non-DBAs what it is we do, and why it is important to have us around & functional (i.e. not burned out!). Please share as you will. email me for a word or pdf version.

Why you need DBA support
There seem to be patterns and cycles in everything. Application development is no exception. For a variety of reasons, organizations of all sizes frequently end up without a DBA.
Lack of a DBA will put organizations at risk in a variety of ways:
· Performance issues, slowly snowballing (or suddenly escalating) until they are intolerable
· Phases of development which become increasingly difficult because the design of the data model did not lend itself to flexibility
· Database corruption, system crash, or other disasters, often to find out that there is no backup, or nobody who knows how to deal with a system down issue
· When your production database goes down, your business stops
· … or a hundred other scenarios
Here, in no particular order of importance, is a Letterman-esque Top 10 List of the reasons that you need a DBA or DBA managed support services:
Preventive Maintenance
Servers and databases need to be properly maintained. When they are not, performance degrades, database consistency is at risk, and cleanup of ancillary processes (for example, growth of the database log) doesn’t occur; it can take a while, but little things can fill up disk subsystems and cause database management systems to halt.
Application Performance
Do you know how to identify what application components are running slowly? When users call and complain about performance, where do you look? Where are the bottlenecks? Is it CPU, memory, disk, network? Even after you’ve identified the component, what is causing the CPU to spike? Answering these questions can be… nontrivial. A common mistake here is to “throw hardware at the problem,” which will not help, may or may not mask the problem for a short period while it snowballs out of control.
Disaster Recovery
Do you have a disaster recovery plan? You’re probably performing backups. When is the last time you tested the restoration procedure? I’ve had more than one new customer retain me just because the restore didn’t work. Did you know that a DBMS can give you point-in-time recovery? Just because somebody deletes a table may not mean it is gone forever, if you plan properly.
Device Management
We tend to treat IO as a bottleneck. How do you maximize throughput? There are a variety of DBMS resources that require frequent IO, including data, logs, indexes, and even to the DBMS executable itself. How are you going to balance the IO across your devices? What are you going to do when your SAN administrators tell you that the SAN is not being stressed, but your DBMS is telling you that there is an IO bottleneck? This happens too often.
Predictive Analysis
When do you need to buy more disk? CPU? Upgrade your hardware? If you’re not measuring / monitoring database growth and a dozen other factors, while managing performance (so that you don’t get false reads), you simply don’t have any way to plan.
Physical Architecture
What makes more sense for you? Clustering? Warm standby replication? Balancing your workloads across multiple machines? Consolidating your servers? These decisions need to be based upon facts, knowledge of your business requirements, and experience. If you don’t have a DBA on staff, or have DBAs in a support model, then you won’t know the right way to respond.
Application architecture
DBAs are great resources when you start to design your application flow. How are you going to bring data together from disparate data sources? How aware are you of your real-time and batch options? How many have you direct experience with?
Business continuity
We recently read a statistic that stated that over 60% of businesses fail after catastrophic data loss. Where were you when the hurricane hit your data center? Worse, where was your data, and how did you continue to function? You need to plan, long term, for hurricanes, fires, floods theft, and other flavors of disaster.
Hardware Performance
When ordering your server hardware, you need to know what the DBMS performance has been over time; you need to know which performance spikes are aberrations and what is normal; you need to know how to maximize the throughput throughout your bottlenecks, by fine tuning cache or other physical resources. Without a DBA, you just won’t know.
Developer training
An oft-neglected but badly-needed area of DBA work is the mentoring of developers, who, as a group, have a very high aptitude for logical coding, but often do not have training in database performance. This often leads to passed user acceptance testing, with significant performance issues at rollout. Worse, the performance issues tend to snowball, as the poor code often propagates.
Summary
This short-list of critical DBA activities requires dedicated support. A developer who is a part-time DBA, even with sufficient background, usually won’t have sufficient time to perform these tasks.
Whether you go with a full-time DBA or managed DB support services, make sure you have adequate expertise at hand.

Microsoft Transact-SQL: The Definitive Guide is coming off the presses shortly

subtitled: How to rewrite bad t-sql code...

Here's the link:

http://www.jbpub.com/catalog/9780763784164/

If I get a discount code, I'll put it here...

This is a great book for folks looking to teach application developers how to rewrite their code more efficiently; you should buy a case of these to hand out to your development teams :-)

Jeff

Mapping Events to Event IDs from a Profiler Trace

Tracked this one down, now I'm sharing:

select name, count(*)
from dbo.TraceTable20091214 tr join sys.trace_events
on trace_event_id = eventclass
group by name
order by 2 desc

sys.trace_events picks up the name of the event_id, which is what gets stored in the profiler trace log.

enjoy!

SQL Saturday #21 in Orlando, 10/17/2009

Hi,

You've seen this post before...

I'm giving my index talks in Orlando again in October... hope to see you there! Send me a note if you want an outline, or better yet, go to

www.sqlsaturday.com

for an outline and to register. Hope to see you there.

Jeff

Load a 1Tb database in 30 minutes!

Some links deserver their own blog post. This one was passed on to me by a client, it's teriffic, you should read it immediately.

http://technet.microsoft.com/en-us/library/dd537533.aspx

SQL Saturday in Miami

Miami anybody?

I'm speaking at SQL Saturday in Miami on 8/8, go to sqlsaturday.com to register & for details if you haven't already.

Concurrency issues during massive updates / archiving

Mass updates / mass deltes often cause contention issue with locking / blocking. One technique I suggest to my students is to update / delete parts of the table at a time, where feasible, so as to avoid embarassing issues like table locks.

The following code could be improved, but it has the right general idea: For each involved table, delete 1000 rows at a time until we're done.

CREATE PROCEDURE [dbo].[DeleteSnapshotData]
@I_lNumDays int
AS

SET NOCOUNT ON
-- get cutoff date string
declare @tablename varchar(50)
declare @dtCutoff smalldatetime
declare @strCutoff varchar(50)
declare @command nvarchar(2000)
set @dtCutoff = getdate() - @I_lNumDays
set @strCutoff = cast(year(@dtCutoff) as varchar) + '-' + right('00' + cast(month(@dtCutoff) as varchar), 2) + '-' + right('00' + cast(day(@dtCutoff) as varchar), 2)

-- build temp table
set rowcount 0
select sysobjects.name into #snapshots from sysobjects join syscolumns on sysobjects.id = syscolumns.id where sysobjects.name like '%snapshot%' and (OBJECTPROPERTY(sysobjects.id, N'IsTable') = 1) and (syscolumns.name = 'tdate')

set rowcount 1
select @tablename = name from #snapshots

while @@rowcount > 0
begin

delete from #snapshots where name = @tablename
if ((@tablename != 'Closing_Charges_Snapshot') and (@tablename != 'Closing_Credits_Snapshot'))
BEGIN
print 'deleting expired data from ' + @tablename + '...'

SET ROWCOUNT 1000
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
SET NOCOUNT OFF
select @command = N'delete from ' + @tablename + ' where tdate < ''' + @strCutoff + ''''
exec (@command)

IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END

COMMIT TRANSACTION
END
END
SET NOCOUNT ON
set rowcount 1
select @tablename = name from #snapshots
end
drop table #snapshots