SQL Server 2000 | Jason Crider

Tag Archives | SQL Server 2000

Project Lucy investigates your SQL Performance for free

I would think that most of the people in the SQL Server community that are active in Blogging or Tweeting have heard about “Project Lucy” by now. I will start by giving you what they define the project as:

“Project Lucy is part infrastructure analytics, part social media experiment, and part market data warehouse. With the performance data you provide us and we evaluate, we’ll be able to provide you with unparalleled knowledge about the performance of your applications, as well as compare it to that of similar businesses.”

The program is still in its infancy, but the coolness factor is definitely there. Basically, you go and grab the Profiler Trace template for the version of SQL Server that you want to trace, read a little bit about how things work and you are good to begin. One thing to note is that the trace templates are for use from within SQL Profiler 2008.

If you are like me, you have quite a few SQL Server 2000 instances that are still in play. And if you’ve tried tuning SQL 2000 at all then you know that you don’t have all the tools that were born out of SQL 2005 and later products. With great anticipation, I downloaded the SQL Server 2000 Trace Template and opened up SQL Profiler 2008. Well, evidently it wasn’t going to be as easy as I thought. I got the following love note:

That red X can never be good.

I got some time to play around with it so I looked at the process used for automating the trace. I decided to open up the trace that I downloaded for SQL 2008 and see what the export options looked like.

Learned a new trick with this one.

Well, I guess I was onto something because after looking at .sql file as well as the page on automating I was able to piece something together that didn’t throw an error. By running a server side trace, I was able to get it to run without giving an error. I haven’t dug into what all it changed, but I do know that by doing it this way, you lose the Auto Grow/Auto Shrink stats that can be analyzed on Project Lucy.

It may be cool to see a comparison chart for sp_trace_setevent for SQL 2000 and later, and I guess I can add that to the list of things I want to blog about but don’t because I don’t make enough time for it. Guess I’ll have to start the draft for that one as soon as I get this one out to push myself. Ok, back to getting a good .trc or .zip file to submit to Project Lucy for SQL 2000.

Yes, I'm so ADD.

After cleaning up the code, and before knowing that the Auto Grow/Auto Shrink weren’t included (based on the Analysis saying it wasn’t) I was left with the code below. I’ve had a little trouble getting Syntax Highlighter working, but I believe it’s working now.

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @expiryTime DATETIME
DECLARE @minutesToTrace INT

/*
Please replace the text InsertFileNameHere, with an appropriate
filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
will be appended to the filename automatically. If you are writing from
remote server to local drive, please use UNC path and make sure server has
write access to your network share
*/

SET @minutesToTrace = 1
--set @maxfilesize = 250
SET @expiryTime = DATEADD(mi, @minutesToTrace, GETDATE())
SET @maxfilesize = 20

-- Reminder to put a \\UNC\Share here.
EXEC @rc = sp_trace_create
@TraceID OUTPUT ,
0 ,
N'InsertFileNameHere' ,
@maxfilesize ,
@expiryTime
IF ( @rc != 0 )
GOTO error
-- Client side File and Table cannot be scripted

