Interview Q and A for SQL Server Part - 1

1. Explain about your SQL Server DBA Experience.

§  This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable you are for the position to which you are being interviewed.

2. What are the different SQL Server Versions you have worked on?

§  The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

3. What are the different types of Indexes available in SQL Server? OR

    What type of Indexes can a Table consist of in SQL Server 2012?

§  A table in SQL Server can have below types of Indexes.

One Clustered Index
Non-Clustered Indexes
Unique Indexes
Index with included columns
Columnstore index
Filtered Indexes
Spatial Indexes
XML Indexes

4. What is the difference between Clustered and Non-Clustered Index? OR

what is a Clustered index in sql server 2008 R2? How many clustered Indexes can we create on a table? OR

what is a Non-Clustered index in sql server 2008 R2? How many clustered Indexes can we create on a table?

§  In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

§  In a Non-Clustered index, the leaf level page does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

5. What are the new features in SQL Server 2005 when compared to SQL Server 2000?

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here:

§  Database Partitioning

§  Dynamic Management Views

§  System Catalog Views

§  Resource Database

§  Database Snapshots

§  SQL Server Integration Services

§  Support for Analysis Services on a a Failover Cluster.

§  Profiler being able to trace the MDX queries of the Analysis Server.

§  Peer-to-Peer Replication

§  Database Mirroring

6. What are the High-Availability solutions in SQL Server and differentiate them briefly.

§  Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog which explains the differences between these 4 features. Comparing the High Availability Features in SQL Server 2005

7. How do you troubleshoot errors in a SQL Server Agent Job?

§  Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

8. What is the default Port No on which SQL Server listens?

§  1433

9. How many files can a Database contain in SQL Server? How many types of data files exists in SQL Server? How many of those files can exist for a single database?

§  A Database can contain a maximum of 32,767 files.

§  There are Primarily 2 types of data files Primary data file and Secondary data file(s)

§  There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files

10. What is DCL?

§  DCL stands for Data Control Language.

11. What are the commands used in DCL?

§  GRANT, DENY and REVOKE.

12. What is Fill Factor?

§  Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

13. What is the default fill factor value?

§  By default the fill factor value is set to 0.

14. Where do you find the default Index fill factor and how to change it?

§  The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.

§  The other option of viewing and changing this value is using sp_configure.

15. What is a system database and what is a user database?

§  System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

§  A user database is a database that we create to store data and start working with the data.

16. What are the recovery models for a database?

§  There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

17. What is the importance of a recovery model?

§  Primarily, recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. 

18. What is SQL Server Replication?

§  Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.

OR

Replication is subset of SQL Server that can move data and database objects in an automated way from one database to another database. This allows users to work with the same data at different locations and changes that are made are transferred to keep the databases synchronized.

19. What the different types of Replication and why are they used?

§  There are basically 3 types of replication: Snapshot, Transactional and Merge Replication.

·       Snapshot replication - As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it's acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected.  An example use of snapshot replication is to update a list of items that only changes periodically.

·       Transactional replication – As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks. 

·       Merge replication – This is the most complex types of replication which allows changes to happen at both the publisher and subscriber.  As the name implies, changes are merged to keep data consistency and a uniform set of data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated.  The merge agent has the capability of resolving conflicts that occur during data synchronization.  An example of using merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized. 

      The type of Replication you choose depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of  remote / distributed systems where the data flow can be from multiple sites, for example sales done at a promotional events which might not be connected to the central servers always..

20. What the different components in Replication and what is their use?

§  The 3 main components in Replication are Publisher, Distributor and Subscriber. Publisher is the data source of a publication. Distributor is responsible for distributing the database objects to one or more destinations. Subscriber is the destination where the publisher’s data is copied / replicated.

21. What the different Topologies in which Replication can be configured?

§  Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:

§  Publisher, Distributor and Subscriber on the same SQL Instance.

§  Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.

§  Publisher, Distributor and Subscriber on individual SQL Instances.

 22. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?

