SQL Server 2008 Compression Features

SQL Server 2005 introduced the concept of variable length fields to enable space conservation by using only the storage needed for the value even if the declared storage is more than that. SQL Server 2008 takes storage space saving not only a step further, but a giant leap ahead. This is especially important since databases in today’s times are reaching sizes of Tera bytes and beyond. I will be covering some high level concepts behind the compression features introduced in SQL 2008. For more detailed reading, you can follow the links provided at the bottom of the post.

Essentially, SQL 2008 provides 2 ways to compress the data:

• Backup Compression
• Data Compression
• Row Compression
• Page Compression

Backup compression enables shrinking the size of the backup files when they are written to the file system. Since almost all important databases have a backup strategy in place, this is a feature that should be applicable in a wide range of scenarios. It can be enabled either on an individual basis using an option in the Backup command or using the system SP sp_configure. Restoring would be the same regardless of whether the backup is compressed or not except the fact that older versions of SQL Server would not be able to read the compressed backups.

Data compression is a slightly more complex topic as compared to Backup compression. Let me first explain the 2 compression techniques in Data compression.

• Row compression uses variable length storage technique for all data types, including fixed data types. For the application, it would still seem that the fixed types are implemented as such but under the hood, SQL Server stores them using variable storage. Row compression doesn’t impose a very big overhead on the system and should generally be enabled on all tables and indexes
• Page compression is a slightly more complicated beast. First and foremost, when you enable Page compression, Row compression is enabled automatically. Additionally, Page compression uses something called Column prefix and Page dictionary to provide a greater degree of compression.
• In Column prefix, SQL Server stores the common values at the beginning of values in a column in the CI(Compression Information) section and the actual cells will have pointers to the CI block
• For Page dictionary, a 'dictionary' of repeating column values on the given page is created in the CI structure, and again, pointers to the value in the dictionary are left in the original column value location

Page compression involves additional overhead in case of querying as well as inserts and updates. So it must be enabled selectively. Generally, it should be enabled only for tables and indexes with very few updates and/or high number of scans.

SQL Server also provides some DMVs and system SPs to help developers/administrators to get a better understanding of the impact and possible gains from enabling compression. Some of these are:

• sp_estimate_data_compression_savings - Allows you to estimate the savings you will be able to perceive for a given table/index
• sys.dm_db_persisted_sku_features - To determine whether a database is using compression
• data_compression_desc column in the catalog view sys.partitions - To determine what is compressed, and how (row or page)
• sys.dm_db_index_operational_stats - To determine the number of Scans (S) and Updates (U) on a table

Some additional points about SQL Compression:

• The data compression feature is available in the Enterprise and Developer editions of SQL Server 2008 only
• It is recommended to compress one table, index, or partition at a time in most cases because compression requires additional temporary structures to be created which impose space and processing overheads
• It is also recommended to compress smaller objects before larger ones so that the space freed up by the smaller ones can be utilised as temporary storage when compressing the larger objects.
• After data compression has completed, the space saved is released to the respective data file(s). However, the space is not released to the file system, because the file size doesn’t reduce automatically as part of data compression. There are ways to release the space to the file system, including using DBCC SHRINKDATABASE and DBCC SHRINKFILE

As mentioned earlier, I tried to cover most of the high level concepts that one would need to understand for using compression. There are some excellent articles on MSDN that cover various implementation details and performance benchmarks regarding compression. I am providing a couple of links below, which you can use for reference.

Data Compression: Strategy, Capacity Planning and Best Practices

Katmai (Sql 2008) - Data Compression (including Backup Compression)

Modify SharePoint website Quick Launch

So after a series of non Technical posts, I am back at writing some technical ones, starting with this one. I was in a Solution Manager role for the last 6 months so I wasn’t getting my hands dirty with tech stuff as much. But now I am back into a Developer role so hopefully there would be many more tech posts from now on.

To start with, I would like to talk about a very basic requirement in SharePoint – adding new links to the Quick launch or modifying the existing links. For the uninitiated, Quick launch is the set of links we get on every page (as long as Quick launch is enabled for a web page; I will be talking about enabling Quick launch for new pages in another post) on the left hand side, just below the title of the site. Commonly, you would have links like Shared Documents, Tasks, Calendar etc. in here by default when you create a basic site. Now you might not want some of these links to appear because your site won’t be using a particular feature, for example, you don’t need the calendar and hence, having the Calendar link in the Quick launch would be meaningless. Or suppose you have created a new page of your own and want it to appear in the Quick launch so that users can visit this page from any page they are currently browsing. Both these requirements can be easily accomplished by editing the Quick launch section.

