Archive for February, 2012

SQL Server 2012 : SSMS Tips February 28th, 2012

Vinod Kumar

I generally have a tendency to try and explore what the tools have to offer when we work with them. Recently working with SQL Server 2012 Management Studio, I found some nifty little gems hidden and thought will be worth sharing. There is 20 mins of tips and tricks we managed to share with our course here. Feel free to nudge us if you would like to see the same.

Update: Read SSMS Tips Part II

Block Selection


The interesting tip is around, SSMS now has the ability to make block text selection.

1. Start SQL Server 2012 Management Studio.

2. Connect to a SQL Server instance and open a Query window.

3. Type several lines of text like “Select query can work in block”

4. Left click and hold + CTRL + ALT + Drag to select text. The text can be copied and pasted into a desired location.

Nice little tip to share.

Moving Status bar and color

Changing the color has been there for a while and I have used it before too. But the nice tip around moving the Status bar to the top beats me today with SQL Server 2012 :).


You will now see multiple options to change status bar location, status bar color, contents of the status bar and contents of the tabs. This allows you to customize what is important to you as the DBA when connecting to SQL Server. Try changing the color of the status bar, location of the status bar and contents of both the status bar and tab. The changes will take effect the next time you open a query window.

Loop through the Tabs

When you have multiple tabs or query windows available, how will you loop through the same? This is very simple.

  1. Use CTRL + TAB and this brings the navigator and you can move between windows.
  2. Another nice feature is to use CTRL + F6 to move between windows.


I hope these are some nice tricks to use. Feel free to share some of your yours. This is just the start to many more to come.

Update: Read SSMS Tips Part II

Continue reading...


SQL Server 2012 – Too many VLF and Performance February 21st, 2012

Vinod Kumar

Personally, this topic is discussed over various blogs on the internet and I will try to refrain from talking about the VLF (Virtual Log Files) basics. I got into this topic as I started to prepare for my talk at TechEd India 2012. The talk fundamentally is about some internals of SQL Server and some great stuff are getting lined up into that session. One of the concept is to talk about VLF but more importantly, we will try to talk about how we can performance tune the database VLF specifically.

SQL Server 2012 Error Logs Help

Have you ever felt the SQL Server Error Logs can help you with this concept? Well, welcome SQL Server 2012 – there are some interesting insights you get as part of the Error Logs. Keep an eye for this behavior. If you see SQL Server database is taking a long time to come online from the “In Recovery” state, one of the direction to look at is the number of VLF’s. Anyways, if you try to access the database now – it would come with an error of.

Msg 922, Level 14, State 1, Line 1
Database ‘TestDB’ is being recovered. Waiting until recovery is finished.

The database eventually after 2-3 mins comes online (in my scenario) even after a proper shutdown. Now to investigate the same, just reading the SQL Server 2012 Error Logs gave away some interesting comments.

2012-02-21 21:21:13.530 spid29s      Database TestDB has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
2012-02-21 21:21:21.810 spid5s       Recovery is complete. This is an informational message only. No user action is required.


Performance Tips in Error Logs

This is awesome information which talks about the possible performance problem and the resolution steps too. To view the VLF’s feel free to use the DBCC LOGINFO() command. Pinal wrote a simple post around this. In my scenario, I had simulated with some 21000 VLF’s :). Also the error message tells you the exact steps required to solve the problem too. Shrink and then grow differently. This is exactly the steps you might do even today. Just to wrap up, when do these messages come up? As I tested, the messages come when –

  1. SQL Server starts/restarts and when the database initializes.
  2. Database attach process

With performance penalties from backup, replication, restore, startup etc – This is a great addition to SQL Server 2012 which I felt was worth talking. At TechEd India 2012, don’t miss to attend my session where I talk about many of these internal hidden gems as part of the talk.

Final words, do drop in your comments if you are planning to attend TechEd India 2012 (and my session :)) and don’t forget to tell me what area’s you want me to explore during the Internals Session (will try to squeeze them too).

Continue reading...


Big Data – Big Hype yet Big Opportunity February 14th, 2012

Vinod Kumar

