Reloading Windows 7…

Turns out it’s a good idea to do it once in a while.  Cleaner installs, availability of new and improved drivers, more thoughtful selection and placement of applications.

This time it was caused by my rush into SQL 2011, which is still at CTP1 level, and accompanying documentation, though warning you about certain things, is not readily available when making a decision of what to load and what to skip.  In my case it completely broke BIDS for SQL2K8R2.  After a day and a half of struggling with attempts to repair and/or reinstall the latter, – I realized it would take me longer than just to reload everything clean.

I also wanted to re-introduce RAID on my drives (2 Hitachi’s), which of course requires a full reload.

Well, 8 hours later I have a much faster system, with everything I need!

Recent Seat Belt Law

It is so saddening to know how we cut ourselves clear from the very freedoms that we’re so loud about!  Recently the city council voted for introduction of yet another seat belt law, requiring all occupants of a vehicle to have their seat belts on.  It also gives law enforcement officer to issue fines to anybody in the vehicle who was caught not wearing the belt when the officer pulled it over.  More so, it gives the officer the right to stop the vehicle if the officer believes at least one of the occupants, including the driver, does not wear the seat belt.

Interestingly, only now the local media started expressing their concerns about such restrictions.  20 years ago, when I first came to this country, the law about wearing a seat belt by drivers was only gaining momentum, and everybody who had concerns about safety, was so anxious to not only support it, but to even report his or her fellow drivers to the police.

I am wondering what these media jockeys thinking back then, when such an intrusion upon personal freedom was made?  Did they not see where it may lead?

My Dad!!!

Tomorrow my Dad is coming to visit me and my family!  For the whole summer!!!

I hope they don’t give him too much static at the Heathrow airport…and then at the customs in Houston…And I hope that the airline doesn’t lose his luggage…

But he is confident, and very excited.  So are we!

Retrieve space used info…

Somebody on the net was looking for a way to retrieve info on all user tables in all databases in an instance.  Here’s another 30-minute solution, addressing this:

create table tempdb.dbo.t (
   serverName sysname null
  ,dbName sysname null
  ,schemaName sysname null
  ,name varchar(255)
  ,rows int not null
  ,reserved varchar(255) not null
  ,data varchar(255) not null
  ,index_size varchar(255) not null
  ,unused varchar(255) not null
  ,DateRun datetimeoffset not null default cast(current_timestamp as datetimeoffset)
)
go
exec master.dbo.sp_msforeachdb '
   if db_id(''?'') > 4 begin
      use [?];
      declare @cmd varchar(max), @schema sysname;
      declare c cursor local for
      select sName = schema_name(schema_id)
        ,cmd = ''use [?]; exec sp_spaceused ''''''
        +schema_name(schema_id) + ''.''
        +name + '''''', true'' from sys.objects
         where objectproperty(object_id, ''IsUserTable'') = 1
      open c;
      fetch next from c into @schema, @cmd
      while @@fetch_status = 0 begin
         insert tempdb.dbo.t (name, rows, reserved, data, index_size, unused) exec (@cmd);
         update tempdb.dbo.t set
            schemaName = @schema
           ,serverName = @@servername
           ,dbName = db_name()
            where serverName is null and dbName is null and schemaName is null
         fetch next from c into @schema, @cmd
      end;
      close c;
      deallocate c;
   end
'
select
   FQName = serverName + '.' + dbName + '.' + schemaName + '.' + name
  ,RowsCount = cast(rows as bigint)
  ,ReservedMB = cast(replace(reserved, ' KB', '') as float) / 1024
  ,DataMB = cast(replace(data, ' KB', '') as float) / 1024
  ,IndexSizeMB = cast(replace(index_size, ' KB', '') as float) / 1024
  ,UnusedMB = cast(replace(unused, ' KB', '') as float) / 1024
   from tempdb.dbo.t
go
select
   serverName
  ,dbName
  ,ReservedMB = sum(cast(replace(reserved, ' KB', '') as float) / 1024)
  ,DataMB = sum(cast(replace(data, ' KB', '') as float) / 1024)
  ,IndexSizeMB = sum(cast(replace(index_size, ' KB', '') as float) / 1024)
  ,UnusedMB = sum(cast(replace(unused, ' KB', '') as float) / 1024)
   from tempdb.dbo.t
   group by
      serverName
     ,dbName