§  I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services; the right side pane displays all of the SQL Server Services / components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

23. What are the different Authentication modes in SQL Server and how can you change authentication mode?

§  SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode. To change the Authentication mode,

24. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

§  On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…

25. What is meant by Active – Passive and Active – Active clustering setup?

§  An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

§  An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

26. List out some of the requirements to setup a SQL Server failover cluster.

§  Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.

27. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

§  Using Cluster Administrator, connect to the cluster and select the SQL Server cluster.  Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.

28. How do you open a Cluster Administrator?

§  From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.

29. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?

§  In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.

30. What are the different ways you can create Databases in SQL Server?

§  T-SQL; Create Database command.

§  Using Management Studio

§  Restoring a database backup

§  Copy Database wizard

31. When setting Replication, can you have Distributor on SQL Server 2005, Publisher on SQL Server 2008?

§  No you cannot have a Distributor on a previous version than the Publisher.

32. When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.

§  Yes it is possible to have various configurations in a Replication environment.

33. What is the difference between dropping a database and taking a database offline?

§  Drop database deletes the database along with the physical files; it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.

34. Which auto growth database setting is good?

§  Setting an auto growth in multiples of MB is a better option than setting auto growth in percentage (%).

35. What are the different types of database compression introduced in SQL Server 2008?

§  Row compression and Page compression.

36. What are the different types of Upgrades that can be performed in SQL Server?

§  In-place upgrade and Side-by-Side Upgrade.

37. What is Transparent Data Encryption?

§  Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.

38. Does Transparent Data Encryption provide encryption when transmitting data across network?

§  No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.

39. What are the operating modes in which Database Mirroring runs?

§  Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.

40. What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?

§  High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.

§  High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.

41. How can we proactively try and make sure that performance issues do not occur?
– Follow best practices related to Operating System, SAN, Network
– Follow SQL Server best practices and SQL Server configuration settings for best performance.
– Make sure there are regular maintenance jobs like smart index maintenance tasks like rebuilding or reorganizing indexes based on fragmentation, Smart Updating statistics with Full Scan.
– Using DMV’s or other monitoring tools to monitor the resource usage on server like CPU, Memory and Disk I/O.
– Monitor for occurrences of locking, blocking and suspended sessions.
– Monitor for occurrences of waits in SQL Server using wait stats DMV’s
– Baseline SQL Server and server resource usage

42. What SQL Server tools are available to troubleshooting performance problems?
– SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, We can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.
– SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.
– SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.
– SQL Server DMV’s available to get lot of in-depth details about query statistics and execution plans.
– SQL Server Performance Dashboard to generate performance related reports(This needs to be installed as additional plugin)
– Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.
– Starting SQL Server 2008 R2, we can Management Data Warehouse (MDW) for performance troubleshooting.

43. How do you troubleshoot slowness with a specific Stored Procedure or a Query?
– First, get more details like, how much time on an average this query was taking previously (baseline)? Were there any changes to the stored procedure or query recently? How often this query does runs? Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
– Check if this query is being blocked by other sessions.
– Check if this query is waiting some any resource using wait stats DMV’s.
– Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
– Check fragmentation of the objects in the Stored procedure or the query.
– Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
– Check for any missing indexes based on the execution plan, based on table or clustered index scans.
– Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.

44. What question do you ask Developers or Client to understand more about the performance issue?
– What kind of performance issue are you seeing, can you be more specific? It is often said that the Application is slow or website is slow.
– Are there any specific feature or a webpage of the application that is slow or is it that entire application is slow?
– Since when you started seeing performance problems?
– Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? or Were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?
– Are you aware of any changes to the data or increase in number of users on the SQL Server recently?
– So far have you observed anything that can point in a direction where could be the problem?
– Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?
– Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?
– Have you performed any troubleshooting thus far and what are your findings, if any, so far?

It is common that Developers or Client as expected do not have answers to most of these questions and general answer is entire Application is slow.

45. How do you approach or troubleshoot performance problems in SQL Server?
There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.