-- Set the events
-- SQL Server Yukon specific events will not be scripted
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 67, 7, @on
exec sp_trace_setevent @TraceID, 67, 31, @on
exec sp_trace_setevent @TraceID, 67, 8, @on
exec sp_trace_setevent @TraceID, 67, 1, @on
exec sp_trace_setevent @TraceID, 67, 9, @on
exec sp_trace_setevent @TraceID, 67, 21, @on
exec sp_trace_setevent @TraceID, 67, 41, @on
exec sp_trace_setevent @TraceID, 67, 6, @on
exec sp_trace_setevent @TraceID, 67, 10, @on
exec sp_trace_setevent @TraceID, 67, 14, @on
exec sp_trace_setevent @TraceID, 67, 26, @on
exec sp_trace_setevent @TraceID, 67, 3, @on
exec sp_trace_setevent @TraceID, 67, 11, @on
exec sp_trace_setevent @TraceID, 67, 35, @on
exec sp_trace_setevent @TraceID, 67, 4, @on
exec sp_trace_setevent @TraceID, 67, 12, @on
exec sp_trace_setevent @TraceID, 55, 7, @on
exec sp_trace_setevent @TraceID, 55, 8, @on
exec sp_trace_setevent @TraceID, 55, 9, @on
exec sp_trace_setevent @TraceID, 55, 21, @on
exec sp_trace_setevent @TraceID, 55, 25, @on
exec sp_trace_setevent @TraceID, 55, 41, @on
exec sp_trace_setevent @TraceID, 55, 6, @on
exec sp_trace_setevent @TraceID, 55, 10, @on
exec sp_trace_setevent @TraceID, 55, 14, @on
exec sp_trace_setevent @TraceID, 55, 22, @on
exec sp_trace_setevent @TraceID, 55, 26, @on
exec sp_trace_setevent @TraceID, 55, 3, @on
exec sp_trace_setevent @TraceID, 55, 11, @on
exec sp_trace_setevent @TraceID, 55, 35, @on
exec sp_trace_setevent @TraceID, 55, 4, @on
exec sp_trace_setevent @TraceID, 55, 12, @on
exec sp_trace_setevent @TraceID, 79, 7, @on
exec sp_trace_setevent @TraceID, 79, 8, @on
exec sp_trace_setevent @TraceID, 79, 1, @on
exec sp_trace_setevent @TraceID, 79, 9, @on
exec sp_trace_setevent @TraceID, 79, 41, @on
exec sp_trace_setevent @TraceID, 79, 6, @on
exec sp_trace_setevent @TraceID, 79, 10, @on
exec sp_trace_setevent @TraceID, 79, 14, @on
exec sp_trace_setevent @TraceID, 79, 26, @on
exec sp_trace_setevent @TraceID, 79, 3, @on
exec sp_trace_setevent @TraceID, 79, 11, @on
exec sp_trace_setevent @TraceID, 79, 35, @on
exec sp_trace_setevent @TraceID, 79, 4, @on
exec sp_trace_setevent @TraceID, 79, 12, @on
exec sp_trace_setevent @TraceID, 80, 7, @on
exec sp_trace_setevent @TraceID, 80, 4, @on
exec sp_trace_setevent @TraceID, 80, 8, @on
exec sp_trace_setevent @TraceID, 80, 12, @on
exec sp_trace_setevent @TraceID, 80, 9, @on
exec sp_trace_setevent @TraceID, 80, 41, @on
exec sp_trace_setevent @TraceID, 80, 6, @on
exec sp_trace_setevent @TraceID, 80, 10, @on
exec sp_trace_setevent @TraceID, 80, 14, @on
exec sp_trace_setevent @TraceID, 80, 26, @on
exec sp_trace_setevent @TraceID, 80, 3, @on
exec sp_trace_setevent @TraceID, 80, 11, @on
exec sp_trace_setevent @TraceID, 80, 35, @on
exec sp_trace_setevent @TraceID, 69, 7, @on
exec sp_trace_setevent @TraceID, 69, 8, @on
exec sp_trace_setevent @TraceID, 69, 9, @on
exec sp_trace_setevent @TraceID, 69, 21, @on
exec sp_trace_setevent @TraceID, 69, 41, @on
exec sp_trace_setevent @TraceID, 69, 6, @on
exec sp_trace_setevent @TraceID, 69, 10, @on
exec sp_trace_setevent @TraceID, 69, 14, @on
exec sp_trace_setevent @TraceID, 69, 26, @on
exec sp_trace_setevent @TraceID, 69, 3, @on
exec sp_trace_setevent @TraceID, 69, 11, @on
exec sp_trace_setevent @TraceID, 69, 35, @on
exec sp_trace_setevent @TraceID, 69, 4, @on
exec sp_trace_setevent @TraceID, 69, 12, @on
exec sp_trace_setevent @TraceID, 51, 7, @on
exec sp_trace_setevent @TraceID, 51, 8, @on
exec sp_trace_setevent @TraceID, 51, 24, @on
exec sp_trace_setevent @TraceID, 51, 9, @on
exec sp_trace_setevent @TraceID, 51, 41, @on
exec sp_trace_setevent @TraceID, 51, 6, @on
exec sp_trace_setevent @TraceID, 51, 10, @on
exec sp_trace_setevent @TraceID, 51, 14, @on
exec sp_trace_setevent @TraceID, 51, 22, @on
exec sp_trace_setevent @TraceID, 51, 26, @on
exec sp_trace_setevent @TraceID, 51, 3, @on
exec sp_trace_setevent @TraceID, 51, 11, @on
exec sp_trace_setevent @TraceID, 51, 35, @on
exec sp_trace_setevent @TraceID, 51, 4, @on
exec sp_trace_setevent @TraceID, 51, 12, @on
exec sp_trace_setevent @TraceID, 52, 7, @on
exec sp_trace_setevent @TraceID, 52, 8, @on
exec sp_trace_setevent @TraceID, 52, 16, @on
exec sp_trace_setevent @TraceID, 52, 24, @on
exec sp_trace_setevent @TraceID, 52, 9, @on
exec sp_trace_setevent @TraceID, 52, 41, @on
exec sp_trace_setevent @TraceID, 52, 6, @on
exec sp_trace_setevent @TraceID, 52, 10, @on
exec sp_trace_setevent @TraceID, 52, 14, @on
exec sp_trace_setevent @TraceID, 52, 22, @on
exec sp_trace_setevent @TraceID, 52, 26, @on
exec sp_trace_setevent @TraceID, 52, 3, @on
exec sp_trace_setevent @TraceID, 52, 11, @on
exec sp_trace_setevent @TraceID, 52, 35, @on
exec sp_trace_setevent @TraceID, 52, 4, @on
exec sp_trace_setevent @TraceID, 52, 12, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 41, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 11, 7, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 9, @on
exec sp_trace_setevent @TraceID, 11, 41, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 11, 4, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 38, 7, @on
exec sp_trace_setevent @TraceID, 38, 8, @on
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 9, @on
exec sp_trace_setevent @TraceID, 38, 41, @on
exec sp_trace_setevent @TraceID, 38, 6, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 14, @on
exec sp_trace_setevent @TraceID, 38, 22, @on
exec sp_trace_setevent @TraceID, 38, 26, @on
exec sp_trace_setevent @TraceID, 38, 34, @on
exec sp_trace_setevent @TraceID, 38, 3, @on
exec sp_trace_setevent @TraceID, 38, 11, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 4, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 38, 28, @on
exec sp_trace_setevent @TraceID, 35, 7, @on
exec sp_trace_setevent @TraceID, 35, 8, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 9, @on
exec sp_trace_setevent @TraceID, 35, 41, @on
exec sp_trace_setevent @TraceID, 35, 6, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 14, @on
exec sp_trace_setevent @TraceID, 35, 22, @on
exec sp_trace_setevent @TraceID, 35, 26, @on
exec sp_trace_setevent @TraceID, 35, 3, @on
exec sp_trace_setevent @TraceID, 35, 11, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 4, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
exec sp_trace_setevent @TraceID, 35, 28, @on
exec sp_trace_setevent @TraceID, 34, 7, @on
exec sp_trace_setevent @TraceID, 34, 8, @on
exec sp_trace_setevent @TraceID, 34, 1, @on
exec sp_trace_setevent @TraceID, 34, 9, @on
exec sp_trace_setevent @TraceID, 34, 41, @on
exec sp_trace_setevent @TraceID, 34, 6, @on
exec sp_trace_setevent @TraceID, 34, 10, @on
exec sp_trace_setevent @TraceID, 34, 14, @on
exec sp_trace_setevent @TraceID, 34, 22, @on
exec sp_trace_setevent @TraceID, 34, 26, @on
exec sp_trace_setevent @TraceID, 34, 34, @on
exec sp_trace_setevent @TraceID, 34, 3, @on
exec sp_trace_setevent @TraceID, 34, 11, @on
exec sp_trace_setevent @TraceID, 34, 4, @on
exec sp_trace_setevent @TraceID, 34, 12, @on
exec sp_trace_setevent @TraceID, 34, 28, @on
exec sp_trace_setevent @TraceID, 43, 7, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 41, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 5, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 29, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 37, 7, @on
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 41, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 26, @on
exec sp_trace_setevent @TraceID, 37, 34, @on
exec sp_trace_setevent @TraceID, 37, 3, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 35, @on
exec sp_trace_setevent @TraceID, 37, 4, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 5, @on
exec sp_trace_setevent @TraceID, 37, 21, @on
exec sp_trace_setevent @TraceID, 37, 29, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 22, @on
exec sp_trace_setevent @TraceID, 45, 7, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 41, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 5, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 29, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 44, 7, @on
exec sp_trace_setevent @TraceID, 44, 8, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 9, @on
exec sp_trace_setevent @TraceID, 44, 41, @on
exec sp_trace_setevent @TraceID, 44, 10, @on
exec sp_trace_setevent @TraceID, 44, 26, @on
exec sp_trace_setevent @TraceID, 44, 34, @on
exec sp_trace_setevent @TraceID, 44, 3, @on
exec sp_trace_setevent @TraceID, 44, 11, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 44, 4, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 28, @on
exec sp_trace_setevent @TraceID, 44, 5, @on
exec sp_trace_setevent @TraceID, 44, 29, @on
exec sp_trace_setevent @TraceID, 44, 6, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 44, 30, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 25, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 4, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 5, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 29, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 40, 7, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 41, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 30, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 4, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 5, @on
exec sp_trace_setevent @TraceID, 40, 29, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - b67e1234-32cd-4c79-af08-0ce2556fa6d3'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

