Preparing for SQL PASS Summit - Part 3

Since I've attended SQL Pass Summit a couple of times, this year I volunteered to help out with the "Summit First-Timers" program. This program is designed specifically to help people who are attending SQL Pass Summit for the first time to get the most out of their experience at Summit. I've decided to post a series of blog entries here to not only help out my flock of first-timers, but to also ensure that the information is shared with as many other first-time (and alumni) attendees as possible. This is the third posting in the series – Part 1 covered planning the trip, getting there and garments to pack. Part 2 provides advice on safe(r) travelling and choosing the right luggage to haul your stuff in.

Yes, Virginia, There Is A Free Lunch (and Breakfast)

One thing I rejoiced about the first time I attended SQL PASS Summit was the fact that most of my meals were included with the Summit registration, which meant I didn't have to eat at expensive restaurants or subsist on fast food and convenience store munchies. Yes, there are great restaurants in Seattle, and yes, people sing the praises of Top Pot doughnuts for breakfast, but since the conference registration includes the cost for these meals, there's no sense in paying twice for the same meal.

Breakfast

Start your Summit out right by waking up bright and early and heading over to the Conference Center around 6:45 AM each morning for a continental breakfast. You'll find the dining area near the vendor's hall (in fact, they're separated only by some ropes marking the border) on the same level as Registration. The room is "4B" if you're looking for it on the Conference Center map.

Grab yourself a hearty breakfast then plunk down in a seat next to a future friend. That's right - the included meals are excellent networking opportunities, as well as convenient ways to ensure you've consumed enough coffee to stay alert through the morning. Always strive to sit near someone different, and be sure to pass out business cards (you did print some up, right?) to everyone you meet. Ask them what sessions they're planning to attend (and why!) - you may discover that a session you dismissed as "not interesting" is actually something you really must attend.

In addition to the Summit-included breakfast, be on the look out for Vendor-sponsored breakfast sessions that will be offered on some mornings. The food will be similar, except you'll be listening to a presentation while eating your breakfast. I like these smaller breakfasts because the Vendors frequently give out awesome door prizes (I recall that at one Vendor breakfast, the grand prize was a new laptop. No, I didn't win...). Check the "After Hours" web page and your conference materials for details on when and where these breakfasts are to be offered. Also, after the session, be sure to stop by that Vendor's booth in the Exhibitor hall and thank them for sponsoring the breakfast (especially if you win something).

Break Time

To ensure you don't get too dehydrated or coffee deprived, there are breaks between some of the sessions. See schedule for exact times since they vary depending upon the day. The breaks area is in the East Lobby of the 6th floor, and usually includes coffee, soda, and water. Make sure you refill your water bottle so you'll have some water during the sessions. They also provide twenty or so computers with free internet access that you can use to quickly check your email or send out a few tweets about how much you're learning (don't want to get your coworkers who stayed home too jealous, right?).

Lunch

After several hours of intense training, you'll be ready for a hearty lunch. Again, your Summit registration includes lunch, so you don't have to get soaked in Seattle's famous rain searching for a bite to eat. Lunch will be offered in the same room as breakfast ("4B") around 11:30. The food is served buffet-style, so you can fill your plate with what you like. They usually have a vegetarian option in addition to something with meat. If you have any special dietary restrictions that makes it difficult for you to eat from a typical American buffet, you may with to contact PASS Headquarters and inquire about the menu to be sure you can eat at least something that's offered.

You may also want to consider attending one of the luncheons instead of the regular Summit lunch. The Women In Technology luncheon will be held on Thursday, and will have several panelists discussing the topic, "Women in Technology: Where Have We Been and Where Are We Going?". The PASS Chapter luncheon (Wednesday) and Birds of a Feather luncheon (Friday) are actually held in the same location as the regular lunches - check the cards displayed on the tables to see who's who. I believe they usually provide a map of the tables in the materials you receive at registration, so you should be able to pick your target table in advance.

Dinner

Since you'll be done for the day (as far as training goes), you may choose to hit a restaurant for dinner, however you really don't have to thanks to our amazing sponsors. There are events each evening which provide some food (and drinks!!).

Tuesday night be sure to not miss the Welcome Reception and Quizbowl (sponsored by SQL Sentry). Not only is the Quizbowl amusing and entertaining, but you'll also have the opportunity to sample various finger-foods. If you over-stuffed yourself earlier at lunch, the amount of food you snag at the Welcome Reception will tide you over until breakfast.