go
drop table t
go

Check for existence and wait…

A friend of mine asked me how to implement this type of logic.  The task was to check for an “existence” of an entry in a table, and if the entry does not exist, – wait for specified time interval, then check again, and only then exit.  Of course the description does not include a positive outcome on either initial check for existence, or the final check.  But the solution must have this logic.  Here’s my 30-minute solution that I worked out for him:

create procedure dbo.sp_CheckForExistence (
   @flag int
  ,@val sysname                           )
as
   if exists (select * from sysusers where name = @val)
      return (0)
   else begin
      set @flag = @flag + 1
      return (@flag)
   end
go
create procedure dbo.sp_Parent (
   @val sysname                )
as
   declare @flag int; set @flag = 0
   declare @threshold int; set @threshold = 2
   while @flag >= 0 begin
      exec @flag = dbo.sp_CheckForExistence @flag, @val
      If @flag = 0 break; --This means that it exists
      If @flag = @threshold break; --We waited and re-tried, but existence is not satisfied, so we quit
      Waitfor delay '00:01:00'
   end
   if @flag = @threshold begin --we know that waiting didn’t help
      /* do something here */
      raiserror ('We waited for %d minutes, but nothing happened.', 0, 1, @threshold) with nowait
   end else if @flag = 0 begin -- no waiting was necessary, the record was there
      /* do something else here */
      raiserror ('We didn''t wait, the record was there.', 0, 1) with nowait
   end else if @flag < @threshold begin
      /* do some other things */
      raiserror ('We waited for %d minute(-s), and it was worth it.', 0, 1, @threshold) with nowait
   end
   return (0)
go
-- Then you execute it as:
exec dbo.sp_Parent N'Joe'

Financial Quarter Calculator

I saw a question on dbforums.com asking for how to determine the financial quarter value for a given date.  I think the solution below will give one a good start on that:

if object_id('dbo.fn__MonthWithOffset') is not null
   drop function dbo.fn__MonthWithOffset
go
create function dbo.fn__MonthWithOffset (
   @monthNumber tinyint
  ,@monthOffset tinyint
  ,@operation   char(1) /* + or - */    ) returns tinyint
as begin
      declare @result tinyint
      if @operation not in ('+','-')
         return (null)
      if @monthNumber not between 1 and 12
         return (null)
      if @operation = '+' begin
         if (cast(@monthNumber as int) + cast(@monthOffset as int)) > 12
            set @result = cast((cast(@monthNumber as int) + cast(@monthOffset as int)) % 12 as tinyint)
         else
            set @result = @monthNumber + @monthOffset
      end else if @operation = '-' begin
         if @monthOffset > 12
            set @monthOffset = cast((@monthOffset % 12) as tinyint)
         if (cast(@monthNumber as int) - @monthOffset) <= 0
            set @result = 12 - (@monthOffset - @monthNumber)
         else
            set @result = @monthNumber - @monthOffset
      end
      return (@result)
   end
go

if object_id('dbo.fn__FinancialQuarter') is not null
   drop function dbo.fn__FinancialQuarter
go
create function dbo.fn__FinancialQuarter (
   @date datetime
  ,@firstMonth tinyint                   ) returns tinyint
as begin
      return (
         select
            case datepart(month, @date)
               when dbo.fn__MonthWithOffset(@firstMonth, 0, '+')  then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 01, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 02, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 03, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 04, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 05, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 06, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 07, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 08, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 09, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 10, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 11, '+') then 4
            end
      )
   end
go

select dbo.fn__FinancialQuarter ('11/01/2009', 11)

My boss…

Tomorrow is his last day…He hired me 2 and a half years ago, when I was happily sitting at home telecommuting for an Austin company, while complaining that it was just too boring to always be in your boxers at the computer.  And true, my kids even didn’t believe that I was actually working.  Well, the promises that were made back then were a lot more than what I was getting working remotely, but they also ended up being a lot more than what I got a year later.

Well, I am still here, and he’ll be gone.  I was tempted to call him and even take him out for a drink, but I kept running into the same uncomfortable state of anticipation of what to tell him.  Should I say that “yeah, the company just didn’t know how to appreciate your talent and your vision”, or should I tell him what most of us (present and former employees) kept on saying, - “his way of running a consulting business is definitely lacking structure and process, and it keeps biting us in you know what…”

Be it as it may, but he’s leaving…