By using the code above you can at least generate a one minute trace or a 20MB trace, whichever comes first. You can play around with the values to get more info, and possibly even add the Auto Shrink/Auto Grow info in there. If I get the time I will come back here and change it with that update.

I’d like to hear from some of you. Is there a better way to do this? Anything I’m missing?

Comments { 2 } Posted on June 9, 2011 in SQL General, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication

Best explanation I’ve seen of SQL 2000 SP4 self blocking

I stumbled upon the best explanation I’ve seen of the self-blocking that we sometimes see in SQL Server 2000 SP4. I’ve used Sherlock and other tools in the past and always been dumbfounded by what I was seeing. There are many folks still stuck with SQL 2000 with no option to upgrade every server, so this still provides some help when we see strange things.

I’m copying the post with a link going back to it as this blog does serve as my repository for something if I need to go back and look at it. If anyone who is more proficient at internet manners (Brent Ozar (Blog|Twitter)), please let me know how I could do this better.

The following is the post and link which I claim no credit for aside from finding it and reposting it:

What you are seeing is not related to locks, rather it is related to the
latch waits you see in sysprocesses. In SP4 we are able to determine the
blocking spid for latch waits when the blocking spid holds the latch in
exclusive or update mode. In these cases the blocking spid information is
populated into the “blocked” field in sysprocesses.
So why is the spid blocked by itself? Well, that has to do with how latches
are used for IO operations. When an IO is issued for page, a latch is held
on the page. Depending on the IO operation, the latch mode acquired is
shared (SH) or exclusive (EX). The latch for the IO is acquired by the
thread that issues the IO. Since all SQL Server IO operations are
asynchronous, if the spid that issued the IO wants to wait for the IO to
complete it will attempt to acquire another latch on the same page after
issuing the IO. The first latch is released when the IO completes. This
release allows the second latch request to be granted.
Here’s an example of how this works:
1. Spid 55 wants to read page P1 which does not exist in the buffer pool.
2. Spid 55 acquires an EX latch on page P1 — this marks spid 55 as owning
the latch. The latch is in an in memory data structure, not the physical
page itself. Since the page does not yet exist in memory the mode is EX in
order to force other spids that may also want to access the page to wait for
the IO to complete and also to prevent them from issueing a second IO
operation for the same page.
3. Spid 55 issues the IO request to read P1 from disk.
4. Since Spid 55 wants to read the page, it must wait for the IO to
complete. It does this by attempting to acquire another latch (in this case
a share (SH)) latch on the page. Since the latch is already held in EX, the
SH request is blocked and the spid is suspended.
5. Upon completion of the IO the EX latch on the page is released.
6. The release of the EX latch grants the SH latch to spid 55.
7. Spid 55 can now read the page.

