SQL Server with Mr. Denny


November 19, 2014  7:00 PM

Moving a Multi-TB Database From One Disk To Another

Denny Cherry Denny Cherry Profile: Denny Cherry
Database instance, Database migration, Server migration, SQL, SQL Server, SQL storage, Storage

Recently a client brought me a challenge. They wanted to more a database from one LUN to another. So far this is pretty straight forward. The trick here is that this is a multi-TB instance with several databases which are about 1TB in size, and the instance has a two hour per night maintenance window.

So the trick here is how to move all the databases (including the system databases) to the new disk within the acceptable outage window. Making things more complex, the database instance is a Clustered SQL Server 2014 instance.

Log shipping would work, but that’s a pretty complex migration strategy for a simple LUN migration.

The easiest solution in this case was to add new database files and let SQL Server move the data. How I did this went something like this.

1. Mount the new LUN to the cluster and format it. In this case as the F: drive (the databases are currently on the E: drive).
2. Robocopy everything except for the database files to the new drive. This has the nice side effect of creating all the folders which are needed.
3. Add a new data file for each of the large databases on the F: drive.
4. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the main data files to the new data file.
5. Once the DBCC SHRINKFILE command is done (it actually throws an error because the system objects can’t be moved). Shrink the main database files down as small as possible. I got most of them down to a few megs in size.
6. Change the auto growth settings on the existing file to be disabled so that it can’t grow beyond this small size.
7. Repeat this for all the databases in question.

At this point we wait for the maintenance window.

8. Stop the SQL Server service.
9. Robocopy the database files to the new drive.
10. Remove the drive letter from the old volume.
11. Change the drive letter on the new volume to the new drive.
12. Start SQL Server
13. Log into SQL Server and use the ALTER DATABASE … MODIFY FILE command to change to filename from the new drive letter to the old drive letter.
14. Restart SQL Server
15. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the new data files to the main data file.
16. Drop the new data files from the database.

Using this approach I was able to move several terabytes of databases from one LUN to another with about 20 minutes of total outage to the system.

Now this does require that there’s a good amount of free space on the new volume as you have to be able to have the two database files on the volume at the same time. But a little drive space is a small price to pay for a short maintenance window.

Denny

November 14, 2014  5:13 PM

Recommended Reading for November 14, 2014 by mrdenny

Denny Cherry Denny Cherry Profile: Denny Cherry
Data compression, SQL Server

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.

This weeks SQL Server person to follow on Twitter is: ciscoDC also known as Cisco Data Center

Hopefully you find these articles as useful as I did.

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

Denny


November 13, 2014  5:00 PM

PASS Speaker Idol 2014 Review and Notes

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

This year the SQL PASS conference held their first Speaker Idol contest with the goal of getting new speakers into the PASS Summit on the strength of their presentation skills instead of their abstracts.

And I think I can safely say that it was a resounding success.

We started with 40 people who applied (38 fellas, and 2 ladies) which were cut down to 12 people who would actually compete during the PASS Summit. Some people noticed that the 12 people who competed were all guys, which was strictly by chance. When selecting people for the 12 spots they were selected based on their answers to the sign up form, which I read and scored without looking at the speakers names. This review cut the list down to about 18 or 20 people. Those 20 were ranked based on speaking experience (were checked against PASS speaker lists from prior years) and the top 12 were invited to present in the speaker idol sessions during the PASS Summit. Sadly one speaker got sick during the summit and couldn’t compete, and there wasn’t enough time to fill the empty stop so we ended up with 11 people competing.

And present they did. All 11 presenters did a fantastic job, as did our judges in providing feedback to them.

Not only did the 11 speakers all do a great job, but they all took the feedback and the four who competed in the finals were even better the second time. The jokes were perfect, the timings were perfect, and some of the speakers took major risks which were so bold that I never would have taken them in a 5 minute session, but they nailed them perfectly.

In the end we had a winner. That winner is our first PASS Summit 2015 speaker (though we don’t know what he’s going to be speaking on yet), and his name is Pieter Vanhove.

Personally my biggest fear this year was that the room would be empty during the speaker idol sessions. My worries were set aside during our first session when the room was basically full. It was a little emptier the next two sessions, but for the finals session, the room was packed. With standing room only.

PASS Summit 2014 Speaker Idol Finals

Now I’ve heard a little bit of a rumbling about the judges selection of the wild card spot. People assumed that the wild card spot would be filled by one of the three runners up. The instructions to the judges was to select anyone that they wanted to see a second time. It could have been anyone from the competition who they felt could have done better than they did. In this case the judges opted to move Rob Volk to the finals in the wild card spot. For next year, I’ll work with PASS on how we want to define the wild card spot, and ensure that the selection criteria for that spot is better communicated to the contestants and the audience. So basically I’m going to fall on the sword on this one. I didn’t define that well, and I didn’t do a good job communicating what the rules around the wild card spot were to everyone.

I hope that next year we can get the speaker idol sessions recorded and included on the USB drives as well as made available for viewing on the PASS Website. The sessions were all informative, entertaining and the judges feedback was very useful for those listening as well as those competing. I know that I got a lot of great tips from the judges, which I hope to include in my presentation style in future presentations.

Overall, I think that the format worked pretty well. I think we need to get regular seats in the rooms so it’s more session like, and he had a little problem with the schedule (the app and my announcements said one time for the finals, but the printed signs in the convention center said a different time) but everyone was able to deal with the change (which actually is a realistic situation as room changes and time changes can happen at the summit).

For everyone that applied to be in Speaker Idol, I thank you. Hopefully PASS will let us do this again, and hopefully I’ll see your name on the list next year. Keep presenting at your local and regional events to improve your skills, and hopefully I’ll see you at the 2015 PASS Summit as a speaker.