– First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
– Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
– Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query. Next steps are specific to general performance troubleshooting.
– Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there is any spikes in any of their usage. If yes, then drill further down in that direction, if every thing looks normal, then will proceed with checking at SQL Server level.
– Check SQL Server errorlogs and eventlogs for any errors.
– Check for any blocking or heavy locking or high number of suspended sessions.
– Check wait stats to see the top waits.
– Checking if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then will implement those which will significantly improve the performance.
– Will run DMV’s to identify Top Duration, Top CPU, Top Read or Write intensive queries and try to tune them by creating appropriate indexes or report them to developer suggesting to re-write those queries.
– Checking for SQL configuration settings like, MaxDoP, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.

Above steps should help in understanding the performance problem and in fixing the same.

46. What are Included Columns in Indexes?
An index contains key columns, which act as pointer to the entire row data and this normally have fewer columns based on where condition, however if the index does not have other columns which are to be returned for the query to complete, SQL Server again have to reach out to clustered index for additional columns which are not present in non-clustered index. We can eliminate additional clustered index search, by including the additional columns in the non-clustered index, but not as key columns. These additional columns are added as “Included Columns”.

This is applicable on below versions of SQL Server

SQL Server 2005 , 2008 R2 , 2012 , 2014

47. What are Filtered Indexes?
Filter Indexes are introduced Starting SQL Server2008, we can create index on set of columns with only subset of rows by using a WHERE condition, so the index will only have the rows which are satisfied by the where condition

48. How does the indexes help with SQL Server performance?
With just a table without any indexes, there is only one path to access table data which is by reading entire table in to memory and this data is not stored in any particular order, so for a request which requires specific data, we need to read the entire table data and also at a given point in time, only one session can use all the data, thus blocking other sessions. By creating clustered index, now the data will be stored in a particular order based on the columns chooses as index keys, now this allows us to directly point to the required data with the help of the index keys, thus improving performance. Creating additional non-clustered indexes on few columns improves the performance further, where multiple queries can access the same data without blocking each other, thus improving the performance further.

49. Are there any tools which help me identify which indexes to create in SQL Server 2008 R2?
– Yes, with SQL Server Management Studio (SSMS) 2008 R2 or higher, we can generate a execution plan and it also suggests if it finds any missing indexes.
– Also, with regular installation of SQL Server, a tool named Database Tuning Advisor (DTA) is also available, which will help in analyzing a query or trace file data and suggest any missing indexes or statistics.
– We can also use SQL Server built-in DMV’s which provide us list of missing indexes based on the index usage data. This index usage data is removed with restart of SQL Server, so be careful.

The suggestions returned by the tools and DMV’s are not accurate and as a DBA, we need to carefully evaluate and test the indexes on a test server and then only create them on Production servers.

50. If my table has high number DML(Insert/Update/Delete) operations performed frequently, then does creating indexes improves the performance?
Not always, need to very carefully test and create the indexes which are required which improves the performance and remove and indexes which are not used much. DML operations will have additional overhead with lot of indexes and can slowdown the performance.

51. Are creating multiple indexes always improves performance?
Not necessarily, having too many or improper indexes also negatively affects the performance, so only after careful evaluation and testing, proper indexes need to be created.

52. Can we create Indexes on Views in SQL Server 2012?
Yes, we can create indexes on a view and it will be persisted on the database, just like an index created on a table.

53. What kind of indexes can we create on Views?
First index we create on a View must be Unique Clustered Index and then we can create multiple Non-Clustered Indexes.

54. Do we have to perform any maintenance on the indexed Views to control fragmentation?
Yes, we need to perform regular smart re-indexing and also statistics updates on the indexed views.

55. Does creating Indexes on View help the performance of queries?
Yes, it will improve the performance, but also can negatively impact the performance in some cases, so test it thoroughly before implementing on Production servers.

56. Do we need to reference the indexed Views in the query for it to be used by the SQL optimizer?
No, even if we do not reference the indexed views, SQL Server may still use the Indexed Views.

 57. Does the Clustered and Non-Clustered Indexes unique data in the key column?
Not necessarily, we can create a Clustered and Non-Clustered Indexes as unique as well and non-unique where multiple rows can share the same key value.

58. A table having Primary Key meaning it also has Clustered Index?
Not necessary, by default, creating a Primary key also creates a clustered index, but we can change that behavior and make it create a non-clustered index or no index at all, in which case the table creates unique constraint to maintain the uniqueness in the primary key column.

59. Can we create Clustered Index without a primary key in the table?
Yes, we can have a table without a primary key, but with a clustered index.

60. What are Heap tables in SQL Server 2014?
Any table which does not have a Clustered index is called as heap table. But it is recommended to have clustered index on all table, of-course there can be special cases where you do not require a clustered index.

61. Can Non-clustered indexes be created on Heap tables?
Yes, we can create non-clustered indexes on Heap tables, it is fully supported, but not recommended.

62. Do we need to perform any regular maintenance on the server?
Yes, we need to regularly perform smart index optimization by rebuilding or reorganizing of all the indexes based on the fragmentation percentage. Also statistics need to be updated on all the indexes with Full Scan preferably to improve performance.

63. Does Shrink operation have any impact on the Indexes?
Yes, Shrink operation on entire database or data files cause high fragmentation and slowdown the performance significantly, so it is not recommended to Shrink data files. Even in special cases if we have to Shrink a data file, then we need to rebuild the indexes immediately to reduce the fragmentation.

64. How do you migrate regular table into In-Memory table?
We can use the memory optimization advisor tool which helps us in migrate the disk-based database table into In-Memory OLTP.

65. How do I know if a Table or Stored Procedure is candidates to be migrated as In-Memory OLTP?
Transaction performance reports tool tell us which tables in our database will benefit if ported into In-Memory OLTP. Transaction performance collector tool is available in SQL Server 2014 Management Studio, which helps us to evaluate, if In-Memory OLTP will improve our database application’s performance. Transaction performance analysis report also tells how much work we must have to do in order to enable In-Memory OLTP in your application.

66. Can I use regular T-SQL queries and Stored Procedures to access In-Memory tables?
Yes, we can use regular T-SQL queries and Stored Procedures to access In-Memory tables. Also, In-memory OLTP engine introduces a new type of stored procedure called natively compiled stored procedure, which can only access In-Memory tables. When we create the natively compiled stored procedures, they query plan gets created during the compile time itself. These stored procedures are compiled by Microsoft’s Visual C/C++ compiler to native code and provides maximum performance benefits and is the preferred way of accessing In-Memory tables. At this time, there is limited T-SQL support for these stored procedures. When this SP gets compiled, DLL will be created and loaded into SQL Server Memory. We can verify the same by running above mentioned SQL query.

67. How do In-Memory tables provide high performance?
In-Memory tables reside completely in memory, so the access to the in-memory data is always very fast, as there is no requirement of physical IO from disk to memory. Using natively compiled stored procedures provide additional performance boost as they are per-compiled and no permission checks need to be performed later, as it is done during the time of compilation itself.

68. Can we create database to hold In-Memory tables using SSMS GUI?
Yes, SQL Server 2014 GUI supports creation of database with In-Memory file group and file stream data file for In-Memory usage. But we cannot create In-Memory table using GUI, we need to use T-SQL commands for that.

69. If we cannot alter the indexes, then how do we reduce the fragmentation for In-Memory indexes?
Indexes do not persist on physical storage, they are recreated every time SQL Server restarts or databases is brought online, so there won’t be any index fragmentation in in-memory indexes.

70. Can we add covering indexes for In-Memory tables?
No, Memory-optimized indexes are inherently covering, meaning all columns are virtually included in the index itself, thus bookmark lookups are not required for memory-optimized tables.

71. Can we create clustered index on In-Memory Table
No, Clustered indexes are not supported on In-Memory Tables.