For the duration between steps 4 (the SH latch acquire) and step 5 (EX latch
release) sysprocesses will indicate that spid 55 is blocked by itself with a
wait type of PAGEIOLATCH_XX (where XX can be SH, UP, or EX) as an indication
that it is waiting for the completion of an IO that it itself issued.

—
Santeri (Santtu) Voutilainen

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en&pli=1

Comments { 0 } Posted on May 10, 2011 in SQL, SQL Server 2000, SQLServerPedia Syndication

Setting up your server

While the Microsoft reference may say that you should keep the operating system the same, it seems hard to believe that there aren’t situations where one would decide to bypass this recommendation. If you are sitting on Windows 2003 x86 with SQL Server 2000 x86 and you have the option to go to Windows 2008 x64 with SQL Server 2005 x64 then it might be a good time to move on that. Microsoft Windows 2008 R2 is the first OS to only be offered in a 64-bit version. If you want to be set for a few years, now might be time to go ahead and bite the x64 bullet. But you should also be aware of the downsides that can be associated with this decision. If you have 100 DTS Packages that primarily use Excel connections then you may eventually regret the decision to go with x64. It would also be a good idea to make a decision about going to Windows Server 2008.

RAID Levels can also be something to consider during this time. Kendal Van Dyke has an excellent series on Disk Performance that can be useful when looking at RAID levels. While you will often read that RAID 10 gives you the best bang, you may not have any buck left. Generally, it’s recommended that you stick with RAID 5 for your data files and RAID 1 for your log files. If you are not connected to a SAN and your drives are local then now is the time to get the RAID levels right.

