Archive | SQL RSS feed for this section

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:

--DECLARE @Text varchar(255)

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

So when I answered a post on 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

I cheated on SQL Server

It’s hard to believe that it has been roughly 2.5 years and 45 posts since I started this blog. I don’t need to do the math to see that I should be blogging more, but in reality…I’m not sure how much I can do at this point in my life. I have two young children that are going to know who their daddy is. I have to support my family so my full-time gig is what keeps things going. That’s where this story began a few years back, with a passion for learning and helping others along the way.

So I guess you may be wondering what I mean when I say that I “cheated on SQL Server”. Maybe the image of a teenager with “SELECT *” on the palm of his hand during an exam comes to mind (right), or being unfaithful to our better half. Well, I haven’t cheated on anyone or anything but I am ashamed of what I have done. I almost let a great deal of hard work and effort go by the way side as I lost track of my vision. A few years back I took a job where I was handed some DBA material and asked to see if I could do some performance tuning and get things to stop crashing. What I saw in the days after that could only be described as an enlightened view into a community different from most others. I was fortunate enough to find a few really bright and passionate individuals who helped me discover the path I was about to take. This list isn’t comprehensive but a few of those individuals:
Brent Ozar (Blog|Twitter)
Thomas LaRock (Blog | Twitter)
Brad Mcgehee (Blog|Twitter)

I wish I could include everyone but there were so many that had a passion for what they did and wanted to help others follow the same path. I started the journey to go from systems administration to “Exceptional DBA”. That meant reading everything about SQL Server that I could get my hands on, getting certified to put a time sensitive deadline as a goal, and starting a blog (“Becoming A SQL Master”) to start giving back to the community. On top of starting the blog, I also began going to forums and try to answer questions by researching it or seeing if I could reproduce the problem. So thus began the long nights of studying SQL Server 2005 when the only servers in my environment were SQL Server 2000. With an infant son, it was quite the struggle to stay dedicated to the goals I had set but I still had my dedicated “home office” at the time so I could close the door and hammer it out. I was tired of being a “Jack of all trades, master of none” I saw that the true experts are the ones who were in demand and could write their own ticket. It sure wasn’t the guy who knew a little bit about everything, and that’s the bucket I fell in.

In Little Rock, there aren’t a ton of junior level SQL Server DBA positions, so the waiting game was long and taxing. I had opportunities in other locations, but I couldn’t pick up my family again and move. I had a couple of contract-to-hire positions come up, but at the time I didn’t feel comfortable enough in my abilities to take that risk.

So if you have made it this far you are either really bored or you’re a family member so I’ll get to the cheating.

Sometime around January of this year (2011), I decided that it just wasn’t going to happen for me. Maybe I was just destined to be a Systems Administrator…not that there’s anything wrong with that and the good Lord may put me back in that role someday. I even went so far as to change the title of the blog to “A hodgepodge of Information Technology and Life” so that I could write about other topics and broaden my reach. At the time of the change I had 19 subscribers on my feed (don’t laugh, as I thought that was a start), but after feedburner got everything sorted out it was down to 4 subscribers. I began to get more involved in Active Directory, PowerShell, Group Policy, and all things IT. I tried to get involved in different communities, but to me there just wasn’t something like the SQL Server community. I began to miss the thing that had encompassed so much of my time and I enjoyed it.

Then it happened…conversation started with a consulting firm that had contacted me earlier but I didn’t feel confident enough to take the risk. This time was different. I had the knowledge, I had the passion, and I had 4 subscribers…I could do this. I’m not afraid of working hard and proving myself and that’s what I have to do. I left a very secure job (nevermind that I was out of room for advancement), to take a contract-to-hire job. The goal is to get hired on full-time. The goal is to start a PASS Chapter in Little Rock. The goal is to become an MVP. The goal is to never lose a passion for being the best that I can be while being unselfish in helping others who want to do the same.

