Had a great time in Baton Rouge. The event was well attended and had an awesome lineup of speakers. I am putting it on the calendar for next year!

You can download my slide deck at http://sqlserverio.com/my-presentations/ and thanks to everyone that came to my session.

I’ll have a more detailed posting later this week so stay tuned.

Ok, now that I have your attention this really should be titled the danger of not qualifying all objects, but that isn’t quite as sensational enough to make you click through to here

Imagine if you will, a developer expects ten records to be inserted into a table and the query has been running for almost an hour.  A quick glance and sp_who2 shows that it is using a lot of IO, I mean a lot. The first thing that happens is a command decision from the boss to kill the query. I took a quick peek and found the plan still in the plan cache while we waited for this thing to roll back. As I look at the plan I see something really wrong. It looks like the table receiving the inserts isn’t getting the ten records we thought but 169 million records, every record from the table in the select clause.

I look at the query and on the surface everything looks good. When I hit the check mark it compiles without error. Looking at the plan in detail I notice a little icon that tipped me off on where to look next.

Oh that’s bad. Warnings: No join predicate. Ouch. But this is a sub-query, how can I have no join predicate? Lets take a look at an example.

DROP TABLE #t1
DROP TABLE #t2
GO
CREATE TABLE #t1 (
  t1id    INT   NOT NULL,
  t1name  VARCHAR(50)   NULL,
  t1place VARCHAR(50)   NULL)

ALTER TABLE #t1
 ADD   PRIMARY KEY ( t1id )

CREATE TABLE #t2 (
  t2id    INT   NOT NULL,
  t1name  VARCHAR(50)   NULL,
  t1place VARCHAR(50)   NULL)

ALTER TABLE #t2
 ADD   PRIMARY KEY ( t2id )

INSERT INTO #t1
SELECT
  spid,
  loginame,
  hostname
FROM
  MASTER.dbo.sysprocesses
WHERE  hostname <> ''

INSERT INTO #t2
SELECT
  spid,
  loginame,
  hostname
FROM
  MASTER.dbo.sysprocesses
WHERE  hostname <> ''

This gives us two tables with some data. We want to find all the records in #t1 that also exist in #t2 but instead of a join we will use an IN and a sub-query.

SELECT
  t1name,
  t1place
FROM
  #t1
