Rose will be a big loss and one that hits close to home.  My thoughts go out to her family. She will be very much missed.

Remember to cherish each day we have with our families, friends and everything else you take on in life.

In reality, troubleshooting is a methodology that has an equation simply stated as – problem * identification / normal operations = solution.  This means that the problem must have an identity in order for a solution to be developed.  The solution cannot be sound or stable without identifying the problem.  Furthering this, we can ascertain that a solution can be an evolving equation as well.  A solution required, in many cases, forms a further problem in the form of changing the data services as they are at the time the initial identified problem was uncovered.  This can be in the form of growth, maintenance, introduction of new variables and so on.

As we can see, this question of “what is normal” is hard to answer as an overall solution without identification and more so, a basis of what is a normal running status.

Baseline Collection

Collecting information on SQL Server has been extremely simplified since the release of 2005.  This has been made possible by the exposure of data management views, system performance information and other metrics that we can easily retrieve and store for analysis.  Even with this ease of retrieval, many times the development effort to set up an automated solution is foregone due to the resources available (or lack of resources).  Fortunately, several SQL Server experts simplify this task further by releasing prewritten queries and distinct metrics that can be beneficial to retain for analysis over periods of time.  Glenn Berry of SQLskills.com is one of those experts.  Glenn has shared with us a series of diagnostic based queries that retrieve information about how SQL Server is running.  These queries also expose information that can be retrieved on a time interval to base analysis over a time period.   These can be used to form a baseline of how SQL Server is running.  With this information being collected, one can fill in the much-needed information to the equation we discussed earlier and base a solution that is more sound and stable.

Even with the work that Glenn has done for us, there is a small amount of effort needed.  In the case of the queries Glenn provides, SQL Server Integration Services (SSIS) retains much power in a rapid development platform that can be automated for later executions.

After reviewing the queries Glenn has provided, 32 distinct tables can be formed from them.  Creating all these tables may seem at first to be a daunting task but it can be simplified.  Working through Glenn’s SQL script, identify the segments and queries that retain value for collecting them over a period of time.  Run each statement taking advantage of the SELECT INTO statement.

Note: before reading on, please ensure to always show the recognition that is deserved while using anything someone has provided to the public for use.  In this case, always place the header in each area the code is used to show Glenn as the author and SQLskills.com as the supporting company.

-- SQL Server 2008 R2 Diagnostic Information Queries

-- They are all noted in the instructions

-- SQL Server 2008 R2 RTM was retired on July 12, 2012

If an author does not provide a header such as the one above, add one stating the source, author and date.

For example:  For the SQL Server 2008 R2 diagnostic information queries, the hardware information query pulling from sys.dm_os_sys_info, holds value and should be retained and monitored for change.  This is evident from wanting to know how a baseline is altered by hardware changing over time.  To make a table based on this query, simply add an INTO <table_name> to the statement and the table will be created.  Remember to always verify the data types that SQL Server has created are accurate by sampling the data and make adjustments as needed.

Other considerations while creating these initial tables are the instance name, collection date and, if applicable, the database name.  Without these pieces of information, a baseline cannot be formed.  Make  these small adjustments so the three key pieces of information are captured.  Using @@SERVERNAME, GETDATE(), and DB_NAME(DB_ID()), they can be obtained quite easily.

After performing this task for all the information to be collected, the tables will start to look much like the ones below.  Ensure all the tables that are specifically designed for database-level information including the database name.

SSIS Automation

After you’ve created all the tables found to be of value, the SSIS package can be created to automate loading the data based on a schedule.  In a stripped down form, the SSIS package can be executed with nothing more than the use of a series of Data Flow Tasks (DFT) and a Foreach Loop Container.   The Data Flow Tasks will be a direct source to destination loading scenario except for the initial task of loading a table with the information for the instances.  Loading the information about the instances is a task that requires a lookup to decide if something has changed.  If nothing has changed, there is no need to insert duplicate rows, which will only make reporting off the information more difficult in the future.  This lookup task would lead off the entire process.