After your final session on Wednesday night, dash on over to the Exhibitor Hall for the Exhibitor Reception. They always provide an amazing variety of foods, so wander around the hall and discover what they're offering both for food / drink as well as products that can make your job easier.

Thursday night, Microsoft has plotted a fantastic evening for everyone - a "Community Appreciation Party" at Seattle’s Experience Music Project (a shuttle to the venue will be available). You should receive some sort of armband in your registration packet that you'll use as an entrance ticket for this party. There will be plenty of food and drinks available for everyone.

Preparing for SQL PASS Summit–Part 2

Since I've attended SQL Pass Summit a couple of times, this year I volunteered to help out with the "Summit First-Timers" program. This program is designed specifically to help people who are attending SQL Pass Summit for the first time to get the most out of their experience at Summit. I've decided to post a series of blog entries here to not only help out my flock of first-timers, but to also ensure that the information is shared with as many other first-time (and alumni) attendees as possible. This is the second posting in the series – Part 1 can be found here. 

Safe(r) Travelling

I’ve heard that as far as cities go, that Seattle (at least the area near the convention center) is safer than many major cities, however as someone who lives out in the country where the most dangerous evening encounters usually involve surprising a skunk or twisting an ankle in a mole’s burrow, any city can be considered a scary place.

• Don’t leave valuables in the hotel room. There’s always the chance that something may “disappear” when the maid cleans the room, or perhaps you may accidentally forget it in the hotel room when heading home and not notice its absence until after it’s too late (those multi-colored bedspreads do an amazing job of making small earrings invisible). It’s better to simply leave those things at home rather than chance loss or theft.
• Don’t tempt the bad guys. In the evenings, you’ll be walking the dark city streets on your way back to your hotel room. You may or may not be able to find a group of others to walk with (my first year, I stayed in a hotel about 10 blocks away and walked back alone each night), so you want to be sure you are not a tempting target for thieves. Expensive, shiny watches or rings are fairly easy for thieves to remove without you noticing. I wear cheap ($10) watch and leave my diamond engagement ring at home.
• Don’t carry a purse. I know many ladies love carrying large purses so they can carry everything that they might possibly need at any time during the day. Leave it at home – if you don’t have a purse, no one can steal it. I carry everything I need in my pockets. Most other items that I carry in my purse are either left at home or are tucked in my backpack along with my notebooks and water bottle.
• Minimize your wallet. Take only what you will actually need – cash, credit card, ID and hotel key. You can leave your store rewards cards, library cards, social security cards, coupons, etc. at home. You won’t need them and they could be lost or stolen, so why bother carrying the extra weight? Besides, without all of those extra items, you probably won’t need to take a wallet.
• Get a money belt. The best piece of “underwear” you’ll ever purchase. It’s basically a wallet you can wear discreetly under your clothing. It is the ideal place to carry your ID, credit card, airline tickets, passport and money. There are several styles available, so research them and pick the one that works best for you. I use one of these instead of my wallet, and it’s thin enough that it is not visible underneath my dresses.
• Pay attention and be alert. Bad guys depend upon surprise for being able to get away with taking your possessions, so when walking the streets, if you are constantly aware of your surroundings and act like you know where you are going and what you are doing, you will more likely notice any potentially threatening people or situations before you get close to them and will easily be able to circumnavigate them. 

 

Luggage

Unless you’re local to the area (in which case you’ll be sleeping at home), you will have to pack some luggage to take all that you will need for the week that SQL PASS Summit will be held.

