SQLAuthority News – Community Tech Days – TechEd on The Road – Ahmedabad – June 11, 2011

TechEd on Road is back! In Ahmedabad June 11, 2011!

techedonroad1 SQLAuthority News   Community Tech Days   TechEd on The Road   Ahmedabad   June 11, 2011Inviting all Professional Developers, Project Managers, Architects, IT Managers, IT Administrators and Implementers of Ahmedabad to be a part of Tech•Ed on the Road, on 11th June, 2011. We have put together the best sessions from Tech•Ed India 2011 for you in your city. Focal point will be technologies like Database and BI, Windows 7, ASP.NET.

REGISTER HERE!

Venue:
Venue: Ahmedabad Management Association (AMA)
Dr. Vikram Sarabhai Marg,
University Area,
Ahmedabad, Gujarat 380 015

Time: 9:30AM – 5:30PM

The biggest attraction of the event is session HTML5 – Future of the Web by Harish Vaidyanathan. He is Evangelist Lead in Microsoft and hands on developer himself. I strongly urge all of you to attend his session to understand direction of the web and Microsoft’s take on the subject.

I (Pinal Dave) will be presenting on the session of SQL Server Performance Tuning and Jacob Sebastian will be presenting on T-SQL Worst Practices. Do not miss this opportunity. Those who have attended in the past know that from last two years the venue is jam packed in first few minutes. Do come in early to get better seat and reserve your spot.

We will have QUIZ during the event and we will have various gifts – Watches, USB Drives, T-Shirts and many more interesting gifts.

Refer the agenda today and register right away. There will be no video recording so come and visit the event in person.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL Azure – Article Links by Dhananjay Kumar

DJ SQL SERVER   Beginning SQL Azure   Article Links by Dhananjay KumarPassion is very important but it needs to be focused on right direction. I just remember conversation with my friend Dhananjay Kumar (MVP) two months ago. He writes on various subject. While discussing technology, we discussed that he will write on SQL Azure and related technology. Since than we did not discuss about this subject later. Yesterday while searching on the subject of Azure, I landed on his site and when I looked at how much he has written on this subject, it did surprised me. I must express the progress of Dhananjay is remarkable and his understanding of the subject is very deep. The best part of his writing is his tutorial style writing, which enable any beginner to learn the subject in no time.

Windows Azure links

http://debugmode.net/category/azure/

If you are a .NET developers and want to start with SQL Azure, read below

http://debugmode.net/category/sql-azure/

When I asked him why he did not tell me that he has written so much on this subject – he answered it calmly with gentle smile – ‘Well, I am in the cloud!’

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Attending MVP Open Day – May 2011

The MVP Open Day is an exclusive event for Asia Pacific & Greater China MVPs. MVPs from the region gather and have great time together. It is mix of education, fun and networking. I have previously written my experience over here.

SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Fun

SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Business

SQLAuthority News – Author Visit – South Asia MVP Open Day 2008 – Goa – Group Photo

This year again, I am going to visit MVP Open Day which is scheduled from 19th May to 22nd May. Every time I attend this event I make a point to maximize my time spend there. I was recently asked by my friend what are my tips to get maximum out of the event.

There is no one size which fits all. From technology to technology and from person to person these tips can vary. However, here is I used to do to maximize my time. You can read the list and decide for yourself.

DO:

  • Do make a list of the people you MUST meet and send them email earlier requesting their time and attention.
  • Do create list of the topics you want to discuss with product team, fellow MVPs and community leaders attending the event.
  • Do express your feeling about product to product team without hesitation.
  • Do attend all the technical sessions related to your technology.
  • Do meet new people when there is no session related to your technology.
  • Do share your business card when introducing yourself. (Excusing for not have your business card handy is usually lame excuse)
  • Do understand that most of your sessions are usually NDA so no sharing of the information outside the group.
  • Do attend all the community and network building parties – they are fun way to know people.
  • Do take photos of emails, phone numbers, links shared by attendees, presenters as that is the most accurate way to capture data.
  • Do make others aware what your current profession is as well what you do. You never know when you need each other.
  • Do blog, tweet your experience it is fun way to document our learning.
  • Do NETWORK, NETWORK and NETWORK – this is your chance!