Internally to the DFT, the process would be a source and then a lookup on the destination.  This would allow the decision to be made if the data requires a new row or an update.

In the case above, the row is always updated.  The process can also make the decision to do nothing or validate what has changed.  In the case of instance information, the count of the overall rows and width of the data is typically so low, the update should not pose a performance problem.

The next series of DFTs are all similar.  Group tables together so multiple queries can be executed and data moved in parallel.  This will speed up the overall execution time of the SSIS package.

Take the same Hardwareinfo query from earlier.  For a DFT to load this table, it would be an instance level task and not in the Foreach Loop Container.

This DFT is nothing more than a direct source to destination

Until we get to the database specific queries, the DFT creation has been fairly simple.  With database specific queries, however, we need to obtain a listing of the databases that should be evaluated and have data collected.  This can be done by using the Recordset Destination populated by a source adapter based on the following query.

Now the Foreach Loop Container can be based on an ADO Enumerator and each internal DFT to the Foreach Loop can be executed only for the specific database that is currently allocated to a variable the Foreach Loop specifies in the object source variable.  This would appear as shown below in the Foreach Loop editor.

Note: setting the MaximumErrorCount higher will prevent certain DMV queries that would error on databases from failing the entire package.  This would allow the collection of databases even if one database caused an issue when the query was executed.

The finished SSIS Package would appear as shown below.

Baseline Reporting

Up to this point, the effort put into the baseline collection process has been minimal.  Without a doubt, the reporting aspect will be a time consumption that far outweighs the SSIS or database creation to retain the information.  Of course, skills may be much higher than this author’s in creating visualization of data.  In fact, for the task at hand, I enlisted the expert knowledge of Jes Borland to offer a hand in making a representation of a few pieces of that data in the collection database.

Without visually being able to review the baseline data, it does not offer much value.  The baseline data can be queried when needed but the overall needs of a baseline do not end with troubleshooting.  We can obtain the needed information for requesting new hardware, consolidation needs and overall knowledge of data growth and business requirements that demand the need for SQL Server and the resources to evolve.

We can also obtain a quick review of how objects like indexes are being used.  Indexes are evolving objects, and in that evolution, daily, weekly and monthly changes occur.  Knowing how they are being used and evolving is critical to making decisions to adjust indexes so they fit the needs of the data services.

For example: looking at a segment of 5 days based on the collection in the IDXReadWriteStats table, we can (or, Jes can) design a chart as shown below on how the indexes are being read.

In this chart, we can quickly see on 4/1/2013, the pk_tblEntry (a master data table), was read heavy but then pulled back on reads on the other collection dates.  This could show a beginning-of-the-month task that would affect the data services.  Thus, the problem that could arise is the same situation where a customer comes to the team with a slow data service.  If the date lands on the first and the database is directly related to the master data database, we know the entire equation to the problem and solution.

Summary

Baselines are a critical information collection process to all data services.  This doesn’t stop with SQL Server but spreads to all data services.  With the help and validation of resources available online in the form of scripts, blogs and articles, a solution can be development and implemented with a much lower amount of resources needed over the complete creation from the bottom, up.

If you would like to play with the SSIS Package that was written for this article and the database, you can download this compressed file. The database is SQL Server 2012. Restore the database and add the DTSX file to a new solution in SSDT 2012. Edit the variable User::SQLInstance to the instance you would like to start collecting from.

The missing index feature, while tuning or checking estimated and actual plans, is very helpful.  Something that is key in that sentence is the word, helpful.  Don’t run out and simply create all of them.  Make sure it is a viable solution that does not implement the three major faults in index strategies: duplicates, overlapping and misalignment.