“Big Data” seems to be the buzz word everywhere and the number of blogs on this very topic has been exponentially growing. Let me take a step back to understand what to expect. Even at India TechEd 2012 we plan to cover this very topic under the Architect track. Personally, I am really excited to see this session discussed from multiple angles. As budding Architects there are tons to look out for. Refer my previous post coming your way on Architecture. So at TechEd India we will have speakers discuss the problem statements and the possible solutions with recommendation on architecture. In this blog post, I am surely talking about some of them – I am not going to steal the awesome content they are lining up :).

Where does Big Data fit? Datasets that exceed the boundaries and size of normal processing capabilities forcing you to take non-traditional approaches.

Fundamental Problem

I was wanting to drop this topic before and strangely figured out that the SQL Community are anyways running the TSQL Tuesday on this very topic. Now with announcements at SQL PASS and investments of Microsoft also in this space – this is huge deal.

When we talk about Big Data we are fundamentally looking at 3 basic dimensions:

  1. Large Data (In ranges of Peta to exabytes and more)
  2. Complex Data (Write once – read many times, Dynamic Schema data)
  3. Unstructured data (Text mining, Images, Videos, Logs)

And these are the same problems we currently have in the industry when it comes to database / data store systems. Look at systems now with RFID tags, Web logs, sensors, medical images, telecom, public sector databases etc all are grappling with this problem.

Where to start?

Hadoop started as a way to quickly process Web log files. Web 2.0 sites were finding that they were accumulating logs that contained valuable click information and user behavior data. As an alternative to parsing log data and storing it in a relational database, Hadoop emerged as a way to keep the log files in their original format and allow processing and analysis.

Though the basic concept is simple and powerful, let me link to some basic explanation to the post Pinal Dave wrote today. He takes a stab at simply demystifying the basics on Hadoop, Pigs, Hives, MapReduce. Feel free to read more on them:

  1. Pig – A high-level language that lets non-programmers use Hadoop
  2. Hive – An SQL query implementation for Hadoop
  3. HBase – A key/value store for Hadoop

One other resource I would like to point in this context is Cloudera from learning resources. Cloudera is a for-profit company that produces integrated, tested, and commercially supported Hadoop releases. Look at some of the other extensions they support as extensions – some new releases make an interesting read.

  • Hue – Hadoop user interface
  • Sqoop – tool to import relational data
  • Flume – tool to import nonrelational data
  • Oozie – workflow engine and many more.

Relational or DW Database Obsolete?

Personally, I don’t think we are talking about this-or-that Boolean approach here. There is something that makes these concepts of Hadoop interesting and viable for organizations to start considering. Let me call out some of them (not exhaustive though)-

  1. Hadoop clusters can be on x86 commodity hardware
  2. No need build cubes for predictive analysis of large data
  3. Relational DB have their own limits on scale-out and scale-up scenarios
  4. Addition of scale-out options easy with Hadoop

With this steady stream of data, is this what the industry is also looking for? Check the McKinsey Global Institute – Big Data: The next frontier for innovation competition and productivity paper and the numbers are bind blowing.

  • 1.5 million more data saavy managers in the US alone
  • 140,000-190,000 deep analytical talent positions
  • €250 billion Potential annual value to Europe’s public sector
  • 15 out of 17 sectors in the US have more data stored per company than the US Library of Congress

Read the whitepaper and there are many more statistics that seem to make this Big Data really Big. Now take examples of big data patterns and sites like facebook or twitter with millions of data stream coming every minute and you want some analytics. Does this Big data architecture qualify here? or do you need a different architectural choices? Well, don’t forget to tune into our India TechEd Architecture track for the details :).

Microsoft Integration Points

From Microsoft, you are going to see lot of work to happen as it is data. Applications like Excel, PowerPivot, Power View, SQL Server Analysis Services, SQL Server Reporting Services are some of the integration we have seen in the recent past at SQL PASS. More about this can be read from the MS Big data home site.

Channel-9 Video: Lynn Langit and Dave Nielsen discuss "Big Data" in the Cloud

MSR Research Paper on Big Data – gives a nice read

Another Research Paper: Big Data and Cloud Computing: New Wine or just New Bottles?

