Monthly Archives: November 2012

GUID’s – Never for Clustered Indexes

Posted by mysticstache on

Globally Unique Identifiers have their place in software development. They’re great for identifying a library in the GAC or windows registry. They are, however, huge data types from the database perspective.

Oracle, MYSQL, Sybase, and DB2 do not provide any special data type for fields storing GUID’s, for these vendors a GUID is a 34-38 character string (depending on including dashes and “{}”). SQL Server has provided a Unique Identifier data type which has some benefits in storage and access speeds over a 36 character varchar, or nvarchar field. However, they’re still huge…

Unique Identifier Data Type

http://msdn.microsoft.com/en-us/library/ms190215(v=sql.105).aspx

SQL Server’s Unique Identifier displays as a 36 character string (dashes and no “{}”) and stores a GUID as 16 byte binary value. There’s no argument that it’s nearly impossible (not mathematically impossible) to create a duplicate GUID, but how many data sources are going to outgrow a bigint (-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)) data type? That’s only 8 bytes, half a Unique Identifier. Hard disk space has gotten cheap, why do we care about data type size anyway?  In the article mentioned above, it’s mentioned that indexes created on Unique Identifier fields are going to perform slower than indexes built on integer fields. That statement hardly scratches the surface of performance implications with Unique Identifier indexes, and it’s all related to the size.

Pages and Extents

http://msdn.microsoft.com/en-us/library/ms190969(SQL.105).aspx

The above article explains how SQL stores data and indexes in 8KB pages. 96 bytes are reserved for the page header, there’s a 36 byte row offset and then 8,060 bytes remain for the data or index storage. If your table consisted of just one column, a page could store 503 GUID’s,  or  1007 BigInt’s, or 2015 int’s. Put another way, the smaller the amount of bytes in a row, the more you can store in one page. SQL Server doesn’t control where the Pages are written on the hard disk, the O/S and hardware decide. The chances of consecutive or sequential pages being stored in distant disk sectors increases with the more pages stored for each table or index in the system. As the number of index pages grows, the more out of sync they become with the data pages leading to index fragmentation.

Index Fragmentation

http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/

http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-2-size-matters/

Let’s recap what we have so far,

  1. GUID’s are randomly generated values without any sequential nature or restrictions.
  2. GUID’s are twice as big as the biggest integer data types.
  3. The larger a tables rows are the more pages have to be created to store the data.
  4. The more pages an index has, the more fragmented they get.
  5. The more fragmented the indexes get the more frequently they have to be rebuilt.

Clustered Index Implications

Clustered indexes set the organization and sorting of the actual data in the table. Non-clustered indexes created on a table with a clustered index have to be updated with pointer changes as records are inserted or deleted, or the clustered index value updated because these changes require the data pages to be resorted and new keys generated. SQL Server Identity columns of an integer data type reduce a lot of I/O overhead and SQL server processing because the rows are always inserted in the correct order. GUID values are almost never inserted in the correct order because of their random nature. Thus, with a GUID clustered index every insert or delete or update of that field requires data page reorganization, non-clustered index updates, more pages to be created, and increased fragmentation.

Steve Jobs Used to Wear a Suit and Tie

Posted by mysticstache on

Let’s talk about your appearance.

I’m sure it’s been said before that the only person who could dress like Steve Jobs was Steve Jobs. Conversely, you’re not Steve Jobs so don’t try to dress like him. In fact for those of you who have no memories of the 80’s Steve Jobs used to wear a suit and tie.

http://thenextweb.com/apple/2011/08/25/a-front-row-seat-to-steve-jobs-career-by-robert-scoble/

http://technology.ezinemark.com/steve-jobs-death-2011-most-famous-quotes-in-his-life-77371348ffa5.html