Here is how you can play around with the Quick launch:

1. From any page, click on Site Actions in the top right corner and then click on Site Settings from the drop down that appears(you need to be an admin to have this option available to you)
2. In the Site settings page, under the Look & Feel section, there would be a link either called Navigation or Quick Launch, depending on the version of SharePoint Server you are working with
3. On clicking this link, a page would open that would show the existing Quick launch items along with options to add a heading, a new link, edit the existing links, change the order of links etc
4. After making the changes, click OK to save the changes and you should be seeing the changes on any of the pages with Quick launch from now onwards

Wanted : Some originality in Indian reality TV

Ever since the craze of reality shows on Indian television has picked up, we have seen time and time again all the channels blatantly lifting concepts from US or UK based shows and creating Indian versions off them. Be it American Idol to Indian Idol, Big Brother to Big Boss, Fear Factor to Khatron ke Khiladi (can’t blame you if you don’t know when this was launched and when it went off air), Who wants to be a millionaire to Kaun banega crorepati, Are you smarter than a 5th grader to Kya aap paachvi pass se tez hai and so on.

There were also other stupid/cheap shows which Splitsvilla, Sawaal dus crore ka, 10 ka dum (I guess Salman himself is funding the producer to keep the show running!!!) etc, all in the name of reality television. In case, these shows have also been ripped off some other shows do let me know.

Till now, according to me, there have been very few shows which have been likeable and logical. The first one is Sa re ga ma pa which incidentally is the father of Indian music reality show craze, Great Indian laughter challenge (minus Sidhu), Kaun banega crorepati (ya ya, I know I have listed it above as a rip off but come on, it had Amitabh Bachhan hosting the show!!!!) and the Bournvita quiz contest. The main reason why I liked these shows was because they weren’t too outrageous and they stuck to the purpose for which the show was designed.

I sincerely hope that Indian serial makers can think of some creative ideas for the reality shows of the future and stop being the Pritam of reality shows.

PS : I wouldn’t really mind reduction in the number of reality shows either, there have been just far too many shows being started by people in the hope that they will do well by riding on the wave of success of reality shows

Golden rule for working out in the gym

If you are one of those who do weight training i.e. lift weights in the form of dumbbells or barbells, then this is something you should keep in mind come what may :

YOU DO NOT NEED TO INCREASE THE WEIGHT YOU LIFT WITH EVERY SET

Often, people forget that there is a second, and equally important parameter that can be varied while exercising: Number of repetitions.

Ideally, one should start with a light weight and and gradually increase the weight over 3 or 4 sets. The breakup should be something like below. The number in brackets signify the breakup in case you are doing just 3 sets.

• Set 1 – 15 repetitions and Stamina building (same)
• Set 2 – 12 repetitions and Stamina building (12 repetitions and Muscle building)
• Set 3 – 10 repetitions and Muscle building (10 or 8 repetitions and Muscle building)
• Set 4 – 6 to 8 repetitions and Muscle building

Whether you do 3 sets or 4 sets depends on a number of factors, primary among them being,

• Type of exercise
• Exercise routine
• Your capacity
• How much time you have on hand

Very often I have seen people increasing the weight repeatedly even when they are struggling to lift lighter weights. I understand that there is always that inner push to increase the weight so that next time you have a conversation with your friend about how much weight you lift, you stand a chance to claim lifting more weight than him. However, doing this is futile as it would, more often than not, lead to improper postures while exercising (for example, bending the back to use it to lift the weight when doing dumbbell biceps) and greatly increase the likelihood of injuries.

Hence, next time onwards, when you start doing weight training, always keep this point in mind and choose weights accordingly.

Effects of Internet on developers : Positive or negative?

I have been thinking recently about what effects the explosion of information on the Internet has been having on software developers and how it has been shaping their skills. On the one hand, one can argue that it has helped them immensely in improving their knowledge and broadening their horizons. It has also helped them in getting answers to their problems quicker than ever, specially with forums like StackOverflow and having an abundance of search engines like Bing and Google to get relevant links at the click of a button.

On the flip side though, it has also in some ways made it less likely for the developers to feel the need to investigate into their problems and do thorough debugging and troubleshooting. All they need to do now is to copy paste the error in their favorite search engine and get links to web pages directly giving them the workarounds and resolutions. Even though this does improve their productivity in the short term, over a period of time it always helps for developers to troubleshoot the problems they face. This would enable them to understand the system better, get familiar with the quirks that almost any system typically has and also, the researching that goes on in resolving the problem also helps them to learn more. This blog post also talks about a similar problem being caused by search engines but for people in general, not specifically for developers.

