SQL Server with Mr. Denny


March 29, 2013  5:04 PM

Recommended reading from mrdenny for March 29, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny

March 27, 2013  2:00 PM

Using Event Viewer Logging for SSIS Performance Trouble Shooting

Denny Cherry Denny Cherry Profile: Denny Cherry

So while working with a client recently I was going some performance tuning on SSIS for them.  While SSIS isn’t exactly my normal workload, the problem wasn’t really in SSIS, but just normal SQL Statements being called from SSIS.

While attacking this problem, the first thing that I did was look into the SSIS package and see what I had gotten myself into.  There were hundreds of objects within SSIS, most of them T-SQL Scripts of Data Pump Tasks.  So simply going through them one by one wasn’t going to be all that helpful.  The client didn’t have any logging to SQL Server or to a text file setup, but they did have the SSIS package setup to log to the Windows event log.

Opening the Application log greated me with one event for the start of each task, and one for the completion of each task.  Not exactly the most useful information ever, but better than nothing, so I’ll take what I can get.  The first thing that I did was export the Application Event log to a CSV file and download it to my desktop so that I could do the processing on my local machine.  The CSV file has a few hundred thousand lines, so I needed to figure out what I had via scripting.  The first problem that I had was that for each SSIS event there were multiple lines within the csv when opened in Excel which you can see below.

Thankfully this made the rows pretty unique looking, so an Excel Macro was able to clean this up pretty easily.  Before I got started I added some column headers into the excel sheet.  The default columns from Event Viewer are Date, Time, Source, Severity, Category, EventID, User, Computer, Description.  This covered columns A through I.  After that I put columns J through P as being Operator, SourceName, SourceID, Execution, StartTime, EndTime and DataCode as these are the specific items that SSIS is logging that you can see above.  Then it was time to bust out a little VBA and flatten out all this data.  The macro shown below looped through the data and flattened everything out so that all the data was on a single line.  Then is went through and removed all the white lines.  This macro took about 3 hours to run.