In SQL Server 2008 and previous versions, the missing index suggestions had a little feature (read as bug) that would suggest an identical index to what was already on the table.  In fact, in some cases, it would use the index that was a duplicate and still suggest to create it.  You can see a great article on this from Paul Randal, “Missing index DMV bug that could cost your sanity…”.

With SQL Server 2012, this was thought to be resolved but we still can end up with the same situation.

For example, the table the example below is based on is a 290 million row table, partitioned by date.  The date value is a range from the first day in a month, to the last.  There is also a record ID for other purposes that is set with IDENTITY(1,1).  When querying by this record ID, the plan suggests creating an index that is identical to the one being used in the plan. Partitioning, the query and the table structure is not what we will talk about today.  What we will talk about is the reaction SQL Server 2012 took and suggested to make this query more effective.  Remember – suggestions are, suggestions.

Query

Plan

The missing index details

The index, IDX_RECORDID_ASC definition

As we can see, the two indexes are duplicated.  If you were to create this suggested index, you would have a major performance impact due to the size of this table.

Summary

We do not always see performance issues from duplicated indexes from blindly creating indexes suggested by SQL Server.  This is typically due to the fact that tables may not be large enough at the time to really see how the impact can degrade performance.  When indexing, you always want to think, at a minimum, 20 times larger than what the table is and how it may impact how the table is used.  If you have that mindset, even if it does not always apply to table like metadata tables, you’ll implement better indexing and better management solutions.

Msg 40552, Level 20, State 1, Line 1
The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction

This error is more of a pain than anything and you can work around it.

The structural architecture that SQL Azure uses and all around resource allocations, doesn’t allow us to go in there and simply size a transaction log like we with SQL Server on metal.  At least, I haven’t found one.  With that, we run into a mess of headaches when we have anything over a few hundred thousand rows in one table.  Here is what you can do, to get beyond this error.

Changing Data and Log Usage Errors

Changing data means just that – updating, inserting or deleting large amounts of data.  If you run into this error while loading data, try to perform the age old method of batch processing data.  Now this goes against almost every fiber of a performance mindset given the advancements in data loading and transformation.  However, it’s a life we have to work around while in SQL Azure.  With SSIS, you can create multiple threads and work on each thread while only sending so much at once to SQL Azure.  SSIS is fully capable of batch processing data also.

Creating, Rebuilding a Big Index and Log Usage Errors

Indexing is what got this article to be published.  If you have a few hundred million rows in a table, create a nonclustered index on that table, you will undoubtedly result in the log space usage error.  For a moment, this raised great concern.  Truly, the data is no good in SQL Azure if you cannot read it in under 30 minutes, preferably seconds.  So you need indexing to support the queries that are going to be used to read the data effectively.  Luckily, SQL Azure does support ONLINE operations in indexing.

In this case, the ONLINE operation with a CREATE INDEX statement provides the functionality of the index to be built by taking advantage of the build main phase.  This performs the primary needs of reading the table and then inserting by means of bulk loading operations, into the new index.

To create the index with ONLINE, you simply specify the ON value in the options.

This “should” allow the index creation to complete.  It will take longer for the creation but, the importance of having indexing is without a doubt, not something you can give up.  Unless the data is simply a DR strategy or some sort of backup.

In this case, the restore statement was as simple as it can get.

The restore ran fine, as shown below, until it hit the database version upgrade section. In this case, this is a SQL Server 2008 database, restoring to SQL Server 2012. Or, database version 661 to 706.

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 532576 pages for database 'TEMPRESTORE', file 'TEMPRESTORE' on file 1.
Processed 4 pages for database 'TEMPRESTORE', file 'TEMPRESTORE_log' on file 1.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 845, Level 17, State 1, Line 2
Time-out occurred while waiting for buffer latch type 3 for page (1:0), database ID 27.

As shown, the error occurred upgrading the database and the restore failed. At this point, the database is left in restoring state or no recovery. What really isn’t shown is the fact the database restore was really done and the database upgrade was all that remained. This is where knowing restore stages and processing is a good piece of knowledge. (To learn more, "Understanding How Restore and Recovery of Backups Work in SQL Server").

