Tag Archives | SQL Server

Deleting Data in Small Chunks on SQL Server

Chunky Shirt

I’ve had this post on the backburner for a bit of time, but Glenn Berry (Blog|Twitter) recently posted on gradually deleting data in SQL Server and that made me think of it again. I would advise looking at his post for guidance on how to effectively clean up your data in chunks, but I want to give you a little more background on what made me want to post this.

For some time, I’ve been using the following code to perform the task:


SET ROWCOUNT 10000
--DECLARE @Text varchar(255)

--SET @Text = 'Deleted 10000 rows'
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
WHILE @@rowcount > 0
BEGIN
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
--PRINT (@Text)
END
SET ROWCOUNT 0
GO

So when I answered a post on ask.sqlservercentral.com that seemed to be fitting for my answer, I got the opportunity to learn a few things that I wasn’t aware of. Pavel Pawlowski (Blog|Twitter) pointed out that it’s better not to use SET ROWCOUNT.

On SQL Server 2005+ it’s better to use DELETE TOP 10000 instead of SET ROWCOUNT 10000. Also you can read on MSDN SET ROWCOUNT (Transact-SQL).

He mentions SQL Server 2005 and later, but what about SQL 2000? Does anyone have a preferred method when dealing with SQL Server 2000 that’s different? Is there a one size fits all?

Comments { 0 } Posted on August 16, 2011 in SQL, SQL Server 2000, SQL Server 2005, SQLServer, SQLServerPedia Syndication

Fill up my Kindle

I finally broke down and got a Kindle 3G Wireless 6″ from Amazon. I’ve been reading a few titles from the Kindle for PC software trying to determine how tech books would show up to see if I would lose anything by not getting the real book but it’s hard to tell. I asked on Twitter a while back and only got one response saying that there weren’t many tech books on the Kindle.

Well, here’s your chance to post your first comment on the ol’ blog. Are you an author who’s book on Kindle show’s up beautifully? Are you an MVP who carries your Kindle around with you? Hoping SQL Server MVP Glenn Berry (Blog|Twitter) gets his way and more great books come to the Kindle. I’ll help him by clicking the link on the Amazon page for his book telling the publisher we want the book on the Kindle. I’ll probably buy the hard copy anyway because of the rave review Brent Ozar (Blog|Twitter) gave it, and the fact that it’s the only book of its kind that I’ve seen. Maybe Glenn can right all the wrongs in the SQL Server building world. I’m pulling for him.

So, if you have a tech book (SQL or not) that you can say for sure looks great on the Kindle and doesn’t lose content or readability by being on the Kindle then please post it here in the comments.

As always, if you have comments or suggestions on how I can make this blog a better resource then please feel free to share. And for the few brave souls who’ve made it to the last sentence of this post…I’ll let you know there’s a big redesign of the blog coming up which will hopefully improve the focus and readability.

Update: Not sure why I didn’t get the email notifying me that Glenn had responded to my comment on his blog, but the book is now available on the Kindle.

Comments { 2 } Posted on August 5, 2011 in SQLServer, SQLServerPedia Syndication

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:

projectLucySQL2000Error

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.

projectLucySQL2008ExportOptions

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.

add squirrel

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.

Example Analysis

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

Another way to Compare Database Tools

In a previous post title Comparison of Database Tools, I told you about a resource that Brent Ozar (Blog|Twitter) let me know about.

Today, I was introduced to a new site that will let you compare certain items based on predefined criteria that appears to be user submitted.

While the usability of the results could be debated, it still seems interesting to me.

Currently, they are somewhat limited on the “SQL Server” front since they only include CE and a Generic SQL Server reference. I’m not sure of the business model or staying power of the site, but I may decide to finish my initial ADD thought of inserting all of the SQL Versions back to 2000.

Since they are comparing items based on preset criteria, something you may want to know about won’t be included in the results.

Nonetheless, it’s pretty cool to play around with:

RDBMS Comparison

Energy Drink Comparison

And if you are a bacon fan like Thomas LaRock (Blog | Twitter) then you can check your breakfast options.

I know I’m violating Ozar’s advice about posting close to the weekend, but I just couldn’t resist the urge of posting lust that I’m going through. I have a list that I’m working and I’m trying to get towards that goal of having at least one post a week.

Update : As Ralph Wilson(LinkedIn) points out here, there are some inaccuracies(more than 1) in their data. One example is how they list Identity only for the Compact Edition. I thought I had alluded to the value that could be placed in the results, and hinted that someone should update it. Anyway, that got me to digging to see how they got their info. If you follow the “Comparison of relational database management systems” link off of the wikipedia page you’ll find similar patterns, but there are still data differences.

Update #2 : I was contacted by Matt Kopjak of FindTheBest.com who offered more info on how the data is populated.

“The results/listings are submitted by researchers at FTB. We compiled 90% of the fields and hope that one day experts will come in and edit/update the rest.”
Matt Kopjak (FindTheBest.com)

Comments { 1 } Posted on December 3, 2010 in Education, SQL General, SQLServerPedia Syndication