72. Can we create Non-Clustered Indexes on In-Memory Tables?
Yes, but you need to create the Non-Clustered Indexes during the time of creation of the In-Memory Table itself, we cannot add new indexes, alter existing indexes or drop any indexes on In-Memory Tables, so need to plan the indexes well before hand.

73. Can my database store regular tables as well as In-Memory tables?
Yes, we can have both regular and in-memory tables in same database.

74. What are different kinds of Indexes supported for In-Memory tables?
We can create two kinds of in-memory indexes for In-Memory tables, which are Nonclustered Hash Index and Nonclustered Range Index.

Nonclustered Hash Index is used for queries which are going to perform equi-joins

Nonclustered Range Index is used for queries which performs range scans and ordered scans

75. Does Non-Clustered Indexes on In-Memory Tables take storage on the disk?
No, Non-Clustered Indexes on In-Memory Tables are only in memory, and they are not persisted on the disk. When SQL Server is stopped or Database is taken offline, the indexes are lost. Also, any operations on these indexes are not logged to transaction log file, as no recovery is required for the indexes. During startup of database, indexes are populated into the memory as part of recovery process.

76. Will my data persist even after restarting my SQL Server?
If we create In-Memory table using option DURABILITY = SCHEMA_AND_DATA, then we will have data retained even after restart of SQL Server. If SCHEMA_ONLY option is specified, then we will loose all the data

77. Can we create multiple in-memory filegroups and files?
We can create only one filegroup for storing In-Memory tables, but the filegroup can have multiple files and can be placed on different drives.

78. Can we create the In-Memory Tables in default FileGroup?
No, In-Memory Tables cannot be stored in default or regular filegroups, we need to create separate filegroup and files to store In-Memory Tables.

79. In which version of SQL Server was In-Memory OLTP introduced? or What is the top feature in SQL Server 2014
In-Memory OLTP introduced was introduced with SQL Server 2014 version.

80. Which SQL Server 2014 Edition include In-Memory OLTP?
In-Memory OLTP is available in SQL Server 2014 Enterprise Edition, Enterprise Evaluation and Developer Edition.

81. Does In-Memory OLTP requires applications to be modified?
Yes, the table schema has to be modified and changes to the application are required as well.

82. What happens when we create in-memory table?
When we run the DDL for creating the In-Memory table, it will be converted into a in-memory table DDL and is loaded into SQL Server memory. We can find what all In-Memory tables are created and loaded in memory by running below query

83. What can SQL Server MTL memory region contain?
MTL regios is used for thread stacks, third party DLL’s, COM components, Extended Stored Procedures, CLR objects loaded in SQL Server, linked server objects memory, any contiguous memory greater than 8 KB.

84. What can SQL Server BufferPool memory region?
SQL Server BufferPool memory region contains data or index pages which are less than or equal to 8 KB size.

86. What is Address Windowing Extensions (AWE) and how is it useful and how it can be used with SQL Server?
Address Windowing Extensions aka AWE is nothing but set of API’s which is used for addressing more than 4 GB of physical memory. On x86 system, SQL Server by default can only use 4 GB physical memory, so to allow SQL Server to use more than 4 GB of memory, AWE needs to be enabled. AWE for 32 bit SQL Server instance can be enabled either from Sp_Configure or from SQL Instance Properties from SSMS.

87. What happens on x86 system with 64 GB RAM and /3 GB switch enabled?
On 32 bit system with /3 GB switch enabled, SQL Server instance can only use a maximum of 16 GB physical memory. This is because, when /3 GB switch is enabled SQL Server process gets 3 GB of VAS, but Kernal mode VAS is reduced to 1 GB, with this 1 GB of kernal VAS, OS can only use up to 16 GB physical memory, thus SQL Server also cannot see beyond this 16 GB, so be mindful with enabling /3 GB switch.

88. Should we enabled AWE in x64 systems?
No, although we have the option to enable AWE on x64 systems, it has no affect. By default x64 systems can use large amounts of physical memory as it has 8 TB if user VAS, so no requirement of using AWE.