• Take only carry-on size luggage. The convention is less than one week long, so a small bag should suffice to hold all the clothing you will need for the days you’re away. Besides, if you are flying, you will avoid paying checked baggage fees since most airlines charge for checked baggage. Having lighter luggage will also make it easier for you to walk from the train station to the hotel (saving you Taxi cab fare money), and the lesser burden will prevent you from becoming exhausted lugging your stuff around.
• Leave your laptop at home. It’s unlikely that you will actually need your laptop computer while at SQL PASS Summit. In the break area of the convention center PASS provides some free internet workstations where you can check your email between sessions. Also, most of the hotels have computers you can use for a small fee. If you believe you really would need to have some sort of computer, consider bringing a tablet since it’s smaller and lighter than a typical laptop. For taking notes, I strongly recommend having a college rule lined notebook and a set of pens in various colors.
• Ensure your jacket / raincoat has plenty of pockets. I love pockets – and the more the merrier. It’s amazing how much one can carry in a jacket, especially one full of pockets. I certainly wouldn’t go as far as Eric Le Fou with over stuffing my jacket pockets, but I hope you can understand the potential there. There are many excellent sites that offer travel gear that is designed for holding more than the typical jackets and coats available.
• Bring a small backpack or tote bag. The bag will be handy for holding your notebook, pens, extra water and other items you will need during the day. Also, since you’ll likely collect a bunch of documentation and items from vendors, you will want a place to put them. Please note that in some previous years, PASS provided participants with a backpack or other bag (covered in sponsor logos) at registration. I do not know for certain whether or not they will provide one this year, so be sure to have an extra bag with you.

For additional tips for packing light, visit OneBag.com.

Preparing for SQL Pass Summit

Since I've attended SQL Pass Summit a couple of times, this year I volunteered to help out with the "Summit First-Timers" program. This program is designed specifically to help people who are attending SQL Pass Summit for the first time to get the most out of their experience at Summit. I've decided to post a series of blog entries here to not only help out my flock of first-timers, but to also ensure that the information is shared with as many other first-time (and alumni) attendees as possible.

Planning the trip

• Register! I know, this sounds like a no-brainer, but there are still some who will register at the last minute. Obviously, the earlier one registers, the lower the total cost for the conference. If you haven't registered yet, please send in your registration now.
• Reserve your hotel room. Because of its proximity to the Washington State Convention Center, the Sheraton Seattle Hotel & Towers is the most popular choice, followed quickly by the Westin Seattle. The Official Seattle.gov site has this PDF listing downtown Seattle hotels. There are a lot of hotels within walking distance, so choose your favorite.

Getting there

OK, you've got your Registration in, and your hotel room is reserved. Now what?
Figure out how you want to get here:

• Drive - If you live within 200 miles of Seattle, you may consider driving. Things to keep in mind:
• Cost of Parking - All hotels charge for parking, and so does the Convention Center. Last I heard, the cost was about $22 / day. Don't count on finding on-street parking - and even if you do find a spot, the meters are the least economical way to pay to park.  
• Seattle Traffic - Take the "excitement" of Boston driving, stir in the hills of San Francisco, add the insanity of Mario Kart racing, and sprinkle in the density of LA Traffic and you've got Seattle driving. Personally, when it comes to driving in Seattle, I avoid it at all costs.
• Amtrak - If you live within 500 miles of Seattle, you may consider taking Amtrak
• Very relaxing way to travel.
• The trains offer food, but you may wish to bring your own because the line for the dining car is often very long, and they may or may not have foods you like. The dining car has some wines / beers available for purchase as well.
• The King Street train station in Seattle is a good brisk walk from the Convention Center. It's all uphill from train station to hotels / Convention Center, so be prepared for some steep climbing. Thankfully, this means the walk back is far easier. I believe it takes about 30-45 minutes to cover the distance depending upon your walking speed and whether or not you get lost or stumble upon a really cool shop that distracts you for a while.
• For those who don't feel like walking all the way to the Convention Center, there are buses and taxis. To find the bus from the King Street train station, take the overpass over the train tracks, and walk a few blocks to the east to the International District Chinatown Stration, and catch a downtown bus up the hill.
• Other Trains - Those who are moderately local to Seattle can take metro transit, Seattle Monorail, a bus or light rail. I'm sure you can figure it out if you're a local.
• Fly - If you live far enough away this is likely your best option.
• Fly to Seattle-Tacoma International Airport
• Take Light Link Rail from the airport into downtown Seattle.
• If you rent a car, refer to the section on driving.

Packing

The most import part of packing for any trip is to ensure you're prepared for the local weather. You should check the 10-day forecast online a few days before you leave to have an idea of what to expect. This is Seattle in November, so plan to dress in layers - you'll be inside the Convention Center most of the day, but some rooms will be too warm and others quite cold - and you will also walk outside a little to get to your hotel, have dinner or go out for karaoke. Expect temperatures to range between 40F and 70F, and expect rain - lots of it. Seattle's rain varies from all-out downpour to a light mist that curls your hair and makes everything feel damp. You'll see both. Or maybe sunshine. It depends.

Here are a few key packing tips:

• Footwear - wear shoes that make your feet happy. If you can't walk 5 miles in them without getting blisters, you will be miserable. The Convention Center is huge, and you may have to walk a very long distance to go from one session to the next. I always pack at least two pairs of shoes and alternate between them to ensure that my feet do not have pressure in the same spots every day. Also, thanks to the Seattle rain, you may slog through a puddle that thoroughly soaks your shoes and you will be greatful that you have another pair to change into.
• Outerwear -  I bring a hooded raincoat (Saf-T-Pockets "Portlandia") made from 2.5 layer waterproof breathable fabric. It's light and thin enough to tuck into my backpack when I don't need it, has reflective trim so cars can see me walking at night, and is long enough to keep me dry in the famous Seattle rain. I prefer the hooded raincoat over an umbrella because Seattle weather frequently features high winds which will instantly mangle even the stoutest umbrella. Also, the raincoat doubles as a windbreaker / layer of warmth for when it's not raining.
• Warm layer - Since the raincoat doesn't provide a lot of insulation against real cold, I've found that wearing a hooded sweatshirt as a middle layer provides sufficient warmth for me to handle whatever Seattle throws at me. A suit coat, cardigan, or other light sweater would serve the same purpose if hoodies aren't your thing.
• Regular clothes - This varies depending upon what kinds of fashions you prefer. Pack enough to cover you each day. I've seen
• Jeans and t-shirt (these range from silly messages to corporate logos)
• Khaki pants and polo shirt (often with corporate logo, but not necessarily)
• Business suit
• Shirt & tie with nice pants
• Dresses
• Blouse and skirt
• T-shirt and skirt
• Hawaiian Shirt and shorts

One other item to consider packing : a kilt. Thursday, the day that the Women and Technology (WIT) luncheon is held, many attendees - both male and female - wear kilts. Some of them wear traditional tartans, while others have Sport Kilts or Utili Kilts. Watch for the #SQLKilt hashtag on twitter for additional information and commentary about kilt-wearing at SQL Pass Summit.

SQL Editor “Presentation View”

I’ve often been frustrated by the lack of real estate in the standard SQL query window. Once the query has been run, the results eat up half of the screen, and if you have multiple grid view results from multiple queries in a batch, it can be difficult to examine all of the data without fighting with the UI to let you move the dividing line up.
Here’s what you often see:

I noticed during an online training session with Paul Randal (blog | twitter) that he was using a set up for his SQL query window that to me was more practical – it had the editor in one tab and the messages / other tabs next to the editor rather than below it.
Like this:

It took me a bit of searching and testing, but I finally located the setting and decided to document it here so I wouldn’t forget where it was again.
From the main menu, click on Tools then select Options…. Once the Options dialog pops up, expand Query Results > SQL Server > Results To Grid. In the list of settings, check both “Display results in a separate tab” and “Switch to results tab after the query executes”. Then click [OK].

SQL will save your settings and next query window you open will use the new view.
In addition, in the Options dialog, you may wish to change the font size. That setting is under Environment – Fonts and Colors. I’d only have it large for presentations where I have enough long queries that I need to display a large section of the window. For ones with smaller queries, I’d use “Zoom-it” because I can zoom in on the text I’m talking about and then zoom back out when finished. Zoom-it also lets you annotate the zoomed in window with drawn lines or additional text in various colors. The annotations disappear when you zoom back out, but they’re handy for pointing out key information.
Happy presenting!

SSIS - Speeding Up Package Load Time

Your Package Will Load...Someday 

While working with SSIS, I was often frustrated by how long the package takes to load for editing. Frequently, I was simply interested in opening the SSIS packages to see what they did, but really had no need to actually edit them. Sadly, if you try to open an SSIS package on a machine that either does not contain all of the databases referenced in the package or that cannot connect to those databases, you often have to wait an incredible length of time for the package to open before you even have a chance to change the connections to something valid.

I discovered a small trick which cures that dilemma - open the SSIS package in a text editor (the package content is actually XML), make some minor edits, save, then finally open the SSIS package in BIDS.

Delaying Package Validation Until Later

 Open your package in a text editor and search for all instances of

 "DelayValidation">0</DTS:  

Replace those with

"DelayValidation">1</DTS:

This simple change will prevent BIDS from attempting to connect to and validate every object before allowing you to view the content of the SSIS package. It will still attempt to validate an object when you click on that object to edit it, but at least you won't have to wait for it to time out on its attempts to validate every item.