DO NOT:

  • Do not over solicit your business at this event. Usually people do not like it. They will reach out to you themselves in need. (Read Suprotim‘s comment below)
  • Do not verbally abuse compete technologies in public. Try to balance your views with reasons and data points.
  • Do not skip breakfast – it is not only most important meal of the day but there is great conversation at the breakfast table.
  • Do not drink too much – no one likes to drag you to your room :)

This guideline is to just give my perspective and no way to constraint the experience at the event for anyone. The goal is to have fun and build relationship with your fellow MVP.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Cartoon Challenge – 140 Character Winner is Here

Earlier Idera has announced contest where participant can win Windows Mobile Phone by writing 140 character. Here is the details of the contest SQLAuthority News – Win Windows Phone from Idera in 140 Characters – A Cartoon Challenge of SQL. We received more than 200 comments on the blog post and more than 250 qualifying entries. It was not possible to pick winner out of all those entries. I reached out to good folks at Idera for helping me select the winner.

After going back and forward and with lots of revision we come up with winning entry. Idera has also selected five more winner who will get surprise gift from Idera.

Here is the winning entry who will win Windows Phone 7 (limit USD 500).

MarlonFRC
WHAT? I told you, change your RDBMS to SQL Server 2008 and you can use Idera Tools for tunning.

Here are runners-up entries. Idera will send them surprise gift.

Jose
SELECT TOP 1 Name
FROM tools
WHERE Vendor = ‘Idera’
AND Type = ‘Performance’
ORDER BY Rating DESC

Wendell
Holy shish kabob Pinal, better purchase Idera and sign up for SQL Smarts Enewsletter!

Srinivas
Why fear when SQL Doctor from Idera is here? With SQL Doctor, we point, click, and cure. Problem solved!

Harlan
Even House isn’t doctor enough for this,… we need Idera’s SQL doctor. STAT!

Rob Rohr
My social life is running slowly, but at least there are tools to solve your problem.

Thank you all for participating in the cartoon challenge. We will have more such a quiz soon.

Before I end I would like to specially mention the entry of John Sansom. He does not qualify for winning entry or even runners-up as his entry was of 160 character, however, his entry is my personal favorite.

“Got no idea? You need Idera! We’ve got just the right tools for all you SQL fools.Performance tuning, monitoring, backups or development.We’ve got you covered.”

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

Read my complete experience series of TechEd 2011, Bangalore

TechED 2011 – Bangalore – An Unforgettable Experience – Day 0
TechED 2011 – Bangalore – An Unforgettable Experience – Day 1
TechED 2011 – Bangalore – An Unforgettable Experience – Day 2
TechED 2011 – Bangalore – An Unforgettable Experience – Day 3
TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

Day 4 – March 26, 2011

I woke up again at 5.00 AM. I really had nothing to do. Everything was over the night before, but waking up at this time had become a habit after I did so for three days continuously. I tried to go back to sleep, but my efforts were in vain. Very soon, my little princess Shaivi (my daughter) was up and the day started. I was happy and relaxed as the event went very fine and that I did my best to participate at various levels of the event.

TechEd2011 02 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day Next

Twitter was very much the ‘in-thing’ during the event, and there were plenty of the tweets during the event. Here are some of the tweets from various people who really enjoyed the event. You can follow me at @pinaldave for my latest updates and can stay in touch with me.

@pinaldave 1st time I have attended ur sessions in this TechEd. The analogy you have used to explain waits and extended events are amazing. By: @rkassetty

@pinaldave u r too humble !!! No wonder the audiences love your style. By @vinodk_sql

@pinaldave With the amount of time, planning that went behind that session. It will be very satisfying. You did it really well. By @vinodk_sql