Sub CleanUpData()
Dim Row As Long
Dim DataRow As Long
Row = 32768
Do While Range("A" & Row).Value = ""
If Range("B" & Row).Value = "" Then
DataRow = Row
Range("A" & Row).Select
Else
If Left(Range("A" & Row).Value, 4) = " Mes" Then
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Ope" Then
Range("J" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 9) = " Source N" Then
Range("K" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 10) = " Source ID" Then
Range("L" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Exe" Then
Range("M" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Sta" Then
Range("N" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " End" Then
Range("O" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Dat" Then
Range("P" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
End If
Row = Row + 1
Loop

Do While Row 2
If Range(“A” & Row).Value = “” Then
Range(Row & “:” & Row).Select
Selection.Delete
End If
Row = Row – 1
Loop
End Sub

Once that was done I needed to set the start times for each event on the same line as the end time for each event. That way I wasn’t scrolling through looking for stuff while trying to do my data analysis. This I was also able to do with a little Macro. In this case I started on row 14 as that was the first entry that had SSIS data. You may need to change the EndRow=14 line to what ever line your data starts on. This puts the start time and the end time into Columns R and S, but only on the line which is the OnPostExecute line.

Sub FindRunTimes()
Dim EndRow As Long
Dim StartRow As Long
Dim SourceName As String
EndRow = 14
Do While Range("A" & EndRow).Value = ""
If Range("I" & EndRow).Value = " Event Name: OnPostExecute" Then
StartRow = EndRow + 1
Do Until Range("K" & EndRow).Value = Range("K" & StartRow).Value And Range("I" & StartRow).Value = " Event Name: OnPreExecute"
If Range("I" & StartRow).Value = "" Then
MsgBox "Ran out of rows to process for ending row " & EndRow
Exit Sub
End If
StartRow = StartRow + 1
Loop
Range("S" & EndRow).Value = Range("B" & EndRow).Value
Range("R" & EndRow).Value = Range("B" & StartRow).Value
'Range("T" & EndRow).Value = DateDiff("mi", Range("A" & StartRow).Value & " " & Range("B" & StartRow).Value, Range("A" & EndRow).Value & " " & Range("B" & EndRow).Value)
Range("R" & EndRow).Select
End If
EndRow = EndRow + 1
Loop
End Sub

Once that Macro was finished I needed to figure out which of the statements was taking to long to run. Putting a formula into the column Q marked the rows that I needed to look into a little more. In this case I was using anything with a run time of 20 minutes or longer as needing to be looked at.

=IF(HOUR(R595)=HOUR(S595)*60+MINUTE(S595),"", "X"), IF(HOUR(R595)*60+MINUTE(R595)+19>=HOUR(S595)*60+MINUTE(S595)+1440,"", "X"))

Once that formula was in there all the rows that were SSIS objects that took a long time to run had an X next to them. Now I still needed to ignore the rows which were containers, but those were pretty easy to figure out from the names of the objects as most of them had container in them (in the SourceName column which was column K of my Worksheet). At this point I was able to move back to the SSIS package and start digging through the package finding the objects which had long runtimes and getting those looked at.

The whole reason I went through this process instead of just adding logging to the SSIS package was for a few different reasons.

  • I didn’t want to make ANY changes to production if I didn’t have to. This SSIS package loads a production data warehouse which the business uses to run their company, so making changes to it wasn’t something I wanted to try.
  • If I had simply put logging into place I would have had to have waited another day to being looking at the problems as the ETL process only runs once a day.
  • The SSIS package takes 8+ hours to run, so sitting around waiting for it to run wasn’t exactly the best use of my time, or the clients money.

If you get stuck in this same sort of problem, hopefully this approach will help you out.
Denny


March 22, 2013  8:04 PM

Recommended reading from mrdenny for March 22, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 20, 2013  2:00 PM

Keeping Your Speaking Commitments

Denny Cherry Denny Cherry Profile: Denny Cherry

Many event organizers are way too polite to publicly admit that this problem exists as badly as it does.  But a problem has been showing up way to much recently.  The problem which I speak of is session speakers not showing up for their sessions, and the even bigger problem of speakers not showing up to an event at all.  Both of these happen on occasion, things happen.  But if they do happen, call someone, send an email, do something do try and let them know in advance that you aren’t going to be there.  Especially if you aren’t going to be going to the event at all.

If you aren’t going to be attending the event which you’ve been picked as a speaker for, you probably know that you aren’t going to be attending at least a few days in advance, at least the day before as that’s probably when your flight would have been.  You owe it to the event team and to the attendees to tell the event team that you aren’t going to be able to make it and that they should fill your spot with another speaker.

If you don’t tell the event team that you aren’t going to be there, you look like a putz.

In the last year there have been a lot of instances of speakers just not showing up for SQL Saturday events.  It has gotten bad enough that some of these speakers are starting to get a reputation as being people that won’t show up to present their session.  That means that these speakers have skipped their sessions several times, at least.  Just because a SQL Saturday is a free conference doesn’t mean that you can skip on your sessions.  Yes as a speaker you aren’t getting paid to attend, and it’s probably costing you money out of your pocket for airfare, hotel, rental car, etc.  But you knew that this was the case before you submitted, so just because it turned out to be more than you were expecting you need to either figure something out, or give the team as much notice as possible that you can’t make it.

If you have found yourself in this situation I implore you to stop submitting to the SQL Saturday’s unless you are 100% sure that you’ll be able to attend.  It isn’t fair to the event team that’s putting together the event to have to stress out over you not being there.  It isn’t fair to the event team that has to deal with upset attendees that the session that they wanted to attend isn’t happening (if you think that people don’t complain when a session at a free event doesn’t happen as scheduled you are very wrong).  And most importantly it isn’t fair to the session attendees who expected to see a session but aren’t able to because you didn’t show up.

If the event team knows in advance, even a day in advance that you aren’t going to be there they can find a replacement.  The speakers who are there will have all their other slide decks with them.  Odds are there’s another speaker there who can fill in.  If the session that you are scheduled to present doesn’t get presented then you are letting down the people who have cleared a day from their schedule to see you present that session.  You are letting down the event team who have trusted you to give the presentation.  If the event team didn’t trust you they would have given the spot to someone else.

A perfect example happened at SQL Saturday Nashville in 2012.  One of the speakers had to back out at the last minute due to a family emergency.  They told the organizers, and then organizers were able to find another speaker who had a session they could present on a similar topic.  The schedule didn’t need to be changed, the attendees weren’t upset.  Just instead of a woman presenting they got a man.  But they still got the information that they were looking for, and after all that is the most important thing when it comes to these events.

Sadly there isn’t really a great way to fix this, except for speakers to ensure that they present the sessions that they have committed to present.  As speakers if we don’t shape up about this the event organizers are going to start tracking this (I know that some events already do) which means that those speakers that don’t make it to their sessions aren’t going to get picked and it’ll probably end up being impossible to get off that list.  Which will lead to not being accepted for larger and bigger conferences.  As speakers if we don’t police ourselves and ensure that this doesn’t happen the people that run these events that we like speaking at will find a way to police us.  And I’m sure that it won’t be very fun.

Denny


March 15, 2013  5:19 PM

Recommended reading from mrdenny for March 15, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 13, 2013  2:00 PM

Deleting LOB Data and Shrinking the Database

Denny Cherry Denny Cherry Profile: Denny Cherry

While attending SQL Saturday 194 in Exeter over in England one of the attendees came to Mladen Prajdić, Andre Kamman and myself with an interesting problem.  She had a database table which was about 200 Gigs in size which she wanted to delete about half of the data from the table.  The catch was that the database table was full of LOB data where the rows were very large, with an average LOB data size of over a meg.  She also needed to shrink the database after the database was deleted so that she could reclaim the space from the database.  Oh and all this had to be done on SQL Server 2005 Standard Edition. (Everything here applies to SQL Server up through SQL Server 2012 as well.)

Deleting the data from the database is the easy part, a simple delete loop will handle that nicely.  The problem is when you delete rows from a table which contains LOB data the LOB pages aren’t cleared when they are deallocated.  We can see this by running the following code.

CREATE DATABASE Lobtest
GO
use Lobtest
GO
CREATE TABLE t1 (c1 int IDENTITY(1,1) PRIMARY KEY, c2 ntext)
GO
INSERT INTO T1 (c2) VALUES (replicate('a', 20000))
GO
DBCC IND ('LobTest', 't1', 1)
GO
DBCC TRACEON(5201, -1)
GO
DELETE FROM t1
GO
DBCC IND ('LobTest', 't1', 1)
GO
DECLARE @dbid as int = db_id('Lobtest')
DBCC PAGE (@dbid, 1, 231, 3)
GO

You can see that page 231 is a LOB page which is allocated to the table t1. When you look at the actual page using DBCC PAGE after the row has been deleted we can see that there is data in the page, and that the page header shows that the page is still allocated to the table t1. This can be seen by looking in the header of the page for the header value labeled “Metadata: ObjectId = 245575913″.

When you go to shrink the database the SQL Server engine will get to the LOB pages and it will need to figure out if the LOB row is a part of a row which still exists or not. In order to do this SQL Server will need to scan through the pages which make up the table looking for any rows which reference the page it is trying to delete.

When doing shrinks after deleing large amounts of LOB data SQL Server will generate large amounts of IO while figuring this out and the shrink operation will take an extremely long time. (Paul Randle talks more about it here.)

So the question that this person at SQL Saturday had was, how can I reclaim the space from my database within a reasonable time.

The solution that we came up with was actually pretty simple. Do the database deletion as normal. Then backup and restore the database. Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce.

This works for a pretty simple reason, because the PFS page shows that the LOB page isn’t allocated even though the page is full of data (you can verify this by looking at page 1 in file 1 in the sample database created by the script above). When the database engine backups up the database the database engine looks at the PFS pages to figure out which pages to back up. Because the PFS pages show that the pages are empty the database engine doesn’t bother to backup the pages, so when the pages are restored they are restored as blank pages. This means that after the restore the shrink operation can run without an issue.

In the case of this application there was a maintenance window which could be taken advantage of which would allow the backup and the restore to happen.

Another option which we came up with which would require less downtime involved using database mirroring. By configuring database mirroring (which is initialized via a backup and restore process giving us the same basic approach) and then failing over to the mirror we would end up in the same position. We could then shrink the database without issue (probably pausing database mirroring so that we didn’t have to wait for the second server to process the shrink in real time) and then fail back the database to the original server.

As geeky as it was, Mladen, Andre and I had a great time figuring this out, and the attendee had a great time watching us go through all the possible options as we excluded them one by one. And most importantly she got her problem solved.

So if you end up in this situation here’s a solution that will help you shrink the database so that you can reclaim the space that the LOB data pages are taking up without having to wait forever.

Denny


March 8, 2013  6:04 PM

Recommended reading from mrdenny for March 08, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 1, 2013  6:34 PM

Recommended reading from mrdenny for March 01, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


February 28, 2013  9:00 AM

Get a discount when attending the SQLPASS BA Conference

Denny Cherry Denny Cherry Profile: Denny Cherry

The SQLPASS BA Conference is coming up in April, just a few short weeks from now.  At this conference you’ll see some of the best and brightest minds in Business Analysis and Business Intelligence all in one conference.  This includes speakers like Stacia Misner, Jessica Moss, Chuck Heinzelman, Joseph D’Antoni, Denny Lee, Cindy Gross, Neil Hambly and many, many more.

The keynote presentations for this conference will be delivered by Steven D. Levitt, Kamal Hathi and Amir Netz.

This conference is going to be full of some fantastic sessions which will help you use technology to find the information in the mountain of data that your company has been collecting. Without knowing how to get access to the information that is within all the data there’s no hope of running business operations at their peak performance.  Don’t forget the full day pre-con sessions where the speaker can really dive into the topic at hand.

Still not sure that you want to attend, the conference has recorded some previews of a few of the speakers and sessions that you’ll be able to see at the PASS BA Conference which you can watch, for free from the comfort of your home or office.

If you’d like another great reason to attend, I’ve got 200 of them for you through a $200 discount on the cost of attending the conference.  Just use the discount code BAC984BL when you register to save $200 off of the cost of this great conference.  So get registered for the conference today.

Denny


February 25, 2013  11:44 PM

SQL Saturday 177 Slide Deck

Denny Cherry Denny Cherry Profile: Denny Cherry

This last weekend I had the privilege of speaking at SQL Saturday 177 in Mountain View, CA.  One of the great things about this SQL Saturday is that in some ways it is an extension of the MVP Summit as for the second year in a row (that I know of) this SQL Saturday has been scheduled the weekend after the MVP summit.  This means that they are able to attack a large number of MVPs from all over the country (and hopefully next year the world) as they all stop by on their way home.  This gives us MVPs a couple of extra days of hanging out and catching up and it gives the attendees the chance to see some speakers that they might not normally be able to get access to.

I gave one presentation this year, and it was a session of table partitioning.  The slide deck has been uploaded to the SQL Saturday site as has the sample code.  You can download it from the session page for my session.

I hope that everyone liked the session, and I hope to see everyone at a future SQL Saturday.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: