About a month ago I started a competition (hosted by the event organizers) to give away a free pass to the SQL Live 360 Conference in Orlando. You can still get a $500 discount by registering through this link by the way.
Now it is time to announce the winner and give some feedback about the winning answers to the questions.
Congratulations goes to Alvaro Costa who will be contacted shortly by the organizers of the event to receive information about how to claim the free conference pass.
Now about the questions. I thought they were pretty simple and straight forward but I was a bit surprised by the answers. There were were a lot of participants that submitted answers but a lot of people did not read the questions properly. The question is really half of the answer and only a few of the participants answered the both questions I was asking correctly. Many people were close and gave some great answers for the questions and I appreciate them taking the time to enter the competition.
The moral of the story here is, even if you have the knowledge and qualifications to answer the questions, you must answer the question that is given. Often I see solutions developed for users/clients where the developer is super smart and talented but they provide a solution that does not meet the needs of the client. I think as IT professionals we have to understand what it is that is being asked from us and provide solutions to that question; not to what we think is being asked but truly what is being asked. I have put the questions and answers below with my comments.
Here are the questions again.
1. Please briefly describe what SQL Server Always-ON means and what it does. Be careful… take this one slowly. Many people answered this question and talked about Always-On availability groups, which is only half of the answer. Always-On is a “marketing term” that Microsoft has coined for High Availability with Clustering and Availability groups. Availability Groups are a hybrid approach between mirroring and clustering and should replace mirroring in future editions of SQL Server. Unlike Clustering, it does not require shared disk. Now, clustering is also considered part of Always-On and the major improvement in SQL 2012 is the addition of multi-subnet clustering (also called Geo-Clustering). This new enhancement gives SQL Server clustering the ability to scale to different physical locations (i.e. company hosted data center with a failover node at a remote data-center).
2. We keep hearing about In-Memory technology. Why is memory such a big deal for SQL Server, how does memory help improve performance? Again, many of the answers talked about Hekaton, which is a new In-memory OLTP feature with SQL 2014. Yes, it is the latest and greatest of memory usage features with SQL Server, however my question is general about how memory improves performance. It’s an easy answer, SQL Server does its operations in memory (insert,update,delete,select, etc) and thus the more memory you have, the less pressure you put on the I/O system which is the greatest bottle neck in terms of speed for all computer systems currently.
Here are Alvaro’s answers:
1. AlwaysOn is just a marketing name created by Microsoft for their new H/A concept. The real features are called AlwaysOn Availability Groups and AlwaysOn clustering, which it was created to enhance the current clustering and replace DB mirroring.
2. Memory is one of the most important resoruces in SQL, because it is where both data and query plans are cached for query operations. The more is available, the more SQL can cache and avoid round trips to the I/O subsystem.
As an additional bonus, I’m pasting an answer from a DC/Baltimore local that I know (Slava Murygin) for question #2. I thought it was pretty interesting and educating answer which everyone will benefit from:
I’m “old-school programmer” and remember working not having hard drive using only floppy disks. At that time the smartest move forward was usage of part of memory as a virtual drive where we put most commonly used system file “Command.com”. That made our system fly.
How does it applicable for SQL Server? – The same paradigm. Local HD or SAN are always slower than internal memory.
So, if SQL server caches most used tables into memory then all selects and joins will be performed hundredths times faster that if it would require to do a reading disk operation.
Giving to the SQL Server as much memory as possible (leaving some for your windows) is always a good practice.
I’d go further. On my test machine I’ve created virtual drive in memory and placed my TempDB and log files there. That improve DB performance tremendously. However that method had some constraints, such as file size and recovering from any failure.
Going to SS2014. from what I already know about 2014 approach it caches tables in the memory not only for selects, but also for updates. Also, (not sure in my correctness) I assume it has virtual indexes, that would be created on a fly in the memory without even accessing a disk.
Thank you to all participants and for the team at SQL Live 360 for providing the free conference pass.