Great Database sessions by @pinaldave | @vinodk_sql .Enjoyed and learned a lot from the sessions. By @parveensapra

Attended @pinaldave ‘s session on Extended events in SQL server. His Heart attack example to explain extended events was fun! #techedin By @SuprotimAgrawal

Attended a highly technical session from @pinaldave. DMVs & SQL performance.. Lots of stuff I don’t know. By @AbhishekKant

Great session on extended events at #techedin by @pinaldave.Next session by @vinodk_sqlob backups. Like both the speakers and topics. By @skganguly

TechED stands for Technology Education, and I had enjoyed this event very much. Please visit the following related posts on the TechEd India. I am looking forward to the next edition of TechEd.

If you have attended my sessions and written about the same, do send me email with the link and I will link your blog post over here. Thanks

TechEd2011 21 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day Next

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechED 2011 – Bangalore – An Unforgettable Experience – Day 3

Read my complete experience series of TechEd 2011, Bangalore

TechED 2011 – Bangalore – An Unforgettable Experience – Day 0
TechED 2011 – Bangalore – An Unforgettable Experience – Day 1
TechED 2011 – Bangalore – An Unforgettable Experience – Day 2
TechED 2011 – Bangalore – An Unforgettable Experience – Day 3
TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

Day 3 – March 25, 2011

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

My wife woke me up at 5.00 AM. Two hours of power sleep seemed inadequate as I had only less than 6 hours of sleep during the last 3 days. Today was a big day for me. I had to present two sessions. I got ready quickly, and headed towards the hotel. While I was traveling, I was continuously thinking about my sessions.

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

I reached the TechEd Venue at 7.00 AM and attended an-hour-long meeting with senior officers for the community. I was very happy as I strongly believed that throughout the year, I will be able to help community really well. I got the much-needed blessings from my senior officers for the same. I headed to Database Administrator Track with Vinod Kumar. As I said, it was a very important day for me.

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

I presented the following two topics during the sessions.

  1. Understanding SQL Server Behavioral Pattern – SQL Server Extended Events
  2. SQL Server Waits and Queues – Your Gateway to Performance Troubleshooting
Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

You can read about them at the following links:

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

My both sessions had more than 200 attendees. I was glad and motivated to see considerable interest of so many people on this advanced 400 level subject. My both sessions had excellent participation and also lots of questions. I enjoy presenting the sessions more when attendees are interested and they start asking questions on various subjects that I am presenting.

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

I was pretty exhausted after my both my sessions. However, I still managed to attend three more sessions myself on the same day.

TechEd2011 05 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 3

  1. Vinod Kumar presented on ”Database Recovery and Backup” – A simple but really interesting session.
  2. Prem Mehra presented on “SQL Server Consolidation” – A real world scenario based session.
  3. Balmukund Lakhani presented on ‘AlwaysOn – High Availability Solution’ – A session looking at the future version of the SQL Server.
Vinod Kumar

Vinod Kumar

It was the third day, and I had not seen my daughter. I was really missing her this time. As from last few days, every day when I left home, she was sleeping, and when I reached home, she was sleeping as well. On the third day, I was missing my daughter immensely. I called up Nupur and Shaivi for lunch with me at Hotel. Nupur and Shaivi both visited me during the lunch time, and I played few games with Shaivi. Nupur also met few of her friends, and they returned back home.

Anil Kumble and Gaurav Kapoor

Anil Kumble and Gaurav Kapoor

The third day was yet not over for me. After the five sessions, I went back to keynote area as we were about to have Anil Kumble as keynote speaker. He did an excellent job and discussed about team spirit and his experience as a cricketer. He was fun and inspiring. I really loved how he presented balanced views. One of the attendee in audience asked a very nasty question, and I loved the way he dodged it with a polite reply.

Pinal Shaivi Nupur

Pinal Shaivi Nupur