Correct Connection Strings

Open your package in a text editor and search for all instances of

<DTS:Property DTS:Name="ConnectionString">

Check the value for Data Source and Initial Catalog - are these correct for the environment you're working in? Many times, the package may have been created with connection strings specific to the server they were built on and either the server no longer exists or is no longer accessible. By correcting the connection string information prior to opening the package in BIDS, you can speed up the package load time and avoid the flood of validation errors that go along with incorrect connection information.*

=======
* Yes, I know there are some SSIS tricks for handling different connection strings, such as using a configuration file to hold the connection information, however many SSIS packages that people inherit do not have configuration files for the connection information.

Adventures in SSIS: Data Migration

After escaping from technical support to the “greener fields” of Product Engineering, I thought that I would finally be in my element and have completely stress-free workdays.
So untrue.
So painfully untrue.
The new position offered enough stress to make event the most stable person ready to fling primary colored cartoon-like stuffed birds at an equally cartoon-like stuffed pig (who, for some unexplained reason, is lime green rather than pink, and seems to be just a head). Not that I do that frequently, but these poor birds have seen better days, as their ruffled feathers can attest. I suppose I could try to take my stress out on other things, but HR gets a wee bit upset if I throw chairs or coworkers (drat).Fortunately, they don’t seem to mind the occasional Angry Bird™ flying through the cube farm, however, so I fling away.
What is the current stressor?
SSIS.
I must admit that up until now I haven’t touched SSIS at all. I have plenty of experience working with DTS packages, but none with SSIS.
Until now.
There is no easy DTS to SSIS knowledge conversion. They’re completely different animals. They may do similar things, but nowhere near in the same manner. Books Online offers some information, but if you don’t already know SSIS the documentation might as well be in Klingon since it assumes you fully understand everything and are just using the books online for a quick definition check.
Great. And it’s due by the end of February. Which, by the way was yesterday.
Thank GOD it’s a leap year, otherwise I would have had to ensure I learned invisibility instantly.
Some of the sections of the package I was able to copy from other migration packages I found in our Source Control (it’s Tortoise Subversion, BTW), but the rest had to be created by me, and quickly.
Some things I could figure out: get data from Source, compare to Destination using LookUp, then Insert New unmatched data into Destination. Works great unless you have some wiseguy who customizes the data and causes a primary key violation between Source and Destination. Simply inserting a new row without keeping the key value didn’t work because the Destination table does not have identity insert (aka “autoNumber”) on the key. I searched everywhere for ideas—checking  “SSIS Upsert” and equivalents in Bing and Goolgle—to see if anyone had written code that could help me. Most articles assumed that the destination had identity insert turned on.
Thankfully, I had the #SQLHelp hastag on Twitter and received responses from some awesome people* who were able to provide me the hints / guidance that I needed to plow through this disaster waiting to happen. The saving tweet (thanks @EricWisdahl) contained a link to an extremely helpful article by Phil Brammer called, “Generating Surrogate Keys”. It was exactly what I needed. 
Well, mostly….
SSIS is extremely fussy about certain things—C# coding is case sensitive; datatypes must match exactly; math on certain numbers mysteriously changes the datatype. Things like that.
The issue I had was that the Source table use int for its KeyID, and the Destination table used tinyInt. Most searches for an equivalent in SSIs won’t tell you the full story… but I finally found it.
First, several sources tell you that

tinyInt is equivalent to DT_UI1 (single byte un-signed integer),