Since the database was restored successfully and the only part that remained was to bring it online, upgrading the internal database version, the following statement should resolve the problem without requiring a completely new restore execution. In most cases, a restore is a one-way street. Start a restore and if it errors, there is no other way but starting over. In this case, don’t be so quick to take that ultimatum.

The resulting messages below show the restore was at a state that allowed the call for recovery to complete and bring the database online by upgrading the internal version. Now, a critical step here to verify the databases integrity is to run a CHECKDB before allowing activity on it. This is even more critical if this is a restore that is required for production. Do not skip a CHECKDB on a database if this happens to you.

Converting database 'TEMPRESTORE' from version 661 to the current version 706.
Database 'TEMPRESTORE' running the upgrade step from version 661 to version 668.
Database 'TEMPRESTORE' running the upgrade step from version 668 to version 669.
Database 'TEMPRESTORE' running the upgrade step from version 669 to version 670.
Database 'TEMPRESTORE' running the upgrade step from version 670 to version 671.
Database 'TEMPRESTORE' running the upgrade step from version 671 to version 672.
………
Database 'TEMPRESTORE' running the upgrade step from version 700 to version 701.
Database 'TEMPRESTORE' running the upgrade step from version 701 to version 702.
Database 'TEMPRESTORE' running the upgrade step from version 702 to version 703.
Database 'TEMPRESTORE' running the upgrade step from version 703 to version 704.
Database 'TEMPRESTORE' running the upgrade step from version 704 to version 705.
Database 'TEMPRESTORE' running the upgrade step from version 705 to version 706.
RESTORE DATABASE successfully processed 0 pages in 289.824 seconds (0.000 MB/sec).

Summary

If you run into the error, “Time-out occurred while waiting for buffer latch type 3 ...” at the end of a restore, run a restore with recovery on the database before taking the long way around and running the entire restore again. If the recovery is successful, run a CHECKDB to ensure the integrity of the database is good and if it is, your error will not have caused you a much longer and time consuming task of running the entire restore over.

Original post blogged on LessThanDot.

Again, speaking is this month’s topic.  Bob has this lined up as, “This month the prompt is how did you come to love presenting? What was the first time you gave a presentation in front of a group and really enjoyed it? Was it something that was required of you in school? Something you did in the workplace? Were you inspired by other SQL community members and thought “I think I can do that too”? Whatever your story is, I’d love to hear it. Not a presenter? Not a problem! Feel free to chime in with whatever you like that’s related to either presenting or SQL Server in general.” As I usually do, I’m going to take the last option and talk about a part to speaking that is completely outside the questions asked.  I’m fully taking advantage of, “Feel free to chime in with whatever you like…”

Picking a Topic

There are a ton of great topics out there and deep dives into how SQL Server works, from page internals to extremely in-depth optimizer information.  These topics are excellent and a must in terms of learning when you truly get down and dirty in the mud with SQL Server.  However, there is another method you can rely on and have great success in when picking a topic; pick topics that people will use in real life from small to large sized SQL Server installations.

What does that mean?  When you’re picking a topic, think about your normal workload or tasks you typically would be performing.  Don’t limit this to daily tasks but projects that take some time to plan and implement.  A great example is a presentation I recently wrote and have given a few times, “Upgrading to SQL Server 2012 with Limited Downtime”.   Everyone at some point will be upgrading SQL Server.  In the real world, almost everyone can retain some value from attending this presentation that goes over real life, executed designs, preparations, implementations and methods to accomplishing the upgrade.