Your place of employment has a relaxed dress code, congratulations! Keep in mind “Relaxed Dress Code” means different things to different companies and people who work at the company. As an employer you have to strike an appropriate balance between the comforts of your employees, the image you wish to leave with visiting customers, and what kind of salary benefits are you willing to pay to attract staff that may be averse to wearing more costly attire. However, having an ambiguous or un-enforced dress code will only lead to easily avoidable challenges. Additionally, in larger organizations the enforcement of an ambiguous dress code becomes more tenuous. Leaving each manager or supervisor to his/her own interpretation of how to dress for the office will lead to great variances in what you as the owner or CEO thought should have been a uniform standard.

If the company that employs you has more than 20 employees there is most likely an official description available of acceptable attire for the workplace. You can safely assume you will not receive a reprimand for dressing in accordance to this document. So, do you want to be a face in the crowd, wearing what everyone else wears? Do you want to stand out by pushing the envelope of your company’s policies? Your attitude in these matters gives impressions of how you are going to approach your career while working for an organization. Impressions like “conforms to the rules” can be a positive or a negative depending on management and the industry you work in. “Shows creativity and energy” has the same challenges.

How do your customer’s dress when they visit your office? Nothing makes me feel more awkward than when a group of professionally dressed clients walks into a beautifully designed and maintained reception area only to be visibly accosted by an employee walking by in sweats and flip-flops.

Flip-Flops! I do not care how much you paid, nor how many plastic ornaments adorn them. If your footwear makes that annoying sound of heal slapping shoe, you are wearing Flip-Flops. You should not assume that everyone you meet has some desire to see your feet either. The only profession where Flip-Flops are acceptable is lifeguard.

Remember that first impressions are based mostly on sensory perceptions. You want the person who may decide where you go in a company to have a favorable first impression of you even if it’s just what they see while looking across a room at a crowed company function.

Consider where you are in the organization and where you hope to one day be. If you are a Jr. Software Developer and you wish to someday climb to VP of Software Development, how does the person currently holding that position dress? How do the successful salespeople dress?  I didn’t always think this way, but I found later in my career it was better to dress for the position you want and not the position that you have.  It may be an easier sell to promote you if you look somewhat like the person who currently holds that position. Decision makers may wonder if you have the wardrobe to start a new position if they’ve never seen you in the appropriate attire for it.

There are cultural differences to consider as well. You may be a fan of various European entertainments and wish to emulate their dress. Just because that overweight gentleman is wearing a bright yellow, skin tight polo with the collar turned up seems to pull it off doesn’t mean that you can. First, understand that outfit probably cost him $1,000.00 or more and you’re not going to be able reproduce it with the fare available at Wal-Mart. Second, you must realize that look is only barely acceptable in the more progressive metropolitan areas of the United States. I find nothing more entertaining than watching someone from NYC pushing the trendy envelope in a meeting of professionals in Atlanta.

If you have aspirations to be the next Steve Jobs, I’d suggest you follow his example in all stages of his career as you grow in yours. Until you’ve created the largest most profitable company on the planet, don’t abandon conservative professional attire for stubble, jeans and a black shirt.

First, Know What You Don’t Know

Posted by mysticstache on

This post goes under “Doing it Wrong”, and it describes a situation where a data model… got out of hand.

A colleague of mine recently said, “When developing a new software product the first 6 months of code are stellar. The team is really focused on getting everything as right as possible. After that first six months the pressure is on to produce something and start making money. Overnight the focus changes to the delivery date and every corner is cut.” No truer words were spoken of the project that is the subject of this article. In the beginning there was a dedicated team for the design and development of the data model to be implemented in SQL Server cobbled together from the more senior developers. This team had complete control over the data tier. They performed their duties with diligence, creating volumes of documentation and naming standards.

One small challenge, however, was that this team didn’t actually have the skills required for such an undertaking. This deficiency manifested itself in the reliance on a renowned modeling tool that was originally designed for Oracle. This tool included SQL server support in later revisions, but it was SQL Server support written by Oracle experts. One of the “features” of this product was automated generation of indexes for parents and children of Foreign Key relationships. The output of this feature created non-clustered indexes for clustered indexed fields. There are several Knowledgebase articles on MSDN explaining why this is a worst practice, but I’ll summarize by saying, “unresolvable deadlocks under load”. Oracle doesn’t have clustered indexes; in fact there are several differences between Oracle and SQL Server that drive professionals to specialize in one product or the other and many skills do not translate between the two platforms.