After the keynote, I rushed to party sponsored by sponsors. It was very good to unwind with community folks after a very successful event. I also rushed to another party right after this, where I met all the key people who had made this event successful. Finally, I decided to relax and mingle with friends – we were there till 1.00 AM. I left the venue at around 1.00 AM in the morning for home.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechED 2011 – Bangalore – An Unforgettable Experience – Day 2

Read my complete experience series of TechEd 2011, Bangalore

TechED 2011 – Bangalore – An Unforgettable Experience – Day 0
TechED 2011 – Bangalore – An Unforgettable Experience – Day 1
TechED 2011 – Bangalore – An Unforgettable Experience – Day 2
TechED 2011 – Bangalore – An Unforgettable Experience – Day 3
TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

Day 2 – March 24, 2011

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

I woke up once again at 5.00 AM as I was planning to leave at 6.00 AM. While I was heading towards the venue, I was thinking about the remaining day ‑ a very eventful day indeed! There were many things lined up and I had not yet reached the venue. Anyway the day started on a good note. I met my MVP friends in the lobby and did not realize that I talked with them for more than 2 hours. I finally went to attend the keynote, but the hall was quite full. I was directed to another room, where live streaming was happening. Live streaming was equally exciting.

Vinod Kumar and Pinal Dave

Vinod Kumar and Pinal Dave

I headed towards Database Business Intelligence Track. I enjoyed the new concepts of BI. I also attended Beauty of the Web track and enjoyed one HTML5 and IE9 track. Following are the sessions that I attended.

Rushabh Mehta

Rushabh Mehta

  1. Rushabh Mehta presented on Semantic Model of SQL Server Denali – it is very new concept and the attendees surely had a great time.
  2. Harish Vaidyanathan presented on “HTML5” – more than just HTML5 – what a fun session it was, I did not know that through HTML, one can have so much fun.
Harish Vaidyanathan

Harish Vaidyanathan

After attending the sessions, I headed right away to the keynote hall. There was going to be Demo Extravaganza, where I was about to present on “Spatial Database”. Spatial Database is interesting concept and I was encouraged immensely by the response and appreciation for the same concept. Though no one noticed, let me share one secret with you regarding this. Even the attendees did not notice this; I actually presented more tricks than what I had initially planned. It was fun that in front of 3000+ people I was able to  show new feature of SQL Server. I had two great Demo Extravaganza hosts – Harish V and Nahash – and it was really fun to be on stage with them. They were really fun to be with, very important, and excellent supporting hosts.

SQL Boys - Vinod, Rushabh, Pinal, Jacob

SQL Boys – Vinod, Rushabh, Pinal, Jacob

After the Demo Extravaganza, I attended community party. It was really gala time to meet friends like Sanjay Shetty, Abhishek Kant, Abhishek Baxi, and all the MVPs. Lots of people attended this event. I had fun time hanging around with fellow MVPs and Community Leaders. I think we should have this type of meetings quite often to keep the community spirit going.

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

I wanted to head home early as I had two sessions to present next day. Even though I was all ready with my presentation, I was slightly nervous. As you know Murphy’s law – if something can go wrong, it will go wrong – and this holds true for anybody at any moment. When I was returning, I noticed that it was already 11 PM. I had one more hour journey back to home. I reached home at midnight, and instead of sleeping, I went back to my sessions. I practiced all the demos and all the slide decks one more time and went to bed around 3.00 AM in the morning. A long day was already over, but about the next day was about to get started in the next 2 hours.

Pinal Dave at TechEd 2011

Pinal Dave at TechEd 2011

Moreover, an exciting World Cup quarterfinal match was taking place between India and Australia that. Despite this, I found that the event was house full! In case you are wondering of the results, India won in the exciting game. Indian bowlers and batsman indeed did a fantastic job on the ground.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechED 2011 – Bangalore – An Unforgettable Experience – Day 1

Read my complete experience series of TechEd 2011, Bangalore

TechED 2011 – Bangalore – An Unforgettable Experience – Day 0

TechED 2011 – Bangalore – An Unforgettable Experience – Day 1

TechED 2011 – Bangalore – An Unforgettable Experience – Day 2