What we can see is, as we get to know this more recent phenomenon of Big Data even the cloud seems to embrace it with two hands. You are going to see some serious integration across the platform and it is a great sign for us –

  1. Connectors for Hadoop, integrating it with SQL Server and SQL Sever Parallel Data Warehouse.
  2. An ODBC driver for Hive, permitting any Windows application to access and run queries against the Hive data warehouse.
  3. For developers, well now addition of JavaScript Layer to the Hadoop ecosystem is very compelling.
  4. An Excel Hive Add-in, which enables the movement of data directly from Hive into Excel or PowerPivot.

Where to start

I highly recommend using Apache Hadoop on Windows WIKI – please bookmark it. Now as a Microsoft ecosystem, there are 3 other interesting pages for reference you don’t want to miss.

On-Premise Deployment of Apache Hadoop for Windows

Windows Azure Deployment of Apache Hadoop for Windows

Windows Azure Deployment of Hadoop on the Elastic Map Reduce (EMR) Portal

This forms a great ecosystem from on-premise to the Cloud. As part of the whole bundle of links here, couldn’t resist from linking Rob Farley who has been kind enough to point out that Big Data now features in 24 hours of PASS too. Nice timing to talk more and more about Big data.


Personally, I see there is tons of learning with Big Data coming our way and 2012 will start the same conversation that we started about BI in Year 2005 timeframe. So get prepared for some Big Hype, Big Challenges, Big Insights and a Big Year of Big Data coming your way.

Continue reading...


Productive Meeting–DO’s and DONT February 8th, 2012

Vinod Kumar

As part of our work, meetings seem to have become an integral way we perform our job. But it is critical to understand the importance of doing meetings on the first place. Have you got a bubble in a middle of a meeting –“Why on earth am I out here?”. In mature organizations, the problem is in the fact you have weekly status meeting, project meeting, Quarterly meeting, monthly meetings, business update meetings and so on. Trust me, more than half of it can be of no significance to you as an individual. Knowing the state of business is critical, but knowing your role and contributing to the business is also critical. Let me take a stab at some of the do’s and don’t when it comes to meetings based on my experience.

Take care of the minutes and the hours will take care of themselves.

Prepare, else prepare to fail

It is a common phrase that if you fail to prepare, prepare to fail. And meetings are no exception. If you are calling for a meeting, make sure you have a proper plan to execute and talk with the members. It has to be clear, concise and the give-gets properly outlined. Walking into a meeting with no plan could make you appear disorganized and uninterested in the topics of discussion. And, your teammates will feel like you don’t value their time.

At the same time, be flexible to constructive and productive ideas that might hijack the meeting. Keeping a closed door to the meeting structure can sometimes restrict creativity and stop people from opening up. Sometimes go with the flow, people do know when it is zero plan unorganized method versus a spontaneous discussion session. Irrespective of the style, know the limits and time constraints as part of the discussion.

People who enjoy meetings should not be in charge of anything. – Thomas Sowell

Focus and don’t get distracted

When I started working in the industry more than a decade back, this was not the case. But this tip is more relevant for the current generation who have tons of choices to get distracted. Answering a phone call, emailing or text messaging have become a way to get distracted easily. These are clear signs of disinterests and can be really frustrating for the meeting organizers.

I personally like to silent my phone before and during the meetings. I even refrain from picking them up till the meeting gets over. There is always choice and most importantly someone has taken time to share something with you, give them the time and attention that they need.

If there is burning issue and you are expecting a call, just inform the organizer of the same well before hand and walk out to attend the call so that the meeting does not get distracted because of the phone ringing at your end.

A meeting is an event where minutes are taken and hours wasted. – James T. Kirk