Execution plan tuning is another idea.  I’ve done a 100-200 level session on tuning in real life patterns.  These sessions have always drawn off what would be reality in a normal administrative and development team.  Of course, this doesn’t mean it is not a good idea if you can spend 75 minutes talking about how, why and what happens when a WorkTable is introduced into an execution plan, to write a session like that.  That information is just as valuable as any session.  However, think about your audience at the same time.  Will the entire audience gain some real return for investing their time attending?  You may get a room of 100 people that will get that from a 500-level session but you may have a better result and build a platform to the 500-level session by implementing a presentation that can be utilized in a broader team set and SQL Server installation base.

So, when you are thinking about what session to write, what the topic should be, don’t spend time thinking about if it will make you look smart enough, be hard enough to follow or be the cat’s meow to the most internal and in-depth topic around.  Think about making an effort to return a higher ratio of the attendee’s investment sitting in your presentation by giving them something that they truly can take away and utilize immediately.

Practice doesn’t always make perfect

I like to think of my presentations as TED.com talks, not so much a timed point and click technical session.  Everyone has their own method and way to practicing a session.  Some practice a session to the point that almost every word is repeated, sequentially and timed to the minute.  You may also think that everyone does that and that is the only way to be successful.  Honestly speaking, that isn’t always true.  I’ve had many sessions that have not been practiced at all.  Well, practiced beyond the morning of the presentation, a run through the slides and a few keywords to make sure the session flow continues as it should.

Practicing simply isn’t for everyone.  A lot of people find that presenting is something that really does come naturally.  Getting in front of a group of people and letting the presentation flow as it should base on how the presentation fits to the group that is sitting in front of you.  I believe each presentation evolves during the time is it presented based on the group that is in the room.  You should take in the expressions on attendee’s faces, listen to the questions they are asking, note if the group is made up of DBAs to Developers.  If the session is on execution plan tuning and I’ve ended up with a room of developers, I’ll manipulate the session as I discuss the best practices and ways to tune execution plans based on how a developer would use it best.  If the room is primarily DBAs, then it will look to that side.  If the room is a good mix, include that mix in how you present things.  Include statistical information as much as coding changes to make plans change.

Now, this does not mean you can change demonstrations per each group, or not practice and prepare demonstrations.  Demonstrations are, and should be, static and functional.  Set them up before hand, ensure they function, back them up, and make them easily recreated on other machines (in case your machine dies 10 minutes before you are going on stage).  Practice the demonstrations but don’t make them the presentation. Make your experience and stories the reason you are advancing your knowledge to the group.

Summary

One of the best presentations I have ever been in was one that Jessica Moss (B | T) gave years ago in Iowa for a SQL Saturday.  Jessica’s presentation was on SSAS and just when the session started, the projector in the room basically blew up and would not work.  Without skipping a beat, the session evolved rapidly and Jessica made that session work without the projector.  Not only did Jessica make it work but, she evolved it into a session that fit the group.  Once Jessica started talking, she noticed the group wasn’t really with the level or the track she was on.  Based on that, she altered the entire hour and evolved the session into what the group really needed to understand the topic.  That, to this day, is still the most impressive show of presentation skills I have seen and I learned from it.  I can draw off evaluations and feedback to say, it seems to be working pretty well and people enjoy the way I present.  Now, just because I present this way successfully, does not mean you have to or will.  Use this and what I’m sure will be countless other recommendations from other common speakers in our community, to build your own method of handing off our wealth of knowledge the best way we can to keep the attention there, the gratitude afterwards and the rewards of what it means to provide a truly good presentation.

SQL Saturday in Madison – This is our second SQL Saturday in Madison.  Last year was a screaming success (I say that because Jes is on the organizing team ;-)  This year will be as good if not better than last year.  The location is the same, speakers and schedule looking awesome and the Wisconsin SQL Community just has a great time networking and learning together.  I have the honor of presenting with the famous Jes Borland too!!!  I may have to get her signature while we’re up there.  I may also get it for saying that.