TechED 2011 – Bangalore – An Unforgettable Experience – Day 3

TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

Day 1 – March 23, 2011

TechEd2011 04 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

After my 3 hours of power sleep, I was up before 5.00 AM. I got ready and headed to TechEd Venue. Even though it was pretty early and very dark, it took me around 45 minutes to reach the venue from my home. When I reached there, I met Vinod Kumar, who had reached there before me. We talked a bit; I had a chat with my manager, and then moved very quickly to location where database track was about to happen. Everything seemed alright at the location; so, I once again headed to the Hotel Lobby as I was expecting many other friends to be there.

TechEd2011 05 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

I met so many people there and I almost lost count of them. I had great pleasure meeting Abhishek Kant (MVP Lead) once again. We shared a few words with each other. After couple of calls, I finally spotted Jacob Sebastian – SQL Server MVP and a very close friend. Jacob and I both worked together in the Ahmedabad community. We worked together in almost every event that happened in the Gujarat region. Jacob and I talked for about an hour and headed towards Keynote. We had excellent key note speakers and cheered at the new technology announcements.

TechEd2011 06 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

When I returned from keynote, I met Rushabh Mehta (PASS Global President and Solid Quality India CEO). I talked for a while with him regarding SQL PASS community. It is always a pleasure to meet him as he has such an interesting vision for PASS for India. Along with few friends and SQL Professionals, I headed towards, Database Development Track. I am a database guy, and I am very interested to hear others who are talk about SQL Server.

TechEd2011 10 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

I attended all the sessions in this track. However, there are two tracks which kept me on edge as I came across some new and useful information.

  1. Jacob Sebastian presented on ‘T-SQL Worst Practices’ – I really enjoyed it as it talked about what not to do in reality.
  2. Vinod Kumar presented on ‘Unexplained of SQL Server Security’ – I learned a few new things over here.

After these sessions, I also went and participated in Chalk Talk of ‘100 TB Migration’ subject. It was fun to attend the questions and talk to people.

TechEd2011 07 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

Well, the day ended with partying. It was a speaker party, and all the Microsoft biggies were attending the same. This great place brewed with some intelligent conversations ‑ in short, a wonderful party and a good place to meet great people.

TechEd2011 08 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 1

Right after the party, I headed towards keynote area which could accommodate more than 3000 people. I went there and did a dry run of the my Demo-X for next day.

When I was returning home, I noticed that it was already 11.00 PM, and there was an hour of journey to reach home.

Nupur (my wife) and Shaivi (my daughter) were already asleep when I returned home. I had not talked to Shaivi the whole day – and I felt slightly guilty for the same.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechED 2011 – Bangalore – An Unforgettable Experience – Day 0

Read my complete experience series of TechEd 2011, Bangalore

TechED 2011 – Bangalore – An Unforgettable Experience – Day 0
TechED 2011 – Bangalore – An Unforgettable Experience – Day 1
TechED 2011 – Bangalore – An Unforgettable Experience – Day 2
TechED 2011 – Bangalore – An Unforgettable Experience – Day 3
TechED 2011 – Bangalore – An Unforgettable Experience – Day Next

TechEd India is the one of the best Technology Events in India. The event venue was at Hotel Lalit Ashok, Bangalore. This three-day event was from March 23 to March 25, 2011, and this was my third TechEd Event.

My First TechEd in 2009

I had presented on SQL Server Management Studio Tricks and Tips.

My Second TechEd in 2010

I had presented three topics: 1) MDS 2) Spatial Database, and 3) Database Myths and panel discussion on Blogging.

My Third TechEd in 2011

TechEd2011 02 SQLAuthority News   TechED 2011   Bangalore   An Unforgettable Experience   Day 0

This time, I presented two level 400 sessions: 1) Extended Events 2) Wait Types and Queues

Instead of writing what happened at TechEd, I am going to write what I did at TechEd. I am sure you will enjoy reading the same.

Session Selections and Preparation