89. What is Lock Pages in Memory?
“Lock Pages in Memory” is Operating System policy, which allows a process to lock its data in memory and does not trim/release that memory working set under memory pressure. This is not completely true, as Operating System can trim working set of any process if required, but it will only trim the process workingset with lock pages in memory enabled at the last. Enabling this for SQL Server service account, will lock bufferPool pages in memory and will not trim the BPool unless OS is completely out of memory and all other processes memory has been already trimmed.

90. Should we enabled Lock Pages in Memory for SQL Server instance?
On x86 system, to use AWE, lock pages needs to be enabled.
On x64 system, if the server is dedicated SQL Server system, then it is good to enable lock pages in memory.

91. Is SQL Server Memory architecture different in x86, x64 and WoW modes?
Yes, SQL Server has different architecture for servers with x86, x64 and WoW modes of SQL Server instances, primarily because of how Virtual Address Space (VAS) is allocated by the Operating System. On x86 system, VAS is limited to 4 GB and out of this 4 GB, SQL Server can only use VAS up to 2 GB by default or 3 GB if PAE or /3 GB switch is enabled, where as in x64 systems, it is as high as 16 TB and SQL Server can use up to 8 TB of VAS.

92. What is Virtual Address Space (VAS)?
Virtual Address Space (VAS) is a Operating System concept, which is nothing but set of addresses which are allocated to each process running on the system. The idea behind VAS is, If processes running on system are only allowed to access the physical RAM, then very quickly the system will run out of memory, so VAS concept was employed, where each process is allocated some virtual addresses and when required, the processes can request the OS to map their virtual addresses with the physical addresses where the data is to be loaded. This way all processes get their own private virtual addresses and can get physical memory allocated as and when required. This way Operating System can manage the available physical memory efficiently across all the processes.

93. What are the available Virtual Address Space (VAS) on x86 and x64 systems for SQL Server?
SQL Server is also a user processes, so it gets its own private Virtual Address Space (VAS) from the Operating System. On x86 system, 4 GB of VAS is available in a system, which is divided into two regions, User mode VAS and Kernal mode VAS. Each process running on the server gets its own 2 GB of VAS and remaining 2 GB is available for the OS for its own use. We can increase the User mode VAS of a process from 2 GB to 3 GB using /3GB switch or /USERVA. so, SQL Server on x86 system is a user process, so it gets 2 GB or 3 GB or VAS depending upon the /3 GB switch. On a x64 system, VAS available 16 TB of which 8 TB is available for each process and 8 TB is available for the OS. SQL Server On x64 system, can use up to 8 TB of VAS.

94. What is SQL Server Memory Architecture or How is SQL Server Memory Divided?
SQL Server user VAS is divided into two regions, which are BufferPool and Memory-To-Leave (MTL).

BufferPool – BufferPool is the region of memory which is used for storing the in memory data and index pages which are <= 8 KB of size.

MemToLeave (MTL) – MTL is contiguous memory which is allocated during the startup of SQL Server, which is used for SQL Server thread stacks, third party DLL’s, Extended Stored Procedures, COM objects, Memory used by linked servers, CLR, any memory allocated for requests greater than 8 KB of contiguous memory.

96. How are the memory sizes for BufferPool and MemToLeave (MTL)?
Size of BufferPool and MTL differs from x86 and x64 systems. On x86 system, during startup of SQL Server MTL is reserved, which is contiguous memory and by default is 384 MB on systems with less than 4 processors. More accurate way of calculating the size of MTL is as below.

MTL = (Size of Stack Size * Number of SQL Server Worked Threads) + Additional space reserved, by default 256 MB and the value specified in -g startup parameter).

– Size of stack on x86 system is 512 KB
– Number of worked threads on a server can be different based on available processors. On x86 system with <= 4 processors, there will be 256 worker threads available, but can be changed using sp_configure options as well. Refer here for more information about worked threads.