“M” Modeling Tool (and language)

I guess I was expecting “modeling”, not a metadata and data viewer.  For the last 2 I can easily use at least 4 other tools (SSMS, Excel, Visio, and Word).  What I was expecting was something similar to Data Diagrams feature that was available since SQL Server 7.0 (if I am not mistaken), or at least since SQL2K for sure, but with some “Erwin/Embarcadero”-type enhancements, that allow you to design a database, and then deploy it by generating a deployment script.  I guess there are a lot of missing steps that I overlooked, not only while using it, but also while reviewing the accompanying documentation (http://msdn.microsoft.com/en-us/library/dd857506(VS.85).aspx).  What frustrates the most is the ease of installation (.NET Framework 4.0 beta is the only pre-requisite - http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en), and uselessness of the documentation (as it’s so very often the case with Microsoft).

So where do I go from here?  Well, back to Database Diagrams, since they’ve proven to be a very nice (considering the price=0 with the Developer edition) and long-lasting in terms of results tool that many DBA’s and database developers have used since 1999.

What do employers pay…

I am always looking at the market, checking out opportunities, considering options.  Even if it is for the sake of knowing where I stand today.  Recently I was contacted by a recruiter with an opportunity in a medical field.  I expressed interest, and when I found out that it will be “starting the whole thing from scratch”, I decided to pursue it.  Well, the recruiter played the game the way they always do: this is a one in a life time opportunity, I don’t think you really need to look at the numbers too close, and that the benefits will outweigh the step back in initial income.  But just to set my own expectations, I confirmed with the recruiter, that it will be at least a 6-figure compensation package, plus benefits.

All is well, I am competing with 4 other guys, and the company picks me and the other 2 as finalists.  That’s when it became “interesting”.  The recruiter told me “a story”, that the hiring manager went with salary expectations to her boss, and the boss said: “What?  100K?”  So the hiring manager (supposedly) goes back to the recruiter and tells her: “My boss just flipped, so we can do 98K.  100 is just too much.”

I am listening to all this, and cannot believe my ears (the conversation was over the phone).  The recruiter tells me that it’s only $200 a moth less, and that it should not be a deal breaker for me.  I am thinking: “Why should it be a deal breaker for the employer?  If they really want an expert in the field, why does it matter for them so much that he/she would be getting $200/month more?  But if it does matter so much, why don’t they lower their expectations, and settle with a 2-3-year experience guy for $65K?”

At the end it finally hit me, that either my recruiter was lying to me from the very beginning, or the potential employer was not really serious as to what they expected from candidates.  So, to make it fare for everybody, at least in my mind, I played out a scenario putting my self into the employer’s shoes.

If I am looking for the “top-of-the-line” expert, then the first thing I do is establish the budget.  If I am hiring myself, then I can be a lot more lenient in terms of a range, because I won’t have to pay anybody a percentage of my future employee’s income.  The next thing to do is to determine the caliber of an expert I will need for the job.  That part is an endless series of situations that are specific to an employer.  But let’s say for the argument’s sake, that I need someone on a higher end of scale.  Now I am ready to check the price for such talent.  I look at the national numbers first, simply because there is a possibility that you won’t be able to find a “top-of-the-line” resource locally.  At this point I may flip, but at least I know what to expect if I have to “ship” my talent from elsewhere.  The next step would be to check the local market, and see what the prices are for such talent around the neighborhood.  If I am still “shocked” over the cost, then I have a choice of lowering my expectations.  In this case I can skip the national salary research, because chances are very high, that I can find the lower end candidates locally.

But if what I see is reasonable, and mostly within the budget that I allocated, then my last thing to do is to determine, to the best of my abilities, that candidates that I am bringing in as finalists, are really the ones that I am willing to work with, for years to come (or whatever the case might be).  At this point I know their expectations, they know the range I am willing to give them, and it all comes down to “who’s the best for the job”.

Having said all this I am even more convinced that there is something fishy and even maybe dishonest that went on here.  Being once a hiring manager myself, I followed the steps above several times, and they never failed.  It doesn’t mean they are bullet-proof, but at least I was demonstrating honesty to my potential subordinates from the very beginning, and was not leaving the penny-pinching bargaining process till the very end, when the candidate already invested considerable amount of time on interviews, and has already written a notice letter to the current employer.

So, the question to the reader is, – is it just me or there is something fishy in here?

Sad case…

Yesterday I was involved in data analysis related to active investigation of theft at a customer’s business.  The evidence was almost on the surface, transactions were increasing exponentially, and reached their peak by Christmas time.  While having to work mostly the night shift, the person was running bogus transactions at a rate of 180 a month.  By mid January it was all over, and now this poor sole is awaiting trial.  But the business will demand retribution for all the stolen money.  The sad part is that there are so many people who believe that steeling is the way to go.  Especially when the chosen method is not so primitive, and does require preparation and methodical execution.  If only people like that could use their skills and creativity in a positive way…

Wishful thought!  A friend of mine is attending management classes, and one of his instructors told him that 25 to 35% of employees, when starting a new job, always try to find a way to defraud the employer.  This attempt manifests itself usually through direct or indirect theft.  The case above is obviously a direct theft.  But in the case of indirect theft the percentage of employees is understated.  Well, that’s a topic of a different post ;)