WHERE  t1id IN (SELECT
                 t1id
                FROM
                 #t2)

Looks simple enough and will pass the compile test all day long even though t1id doesn’t exist in #t2. Since you can use columns from the top level query in the sub-query this is a perfectly valid piece of T-SQL.

It gives us this plan when we look at it though.

And there you have it, a join without an ON clause as far as the optimizer is concerned. By not qualifying all the columns in the sub-query we opened ourselves up to this error. This isn’t the optimizers fault! If we re-write this as it was mean to be:

SELECT
  t1name,
  t1place
FROM
  #t1
WHERE  t1id IN (SELECT
                 t2id
                FROM
                 #t2)

We get a plan that is more to our liking.

You could also re-write this as a join instead of using the IN clause and would have avoided this problem as well. Scary way to learn that SQL Server will do exactly what you tell it to even if you are wrong!

And it is well worth the upgrade. I recently had the opportunity to interview David Flynn CEO of Fusion-IO and that will be coming up soon. I have been beta testing the 2.0 driver for quite some time and have been very happy with the performance and reduction in required system memory (by half!). The 2.1 driver is the official release of the 2.x series and has some gains even over the 2.0 drive I’ve been testing. I always to a little test run with HD Tach before diving into my other more detailed tools and right off the top the 2.1 driver is faster yet again than the 1.27 and the 2.0 driver. The blue is the 2.0 the red is the 2.1. I don’t know about you but getting a performance bump from a firmware and driver upgrade is always a good thing!

Many years ago, in the dark ages of DTS I created a little app that would take two parameters and build a UDL file. This an some crafty VBScript allowed me to loop through a list of servers on the fly. I haven’t thought about this code in almost ten years when I came across John Paul Cooks’ blog post on using UDL files. I thought I’d just post up the code, it is basic but got the job done! I did clean it up a bit since it was written when .net 1.0 was all the rage.  The secret sauce is writing the file encoded Unicode and doing binary writes to get all the little bits in the correct palace. the UDL file format is picky that way. Enjoy!

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
namespace CreateUDL
{
 class Program
 {
 static void Main(string[] args)
 {
 string str2 = "";
 string str3 = "";
 try
 {
 str2 = args[0];
 str3 = args[1];
 }
 catch
 {
 if (args.Length < 2)
 {
 Console.WriteLine("Not enough arguments!");
 return;
 }
 }
 str2 = str2 + str3 + ".udl";
 if (str2.Length == 0)
 {
 Console.WriteLine("must provide file name");
 return;
 }
 if (str3.Length == 0)
 {
 Console.WriteLine("must provide server name");
 return;
 }
 else
 {
 try
 {
 FileStream output = new FileStream(str2, FileMode.OpenOrCreate);
 BinaryWriter writer = new BinaryWriter(output);
 writer.Write((short)(-257));
 string s = "[oledb]\r\n";
 byte[] bytes = new byte[(s.Length * 2) + 1];
 bytes = Encoding.Unicode.GetBytes(s);
 writer.Write(bytes);
 s = "; Everything after this line is an OLE DB initstring\r\n";
 bytes = new byte[(s.Length * 2) + 1];
 bytes = Encoding.Unicode.GetBytes(s);
 writer.Write(bytes);
 s = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"+
 "Persist Security Info=False;"+
 "Initial Catalog=master;Data Source=" + str3 + "\r\n";
 bytes = new byte[(s.Length * 2) + 1];
 bytes = Encoding.Unicode.GetBytes(s);
 writer.Write(bytes);
 writer.Flush();
 writer.Close();
 writer = null;
 output = null;
 }
 catch(Exception e)
 {
 Console.WriteLine(e.Message);
 }
 }
 }
 }
}

April of 2009 I decided it was time to blog about my SQL Server experiences in earnest. Steve Jones (twitter|blog) over at SQL Server Central was very supportive and gave me some space there. I’ve been playing with diffrent blogging platforms over the last six months and have settled on wordpress. I’m also taking some advice from Brent Ozar (twitter|blog) and syndicating it out to the world. Again, Steve has been nice enough to just feed my new blog into my old blog space on SSC so there won’t be any loss of old articles or comments on them. With that said you can visit me at http://www.sqlserverio.com I plan to grow it to be more than a blog but who knows how that will turn out!

In my last two posts I talked specifically about the process. This round I’m going to discuss the tools of the trade. Note: These are my views and opinions and not that of PASS in any way shape or form.

This was my first year on the program committee. From talking to other people on the committee, there have been several tools and methods used in the past to do the work of selecting abstracts. I can’t speak to the previous tools just to this years. And it will change again next year. PASS is a dynamic, volunteer, organization things can change pretty quickly when they need to. I also have to say that Elena Sebastiano and Jeremiah Peschka were both very helpful and responsive to questions. Lance Harra was also on the Professional Development track team and was easy to work with. This isn’t Lance’s first time doing this and for that I was grateful. He helped me stay focused and really helped guide the selection process. In all, the final selection process was pretty smooth in that regard.

The Tool
At first blush the web based tool we were to use seemed pretty simple. Jeremiah did a training session and I felt like I could use it without much fuss. The tool is integrated into the main PASS website, which is based on DotNetNuke. Since they have to work inside the DNN framework there are some limitations. It has a limited amount of space to display a lot of information. In the inner panel you could have to scroll down and to the right.

The main page has all the sessions listed, but on multiple pages. I actually missed this my first night going through the first page of submissions. I thought there were only 20~ submissions because I couldn’t see the page counter until I scrolled the frame all the way to the bottom.

The detail page was laid out pretty well the first thing I would change if possible would be the column names. They looked just like that, camel case column names. Secondly, the section to enter notes and set ratings was a little slow to use. You had to click on a rate button fill out the form and submit. This requires a server round trip every time. It does keep you from losing anything you have put into the form so far though.

When you are done with the detail ratings you are back to the main page for your final ratings. Again every button push was a server call and got tiring at times. This is just your final ratings and reason for rejection or to approve an abstract. You also have to set a final reason that ultimately is used by the heads of the program committee to pick the session list.

This brings me to the selection of reasons an abstract was rejected. I have to say it was limited and was difficult to choose. There isn’t a “You were awesome but not enough slots” in the drop down. We have to put in a reason so I tried to pick the most appropriate one I could.

In all, the tool was functional and allowed us to do the work. Again, this is the first year for this tool and I’m sure it will undergo some changes.

Odds and Ends

One of the things I thought was odd was the lack of knowledge sharing. I could see my partners totals for each submission but not any of the notes. Since we aren’t in the same room let alone in the same state it pretty much means out of band emails or phone calls to talk about abstracts. Also, as my first time doing this it would have been nice to see why Lance had rated an abstract the way he did.

After talking with some of the submitters, it appears that they don’t get any feedback on why they were chosen or not, just what we picked in the reasons drop down. I took notes on pretty much every abstract with the assumption that it would be fed back to the submitter, so if they chose to submit again next year they wouldn’t make some of the same mistakes.

Lastly, a speaker is limited to the number of sessions they can present. This guarantees that you don’t see the same three people the whole summit. The problem is we don’t know if they have been chosen more than the allotted times. If we pick them then they get pulled for another track they have to depend on our alternate selection to fill a slot. We did some second guessing on some folks with the assumption they would be gobbled up by other tracks. In hind sight I it would have been helpful if we knew the person had put in say five submissions to and what tracks to make our choices a little better. Possibly prioritize the tracks and publish to the tracks down stream who is off the table. Maybe even allow the submitter to put a preference on their submissions so we have just that little bit more information on what they would like to speak on as well.

In part one I talked about the abstracts. While important, abstracts are only one part of a complicated selection dance. Since there are so many submissions and so few slots even the best abstracts may not be chosen.

The Selection Criteria

We were given a set of instructions and categories to rate things. There weren’t too many of them and they were subjective in nature. We rated abstracts, topics and the presenter with a final subjective rating to act as a catch all.

Abstracts
Covered in part one.

Topics
This was a little odd to me. The topic and abstract are clearly dependent on each other. I wouldn’t approve an abstract if the topic wasn’t something appropriate. Conversely, I wouldn’t approve a topic if the abstract was weak. Also, don’t confuse the title of the abstract with the topic. The abstract could be the best written one in the world and not get approved if the topic was say how to knit doilies.

Presenter
This was probably the one thing that soaked most of my time. It involved lots of footwork on my end. If I don’t know you, haven’t seen you present or don’t have enough information how do I rank you? This is where filling in your Bio on the abstract submission is important. If you had spoken at a previous PASS Summit there is a possibility I could see your past rankings. They range from 1 to 5. If you are in the high 3’s or 4’s that works out well enough for me. If you haven’t spoken at the Summit I would look at your online presence. Do you blog, tweet or do other things to show you can communicate with the community? Also, I look to see if you have presented at a regional or local level and try to contact people I do know to ask how they thought you did. If you have spoken and I can find your slide decks that also helped me out. Luckily, I have been involved with the community for a long time and have attended every PASS since 2003.

Subjective Rating
I really don’t know how to deal with this one. I used it mostly to sum up my thoughts put a final rating on the submission. To me, all of it is subjective. It’s my opinion if the submission should move forward. This is like saying “I love the abstract, topic and speaker but the sky is blue today so I’m giving it a 1”.

Final Thoughts

Personally, I’d like to see clear breakouts with instructions on how to use them. There are several fundamental criteria that would keep a session out right off the top, you answer them and if they don’t tally up you move on to the next submission. There are exceptions to every rule, but in most cases I think it would work well.

My next, and probably last, post will cover the tools and processes that PASS makes available to do this job.

Well, I have just finished up my part in the abstract selection process for the PASS Summit. This was my first year as a volunteer in this process so I don’t have any specific previous experience to draw on. I have some experiences that are similar. Some of you may know that I was actually a double major Mass Communications and Theatre in college. I have experience in putting together competitions and also as a judge at several events. So, while I might not have PASS experience I have had to judge others on their abstract writing and presentation skills. This process isn’t an easy one. It also is getting more difficult every year just due to the volume of submissions. To put into perspective, there were 7 regular session professional development slots with 4 alternates and 47 submitters. We had more seasoned speakers than slots for sure. I won’t know if the sessions I promoted will be selected or not. Unlike Steve, I didn’t get the impression my word was the final one. I understand why Steve isn’t happy about the selection process, I just assumed that my recommendations were just that, recommendations and someone else would have the final say. There are other factors I have no control over. If a speaker is chosen by other tracts there is no guarantee that the speaker will be available for the PD track.

Abstracts

Quite a number of the abstracts were well written with clear goals. A number of them though needed some revisions or additions. I went to the PASS website and looked at the abstract submittal guidelines and found them incomplete. If these had been submitted without some additional information or not by a known speaker and leader in the community I saw them as incomplete. Lets break it down.

The Title:
Being witty is fun, but if I don’t know what are actually talking about the people seeing it in the program guide won’t ether and may skip you over.

The Bio:
Even if you are well known, a Bio just helps set that in stone. It also keeps me from doing two things, digging around the internet to see what you have done or relying on my less than perfect memory about you. Without a Bio you are trusting me to gather the information and set you apart from the other 40+ people wanting a slot.

The Abstract:
You don’t have to write War and Peace. You do need to be descriptive and clear. If you can do that in the space of a Tweet great, you have 1000 words don’t be shy. Focus your topic. Don’t mix and match several things that may not even be related in the same presentation. If the first third applies to me but the other two thirds don’t I probably won’t go to the presentation at all. Again, funny is great but if you don’t cut to the chase I don’t know what your topic is really about.

Session Prerequisites:
Unless this is a 100 level session you will have to detail some prerequisites that your audience should have. Since of humor or personality flaws aren’t prerequisites. One year of analysis services is.

Session Goals:
Right now we have three slots on the form. Most folks didn’t take full advantage of listing the learning points.  The goals should be tangible and something you can repeat on your own after sitting through the session. Feeling better about yourself is something you get from therapy. Learning how to prioritize tasks is a goal. If there aren’t enough slots then use your abstract space for that as well.

Level:
This seems to be one of the more difficult areas. It can be subjective. I would use your prerequisites and goals guide you. If there are a few specific prerequisites that generally indicates something higher than novice. If it requires specific knowledge about a specific feature that may be advanced or higher. If your goals are very specific about a feature or technique that may also raise the session level.

Next post I’ll talk about the process and tools that we used to make these difficult decisions.

First of all, I want to congratulate all the volunteers that made this happen. It was a very well organized event and ran smoothly. I had a great time. It was nice meeting people that I couldn’t have met any other way.

As A Vendor…

The Good:

Ryan Adams did a very good job keeping things coordinated up to the event. Making sure that everything we were entitled to we got. Always very responsive to emails and questions.

The day of I always had Ryan or one of the volunteers stop by between sessions and check that everything was good. I have always had a good experience with PASS events, but I’ve never had so many people checking on us before!

Needs Improvement:

Table placements. I just didn’t understand the flow and layout of the event until I saw the venue first hand. I would have picked a different table. I don’t think it hurt us, we had crazy foot traffic and lots of conversations.

It did bottleneck up sometimes around the vendor tables as sessions let out but I think over all the placement was OK. There isn’t much room to work with and I don’t know if I would have done much better in their shoes!

Overall:

As a vendor I was very happy with the event and the amount of time I got to spend talking to folks about Nitrosphere and what we do. As a new company getting out and meeting people is very important.

Only having one or two big conferences a year is difficult and costs 5 to 10 times the amount of money that a SQL Saturday does.

As a Speaker…

The Good:

Again, well coordinated no scheduling issues or anything like that. I found the different tracks layer out well. The meet and greet the night before was nice.

Speaker room was big enough.Internet access seemed fine to me.

Again, I was checked on by the staff over and over to make sure things were OK.

We also had a handler feeding us time to help keep us on track.

Needs Improvement:

Recording sessions was spotty. It was a last minute thing and most of us could have used a little hand holding getting it right.

Overall:

As a speaker I was happy again with the organization and attention to detail.

As an attendee…

The Good:

Lots of tracks an sessions for everyone. I enjoyed seeing so many local and new speakers making the break.

Plenty of interaction between people and speakers.

The food was great, I NEVER get the chicken salad, I ate two for lunch :). Oh the ice cream…. so evil.

Needs Improvement:

Bathroom Queue Length’s were a little long but did clear up.

Finding the stairs to the second floor was fun.

Overall:

Yet again, no real complaints. Plenty of seating solid flow and awesome choices. I still can’t believe this was a free day of training!

I will be making room for other SQL Saturdays going forward.

Like many of you I’ve heard the developer community going on about Rails for quite a while now. It wasn’t until recently I had any reason to dip into that world. Over at Nitosphere the website is all run on Rails. We got a inexpensive web host and it was pretty easy to get it up and running. Like most shared web host, it is all linux/open source based. We have now grown to the point that hosting our own server would be cheap enough and give us complete control over the box. As a Microsoft ISV I thought it would be nice to have our new box be a Windows box. It would also be nice to hook in to SQL Server instead of MySQL as well. After a little digging I did find that Microsoft is sponsoring IronRuby, a Ruby clone that runs on the .net platform. Unfortunately, it isn’t completely compatible with one of the packages that we need to run the website on. So, back to Ruby. There is also a gem to run Rails apps against SQL Server, It isn’t compatible with some of the stuff on our website ether. Finally, I fell back to ODBC to connect to SQL Server. Everything wired up but there was still an incompatibility issue. I’ll keep trying to work it out but our fall back was MySQL.

You will need:

Source Control:

If you plan on getting the source for anything you will need ether GIT or Subversion.

GIT for windows:

http://code.google.com/p/msysgit/

http://msysgit.googlecode.com/files/Git-1.7.0.2-preview20100309.exe

Subversion clients:

http://www.sliksvn.com/en/download/ basic client 32bit or 64bit

some folks prefer tortoisesvn

http://tortoisesvn.tigris.org/ 

Ruby core:

http://rubyinstaller.org/

rubyinstaller-1.8.7-p249-rc2.exe

Ruby 1.8.7 has the most compatibility with existing gems. Get the latest installer if the one linked isn’t it. There are installers for 1.8.6 and 1.9.1. Again, check to see if they will support the gems you will need to get your site up and running!

http://rubyforge.org/frs/download.php/66888/devkit-3.4.5r3-20091110.7z

install the dev kit if you would like to compile some gems instead of manually downloading them. If you don’t install the devkit some gems will fail to install since they can’t compile to a native extension. To get around that you can also use --platform=mswin32 when you install a gem.

Example: gem install fastercsv --platform=mswin32 will fetch the precompiled windows gem if it exists. for more information on the devkit check out this link. http://www.akitaonrails.com/2008/7/26/still-playing-with-ruby-on-windows

Databases:

Sqlite

http://www.sqlite.org/download.html

http://www.sqlite.org/sqlite-3_6_23.zip

http://www.sqlite.org/sqlitedll-3_6_23.zip

By default when you create an application Ruby on Rails defaults to sqlite3. If you want to use Sqlite3 you will need to download the dll’s and the command line executable.

MySQL

http://www.mysql.com/downloads/mysql/5.1.html 

Since I couldn’t get our site to talk to SQL Server we are staying on MySQL for now. You can use the latest installer 64 bit or 32 bit but you must have the 32 bit library for Ruby to work properly. The libmySQL.dll from the 5.0.15 install did the trick for me.

http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.15 

SQL Server

If you would like to try the SQL Server adapter just do a gem install activerecord-sqlserver-adapter to get it.

HTTP Ruby Server:

I went with mongrel, it may not be the best but it was pretty easy to setup and get up and running. I am running version 1.1.5 right now since that seems to work best with mongrel_service which you will need if you don’t want to stay logged into your web server with a dos prompt running. I opted for the latest beta of mongrel_service since it cut out some dependencies and seems pretty stable at the moment. As always adding –pre gets the latest beta gem. Also, –include-dependencies will grab everything the gem will need to run, including mongrel.

Gems specific to my install:

Substruct uses rmagick for thumbnail generation, which requires image magic to do the actual work.

http://www.imagemagick.org/script/binary-releases.php?ImageMagick=dv31jd0gev1d3lk182a4pma8i6#windows

http://www.imagemagick.org/download/binaries/ImageMagick-6.6.0-7-Q8-windows-dll.exe

Redcloth is a textile markup language for Ruby. If you didn’t install the devkit don’t for get to add --platform=mswin32 to your gem install commands.

http://rubyforge.org/frs/?group_id=216&release_id=36337

My installation steps:

Install Ruby

Make sure c:\ruby\bin (or where you installed it to) is in the path. I recommend a path with no spaces so no c:\program files.

extract the devkit to the c:\ruby directory.

extract the sqlite exe and dlls to c:\ruby\bin

extract libmySQL.dll from the 32 bit 5.0.15 archive

Open an command prompt with administrator privileges.

Issue these commands:

gem update –system

gem install rails –no-ri –no-rdoc

gem install sqlite3-ruby –no-ri –no-rdoc

gem install mysql –no-ri –no-rdoc

gem install mongrel_service –no-ri –no-rdoc –platform mswin32 –include-dependencies –pre

After that install any gems you need for your Rails app. Make sure and test that your app works in production mode with mongrel before anything else. There will be some kinks to work out I’m sure. Once you are happy that everything is running as expected you can install your mongrel service.

mongrel_rails service::install -N MyAppsServiceName -c c:\app\myapp -p 3000 -e production

The –N is the service name. –c is where the app will be served from. –p is the port number that it will listen on. –e is the mode it will run in like development or production. I chose a few high ports 3000 to 3008 for my services to run in.

You can always remove a service if something is wrong.

mongrel_rails service::remove -N MyAppsServiceName

Setting up IIS7:

Install the application request routing 2.0 and URL Rewrite plug-ins using the Web Platform Installer

http://www.microsoft.com/web/Downloads/platform.aspx

Once that is done you will need to create a new web farm.

Next you will need to add at least one server entry. You may want to edit your host file and add additional aliases to your IP Address so you can run multiple copies of mongrel to service all request. The recommendation is one per cpu/core.

The last step the wizard ask if you want to add the routing rules. The answer is yes.

You can confirm the routing rules are in place.

Make sure you have a website in IIS running and listening on port 80. Without this there is nothing for IIS to route to your new server farm.

If you have any questions post them up. I’m not a Rails expert but I have just been through the pain of Rails on Windows!

Date on Database: Writings 2000-2006 This is a collection of writings by C.J. Date, one of the fathers of the relational model. It has a nice tribute to E.F. Codd, inventor of the relational model. If you are looking for tips and insights into relational databases on a higher level this is a solid read.

SQL and Relational Theory: How to Write Accurate SQL Code Another solid text from Date. This one helps you understand the theory so you can write effective real world code. It has lots of solid examples. It covers topics like granting access directly to the data or through views. How NULLS effect the answers returned by your queries. Advanced coverage of constraints. I’m currently reading this book again. I don’t think you could absorb all of the goodness this book has to offer in one pass.

Temporal Data & the Relational Model (The Morgan Kaufmann Series in Data Management Systems) If you are working with dates and time this book will teach you a new modeling technique. Unlike Domain Key Normal Form this builds on the previous Normalization rules and is a logical extension of them. It does use Tutorial D to explain its examples which is kind of a pain.

Database in Depth: Relational Theory for Practitioners Another fundamentals book that transcends any particular product line and gets to the heart of the relational model. It is a short but concise read and one I generally recommend to people wanting to expand their theoretical base.

Refactoring Databases: Evolutionary Database Design If you have ever had to refractor a database this book is for you. I’ve read and re-read this book over the last few years. It isn’t the easiest read in the world but it can help you apply what you have learned from the books above to your current database without having to start from scratch.

As always if you have any questions or want to suggest a book let me know!

The Problem

SQL Server is a huge product with lots of moving parts. Bugs happen. Microsoft has a place to voice your issues or problems. They allow you to vote on the issue and then decide when or if it will get fixed. I’ve used Connect when I hit a bug and I have voted on items that were important to me. Recently I hit a bug in sp_createstats. I use this system stored procedure generate statistics in an automated process I’ve got that manages statistics. I added a new vendor database to the system and on the first run hit “Column 'DAYSOPEN' in table 'dbo.TBL_OPPORTUNITY' cannot be used in an index or statistics or as a partition key because it is non-deterministic.”. Well, we all know you can’t create stats on a computed column! I quickly went to the connect site and someone else had already entered it. The down side was it had so few votes it was only slated to go into the next cumulative update/service pack. When I hit this issue they hadn’t yet announced service pack 4. I already had this procedure coded into my routines and really didn’t want to rewrite them to get past this one problem.

The Solution

!!WARNING!!

By doing what I am about to describe could break at a later date or randomly kill baby kittens.

Since it is a system stored procedure I am loathe to make any changes to it directly. There are ways to modify some system stored procedures but they involve the installation CD and creativity. With that door closed there was only one avenue open to me. Create my own system stored procedure with the fix in it. There is a problem with this solution as well, if it gets dropped due to a service pack or an upgrade anything calling it will break. The first thing I did was to see if the procedure text was available by executing sp_helptext sp_createstats. Luckily it was! Now all I had to do was figure out where it was broken. The procedure is pretty simple and uses some cursors to loop through all the objects and create column statistics where they don’t exist.

declare ms_crs_cnames cursor local for select c.name from sys.columns c  

     where c.object_id = @table_id  

     and (type_name(c.system_type_id) not in ('xml'))  

     and c.name not in (select col_name from #colpostab where col_pos = 1)  

     and ((c.name in (select col_name from #colpostab)) or (@indexonly <> 'INDEXONLY'))

    -- populate temporary table of all (column, index position) tuples for this table  

It was pretty easy to spot. The weren’t checking to see if the column was computed so I added a line to the where clause.

and c.is_computed = 0

That’s it. One little check to see if it is a computed column. Now that I had fixed it I created a new procedure named sp_createstats_fixed in the master database. Just creating it in master doesn’t make it act like the original procedure or make it a system stored procedure. For that I had to execute EXECUTE sp_MS_marksystemobject 'sp_createstats_fix'. This is an undocumented stored procedure and could change or go way any time. The only way to unmark it in SQL Server 2005 is to drop the procedure and recreate it. Now it acts just like the old procedure. Next I had to replace all references to the old proc with the new one. I made an entry into our bug tracking system about the change so we would have a record of what I did and why.

Conclusions

This wasn’t the most elegant solution. It could break later. The upside is it only took me about 30 minutes to fix and deploy versus the hours of re-coding and then testing that I would have had to do before. Do I think you should go around creating your own system stored procedures? Not at all. I don’t recommend you put anything in the master database period. If the problem had been more complex I would have redone the original routines to exclude the broken procedure. This time it just happened to be a very quick fix to a non-critical part of our system.

Over and over again we are told that the DMV’s only hold data since your last reboot. So, how do you know when your server was last rebooted? Well, every time your SQL Server service restarts tempdb is recreated every time. With a quick query to sys.databases we can get the creation date of tempdb! Armed with that little nugget you can then analyze what is in the DMV’s relevant to the last system restart.

SELECT 

    create_date AS last_restart_time

   FROM   

    sys.databases

   WHERE  name = 'tempdb'

Huh? My story? Okay. It was never easy for me. I was born a poor black child. I remember the days, sittin' on the porch with my family, singin' and dancin' down in Mississippi. – The Jerk

This meme was started by Paul Randal (Blog | Twitter) who asks the question "What three things or events brought you to where you are today?" I was tagged by David Taylor (Blog | Twitter). I wasn’t born in Mississippi but we sure were poor. I grew up I a small West Texas town of farmers and ranchers. I grew up between two households. I am a single child but have a large extended family and was a middle child in that mix. I as never as tall or fast as my closest brothers but I have always had a very sharp wit and a quick thinker. With all of those factors competition for resources was pretty fierce.

Turn number one, being broke breeds invention!

My aptitude for mechanical things and electronics was my first love. Being handy with a socket set has a great value when you have to fix everything you own yourself. It also allowed me to scavenge parts where ever I could to build stuff I wanted. It wasn’t ever pretty but it was mine. The bike I rode during my middle years was made up of at least four others. My radio was also made up of three or four other radios. One of the great things was one of my best friends had a great hookup into technology, his dad owned the Radio Shack in town. I spent a lot of time there and got to hone my soldering skills there on a real Weller station with adjustable temp!. My mom saved and saved. She new computers were the future. Having a Radio Shack meant I got a TRS-80! I scraped up the money to buy a tape drive and eventually a modem. I’ll never forget them telling my mom that this thing would take me through high school and college! I spent the better part of my junior high and high school life on this and later a CoCo3. My High school had Apple II’s and eventually some very coveted Mac’s. My best friend through high school was one of two people that had those “IBM PC” things. I fell in love with those 8088’s and his dads “high end” 286 and got to spend a lot of time on those as well. Those few of us that had modems would dial into each others machines. Needless to say my folks weren't real happy when the picked up the phone and a computer was screaming at them. I wasn’t to happy ether since they usually killed me right at the end of a two hour download :)

Turn number two, the college years and dial tones.

Alas, it wasn’t my goal to be a computer guy. I loved speech and theatre and did quite well in both. I dabbled in physics but quickly realized it was hard and I wasn’t very fond of the math. The other thing I majored in was partying. I slowly quit playing with computers all together and for about a year was just a college bum. My first real friend in college had a 286 and a modem as well. He showed me the local BBS’es and I was 100% hooked again. He gave me a old hard drive and I went down to a local computer store to buy a used computer and start learning all over again. The shop owner was a friend of my friend and knew who I was. I walked in with enough money to buy a 286 he had for sale. I told him I needed a computer and was planning on starting a real BBS. He smiled, and flatly refused to sell it to me. I argued with him for the better part of an hour. I left his store pretty mad but none of the other computer stores in town had a computer that met my limited budget. I went back the next day and started in on him again. He stuck to his guns telling me I wouldn’t be happy with that old clunker. My friend had told him I was handy with the soldering iron so he made me a deal. I put a down payment on a cutting edge 486 and worked the rest off fixing computers. He took me under his wing and helped fill in the gaps in my computer hardware education. He had a crazy huge stockpile of old stuff that I could tinker with. That is where I found out I truly loved computers and connecting with others via my BBS. Looking back on what Duane did for me is one of the biggest reasons I give back to the community today. Spending hours working on my board I learned to love DOS like no other. It very quickly grew into a rekindling of my passion of programming as well. I changed majors(again) and started the computer science thing. Having people come to the house to play their turns on BRE or Trade Wars, posting up on fidonet groups or sending mail to friends around the world was a huge kick for me. I met some of my closest friends through the board. The Internet will never replace the BBS in my heart. I had a loyal local group of BBS’ers with the ability to communicate globally. Eventually, I dropped out of school to work full time again. As the Director of Technology for a small school outside of San Angelo, Texas I had a huge impact on the students and helped bring computers and the Internet, into every classroom. I also participated in the speech and theater stuff with the high school students as a coach.

Turn number three, the big city.

After a shakeup at the school system I was working at occurred and the Superintendent of Schools left, I decided it was the right time to make a move to the big city. I had always loved Austin. I’d done a tour of service in the other major cities and found that Austin was the only one that felt like a real community. I did what lots of folks did in the late 90’s and interviewed at start ups. To be honest, they scared the crap out of me. Most of the time it was people younger than me, with more money than I’d ever seen, doing whatever the hell they wanted. It looked like a lot of fun, but not so much a solid way to build a business. So I did what everyone does, I went to work for Dell. Did it suck? Yeah, kind of. I met a great group of guys there though. It was also the first place I’d ever been that had what you might call a growth path. My boss and my team mates were great people to work with. I had been doing side work pretty much most of the 90’s small software projects or

this image is property of Dan Morales www.flickr.com/.../set-72157601003144486

working in the new open source movement. I loved data, so I spend a lot of time working with databases or building custom data engines. When I had landed at Dell I’d already had several years of tinkering with SQL Server. We had a single server at the school and I worked with clients I met through the computer store as well. I also love a puzzle. I watched the calls that came in. Saw the patterns in them and started plugging them into a brand new SQL Server 7.0 database. Quickly I moved off the phones full time and started working on a special projects team analyzing call volumes, doing root cause analysis and coming up with solutions. Dell also opened my eyes to the possibilities and gave me the confidence boost to just strike out and blaze my own path. I did my time at Dell and moved on to another large company, Broadwing. Again, great group of guys, not so great working environment. But, for the first time I was a full on DBA. Not a DBA/developer/janitor/whateverthebossneeds. I LOVED IT. Lots of machines, lots of data. From there it has just been a natural progression always growing and learning but staying laser focused on SQL Server. Like many folks, the 90’s was an awesome decade for me.

Today

Well, I’m still with SQL Server. I love community work. I’m a family man. None of the things I take for granted today wouldn’t have happened if I’d never been exposed to computers at a young age, or had a stranger take a chance on me, or worked up the nerve to just move to another city when I had a stable job where I was at. Give back when you can. You never know how you will shape someone else’s road for the better.

If you read Fundamentals of Storage Systems – Stripe Size, Block Size, and IO Patterns you know I built a little web tool to help you with sizing and estimating your RAID array’s performance. This is way out of my area of expertise. Luckily for me I like a challenge and had a guiding hand from some friends. I haven’t done any web programming since I wrote a photo album mod for Snitz! forum package in 2002, using classic ASP. I still get thank you emails from folks that have been running it for years. Needless to say my skills are a little rusty. My first instinct was to fire up Visual Studio 2008 and build an ASP.net page tied to a back end SQL Server 2008 database. Well, ASP.net is just different enough that I was struggling to do the most basic things. So, I thought it would be good to “get back to basics”. A very good friend of mine has been a professional web developer for the same company since about the time my photo album came out. He lives and breathes web technology. The problem was he doesn’t do ASP.net at all. Everything he does is standards compliant HTML and JavaScript. I told him about my spread sheet of calculations and my desire to turn it into a web page. Joe quickly begged me to leave him alone. Once I calmed him down, he did a little sample page to get me on the right track. I took a look at it and thought it would be easy to goof around in this JavaScript stuff. I was wrong. What a convoluted world web developers live in.

I hacked on it the weekend before Christmas and was pretty happy with my handy work, right up until I showed it to some people. Comments like “Kill it with fire!” were common. I explained what it was and that took some of the hostility out of the feedback. The next Monday I asked the two lead web heads at work to look at it. Once Ben had washed his eyes out with bleach he told me under no certain terms could he fix it. My powers of persuasion, and a threat to never approve another schema change, helped bring him around. He explained several new technologies to me I wasn’t aware off, like CSS. Ben showed me quickly how to format the page so peoples heads wouldn’t burst into flames when they saw it. He also spoke of things like Ajax and JSON. Being the clueless data guy that I am I turned to my trusty friend yet again, Google.

Quick Definition List

JavaScript, the underpinning of the modern dynamic web 2.0 world.

Ajax (asynchronous JavaScript and XML, a group of interrelated web technologies that allow for dynamic web design.

JSON (JavaScript Notation), a lightweight data-interchange format, a way to structure data like XML implementing name/value pairs and ordered lists.

JQuery, a JavaScript library used to traverse HTML elements and build dynamic content easily.

JavaScript Implementation

What struck me was just how much had changed, yet stayed the same. One of the things I’ve always hated is no two browsers render the page the same way. If it worked in Firefox it didn’t work in IE. The other thing I didn’t realize for quite a while was any of the browsers would happily run the worlds worst code. I’m not talking poorly written code, I mean flat wrong. They will let any old JavaScript run. JavaScript is defined as a loosely typed language. The way it is implemented it goes from loosely to sloppy very quickly. Don’t get me wrong here. The language specification EMCAScript, is solid. The implementations are very poor in most browsers and that is where the problems developing for JavaScript really come in. I’m not saying JavaScript is slow, quite the opposite in fact. It is just difficult to make sure you are actually writing the correct code. Having worked with Visual Studio for quite some time, I’ve become spoiled with having my IDE tell me when the syntax is wrong before I hit the compile button. So, I was back to Google again looking for tools to help me out.

The Tools

I quickly settled on Aptana Studio 2.0 to do the actual code work. I also used Notepad ++, which is by far the best text editor in the world. with Aptana Studio allowing me to see errors in real time and use the preview function to look at page renders without having to have browser windows open. I did look at Microsoft’s new Expression Suite 3.0. It is nice. The designer is slick, but in typical Microsoft fashion it mangles some parts of the code that I then have to fix by hand. Aptana Studio also has a built in JavaScript minimizer that shrinks the size of your JavaScript files for quicker load times. Aptana Studio doesn’t enforce the use of line terminators and when I compacted my JavaScript it wouldn’t load. I did some additional digging and found an awesome Lint tool for validating JavaScript JSLint. My favorite thing about it is the quote and link on the front page “Warning: JSLint will hurt your feelings.” I knew I had found the right tool. After several passes I got my code to validate and minimize without any errors. I quickly looked for other Lint based tools and found one for JSON as well JSONLint.

The Work

Over the course of a few weeks I tinkered with the page, adding features, correcting mistakes and cleaning up the layout. The biggest thing that I learned is just how fast JavaScript can be. Even after I did the initial page I had no doubt a database would be involved at some point. I had compiled a small database of hard drives and their characteristics and wanted to add that to the page. The problem was I would basically have to redo the page again in asp.net or some other dynamic language to pull the data from the database. So, I cheated. As a proof of concept I built a JSON structure with a sample of the data to load the drop down list box and all the variables dynamically. I was stunned at just how fast the page was to respond. I hacked together some T-SQL that would generate the JSON object from my database, all 1200 entries. I cleaned it up a bit made sure that JSONLint validated it ran the minimizer on it and plugged it in. Holy cow, it worked! It was also still very vast. I just couldn’t believe it. The only drawback is having to update the JSON if I add new drives to the database.

What I’ve Learned

The main takeaways for me on this project are simple, web development stinks. Now I know why web folks have a hard time with T-SQL, they already have to be experts in five or six different technologies across two or more platforms. I use to introduce Joe as “My friend who does JavaScript”, now I know is is a hard core developer! What I build is simple and works. I think the layout is tolerable. If you have any suggestions or feed back just drop me a note.