Every speaker knew the sessions they are going to present several months in advance. I had started to prepare for my sessions from then on. I will write a separate post on the how I prepared the sessions. Following were my major milestones in the preparation process:

Vinod Kumar and Pinal Dave

Vinod Kumar and Pinal Dave

  • Two long discussions about the two sessions I was going to present with the Database Technology Evangelist Vinod Kumar (he is currently the Technology Specialist at Microsoft Technology Center).
  • Vinod and I had 3-4 revisions regarding the outline of the sessions.
  • I studied Extended Event for more than a month to understand the entire technology.
  • I wrote a month-long series on the wait types and queues and learned this subject in depth.
  • I spent hours creating slides. Each slide deck had more than 40 slides – where 15 slides were meant for presentations and 25 slides were kept as backup reference.
  • I did not rehearse my session speech as it is in prompt, but I tested my scripts almost 2-3 times a day till the last moment.
  • Following people helped me at various levels to prepare the sessions.
    • Vinod Kumar – He helped me at every single level. From sessions review, flow review, additional resources, pre-session feedback, post-session feedback and much more.
    • Govind Kanshi – He helped me with some key input for presentations and gave me opportunity to work with a real client for additional insight.
    • Jacob Sebastian – He took my sessions and studied them diligently. In case, I was not there to present due to any unavoidable issue, he was ready as the backup speaker.

I created one more presentation on T-SQL Tricks and Tips in case of any emergency. So, I was ready with a total of three sessions.

Jacob Sebastian and Pinal Dave

Jacob Sebastian and Pinal Dave

Day 0 – March 22, 2011

I went to the hotel at 12.00 PM. I checked all the locations and stage where database track was going to happen. I joined Vinod Kumar and learned so many things about pre-day preparations. We tested all the audio and video as well stage locations. We were there till late evening. I ran all my demos one more time on Day 0. When I reached home, it was already 10.00 PM. I practiced my sessions demo one more time. By the time, I went to bed, it was like 2.00 AM. I set three alarms to wake up at 5 AM as I had to leave from home at 6.00 AM to reach to venue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28

johnathan SQL SERVER   Guest Post   Jonathan Kehayias   Wait Type   Day 16 of 28 Jonathan Kehayias (Blog | Twitter) is a MCITP Database Administrator and Developer, who got started in SQL Server in 2004 as a database developer and report writer in the natural gas industry. After spending two and a half years working in TSQL, in late 2006, he transitioned to the role of SQL Database Administrator. His primary passion is performance tuning, where he frequently rewrites queries for better performance and performs in depth analysis of index implementation and usage. Jonathan blogs regularly on SQLBlog, and was a coauthor of Professional SQL Server 2008 Internals and Troubleshooting.

On a personal note, I think Jonathan is extremely positive person. In every conversation with him I have found that he is always eager to help and encourage. Every time he finds something needs to be approved, he has contacted me without hesitation and guided me to improve, change and learn. During all the time, he has not lost his focus to help larger community. I am honored that he has accepted to provide his views on complex subject of Wait Types and Queues. Currently I am reading his series on Extended Events.

Here is the guest blog post by Jonathan:

SQL Server troubleshooting is all about correlating related pieces of information together to indentify where exactly the root cause of a problem lies. In my daily work as a DBA, I generally get phone calls like, “So and so application is slow, what’s wrong with the SQL Server.” One of the funny things about the letters DBA is that they go so well with Default Blame Acceptor, and I really wish that I knew exactly who the first person was that pointed that out to me, because it really fits at times. A lot of times when I get this call, the problem isn’t related to SQL Server at all, but every now and then in my initial quick checks, something pops up that makes me start looking at things further.