I think that in today’s times, where developers’ time comes at a premium, there has to be a middle ground that needs to be chosen by the developers. It certainly helps and makes sense to make use of the search engines and developer forums to get answers to get unblocked and resolve problems. However, instead of doing a search the moment a problem is encountered, developers must try and see if they can find the resolution themselves and invest time in troubleshooting as much as they can afford to. Only if they cannot get a resolution and time is running out, should they look it up on the Internet.

What do you think developers should be doing? Would trying to troubleshoot it themselves help or should they just give importance to saving time and resolving problems as soon as possible?

Why India lost in the T20 World cup

By now, a lot has been said about India’s unexpected loss in the group stages of the world cup. A lot was expected from the Indian team and they were probably the favorites along with South Africa and Sri Lanka. However, I had my doubts right from the beginning and I had shared this with my friends then itself. Unfortunately, mine and many Indian fans’ worst fears came true.

There have been many excuses flying around post the debacle, from Indian coach, Indian captain, BCCI and the innumerable so-called experts. Some of the blame has been put on the IPL for tiring Indian players. However, the people making this excuse (read Gary Kirsten) don’t seem to realize that most of the big players from the other major teams were also part of IPL and they also put in just as many hours on the field. According to me, fatigue was not the reason for India’s defeat. Some of the reasons that I feel led to India’s loss are :

• Insufficient batting prowess : India was missing some of the big players and hardly had any experienced, consistent performers on whom we could have banked upon. The established players were also woefully out of form
• Lazy fielding : Our fielding was nowhere near the standard set by teams like South Africa, New Zealand etc. Eventually, these misfields led, in one way or another, to the defeats and cost us dearly (remember Yuvraj misfielding a wide which went for a boundary to concede 4 byes in the last over against England)
• Dhoni : In the recent past, a lot of praises had been showered on Dhoni, both as a player and as a captain. He was called the man with the Midas touch and what not. However, those were the times when whatever he did luckily turned out as planned. However, there were many tactics that came unstuck in this world cup. The worst moment was in the match against West Indies. Normally, when India is batting well, Dhoni promotes himself to get the most of the remaining overs, sometimes even coming in at one down. However, in this match, where the West Indian pace bowlers had scared the living daylights out of the Indian batsmen, he chose to protect himself and Yuvraj and sent in Jadeja……bad move Mr.. Dhoni
• Inability to handle quick, short pitched bowling : This has long been the bane of Indian cricket and once again, it has been exposed badly. In both the super league matches, we were just not able to survive, let alone score off,the quick short pitched bowling unleashed by the West Indian and English quick bowlers
• Complacency : I always get this feeling that the senior Indian cricketers get complacent and perform mostly when their place in the team starts to come under pressure. They are not worried about how their performance, or the lack of it, is going to affect the team nor do they seem to be affected one bit by India’s losses.

I won’t claim to have predicted India losing in the league stages itself but I certainly wasn’t in agreement with the people predicting India to win the world cup. Unfortunately, my feeling came true and we lost….and very badly at that.

Indians desperately need some Etiquettes

I have been frequenting some crowded public places (read airports and train stations) over the last few weeks and there have been some pretty frustrating moments during these visits.

In India, wherever we go, people just don’t have the sense of following queues; everyone wants to get in at the head of the line while being completely oblivious to the fact that there are tons of people standing behind in the queue, patiently waiting for their turn to come. If you don’t have queues then it becomes a bit easy because you know that you should expect chaos. But people don’t behave even when everyone is standing in a queue. Two places where this stood out the most was

• When going for security checks in the airports, people want to put their stuff the first on the conveyor belt so that they can jump the line and go first for the security check. Come on, going first in the security check queue won’t make your flight take off early and make you reach your destination ahead of time
• I was travelling by the Delhi metro recently and there were operational staff at the station specifically there to make people waiting for the trains to stand in queues and get into the train systematically. But no, even here the typical Indians want to jump the queue, to the extent of pushing and shoving the people standing in the queue just to get into the train soon.

These are just 2 of the numerous instances where people have pissed me off by their ignorant and uncivilized behavior. If you go to any western country, people are so courteous. They invariably give way and say sorry for the minutest of physical contacts. Out here, pushing and shoving is more like a norm in public places. I feel these are small things that contribute to the perception of a nation of being civilized as well as act as a measure of how hospitable the country is. It is high time we wake up and start being polite, courteous and civilized to our fellow citizens.