For everyone that came to the Speaker Idol sessions, I thank you as well. The 11 speakers who were there did a great job, and it makes it a lot easier to present to a full room compared to an empty one (trust me, I’ve done it).

Also, a big thank you to the judges who were willing to give up a decent chunk of their week to sit and listen to people give 5 minute sessions.

Without everyone coming together like they did, this wouldn’t have been the big success that it was. Personally I hope to see a bunch of the 11 people who presented as speakers at the PASS Summit 2015 so that we can get a bunch of new people into the speaker idol in 2015. And to Luan who got sick this year, I hope to see you at the PASS Summit next year; just this time healthy enough to present a session.


November 7, 2014  3:10 PM

Recommended reading from mrdenny for November 07, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

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.

This weeks SQL Server person to follow on Twitter is: sqlpass also known as PASS

Hopefully you find these articles as useful as I did.

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

Denny


November 4, 2014  7:00 PM

SQL Karaoke Is TONIGHT! – Or it would have been, if this post went out on time.

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

I just want to remind everyone that SQL Karaoke is Tonight at Cow Girls, Inc.

Be sure to get signed up and show up for a great time with an awesome Rockaraoke Band.

A great time will be had by all. I can’t wait to see you there.

Denny


October 31, 2014  5:10 PM

Recommended reading from mrdenny for October 31, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

Happy Halloween!

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.

This weeks SQL Server person to follow on Twitter is: MartyCatherall also known as Martin Catherall

Hopefully you find these articles as useful as I did.

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

Denny


October 29, 2014  7:00 PM

A Seek Isn’t Always Good Enough

Denny Cherry Denny Cherry Profile: Denny Cherry
Execution Plan, Query tuning, SQL, SQL Server

Index seeks are good, right? Normally they are, unless the index that you are seeking against isn’t the correct index. If we look at Figure 1 everything in this plan looks good, right?

Blog 1 Figure 1

The cost of the index seek against Table1.Index4 is 0%, the IO cost is very low as is the CPU cost. So where’s the problem? The prblem is that the index seek is being executed over 20k times. We can see this by looking at the tool text information about the specific operator which we can see in Figure 2.

Blog 1 Figure 2

Because of this the other operators are executed ones for each row which is returned, causing each of them to then be executed 20k times once for each row from the parent side of the join. We can see this a little better if we look at the exeuction plan in SQL Sentry Plan Explorer which we can see in Figure 3.

Blog 1 Figure 3

Counting the operators and the number of executions we can estimate the amount of IO that this query is going to generate when the query is run. Just looking at the operators on the right hand side of the plan, and ignoring everything else we can see ~320k seeks and/or lookups. If we assume that each seek and/or lookup requires 3 IOPs that’s about ~960k IOPs. That’s about 7.3GB just for this portion of the plan. That doesn’t include work tables, spills, etc.

This specific query is executed thousands of times per hour for 8-10 hours per day. Assuming that it’s just 1000 executions per hour for 8 hours a day, that’s ~570TB of logical IO per day, for one query.

I don’t care how many CPUs the system has, or how fast the RAM is on the server, that’s going to cause some massive delays when running the query. In this perticular case this query is run every time a user clicks a button within the application. This button click would take 6-8 seconds to complete.

So how do we fix this problem. We need to look at the query that is using this plan. We see that the index which is being used is filtering based on a status column, which means that there are 20k rows which have the status set to active. When we add an index on the table, which matches the other predicates of the query (the where clause). When we did this to this specific system, the first index seek which was executed dropped from ~20k rows being returned to 5. The number of executions for the other operators also dropped to a small number (we also fixed the indexes so that they would address the key lookup problems). When all was said and done we reduced the when to running in just a few milliseconds and we went from almost 1M IOPs to several hundred IOPs.

Because of all the extra logical IO that the SQL Server was doing for these queries the CPU was running quite high as well. There were 24 vCPUs allocated to the Virtual Machine, which were running about 45% CPU workload. After adding in the indexes for this query, the CPU workload dropped to an average of 4% workload with spikes to 7%. A massive improvement.

It just goes to show, that just because your query is seeking on your indexes that doesn’t mean that you are done tuning your query.

Denny


October 24, 2014  4:54 PM

Recommended reading from mrdenny for October 24, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Mail, SQL Server, SQL Server 2014

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.

This weeks SQL Server person to follow on Twitter is: SQLSWAGG also known as Dan King

Hopefully you find these articles as useful as I did.

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

Denny


October 22, 2014  7:00 PM

And your SQL PASS Speaker Idol Judges Are…

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

If you are attending the SQL PASS Summit in Seattle, WA in November 2014 you’ve probably heard that the SQL PASS Summit is going to be having it’s first speaker idol with the winner of the competition winning a speaking slot at the SQL PASS Summit in 2015.

In order to have a contest we need judges to tell the contestants what they’ve done right and what they’ve done wrong.

So without further ado, I present the judges for the 2014 PASS Summit Speaker Idol, in no particular order.

Tim Ford
Grant Fritchey
Karen Lopez
Jason Strate
Allen White
Stacia Misner
Joey D’Antoni

These judges will be reviewing the 5 minute session that our lucky 12 contestants (to be announced shortly) will be presenting during the PASS Summit. After each person’s presentation the judges will have a few minutes to provide feedback to the contestant. After all the contestants have given their presentation the judges will select the winner from that round.

I look forward to seeing what our contestants bring to their sessions, and I look forward to seeing everyone at the PASS Summit.

Denny


October 17, 2014  3:42 PM

Recommended reading from mrdenny for October 17, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Administrator, Git, Meeting, SQL, SQL Server, Tor

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.

This weeks SQL Server person to follow on Twitter is: Ike_Chuks also known as Ike C

Hopefully you find these articles as useful as I did.

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

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: