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!
Tuesday, December 15, 2009
Sunday, October 4, 2009
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
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
Monday, August 3, 2009
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
http://technet.microsoft.com/en-us/library/dd537533.aspx
Labels:
data load,
data warehouse,
sql server 2008,
tb,
terabyte
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.
I'm speaking at SQL Saturday in Miami on 8/8, go to sqlsaturday.com to register & for details if you haven't already.
Tuesday, March 3, 2009
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
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
Labels:
concurrency,
mass updates,
SQL Server 2005,
sql server 2008
Monday, March 2, 2009
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:
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!)
Thursday, February 26, 2009
SQL Yesterday vs. SQL Today
Quick Tip: Use the ANSI Standard Join syntax in all of your queries.
I've been writing SQL for abotu 20 years now, and have been forcing my hands to type the new code. It's not just a good idea; it's now critical to performance. I've talked to hosts of people who claim that their performance has improved by changing format from the old syntax (where joincol = joincol) to the new syntax (ON joincol = joincol) ... I'm sure the "Why" has been documented / blogged about, so I'll just leave the top & a sample correction here...
Reproduced here with the client's permission:
SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities, dbo.Trips Trips, dbo.TripNotes Tripnotes, dbo.Notes Notes
WHERE
Trips.dfac = Facilities.code AND
Tripnotes.job = Trips.job AND
Notes.code = Tripnotes.note AND
Trips.tdate = Tripnotes.tdate AND
(
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1
)
And recommend it be rewritten like this:
SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities join dbo.Trips Trips on
Trips.dfac = Facilities.code JOIN
dbo.TripNotes Tripnotes on
Trips.job = Tripnotes.job AND
Trips.tdate = Tripnotes.tdate JOIN
dbo.Notes Notes ON
Notes.code = Tripnotes.note
WHERE
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1
I've been writing SQL for abotu 20 years now, and have been forcing my hands to type the new code. It's not just a good idea; it's now critical to performance. I've talked to hosts of people who claim that their performance has improved by changing format from the old syntax (where joincol = joincol) to the new syntax (ON joincol = joincol) ... I'm sure the "Why" has been documented / blogged about, so I'll just leave the top & a sample correction here...
Reproduced here with the client's permission:
SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities, dbo.Trips Trips, dbo.TripNotes Tripnotes, dbo.Notes Notes
WHERE
Trips.dfac = Facilities.code AND
Tripnotes.job = Trips.job AND
Notes.code = Tripnotes.note AND
Trips.tdate = Tripnotes.tdate AND
(
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1
)
And recommend it be rewritten like this:
SELECT
Facilities.code, Facilities.name, Trips.veh, Trips.status, Trips.priority, Trips.tdate,
Trips.dstatus, Facilities.initials, Trips.dispdate, Trips.disptime, Trips.enrdate,
Trips.enrtime, Trips.atsdate, Trips.atstime, Trips.atddate, Trips.atdtime, Trips.tradate,
Trips.tratime, Trips.cmpy, Trips.daddr2, Trips.daddr, Trips.avldate, Trips.avltime,
Trips.IncidentID, Trips.RunNumber, Notes.NoteDate, Notes.Description,
Trips.transpriority
FROM
dbo.Facilities Facilities join dbo.Trips Trips on
Trips.dfac = Facilities.code JOIN
dbo.TripNotes Tripnotes on
Trips.job = Tripnotes.job AND
Trips.tdate = Tripnotes.tdate JOIN
dbo.Notes Notes ON
Notes.code = Tripnotes.note
WHERE
Trips.dstatus IN (4,5,6) AND
Trips.tdate >= convert(char(10),getdate()-1,126) AND
Facilities.code <> 1
Subscribe to:
Posts (Atom)