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

Undocumented system procedure

Critical path for most folks is preventive maintenance, whether they know it or not.

If you're ever looking for a quick way of doing something on all (user) tables in a database, there's a system stored procedure for it.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

This command will run the "dbcc DBREINDEX" command on each table in the database.

Your table-level preventive maintenance regiman, CRUCIAL to performance success, include:

• keeping all table statistics up to date
• Keeping index structures balanced; this may be an indexdefrag (which, while intrusive, can be performed online) or preferably by rebuilding indexes (dbcc DBREINDEX )
• Making sure you have the indexes you need, and remove indexes you don't use (see new managment views for that!)