PASS Business Analytics Conference – The window sticker, “Immerse yourself in the world of data-driven insights with over 60 sessions by top Microsoft and industry experts – including award-winning economist and Freakonomics co-author Dr. Steven D. Levitt. Gain real-world insights, prescriptive guidance, best practices, and strategic vision to expand your ability to predict business performance.”  The non-window sticker:  this conference is looking better and better each time I look over the sessions.  This is coming from a not-so-BI-driven-SQL-Guy.  If a conference geared towards the BI space of SQL Server can intrigue a diehard internals SQL MVP, you shouldn’t be missing this one.  If you are cringing at the cost, make the case to have your company send you.  The ROI and value is there.

The week comes to an end on a high note with SQL Saturday in Chicago.  This SQL Saturday was one of the first I helped organizer and one of my favorite.  The team, the location, sponsors and schedule are always amazing.  The best part, like any SQL Saturday, it’s free and still Summit level content.  I’ll be helping DeVry again by teaching students in their IT programs but also ending the day with a session on upgrading to SQL Server 2012 with limited downtime.  This session was given recently for the Chicago SQL Server User Group and a great session.  Not so much for my side as a speaker, for the discussions and interaction it sparks with everyone wondering just how easy it should be to upgrade to SQL Server 2012.  I’ll have demos on upgrading from SQL Server 2008 to SQL Server 2012 already setup with Availability Groups and a quick application review of utilizing connection strings to show mirroring at work for upgrade usage.

If you want to stretch the week out even longer, Fargo is holding its first SQL Saturday on April 27^th!  This event was the brain child of good friend Jason Strate.  I was lucky enough to have helped as an organizer and can with complete confidence say, this SQL Saturday is going to be a huge success and one that will become an annual event for another great region.

As you can see, there is a lot in the North-Central to Midwest PASS regions this coming week.  Plenty of reasons to learn some new things about SQL Server and network with people that have that passion about this product line just like you do.

I hope to see everyone at one of these events.  They all should be a great success.

Simple Testing

A basic test from a box version of SQL Server to a SQL Database is pretty uneventful.  In the past, these tests have proven effective with only a few catches.  A few of those are

1)      Network, network and network.  Your line to the “cloud” is critical to stability and speed.

2)      Disconnections can be painful so plan for them. Have a restart point.

3)      Manipulate your connections and data flow so they are tuned for a push to a cloud environment.

Before going too far, the testing so far has been limited to SSIS.  BCP and Bulk Copy are two other options that should be investigated if you run into performance problems.  However, SSIS being the flagship ETL for Microsoft Platforms, it is a given this is the tool you will look at initially. There is a need to state, “Use the right tool for the task”.

The first thing you can do in order to perform a simple test of pushing data to a SQL Database is go out and get a trial of Windows Azure.  The trial is pretty small in terms of nodes and performance, but it serves as a great introduction to working with Azure.  You can connect to SQL Azure with a number of methods.  SSMS is typically going to be a common one if you are a DBA type or used to working out of SSMS.  To connect to SQL Azure from SSMS, you can follow the instructions here, “Connecting to SQL Azure from SQL Management Studio 2008” or for a more visual look and addition notes on firewall changes, “Connecting to SQL Azure with SQL Server Management Studio 2008 R2”.   With SSMS 2012, all of these steps are identical and nothing changes.

Once you configure your SQL Database, a good test is to push some data from AdventureWorks.  This task is almost identical to the task of pushing data from one SQL Server to another.  Create a connection (ADO.NET or OLEDB) for a source from SQL Server and AdventureWorks and then create a destination using the same guidelines and options as how you would connect to SQL Azure from SSMS.

Add a data flow

Push some data

This article will not go into the basic steps of the SSIS setup but is more of a guide to what you need.

Some things that are important and also recommended from the SQL CAT team when it comes to SQL Azure.

1)      Use multiple streams.  Cut your data up into multiple packages or data flows and run concurrent loads

2)      Manipulate your network packet sizes given the concept of pushing data out to the internet ( that is the best way to say it)