So, on x86 system with <= 4 processors and -g option not used, MTL = ((256 * 512 KB)/1025) MB + 256 MB = 384 MB. Once this 384 MB contiguous memory is allocated out of available 2 GB VAS, there will be 1.6 GB of memory remaining which will be used as BufferPool memory.

On a x64 system, SQL Server Stack Size is 2048 KB. So a x64 system with <=4 processors will have MTL = ((512 * 2048)/1024) MB + 256 MB = 1280 MB. This is contiguous memory allocated during the startup of SQL Server and then BufferPool is allocated and finally remaining VAS is left to be used for MTL.

BufferPool is also calculated, where BufferPool = Minimum(Physical memory, User address space – MTL) – BUF structures

On x86 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 2 GB – 384 MB) – 32 MB = 1632 MB ~ 1.59 GB

On x64 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 8 TB – 1280 MB) – 32 MB = 4064 MB ~ 3.96 GB

97. What are Recovery Models in SQL Server?
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.

Full Recovery Model in SQL Server – SQL Server FULL recovery is very commonly used recovery model allows SQL Server to store all transactions data in the transaction log until a transaction log backup occurs or the transaction log is truncated. Full recovery model is the most complete recovery model which allows us to recover all of the data to any point in time, as long as all backups are available.

SIMPLE recovery model in SQL Server – SIMPLE recovery model is the most basic recovery model in SQL Server where every transaction is still written to the transaction log, but once the transaction is committed and the data has been written to the data file the space used for that transaction can be reused for new transactions. As the log file is getting reused, we cannot perform log backups and thus cannot recover data to point in time. We can only restore an FULL database in case of disaster and causes loss of data. This recovery model is used for databases which are not critical and where data loss is acceptable.

Bulk-Logged Recovery Model in SQL Server – In Bulk-Logged Recovery Model all transactions are logged similar to FULL recovery model except for transactions which involve bulk operations like BULK INSERT, CREATE INDEX, SELECT INTO, etc. These operations are not fully logged in the transaction log and therefore do not take as much space in the transaction log, but does not allow point-in-time recovery of database during the time when there are bulk transactions. This recovery model is used if there are regular bulk operations which happen on a database.

98. How to plan a backup strategy in SQL Server?
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.

The planning for database backups change based on server to server, or can be different in different environments depending upon Size of database, importance of data, amount of allowed data loss and available storage to store the backups.

Example1:
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan

– Set the database to SIMPLE recover model.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.

Example2:
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan

– Set the database to FULL recover model.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.

99. How do you recover a database which was crashed or corrupted using database backups?
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.

Normally, you will be give a backup scenario and will be asked on how you will you recover the database using the available backups. Below is the scenario.

Scenario: There is a backup strategy in place where you have weekly FULL database backup which is performed every Sunday at 12:00 AM. There are daily Differential backup which is performed at 12:00 AM, except on Sundays. There are transaction log backups which are performed every 1 hour. Database crashed on Tuesday at 06:10 AM, now you need to recover as much data as possible, how will you recover the data?

Answer: First, we need to restore FULL database backup which was performed on recent Sunday at 12:00 AM. This needs to be restored with NoRecovery option. Next restore latest differential backup which was performed on Tuesday at 12:00 AM with NoRecovery option. Restore all transaction log backups in sequence taken from Tuesday 12:00 AM till 05:00 AM with NoRecovery option and finally restore the last transaction log file taken at 06:00 AM with Recovery option. This should now bring the database online with all the data till Tuesday 06:00 AM. There will be 10 minutes worth loss of data which was stored between 06:00 AM to 06:10 AM.

100. How will database recovery model impact database backups?
First the database recovery model is responsible for the retention of the transaction log entries.  So based on the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.

• Simple – Committed transactions are removed from the log when the check point process occurs.
• Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.
• Full – Committed transactions are only removed when the transaction log backup process occurs.

Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.

– When a database is set to SIMPLE recovery model, only FULL and DIFFERENTIAL backups are allowed. Transaction Log backups cannot be performed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.