Application Maintenance

Every organization has either thought about having a custom app written for them, or already has one that is deployed and operational.  This fact in itself is a “trigger” for someone at some point to ask a question: “Who’s the owner of this app?”

Many companies maintain an in-house team of support personnel that “keeps the train running”, while others outsource this task to onsite/offsite contractors or a consulting firm.  Both approaches have pros and cons.  Let us take the first one, an in-house team.

Pros – the resources are always available, they possess the necessary knowledge about the application and the business, thus the implementation of modifications can be very quick with minimal downtime.

Cons – the cost of keeping such resources in house while development is needed on occasion may not be easily justifiable, which means that either those resources have to have additional workload, or the business will be facing the need to start cutting down the number of those resources.

The other approach, – fully outsourcing the task of application maintenance, also has its pros and cons.

Pros – No need to maintain a technical staff at all times, the bid for the contract can expand the spectrum of resources available for the project, and the winning contract is legally binding the consulting firm to complete the work on budget and on time.

Cons – the tendency to go with the cheaper bidder may result in poor quality and/or extend the period for delivery.  In the end, it may cost more, and in many cases will cost a lot more than the original budgetary estimate; going this route is definitely more expensive, even when using the cheapest bidder, than the cost of in-house resources for the period of time required to complete the work.

So how can we find this fine line and perfect condition that would be beneficial for the business in terms of availability of support, without breaking the bank, while not having to carry the liability for the idle resources?

First, let’s have a look at a typical scenario of how an application ends up as a business system.  2 to 5 years ago a business had a semi-full staff of IT professionals, that were eventually tasked to provide a computerized solution to a specific business problem.  As it’s usually the case, it started small, with an Access database and a couple of forms.  Eventually it grew and passed the limitations of MS Access platform.  The original developer long left, the documentation is sparse if present at all.  Now the company’s management is facing a dilemma, – to hire a new developer in hope of re-creating development environment for the application, or to outsource the effort all together.

What is frequently overlooked when considering an in-house resource, is liquidity of the presence of such resource, as well as the cost associated with finding and training it to fit the organizational needs.  This means that the company will periodically find itself in the same situation, and the cost will be unavoidable.

However, as we determined earlier, going with option 2, – outsourcing the entire effort, also has negative aspects.  Does this mean that the solution is to go with the “lesser of the two evils”?

The answer probably lies in approach that the company chooses to structure the relationship with a consulting firm.  And there are different such approaches that traditionally dominated the decision-making process.  One way to go about it would be to follow the trend that developed in the past 20 years, where the company contracts a consulting firm to support organizational needs on a long-term basis.  This virtually means to fully outsource all aspects of IT functions, including support, maintenance, new development, and even help desk.

Just a start...

That's it, I am finally at the point when if I don't start writing about what I do, - I will never be able to recall everything I've done. So, here it goes, and I hope it'll keep on going from now on, on a regular basis.

For now I just want to jog down some thoughts that I might need for writing my first news letter, that I was asked to come up with. It's actually (and thankfully) not a full letter, but just an article for it. But it already seems as a challenge to me, because the topic is somewhat vague, but at the same time should be covered in a very concise and down-to-the-point manner. It also needs to be catchy and appealing for people to read, while remaining semi-technical.

And now, the topic. I was told that it probably should be about performance, but I was thinking more like maintenance, and to be more specific - application maintenance with an emphasis on performance.