Creating and Building Indexes

Just a few Index 101 type points of advice.

When creating an index on an existing table, that has populated data, keep in mind that it could take a long time.  If it takes too long and is initiated via the SQL Management Studio the result is very likely to be a timeout.  Ideally when creating an index via the SQL Management Studio it is good to create the index before data population.

Otherwise the best way to create an index is either via T-SQL directly or create the index and maintenance of the index via a SQL Server Job.

To setup an index via SQL Management Studio follow these quick steps.

First right click and select Indexes/Keys… and click.  A dialog will pop up.

In the dialog, first click add (#1), then name the new index appropriately (#2), and then select the actual column or columns that you want the index placed on (#3).  The results should be similar to the image below.  Once everything is set click on close.

Next click on save (circled below) and the index will be created.  Remember, if there is anything in the table there is a good change at a time out.

To do it the truly über easy way, execute the following T-SQL command.

CREATE NONCLUSTERED INDEX IX_analysis_time ON dbo.profile_stats (analysis_time)

As mentioned, the UI times out trying to build this index for me because of the rows in the table.  This table had a mere 283204 rows and took 4:40.  So you can imagine with more data how much longer it might take.  A small 10-30 second time out for the UI is good considering it locks the interface up while it is trying to create the index.  For more information on the command check out the BOL entry on MSDN for CREATE INDEX.  Also it is good to check out the general index design guidelines.  Also check out my more exhaustive best practices entry for indexes.

Keep in mind when creating a index rebuild or index reorganization job that a period of time should be taken to observe the baseline behavior of a table.  Then setup a maintenance task to run on a scheduled basis of when you observe fragmentation and other things starting to occur.  For more information on reorganizing and the specific steps and commands for fragmentation and such, read the MSDN article.

To setup a job follow these steps.

Open up SQL Server Management Studio.

Expand the SQL Server Agent.  Select the Jobs Folder and right click, on the menu select New Job….

The New Job Dialog pops up.  Fill out each of the General Section fields with pertinent information.

Next select the Steps Section.

Another sub dialog appears, add the pertinent data to this field and add whatever the maintenance command is that you wish to execute on a regular basis.

After that, click on OK and then move on to Schedules.  Again click on the New… Button to bring up another sub dialog.

A New Job Schedule sub dialog appears.  Again, you know what to do.  Add pertinent information.

Next add any Alerts… same process.

You can even setup a specific response for the alerts.

Next setup any operators or new operators that you want to receive the alert.

Select the method that will receive the error alert.

Click ok until you’re back out to the main screen.  At this point you should see the job item within the job folder.  It will run at the scheduled time or just right click on it and select the run option that way.

…and that’s it.  Build, run,  and maintain those indexes!

Building a Specific .NET Framework Version :: Tip o' The Day

I was working through changing a web project from building to .NET 3.5 to just .NET 3.0.  It was taking a little effort as I removed the .NET 3.5 features.  During that time though I had a problem with System.Web and I realized a cool feature that Visual Studio 2008 has built in.

When one selects a pervious build version, and you try to add a reference, the references that aren’t available anymore are grayed out in the Add Reference dialog.

Visual Studio 2008 Virtual Host Rabbit Multiplier

I took a ride on the MAX today, just to do some thinking and get away from downtown for a little while.  On the way back from the west side I’m sitting and just typing away at some code.  I’m not hitting F5 to run or anything, just merely typing some code and then saving, then viewing the web page.  The web page was appearing just fine.  But on the background I noticed that the virtual hosts where multiplying like rabbits!

Has anyone ever seen this?  What in the world is Visual Studio doing?  Is it just launching a new one of these every time I force a build by saving and viewing the URI?

I closed down Visual Studio 2008 but I’m watching to see if this repeats itself.

Good Coder/Engineer/Programmer

A great list was put up at inter-sections.net on how to recognise a good programmer.  Check it out, it is definitely a good read.

One of the comments (by Ashley Moran) struck me as very true also, “Also, something that has stuck with me long after I forgot where I read it (possibly the Jargon File?) is that you should look out for developers with highly-developed outside interests. Generally, the ones who excel at or show extreme dedication to their hobbies are better hackers than those that don’t.”

I would agree 100% (maybe because I have highly developed hobbies?) that people who develop their outside hobbies give them much more perspective in which to reference and resource the work they do in development each day.

Technorati Tags: Jargon,Good Programmer

del.icio.us Tags: Jargon,Good Programmer

Human Task Switching is Horrible for ROI

Some people get it, some companies do, some other don’t, and it really goes to show on the company’s bottom line.  One can literally make major decisions about a company based upon what they expect from their employees in the way of task switching.  There are of course levels, but I’ll lay some descriptions out real quick for more context on what exactly I’m talking about.

Task-switching is when an employee literally stops one task to begin working on another task.  Rule out multi-tasking, that is a myth to begin with.  Task-switching is a part of everyone’s daily work.  The impact of task switching is directly related to the number of tasks.  I’ve created a quick little spreadsheet based on some of my research to show how exponential multiple tasks decrease worker productivity.  It is truly scary how fast someone can start producing nothing by trying to or being forced to attempt to produce too much!

So here is a quick “spreadsheet” run down of how quickly tasks will fall apart with the addition of more tasks.  With 7 tasks equating to the loss of diminishing returns and the fall into negative productivity.  Some of this loss, albeit not much, can be stemmed with effective and proactive management.  Basically take a percentage, of the percentage of time task switching, and effective management can remove that workload to decrease the task switching impact.

Other References for this Topic

http://weblogs.asp.net/erobillard/archive/2005/07/20/420022.aspx

http://www.joelonsoftware.com/articles/fog0000000022.html

Heavy Duty Scientific References

http://citeseer.ist.psu.edu/654767.html

http://www.apa.org/releases/multitasking.html

http://www.psychologymatters.org/multitask0306.html

http://www.ncbi.nlm.nih.gov/sites/entrez?cmd=Retrieve&db=pubmed&list_uids=11976394

A No-no of Task Switching Items

  1. More than one “status report” a week.  Each additional status report added is a large % increase to the task switching effort.  Often times a written status report done a daily basis will have the reverse effect if done in a SCRUM type fashion.  But if the status report is too excessively detailed, even a weekly written status report, it often leads to de-motivation and a significant decrease in productivity.
  2. More than 15 minutes in a meeting.  As minutes are added, the return to work is exponentially more time consuming.
  3. No more than 2-3 meetings in a day.  Each one will increase the task switching time for returning to task efforts.
  4. Don’t make administrative staff do things they aren’t trained to and don’t turn developers and highly paid professionals into administrative staff.  Admin staff are for paperwork, developers are for developing, DBAs are for database administration, don’t get the titles into a spaghetti mess or the chart above becomes an extremely optimistic estimate in productivity loss.
  5. Management should also work diligently to keep the aforementioned “creative professional” interested in what they’re doing.  Work with them in this factor.  If you don’t, forget the 7 task maximum, think more around the 2 task is a red line limit.  They will get bored, they will lose productivity, and they’ll eventually leave or get fired.

For more elaboration on this topic definitely check out the two first blog entries I mentioned above from Joel on Software and Ero Billard.