but if you check the list of datatypes for variables, you’ll note that it’s missing (or if not missing, then named something that is NOT intuitively equal. What does DT_UI1 mean anyway?).
After many false starts (including learning that the C# scripting is painfully case sensitive), I discovered that

the Byte data type works for tinyInt Variables.

The other thing I found was that in SSIS scripts, when you add something to a value, it automatically forces a conversion to int datatype—and it’s very difficult to undo that. What I did was use Eric’s script unchanged, except to the final statement

Row.ClientKey = NextKey;

I added a conversion to byte:

Row.ClientKey = (byte)NextKey;

and it worked.

Now stress is greatly reduced and my Angry Birds and Green Pig are happily noshing on my cereal.



* HUGS! to @onpnt and @EricWisdahl for being “first responders”

January #Meme15 Assignment

<*Cough* *Cough*> It’s awfully dusty here, isn’t it? Yeah, I’ve been neglecting my blog. Terribly sorry about that…. Well, nothing like trying to get things going again at the start of a new year. Hopefully this year I’ll be more consistent about posting (and not quit half-way through the year)

#Meme15 Assignment #2

The #Meme15 is a meme started by a group of people in the #SQLFamily who wanted to discuss how they use Social Networks to enhance their careers and professional development.
The assignment for this month was posted on Jason Strate’s blog – talk about Twitter, answering “Why should average Jane or Joe professional consider using Twitter?” and “What benefit have you seen in your career because of Twitter?“
Let’s get started.
 

Why should average Jane or Joe professional consider using Twitter?

That’s exactly what I wondered when I first heard about Twitter – why bother slogging through countless random postings about useless things written by strangers who have too much spare time on their hands? I really don’t need to know that you’re taking your goat for a walk or that you ate sushi last night. Besides, I likely already saw your post on Facebook, Linked In and Google+ on exactly the same thing. Sounds like a major time waster, right?
If that’s all there was to it, then it would probably have gone the way of the 8-track tape within a few months. But thankfully, following people on Twitter can offer far greater benefits, as I discovered at the 2009 SQL PASS Summit conference. About halfway though the first day, I found out that the majority of the SQL people I really admired were all using Twitter as their main means of keeping in touch with other SQL professionals. And they weren’t tweeting useless stuff – they were posting announcements of new blog posts, links to articles about SQL, free online training, and other SQL-related items.
For SQL server professionals, Twitter definitely has benefits – just follow all of the awesome SQL gurus and the #SQLHelp and #SQLPass hashtags. For other professionals, it may or may not be helpful – it all depends upon whether other professions have a significant number of people tweeting about their profession.

What benefit have you seen in your career because of Twitter?

I’ve used the #SQLHelp hashtag several times to ask SQL-related questions and have received answers so quickly from SQL experts that it felt like they were right there with me helping me along.
From Twitter, I’ve also been able to find out about free online webinars and more SQL articles and blogs than what I have time to read in a day. Without Twitter, it would likely have taken me far longer to find the same information – or I would’ve completely missed seeing the information at all.
Finally, the most important benefit of chatting on Twitter with all of these SQL professionals is that when I attend a SQL conference these people actually know me by name – which has made networking so much easier.

Are Your Database Statistics Fresh?

SQL maintains a vast amount of data – or statistics – about the content of each object in a database. The statistics can become stale if they have not been updated very often, or if a large number of changes have occurred within the database. As the statistics become less useful, the time for running queries can increase dramatically.

In Production systems, statistics should be updated during the usual maintenance window to ensure that the metadata is fresh.

To see how fresh the statistics are for one object, run:

DBCC SHOW_STATISTICS ( 'CCAuditSessionType' ,CCAuditSessionType_PK)

If you need to see the statistics for all databases, run this instead:

select STATS_DATE(o.object_id,s.stats_id) as StatsDate,o.name as TableName, s.name as StatsName, auto_created, user_created, no_recompute

from sys.stats s

join sys.objects o on s.object_id=o.object_id

where o.type='U'

To update statistics that are out of date, execute the command

exec sp_updatestats

on each database on the server that needs to have its statistics updated.

Use the following query to generate a script to update the statistics on all databases

declare @db varchar(30)

, @dbID int

, @sql varchar(max)

create table #t

(DbName varchar(30), databaseID int)

Insert #t (DbName, databaseID)

select [name], database_id

from sys.databases

where database_id > 4

Select @dbID = MIN(databaseID)

from #t

While @dbID is not NULL

BEGIN

   select @db=DbName

     from #t

    where databaseID=@dbID

   set @sql = 'Use [' + @db + ']' + CHAR(13) + 'go ' + CHAR(13)

   set @sql = @sql + 'exec sp_updatestats' + CHAR(13) + 'go '

   PRINT @sql

   Select @dbID=min(databaseID)

     from #t

    where databaseID>@dbID

END

drop table #t

Copy and paste the printed output from your result set into the query portion of a SQL Server Agent job and this will ensure that the statistics are updated for all databases on a regular schedule. NOTE: the query above excludes the system databases.

Adventures with Denali CTP3–Part 1

I usually only realize how slow downloads can be when I’m eager to begin working with the item being downloaded. The hour it took to download the AdventureWorks sample databases felt far longer than it actually was.
One thing that surprised me was that the downloads for the databases were only the MDF (data) file – the log file was not included. After fiddling unsuccessfully with attaching it using the UI in Management Studio (no, I didn’t think of deleting the log file name from the file list in the UI – I’d assumed it was required and didn’t realize that if you did not list a logfile that it automatically treated it as an ATTACH_REBUILD_LOG command), I finally decided that it would be sensible to actually read the instructions. Technet provided me a very simple query to attach the database
CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG ;
Worked like a charm.
I modified the query and attached the AdventureWorksDWDenali database in a similar manner then ran a few quick SELECT queries on various tables to see what they contained. I was pleasantly surprised to see that the OLAP database’s dimDate table contained English, French and Spanish day and month names.
I then launched the BI Development Studio and opened the AdventureWorksDWMultidimensionalDenali project (provided in samples pages on Codeplex). I verified the connection information in the datasource and successfully deployed the cube.
If everyone knew how easy this was, I’d probably be out of a job.

Query to Pull Database File Information

This query will list the name, size and location of all files for all databases. This is handy for checking and documenting database server configuration to confirm whether the server follows our recommended best practices.

set nocount on

declare @sql varchar(max), @sql2 varchar(max), @name varchar(100)

if object_id('tempdb..#t') is not null drop table #t

create table #t (DbName varchar(30), LogicalName varchar(30), FileName varchar(100), sizeMB int, UsedSpaceMB int, growthMB int, is_percent_growth bit)

set @sql = ' substring(name,1,30) as LogicalName,substring(physical_name,1,75) as FileName,'

set @sql = @sql + 'size * 8192./1024/1024 as SizeMB,sum(a.total_pages * 8192./1024/1024 ) as UsedSpaceMB, '

set @sql = @sql + 'growth * 8192./1024/1024 as growthMB, is_percent_growth from '

declare c cursor for

select name from sys.databases where database_id > 4

open c

fetch next from c into @name

while @@fetch_status=0begin

set @sql2 = @sql + @name + '.sys.database_files df left join '

set @sql2 = @sql2 + @name + '.sys.allocation_units a on df.data_space_id=a.data_space_id left join '

set @sql2 = @sql2 + @name + '.sys.partitions p on p.partition_id = a.container_id '

set @sql2 = @sql2 + 'group by df.name, df.physical_name, growth, is_percent_growth, df.size'

begin try exec ('insert #t select ''' + @name + ''' as DbName, ' + @sql2 ) end try begin catch end catch

fetch next from c into @name

end

close c

deallocate c

select * from #t

I’m sure that there are other ways to pull this data, however, in some environments your permissions may restrict you from using any method other than this to pull the data.

SQL Recovery Mode Adjustment

Often when new Development boxes are handed over to my group, the databases are set to Recovery Mode = FULL because the setting match production recovery modes. Unfortunately, since the Dev boxes rarely have any backups running, eventually the transaction logs fill up the drive. When that happens, the databases can no longer accept new transactions and we are left with a (temporarily) non-functional box.


Run this script on a Dev box to set the recovery to SIMPLE for all databases to avoid the above scenario. It works on SQL 2000, 2005 and 2008 SQL servers.


NOTE: it is recommended that PRODUCTION servers use FULL recovery mode rather than SIMPLE.


use master

go


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE DatabaseCursor CURSOR FOR


SELECT name FROM master.dbo.sysdatabases
where Name not in ('tempdb') -- cannot set recovery for Tempdb.
ORDER BY 1


OPEN DatabaseCursor


FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN


     set @cmd = 'ALTER DATABASE ' + @database +'
     SET RECOVERY SIMPLE'


     EXECUTE sp_executesql @statement=@cmd


     FETCH NEXT FROM DatabaseCursor INTO @Database


END


CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


For your homework, you can substitute a WHILE loop for the cursor.  
If your environment is running mostly SQL 2008 (or higher), please check out SQLChicken's article on setting up Policy Based management to handle ensuring that the dev boxes are all set to Simple Recovery Mode.

Meme Monday: I Got 99 SQL Problems And the Disk Ain’t One

This month, Thomas Larock (Website| Twitter ) declared a meme Monday inspired by the Hugo song 99 Problems - aside from disk issues, name 9 problems you frequently see in your shop which are not related to disk issues.

1) Using default install settings for file growth
Despite numerous examples from live systems showing that those settings are not appropriate for our product's databases, we frequently see new customers with all their databases set to the default 10% growth setting, despite the statement in the "best practices" documentation that tells them otherwise.

2) Bloated SQL error logs
Many times when customers report having issues and we're called in to examine what's happening with their SQL server, we find that we can't open the SQL Error Logs because the customer's SQL server hasn't been restarted in a long time and the errorlog is so bloated that it's too big for the UI to open in a timely manner. The simple fix, of course, is to set up a SQL job that runs sp_cycle_errorlog periodically.

3) Not doing ANY index maintenance
Frequently, when I hear about SQL performance issues, I find that the customer has turned off the regular index maintenance jobs "because they take too long". Eventually, this results in painfully out of date statistics, severely fragmented indices and terrible performance.

4) Shrinking databases as "maintenance" to "free up disk space"
I try my best not to use profanity or to scream (loudly) when I see this enabled on customer servers. I just take a deep breath and forward the following links to the guilty party:
Paul Randal: Why You Should Not Shrink your Data Files
Brent Ozar: Stop Shrinking Your Database Files. Seriously. Now.

5) Developers "testing" code on production
Don't get me started....

6) Poor backup plans not even close to SLA requirements
High volume OLTP Production database, full recovery with log backup once a day at midnight and full backup once a day at 1AM - but their SLAs say they have to completely recover from failure within one hour. They claim that because the SQL server is clustered, that they don't have to back up the databases more often. Really? Please don't call me when things go south.

7) No disaster recovery plan
... And office in the middle of Tornado alley. Again, please don't call me to resurrect your SQL server when your data center gets destroyed by one of the 500+ tornadoes that went through town. You don't have a copy elsewhere and I can't create something from nothing.

8) Letting idiots have access to the Server room
Believe me: I can't make this one up - it actually DID happen.

A particular person on the night cleaning crew entered a server room to vacuum it. Because the vacuum's cord was too short to allow him to vacuum the far side of the server room, he unplugged something to free up an outlet so he could vacuum the far corner of the server room. The "something" he unplugged was the main power for the SQL server, which knocked out the customer's website until someone entered the server room in the morning and noticed that the server was unplugged.

9) Not having automated monitoring on servers
You'd think this was obvious, but I've been called too many times to count late at night to hear that someone's server is "down", only to find out the reason the SQL server crashed or the backups failed was because the disk was full. Automated disk monitoring systems have been around for over a decade, yet many of our customers don't have any automated monitoring and I doubt that their IT people check the servers every day since they seem so surprised to discover that their disk has filled up completely.

After just thinking about those 9 items, it's time for a stress pill.

SQLRally - Birds of a Feather

Earlier today, Jorge Segarra (twitter) sent via twitter a request for volunteers to head up Birds of a Feather tables at SQL Rally. I asked him if he had someone to head up a PASS WIT table.

He replied, "@LadyRuna if you're volunteering, you could host it :-D #sqlWIT #sqlRally"

So it looks like I'm hosting the WIT table at the Birds of a Feather event at SQL Rally. Please stop by and say hello if you're there. I've never run one of these before and I'll be making things up as I go along.

Hooray for Wonderful Husbands

I was talking to my boss yesterday about my professional development plan for this year, and the subject of training came up. He said we only had enough money in the budget to send one person to a conference this year, and since I’d attended SQL PASS Summit last year, they were going to send my coworker this time – meaning I wasn’t eligible to attend any offsite training at all. He suggested I find some books to read to fulfill my professional development plan.

Needless to say, I was not at all thrilled, and mentioned it to my darling husband.

He, too, was unimpressed. He knew how important and useful attending SQL PASS Summit had been for me.

Over dinner, he casually asked me, “Do you think you can take time off from work the second week of May?”

I checked my calendar – I was not scheduled to be on call, so I could take time off.

I replied, “Sure. Why?”

He held my hand and said, “There’s this PASS SQLRally thing in Orlando that week. We have enough vacation money saved up to send you.”

(Queue romantic music and sappy romantic scene….)

Ok, get your mind out of the gutter… The final result is: I’m going to PASS SQLRally and even get to attend one of the Pre-con classes. Hooray for wonderful, amazingly loving husbands! I certainly picked the BEST husband in the world. See you at PASS SQLRally!