Then came crunch time; having been perceived as the “bottle-neck”, the data tier team was broken up. Each member joined a different development team where it was assumed they would perform the same function as before, but concentrated on their team’s realm of responsibility. Now there was no more coordination of the data model design and each team was given full reign to create whatever entities they thought they needed. The naming standards were abandoned, the index issues propagated through “cut and paste”, data duplication ran amuck, and you name it.

Well, actually none of this was any issue really, until the company wanted to go after larger clientele. This monstrosity of a data model worked perfectly for small clients. In fact, no one took any issue with running some of the most poorly written custom reports ever conceived against the transaction database during business hours, until that large client signed the bottom line.

Suddenly, the application was not performing well. The product could not service the demands of a large customer. What should this company do?

They still had the chance to do what they should have done in the beginning. Involve some real expertise, either by hiring more knowledgeable technical resources, or working with a consulting team to get the product started in the right direction.

After three years of unstructured, unguided software development, the issues aren’t going to be resolved by the team basically capable of layering on more weak bandages and spaghetti code. My colleague again, “It doesn’t matter how much you tune it or modify it; you’re not going to get a sub-compact to perform like a Formula 1. If you have, you’ve replaced so much of the original that you should have just bought the better car to start with.”

Many enterprises aren’t concerned with the long term effects of making short term staffing decisions. Focus is usually placed on large head count over high talent. Highly talented development professionals not only have a higher level of quality output, but quantity as well. Such resources will already know the answers or the correct solutions to challenges unseasoned employees will generally research and then make the wrong choices on.

Minimally, recruiting a devoted product architecture team will set new efforts ahead in the game just by relieving the burdens of keeping up with new technologies from the development staff at large. A qualified SQL Server professional would have spotted the index issue immediately. That same professional would have been able to design a data model that would allow for the growth of features without the expediential growth of data model entities in such a way that product delivery timelines aren’t jeopardized.

Notice also I’m speaking of a SQL Server professional. If even within the realms of database experts individuals tend to specialize a particular platform. Is it not reasonable that someone who excels at writing compiled code is not going to naturally have the same success in developing data models? It has been my experience that even the best web developer will struggle to keep up with a mobile applications developer developing mobile applications. Additionally, neither resource is highly skilled at writing the best formed Transact SQL queries. Trying to form a data architecture team out of developers who haven’t specialized in data architecture is a losing proposition.

In summary, pick the right tool for the job from the start; be that a modeling tool focused on the platforms you want to use or a professional specializing in those same technologies. If the staffing cost seems too much at first, you might have a great opportunity for a temporary jump start form a team of talented consultants. In all honesty it will be cheaper than trying to turn a sub-compact into a Formula 1 race car.

Thanksgiving

Posted by mysticstache on

As you sit down to enjoy your Thanksgiving meal today, please take a moment to consider those men and woman who made the difficult decision to sacrifice family time and the comforts of home in the service of others. Some may be standing in the chow line in a distant country protecting our freedom, some may be opening an MRE while protecting his or her brothers and sisters in the chow hall or mess tent. I’m thankful for the food I’ll enjoy, the time with my family this holiday, and those working so hard to keep us safe.

Welcome to Wisdom From the Stache

Posted by mysticstache on

I have over 15 years of software and database design/development experience relating to many divergent industries. I’ve been a party to, or a witness of, nearly every variation of the wrong way of producing technical solutions. On occasion, I’ve experienced the unparalleled joy of the right way. This blog will explore the truths, falsehoods, fallacies, pitfalls, misconceptions and best practices I’ve acquired in my journeys. I hope you gain some benefit from them.