The SQL Server is slow, we see a number of tasks waiting on ASYNC_IO_COMPLETION, IO_COMPLETION, or PAGEIOLATCH_* waits in sys.dm_exec_requests and sys.dm_exec_waiting_tasks. These are also some of the highest wait types in sys.dm_os_wait_stats for the server, so it would appear that we have a disk I/O bottleneck on the machine. A quick check of sys.dm_io_virtual_file_stats() and tempdb shows a high write stall rate, while our user databases show high read stall rates on the data files. A quick check of some performance counters and Page Life Expectancy on the server is bouncing up and down in the 50-150 range, the Free Page counter consistently hits zero, and the Free List Stalls/sec counter keeps jumping over 10, but Buffer Cache Hit Ratio is 98-99%. Where exactly is the problem?

In this case, which happens to be based on a real scenario I faced a few years back, the problem may not be a disk bottleneck at all; it may very well be a memory pressure issue on the server. A quick check of the system spec’s and it is a dual duo core server with 8GB RAM running SQL Server 2005 SP1 x64 on Windows Server 2003 R2 x64. Max Server memory is configured at 6GB and we think that this should be enough to handle the workload; or is it? This is a unique scenario because there are a couple of things happening inside of this system, and they all relate to what the root cause of the performance problem is on the system.

If we were to query sys.dm_exec_query_stats for the TOP 10 queries, by max_physical_reads, max_logical_reads, and max_worker_time, we may be able to find some queries that were using excessive I/O and possibly CPU against the system in their worst single execution. We can also CROSS APPLY to sys.dm_exec_sql_text() and see the statement text, and also CROSS APPLY sys.dm_exec_query_plan() to get the execution plan stored in cache. Ok, quick check, the plans are pretty big, I see some large index seeks, that estimate 2.8GB of data movement between operators, but everything looks like it is optimized the best it can be. Nothing really stands out in the code, and the indexing looks correct, and I should have enough memory to handle this in cache, so it must be a disk I/O problem right?

Not exactly!

If we were to look at how much memory the plan cache is taking by querying sys.dm_os_memory_clerks for the CACHESTORE_SQLCP and CACHESTORE_OBJCP clerks we might be surprised at what we find. In SQL Server 2005 RTM and SP1, the plan cache was allowed to take up to 75% of the memory under 8GB. I’ll give you a second to go back and read that again. Yes, you read it correctly, it says 75% of the memory under 8GB, but you don’t have to take my word for it, you can validate this by reading Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2. In this scenario the application uses an entirely adhoc workload against SQL Server and this leads to plan cache bloat, and up to 4.5GB of our 6GB of memory for SQL can be consumed by the plan cache in SQL Server 2005 SP1. This in turn reduces the size of the buffer cache to just 1.5GB, causing our 2.8GB of data movement in this expensive plan to cause complete flushing of the buffer cache, not just once initially, but then another time during the queries execution, resulting in excessive physical I/O from disk. Keep in mind that this is not the only query executing at the time this occurs. Remember the output of sys.dm_io_virtual_file_stats() showed high read stalls on the data files for our user databases versus higher write stalls for tempdb? The memory pressure is also forcing heavier use of tempdb to handle sorting and hashing in the environment as well.

The real clue here is the Memory counters for the instance; Page Life Expectancy, Free List Pages, and Free List Stalls/sec. The fact that Page Life Expectancy is fluctuating between 50 and 150 constantly is a sign that the buffer cache is experiencing constant churn of data, once every minute to two and a half minutes. If you add to the Page Life Expectancy counter, the consistent bottoming out of Free List Pages along with Free List Stalls/sec consistently spiking over 10, and you have the perfect memory pressure scenario. All of sudden it may not be that our disk subsystem is the problem, but is instead an innocent bystander and victim.

Side Note: The Page Life Expectancy counter dropping briefly and then returning to normal operating values intermittently is not necessarily a sign that the server is under memory pressure. The Books Online and a number of other references will tell you that this counter should remain on average above 300 which is the time in seconds a page will remain in cache before being flushed or aged out. This number, which equates to just five minutes, is incredibly low for modern systems and most published documents pre-date the predominance of 64 bit computing and easy availability to larger amounts of memory in SQL Servers. As food for thought, consider that my personal laptop has more memory in it than most SQL Servers did at the time those numbers were posted. I would argue that today, a system churning the buffer cache every five minutes is in need of some serious tuning or a hardware upgrade.

