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.

Too-Many-VLF

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.

Finally

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...


 

Top 5 things while Mentoring? January 28th, 2012

Vinod Kumar

This is a very sensitive topic because I have had people come to me and asked for mentoring – career or business related. I am all ears and out there to help as many people as I can, most of the time it is just the direction I give and want the passion to take over to guide them rest of the way. So that has always been my style. Taking a step back to think, what is it that I look for in my mentors and what do I look for people who come to me with this request? Personally, I am gifted to have met tons of interesting people and have made many as my mentor. The list is literally a big one – trust me :).

Mentor is someone who praises when you deserve it and gives heads-up when trouble is coming probably eve before you even have noticed it.

I see multiple qualities in a mentor to guide me. These may not align to your needs but I can assure you that these are non-negotiable for me. These are my Top 5 -

Take time for you

Networking isn’t just time to find your new customers, it is also a way to find new mentors if you like. One who takes time in writing a note to you, remembers you when you need them are critical aspects for a great mentor. More importantly, you want someone who knows the market out there, trends and is willing to point you in the right direction.

Mentor: Someone whose hindsight can become your foresight.

Knows your needs

As you grow the mentoring needs change, you change personally and professionally – knowing someone who can properly guide you in the direction you want to go is critical. Take time clearly to understand what type of mentor you want, understand where you want to go from where you are. What are your key plans, strategies to reach there? Now a mentor has clear understanding to guide you with the intermediate milestones to reach that destination.

For many people who have come to me, I ask them where they want to be in 5 years time. The answers vary from a blank face to something like “I want to be ***** in 5 years”. Now if you have such goals, who are your idols? What has been their path? If you have a reference and you know how they got to where they are – this give you a good indicator to what steps you need to take. Isn’t this a simple strategy? There are no shortcuts to this exercise.

I can accept failure but I can’t accept not trying.

Listen and be Open to ideas

Given the enthusiasm of people, I often see people find it hard to stay calm and listen for the conversation to finish. Listen carefully and to find a mentor this is critical. Pay attention to the choice of words they use, how real they are, and what smart choices are they giving you. These are critical because you never know what ideas might spark from that 5 minutes conversation.

Do your homework, more importantly be open to receiving. If you come out as someone who knows everything the probability to find a right mentor is down the drain. Don’t fall victim of your own knowledge (or lack of it).

The happiness of your life depends on the quality of your thoughts.

Look beyond the traditional

We are in the age of internet and connected world. So don’t always derive source of inspiration from people around you. There are interesting speeches, videos from TED, books and of course the family hero’s around you from whom you can derive immense inspiration from. You don’t have to meet people to get that insights, but given an opportunity take the extra step in meeting them. The world of social networking has surely brought the divide between people a step closer. Don’t miss out on these channels of Facebooks, Twitters etc !!!

Thank in advance

People help you intentionally or unintentionally, let them know of that help. Someone mentors not for money but for the good will in seeing you successful.  The very thought that they want to help someone grow is noble – hats off.

I know there are more to the points I have discussed. Please feel free to share your experiences, I would surely love to hear them. Thanks for reading this far.

Continue reading...