Once you have your OS installed, its time to get it configured so that it works optimally for SQL Server. There are several key fundamentals about SQL Server installations that you will need to understand as you move forward and there are great resources from Brent Ozar and Chad Boyd included in the resource links below. Another item that Brent touches on briefly is that of Disk Partition Alignment. Jimmy May has some excellent information on that, and the SQL CAT team recently published a new whitepaper that is helpful.

Resources:
SQL Server 2005 on a 64-bit Platform
32-bit vs. the 64-bit SQL Server performance surge
Q&A: Making the jump to 64-bit SQL Server 2005
Disk Performance Hands On
SQL Server Setup Checklist – Part 1
Installing Clustered SQL Servers – Outline, Checklists, Document Sheets
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck
Disk Partition Alignment Best Practices for SQL Server

Comments { 0 } Posted on September 22, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Upgrading your Plan

After reading through the SQL Server 2005 Upgrade Technical Reference Guide, you should have a good idea about which direction you want to move forward. The upgrade path and upgrade method will help you modify your plan so that you have the best chance to succeed. Depending on your environment, your choices may already be made for you. Regardless of whether or not you are doing an In-Place or a Side-by-Side upgrade, it will be very beneficial for you to do some testing before you flip any kind of switch to go live with SQL 2005.

In the best of scenarios, you can find another server that you can use to do testing and simulate what the environment will look like. If you cannot find another server that can be used solely for this purpose, maybe you could look at using virtualization to meet this need. You probably will not be able to simulate the hardware completely, but it should be better than nothing. Setting up a virtual instance is beyond the scope of this article but it should be fairly easy to set that up with resources found online. VMware and Microsoft both have virtualization software that can be used for free.

Scalability Experts has a tool called SQL Server Upgrade Assistant 2005 that can be used to verify how your applications will run against SQL Server 2005 instead of 2000. Even if you do not use this tool, by reading through the User Guide you can develop your own plan for testing and that should help you discover the items you need to address.

In the fourth part of this series, I’ll describe some things you should consider as you are setting up your new server.

Comments { 0 } Posted on September 21, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Upgrading from SQL Server 2000 to 2005

This is part one in a series of upgrading from SQL Server 2000 to SQL Server 2005. The first question I want to address is why would you want to upgrade in the first place. It’s important to know why you are doing something if you are going to be able to truly understand what you are doing. Most of my opinion and experience comes from the admin DBA side of the fence so you won’t find a ton of developer jargon thrown around because that’s not a strong point for me. I’m in the process of upgrading from SQL Server 2000 to SQL Server 2005 as well, so I hope I can chronicle the journey and help others if they are in the same situation.

It goes without saying that the biggest reason for upgrading is that the product is basically End of Life. While you can still get extended support through 2013 it would still be wise to start the process of moving forward. There are plenty of resources on the internet about the new features that were introduced or approved upon with SQL Server 2005. Since I am more of an administrator I get a little more warm and fuzzy about Table and Index Partitioning, All Permissions Grantable, Mirroring, Snapshots, Online Restores, and Performance DMV’s. While the new data types can be very useful and I’m sure CLR has its place it is hard for me to get as excited about those items. A full list of features can be found by digging through Microsoft’s full list or an abbreviated Top 30.

For us, SQL Server Integration Services this was one of the biggest hurdles to overcome because of the number of DTS packages that we are dependent upon. Microsoft has completely redone the way that data is moved from the ground up and there is a learning curve. We eventually decided to purchase a few licenses of a third-party piece of software to help with the package transition.

There’s also no more Enterprise Manager, and for the most part that’s a good thing. Visually, it’s much more appealing and it’s worth running a client to access SQL 2000 instances to do most things. This late in the game I’ve even been running a SQL 2008 client because of the added features that it gives me as an administrator.

Coming in part 2, Upgrade Education and Fact Finding.

Resources:

Landing page for SQL Server 2005
Microsoft whitepaper on “Why Upgrade to SQL Server 2005”
FAQ: Why Upgrade to SQL Server 2005 now
What’s new in SQL Server 2005
SQL 2005 Books Online : What’s New
Top 10 new features in SQL Server 2005
Consider what’s new in Microsoft SQL Server 2005
New T-SQL Features in SQL Server 2005 Part 1
New T-SQL Features in SQL Server 2005 Part 2
The Differences Between SQL Server 2000 and 2005
The Differences Between SQL Server 2000 and 2005 – Part 2