Some more non-negotiable

  1. Honor time of others, be there on time. Those in the meeting will be appreciative that you respect their time and, will in turn, respect what you have to say. Sometimes running a few minutes over is unavoidable, but consistently keeping your audience waiting is disrespectful of their time.
  2. Avoid offending anyone during the meeting. Don’t get into a conversation just with one person but try to get a gauge of the situation and address all the audience.
  3. Avoid eating during meetings, it most of the times distracts and annoying too. If you keep munching a packet of chips during the meeting, it will surely annoy the person sitting next to you. Unless this is a breakfast or dinner business meetings where eating is encourages during the talk – my suggestion refrain. It is not for you, but to respect the person next to you :).
  4. Learn how to run a meeting. There is a subtle difference between running a meeting and throwing a party. Engage with people and get opinion from all to get to an common consensus. This is critical.
  5. Take notes during the meeting and follow up post the meeting. This is critical because you want a proper closure to the activities and make people aware of what they need to do post the meeting. Assign action items at each meeting (who commits to do what and by when) and do so in writing.
  6. Before any meeting, communicate your own thoughts in e-mails and casual conversations. If you see to many emails floating, call for a meeting and keep announcements brief and crisp – discuss and then close the activity.
  7. Meeting organizers sometimes feel they are inspiring and know everything, the fact remains completely opposite to what reality is. So be critical about the whole meeting process and design a meeting where the attendees get an opportunity to contribute too.
  8. Look for signs of agreement – seeing nods, smiles and eye contact does measure some acceptance. If people think you are upset, they might just accept and agree to what ever you say. This is a bad sign because post the meeting, they forget the meeting, ignore the messages discussed and more alarming – ignore the idea shared.
  9. Don’t be a slave of the meeting duration. If the meeting gets over in 25 minutes, give back the rest of the time.  Don’t waste time as it would be demotivating.
  10. Avoid death by Powerpoint – get the message, discussion points and closure in simple steps.

Meetings are everyday activity for many of you who are reading this article. I just wanted to air some of the facts and things that I personally felt were important and non-negotiable. Hope these made sense and do drop in a line if you had more additions.

Continue reading...


TechEd India 2012: Architecture Track February 6th, 2012

Vinod Kumar

Yes, you read it right. I will be at the TechEd 2012 India event (March 21-23, 2012) in Bangalore again and this is one event that is exciting for me every year. This will be the 9th year that I will be presenting here at TechEd India. It is nostalgic and brings fond memories of connecting with each one of you out there.

Just l try to prepare something completely new and interesting every TechEd. In the past 3 years on the SQL Server track I have become the defacto person to deliver a session around Backups and Recovery. This year I will also be at the Architecture Track delivering a session. Tough the specifics of the session will come out soon on the website, I wanted to take a moment to give you a sneak preview of what you can expect coming your way in that track.

Being as part of Microsoft – MTC Team I have had the privilege of meeting a number of customers and solve their technology and architecture problems. Most of them are generic learning and these will be discussed at length in the session. We will touch on some of the basic concepts of performance and scalability in mind. Below are some of the high-level discussions you can expect from the session.

  1. If you care about speed & scalability, your web service will always check the distributed cache before querying the database. 
  2. If you have to get your answer from your database, make sure to add the answer to your distributed cache concurrently with returning it to the user. 
  3. Don’t ever call your primary database directly. Call one of your horizontally-scaled relational or NoSQL replicas.
  4. Use download-only replication to create as many read-only replicas as you need. Your replicas can contain complete copies of the primary database or just shards of data as appropriate. (SQL Server 2012 – AlwaysON technologies can help here)
  5. When sending data up from the clients to your servers, never insert, update, or delete directly against the primary database or the replicas. (think Scale-outs)
  6. Have your web services drop that data in a queue and let worker processes update the distributed cache and perform those DML operations against the primary database. 
  7. Use peer-to-peer replication to maintain more than one read/write primary database at the top of your hierarchy. This is architecture problem and not a database deployment problem.
  8. Remember to create and maintain more than one hierarchy in different geographies using the same peer-to-peer replication to provide even more availability, scalability, and performance to your mobile users all around the world.
  9. Have clear expectation on what works on mobile devices as an functionality. Using plugin frameworks are great, but use them with caution if scalability and performance is needed.
  10. Your mobile users will be connecting to your service via slow, intermittent, unpredictable wireless data networks so always use small, efficient wire protocols like REST and data serialization like JSON.

So now that you get the drift where we are taking this conversation. Are you excited to be at the session? I will also be at the database track doing some session, but these sessions and I will post what you will expect from those sessions too. BTW, if you are excited with these sessions, there are sessions on Architecture track on Big Data and beyond too – so much to look forward and not to be missed.

Do drop me a line if you will be at the event – will surely make sure to pass an HI :).

Continue reading...