Back to our problem and its investigation:

There are two things really wrong with this server; first the plan cache is excessively consuming memory and bloated in size and we need to look at that and second we need to evaluate upgrading the memory to accommodate the workload being performed. In the case of the server I was working on there were a lot of single use plans found in sys.dm_exec_cached_plans (where usecounts=1). Single use plans waste space in the plan cache, especially when they are adhoc plans for statements that had concatenated filter criteria that is not likely to reoccur with any frequency.  SQL Server 2005 doesn’t natively have a way to evict a single plan from cache like SQL Server 2008 does, but MVP Kalen Delaney, showed a hack to evict a single plan by creating a plan guide for the statement and then dropping that plan guide in her blog post Geek City: Clearing a Single Plan from Cache. We could put that hack in place in a job to automate cleaning out all the single use plans periodically, minimizing the size of the plan cache, but a better solution would be to fix the application so that it uses proper parameterized calls to the database. You didn’t write the app, and you can’t change its design? Ok, well you could try to force parameterization to occur by creating and keeping plan guides in place, or we can try forcing parameterization at the database level by using ALTER DATABASE <dbname> SET PARAMETERIZATION FORCED and that might help. If neither of these help, we could periodically dump the plan cache for that database, as discussed as being a problem in Kalen’s blog post referenced above; not an ideal scenario.

The other option is to increase the memory on the server to 16GB or 32GB, if the hardware allows it, which will increase the size of the plan cache as well as the buffer cache. In SQL Server 2005 SP1, on a system with 16GB of memory, if we set max server memory to 14GB the plan cache could use at most 9GB  [(8GB*.75)+(6GB*.5)=(6+3)=9GB], leaving 5GB for the buffer cache.  If we went to 32GB of memory and set max server memory to 28GB, the plan cache could use at most 16GB [(8*.75)+(20*.5)=(6+10)=16GB], leaving 12GB for the buffer cache. Thankfully we have SQL Server 2005 Service Pack 2, 3, and 4 these days which include the changes in plan cache sizing discussed in the Changes to Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 blog post. In real life, when I was troubleshooting this problem, I spent a week trying to chase down the cause of the disk I/O bottleneck with our Server Admin and SAN Admin, and there wasn’t much that could be done immediately there, so I finally asked if we could increase the memory on the server to 16GB, which did fix the problem. It wasn’t until I had this same problem occur on another system that I actually figured out how to really troubleshoot this down to the root cause.  I couldn’t believe the size of the plan cache on the server with 16GB of memory when I actually learned about this and went back to look at it.

SQL Server is constantly telling a story to anyone that will listen. As the DBA, you have to sit back and listen to all that it’s telling you and then evaluate the big picture and how all the data you can gather from SQL about performance relate to each other. One of the greatest tools out there is actually a free in the form of Diagnostic Scripts for SQL Server 2005 and 2008, created by MVP Glenn Alan Berry. Glenn’s scripts collect a majority of the information that SQL has to offer for rapid troubleshooting of problems, and he includes a lot of notes about what the outputs of each individual query might be telling you.

When I read Pinal’s blog post SQL SERVER – ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28, I noticed that he referenced Checking Memory Related Performance Counters in his post, but there was no real explanation about why checking memory counters is so important when looking at an I/O related wait type. I thought I’d chat with him briefly on Google Talk/Twitter DM and point this out, and offer a couple of other points I noted, so that he could add the information to his blog post if he found it useful.  Instead he asked that I write a guest blog for this. I am honored to be a guest blogger, and to be able to share this kind of information with the community. The information contained in this blog post is a glimpse at how I do troubleshooting almost every day of the week in my own environment. SQL Server provides us with a lot of information about how it is running, and where it may be having problems, it is up to us to play detective and find out how all that information comes together to tell us what’s really the problem.

This blog post is written by Jonathan Kehayias (Blog | Twitter).

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)