Happy Monday, ALL!  I recently announced that I am hosting an awesome contest this month, sponsored by Safe Peak, that give you the opportunity to share your SQL Server performance experiences with the SQL Community, AND have multiple chances to Win an iPad2.

So, to warm all you up, I decided to share one of my own performance stories with you below, and posted to the contest.  Now, don't worry, mine doesn't count, so all the prizes goes to the lucky winner - but you have to submit your stories ASAP!  Here is the original post with all the detes, rules, and buzz: TELL ME HOW TO WIN AN iPAD2.   To submit your story RIGHT NOW, goto this direct link for the SQL Server Performance Story Contest, 2011!   Here's my story:

Operation Crown Jewel

Allow me to break the ice, and share one of my performance stories with you with respect to SQL Server.  In this instance, the problem wasn’t missing indexes or un-optimized code.  It required a little out-of-the-box contemplation, but nonetheless was the cause of extremely slow performance, and the clients were not happy!

It was quite a number of years ago at an e-commerce company that still was running its core business on v6.5.  Even after v.7.0 has been out for a year with SP1 already released.  After my assessment, I issued a DBA report that the entire business is at risk due to its lack of adaption of current technology.  Not only would it fall behind its competition, they actually risked losing customers and having its server crash with no failover or HA protection.   (I even built my own automated log shipping scripts, before it was part of the Enterprise package.)

Sadly, what every true technologist wishes to avoid, there were office politics involved, and it came down to two camps.  I was aligned with my manager, the CFO and CEO, while the rest of the hotshots before me aligned with the Chairman and CTO.  I just wanted to ensure that from a database perspective, they would follow industry standards and best recommended practices.

So, as clients relied on the system to return client data reports on-demand, this process became painfully slow and even time-out way too often.

After some intense research and analysis, I saw that network packets were being dropped.  After looking at the network protocols setup, I saw that ONLY named pipes were enabled.  The protocols were a bit nebulous back then, and you had to decide at installation which protocols were installed. I have reading a bit on TCP\IP, and was convinced that this would be the appropriate route seeing that we tried almost everything else.  We already knew it was a network communication issue, so I was so confident to try to enable TCP\IP. 

By default TCP/IP protocol is not enabled in v.6.5, and had to go through “SQL Setup” from the MSSQL Server v6.5 on the programs menu to install.  I had to add the network library for TCP/IP and then after you must restart SQL services for the new settings to take effect.

This process of course, needed full permission and sign off of the executive team, and only half agreed.  Meanwhile, the client report generation suffered and suffered.

My manager asked me to put together a short technical summary lending support to my theory, and why we should go with TCP\IP.  Of course today, this is a no-brainer, but it wasn’t really apparent back then, especially in the non-technical executive branch.  The irony is the CTO was a very influential, yet closed minded person.  Rather than work and embrace my theory for the greater good of the company, he wasn’t going to let a young “snot-nosed” DBA interfere with his absolute technical know-how!  I basically outlined in my summary, the technical info below:

In a fast local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable in terms of performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the communication (IPC) mechanisms communicate between peers.

For named pipes, network communications are typically more interactive and very chatty over the network. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it begins to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.

For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on, which can be very beneficial in a slow network. Depending on the type of applications, such performance differences can be significant.

TCP/IP Sockets also support a backlog queue, which can provide a limited smoothing effect compared to named pipes that may lead to pipe busy errors when you are attempting to connect to SQL Server.

In general, sockets are preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

I was approached by the CEO, who after speaking to my manager, decided to throw in his lot with the DBA (me :-), and arranged for an “executive meeting”.  During that time, I would go ahead and install and enable TCP\IP, which require a SQL Server service restart.  The CEO was also the founder, and since it was his baby, he really had his heart in it, and the most invested.

Now the mission began - one of espionage and intrigue. 🙂 Like any good spy movie to retrieve the “crown jewel” (We actually dubbed this operation “crown-jewel”), we cut the feed to the cameras, in this case, disable the monitoring system so there would be no alert generated when the SQL service briefly went down.  A few minutes into the “executive” meeting, the CEO stepped out of the board room, to give the green light. 

I truly felt I was in the middle of a Mission Impossible film, and if I was, the CEOs final words would be, “Your Mission, should you choose to accept, is to install and configure TCP\IP, restart the service and re-enable the monitoring system.  Once that’s done, we will contact client X standing by to test the reports.  As always, should you or any of your team be caught or killed, the CEO will disavow any knowledge of your actions.  Your DBA career will self-destruct in 5-seconds…. Good Luck.”

Well, that’s reassuring!  But, I was determined not to let him down.  But just in case, I had updated my resume.

Anyway, I began to do the needy, and after all was said and done, the reports ran successfully in less than 30 seconds.  This was down over 4 minutes and 30 seconds, from 5 minutes and change.  Plus, after multiple tests, there were no network time-outs. Performance was significantly increased. Mission accomplished.

Although the other side wasn’t informed right away, they chalked it up to some “new” code released some 4 days before.  Ha!  The CEO knew the task was a success, and the clients happy as a clam.

The point here is, sometimes, even a small configuration change can make the world of difference, but stubbornness and inflexibility can stand in the way of what seems to be common sense.  Fortunately, it turned out ok, and I stayed a little while longer.  The CTO still seemed to undermine me, and I eventually moved on.  However, the CEO was grateful, and personally offered his highest recommendation to my next employer.

We now know TCP\IP is the industry standard protocol used in today’s SQL Server versions, especially in e-commerce and communication to remote clients over the internet. 

Now that you’ve read my performance story, let’s see yours!  Don’t you guys want to win a FREE iPad2?

                                        SQL Server Performance Story Contest, 2011!

*** REMINDER***Don't forget tomorrow is T-SQL Tuesday.  Here is the Invitation for T-SQL Tuesday #22 – Data Presentation.  The topic of this month’s T-SQL Tuesday is, “data-presentation” Or put better, formatting data for presentation to the end-user.  Use the hashtag #tsql2sday.