3)      Think about indexes. If you have a ton of indexes, consider disabling them, dropping them and so on for a data load situation. This is the same with any data loading event.

“Loading data to SQL Azure the fast way” is a great resource for these and other testing and options to pay close attention to.

Lots of Data

The simple test of pushing data into SQL Azure is excellent for getting used to the slight differences and dealing with throwing data out into the cloud.  The problem you run into is going to be volume of data.  This is where the typical trial will cause some suffering on performance.

Recently, I had the chance to finally load a real life amount of data into SQL Azure and I wasn’t all that happy with it.  Now, before going on, there are some things that could make it better.  More nodes in the cluster, a thicker network, possibly a design change to the table.  The list could go on but we’re talking about real life and I wanted to share this experience in case you run into it and know what to be prepared for.  Remember, this is pushing over the internet.  We’re not hard-lined into a network so that must be taken into account when we say, “it was slow”.

The amount of data that needed to be loaded from a SQL Server instance to SQL Azure was 90GB, comprised of one table.  The table was thin at 20 columns and a maximum row size of 233 bytes.  This information is important when considering your packet size.

The test performed was in SSIS with 4 segments of the data needed to load.

The packet size was lowered slightly and use bulk load when available was checked. Also, the batch size was left at the defaults.

How long?

This was the painful part and really, why I’m writing this blog – prepare yourself.

The SQL Azure destination had no indexing on it to enhance the ability to load the data.  The schema was identical as well so the data flow was a direct, source to destination (as shown above in the image).  The rate of loading the data was around ~500,000 rows per 14 minutes.  Minutes is not a typo.  Yet again, this is pushing over the internet to the cloud.

Given this rate and the volume that needed to go up being around 260,000,000 rows in the first load, the estimated total time for this task was around 121 hours.  Whoa!!!  There’s some planning that needs to be done here, resources increased, horses kicked…something.

Summary

This article wasn’t intended to show you a step-by-step SSIS setup to load data to SQL Azure.  Truly speaking, it is just another data source.  What we did take away is the time that is needed when you are loading a massive amount of data to SQL Azure as part of either a migration to the cloud, integration of historic data or other needs that have pushed your data into SQL Azure.

Plan for these lengths of time, take the options that need tuning into consideration, look at testing other load methods, and make sure the other cloud options are investigated.

The next step that will be performed is a load into RDS to see if there is improvement.  That will be posted in another article and should give a good view into the choices available.

Lastly and to reiterate, this was a large volume data load as an initial setup of SQL Azure as a source.  Typically, we do not push 90GB tables into the cloud daily (not sure you could) so take this as an article outlining the need for really paying attention to your planning and designing when converting over to SQL Azure, if that has been a decision that was made.

I’m not going to run through actually performing an upload because Aaron Bertrand already has a detailed article over on SQLPerformance.com on how to upload and review your plans on the site.   With Aaron’s article, I didn’t want to just rewrite something that is already perfectly outlined, as Aaron always does.  What I do want to say is, the feature being handed out by SQL Sentry within Plan Explorer is extremely valuable in a lot of ways, but most of all, it shows the commitment SQL Sentry has for delivering a truly useful product and ensuring everyone has access to the assistance we need to truly result in the most optimal execution plan performance possible.  This is an easy extension of resources that expands an individual’s or team’s ability to resolve and learn about the optimizer and how execution plans are created, work and can be manipulated.

If you haven’t downloaded Plan Explorer, give it a try.  If you find yourself not wanting to work without it, give the Pro edition a try next.  You won’t regret it.  You also will have an extremely hard time working with execution plans in SSMS once you start using Plan Explorer Pro.

To read more about Plan Explorer and the Pro features

A Glance of Plan Explorer Pro’s Deadlock Analysis

SSMS Add-in for Plan Explorer Update

Plan Explorer Pro – First Glance