I’m back with SQL Server and it’s pretty exclusive. I’m throwing away my little black IT book and going back to full-fledged SQL Server. Hopefully I don’t get off track again for some time.

Comments { 2 } Posted on July 22, 2011 in SQL, 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

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

Where’s the page file when you need one?

I’m not sure how I missed this one given that I do quite a bit of reading online about best practices. My intentions were noble. I wanted to give my SQL Server the perfect environment so that it could flourish and thrive.  I wanted to allow the latest and greatest Microsoft OS (Windows Server 2008 R2) to shine on its pretty new C: drive. What I learned in my attempts at greatness is that I dropped my guard. I didn’t have the iron-clad check list that I thought I had. So now, it’s back to the drawing board to come up with my ideal checklist and installation guide for the Operating System and SQL Server 2008 (post date TBD). And if you are really curious about my fatal mistake that cost me a day and a half before I gave up trying to bring the server back up read on.

For the 3rd time, I’ve had a Dell R900 with SQL Server 2008 and Windows Server 2008 R2 start dropping iSCSI drives. When you believe you’ve properly laid out your separation of duties for SQL (E:\SQLData, F:\SQLLogs, T:\TempDB, and X:\SQLBackup) one would hope there wouldn’t be more problems. But for some reason, these ingredients mixed together have caused me quite the headache. I’m not giving up on Windows Server 2008 R2 just yet, as I’m trying to learn from my mistakes and kick this problem’s tail. There are two crucial mistakes I made this time which caused me more downtime and gave me more work to do in order to get back to an operational state. I listed four drive specifications above that I have used for everything but the OS, SQL Install files and Shared Features, and OS Paging File(s). I have had a practice of doing the following:

  • The OS on the C: drive.
  • The SQL Binaries on the D:\ drive (minus the shared resources that have to go on the C: drive).
  • Remove the paging file from the C: Drive, and place a paging file 1.5 times the RAM on the D: drive.

Maybe some seasoned veteran is reading this and laughing at me, and I’m sure I deserve that after the blunder I made. Because I didn’t have a page file of any size on the C: drive, I was unable to analyze the .dmp file to find out what exactly was causing Windows to go into a reboot cycle. I had lost one of the iSCSI connections so SQL was dead because it kind of needs that Tempdb thing. So I panicked and rebooted, but this time the Logs drive didn’t come up. So I started having deja vu from my previous experience with Windows Server 2008 R2, iSCSI, and SQL Server 2008 only to remember something in my mind thinking that a patch might have caused this since I had been running fine for a few days. So I uninstall the patch that was put on earlier that day (slight possibility that it was SP1 but I don’t believe so), and it goes to reboot. Now we are in an endless cycle of reboots, and there’s no turning back. Oh, but let’s count the positives that came out of this. I now have a few future posts in the works (when I can find the time with two young children) ranging from the Microsoft Disaster Recovery Toolset, Operating System Installation Guide and Checklist, a SQL Server Installation Guide and Checklist to recovery options when you have hit a wall.

Let’s get back to the biggest lesson learned here. You really do need a paging file on your boot volume. Coming from the Windows side of things the best document I could find was a technet post by CC Hameed it suggests RAM + 1MB for a complete dump. For something specific to Windows Server 2008 R2 you can look at at these Memory Dump Options.  You can also learn how to generate a dump file in Windows Server 2008.

From a SQL Server side, that’s probably not realistic. And you’ve got to look at what your physical resources are as well. I would point you to a great article by Buck Woody (blog|twitter) entitled “The Windows Page File and SQL Server”. I hope to write a follow-up post with an example of this process from one of my servers.

So the takeaway from today is not to end up with the inability to diagnose your problem by not having a paging file on your boot drive. How you determine to size that paging file is going to be up to you and your system. Use the resources I’ve listed and please include new ones that you find.  Feel free to add any experiences that you have had that you can’t believe you made or gotchas that others can learn from.

Comments { 2 } Posted on March 1, 2011 in SAN, SQL, SQLServerPedia Syndication, Windows Server