Answer to SQL Server Interview Questions & Answers – # 4 ( Part 2 )

Today I will post some of the answers to the questions I posted here(https://msbiskills.com/2015/08/25/sql-server-interview-questions-answers-4/). Also note that answers i am providing are to the point answers, for details please refer online material.

Question – What are column store indexes? How they are better for performance?

Answer-

• In SQL Server 2012, Microsoft introduced the columnstore index, a memory-optimized column-oriented index. They are used to handle data warehouse workloads.
• An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index.
• For some types of queries, the SQL Server query processor can take advantage of the columnstore layout to significantly improve query execution times. The explosive growth of data warehousing, decision support and BI applications has generated an urgency to read and process very large data sets quickly and accurately into useful information and knowledge. This growth of data volumes and escalating expectations are challenging to maintain or improve through incremental performance improvements.
• SQL Server columnstore index technology is especially appropriate for typical data warehousing data sets. Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.
• SQL Server 2014 adds support for updateable clustered columnstore indexes. With SQL Server 2014, the clustered columnstore index can be used in place of a traditional rowstore clustered index. The clustered columnstore index permits data modifications and bulk load operations. Although it has limitations, the clustered columnstore index still represents a significant step forward in providing an indexing strategy better suited to the enterprise data warehouse.
• If you need further details click below –
  https://technet.microsoft.com/en-us/library/gg492088(v=sql.110).aspx#HT_create
  https://technet.microsoft.com/en-us/library/gg492153(v=sql.110).aspx
  

Question – How SQL Server handles concurrency in SQL 2014?

Answer – In-memory OLTP the Concurrency model is a version-based supporting multiple versions of the rows with different lifetime. SQL Server stores in tempdb copies (versions) of all the previously committed versions of any data rows, since the beginning of the oldest open transaction (i.e. it keeps those copies as long as there are any transactions that might need to access them). The space in tempdb used to store previous versions of changed rows is the version store. When using this row versioning, readers do not block writers, and writers do not block readers (though writers do still take locks and will block other writers). I suggests please go through the link below –
https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/

Question – How you can achieve high availability in SQL SERVER, Your database should be up all the time, 0% downtime is required.

Answer – SQL Server provides several options for creating high availability for a server or database. The high-availability options include the following:

• AlwaysOn Failover Cluster Instances
• AlwaysOn Availability Groups
• Database mirroring
• Log shipping

Also consider Vmware Vsphere – It is used to minimize Downtime from Server and OS Failures. Explained in detail in the next question.

For details follow this link – https://msdn.microsoft.com/en-us/library/ms190202.aspx

Question – How you can achieve high availability in applications, Your application should be up all the time,0 downtime is required.

Answer – You can use Vmware Vsphere – It is used to minimize Downtime from Server and OS Failures

VMware vSphere® High Availability (HA) delivers the availability required by most applications running in virtual machines, independent of the operating system and application running in it. HA provides uniform, cost-effective failover protection against hardware and operating system outages within your virtualized IT environment.HA provides the foundation for a highly available environment by monitoring virtual machines and the hosts upon which they run. HA is a mature solution that provides scalability, reliability and usability.

Scalability

• Master-slave node relationship – This new relationship model between nodes in a cluster replaces primary and secondary nodes. Availability actions are coordinated by a single master node, which communicates all activities and states to VMware® vCenter Server™. This eliminates much of the planning required to design a highly available environment—administrators no longer need to worry about which hosts are primary nodes and where they are located. This is especially significant when deploying HA on blade chassis and in stretched cluster environments.
• Support for IPv6 networking – Enables IT departments in need of a larger “address” space to fully leverage their network infrastructure.
• Simple deployment mechanism – Fast and easy completion of routine tasks such as deploying vSphere HA agent and configuring HA functionality.

Reliability
When an outage occurs, the last thing administrators need is to worry about whether their high availability solution will work. Guided by real-world customer feedback, VMware has added capabilities to maximize your confidence in HA:

• No external component dependencies – HA has no dependency upon DNS resolution. This reduces the odds that an external component outage will impact HA operations.
• Multiple communication paths – HA nodes within a cluster can communicate through the storage subsystem as well as over the management network. Multiple communication paths increase redundancy and enable better assessment of the health of a vSphere host and its virtual machines.
• VM-VM anti-affinity rules – HA has been enhanced to respect VM-VM anti-affinity rules defined in VMware vSphere® Distributed Resource Scheduler™, eliminating the need for VMware vSphere® vMotion® migrations after failover.

Usability
HA’s enhanced interface lets users quickly identify the role and state of each node in a cluster. Error condition messages are also easier to understand and act upon. In the rare case that an issue does occur with HA, only one log file needs to be reviewed, speeding time to resolution.

• For more details please click here – http://www.vmware.com/in/products/vsphere/features/high-availability

Question – You have given a requirement? How do you start DB design for it?

Answer – You can start with some points mentioned below.

• First try and understand the purpose of your database.
• In the second step find the Entities (Tables)
• Understand you data – Duplicate and non duplicate
• Find out fields for each entity(Table), Decide on their data types and size
• Decide you primary keys or clustered indexes based on the business, They are not required but highly recommended
• Check normalization based on OLAP or OLTP
• Find out the relationships between these entities
• Create extra tables if you have M-N relationship between tables
• Decide on the foreign keys, Add foreign keys in respective tables
• Decide on the fill factor of the tables (OLAP/OLTP)
• Find out the Non clustered indexes you want to put on each tables
• Also decide on whether you want to put all the file groups on the same server or on different servers
• Check hardware based on OLTP/OLAP, if you need high availability go for VMware vSphere, EMC XtremeIO etc
  

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com