MSSQL Concurrency Control and Locking – MSSQL Interview questions with answers

Posted on October 29, 2014 by uday arumilli

Concurrency Control And Locking

http://3.bp.blogspot.com/-zSUiqLh2JzY/Ubd8AYRzTyI/AAAAAAAAAS4/-7dDSJz1LMY/s1600/deadlock.jpg

https://d3glfbbr3jeumb.cloudfront.net/assets/key-value-store/features/concurrent-connections-208bcc5d5db456d66914e808c42a4c05.png https://d3glfbbr3jeumb.cloudfront.net/assets/key-value-store/features/non-blocking-2fee7486f09488eb7ce59209293e6638.png

SQL Server Concurrency Control Interview Questions

Concurrency and Locking – Interview Questions with answers

1. What are the concurrent problems occur in accessing database?

Ans:

Tr – Transaction

R – Resource

Uncommitted dependency/dirty reads:

Tr1 Updates Data R1

Tr2 Reads Data R1

Tr1 Rollback the Update Operation R1

Now Tr2 has the inconsistent data or wrong data.

Inconsistent Analysis/non-repeatable reads

Tr1 Reads Data R1

Tr2 Updates Data R1

Tr1 Again Reads the same data R1

Wrong match between first Tr1 and Second time Tr1

Phantom reads (via insert/delete)

Tr1 Reads Data (Result Contains 10 Records R1

Tr2 Insert/Delete Data (insert 6 new delete 1 Record) R1

Tr1 Again reads the same data R1

In Second time Tr1 we found 6 New Records and we can’t find a record which retrieves in first time…..

2. What isolation levels will provide completely read-consistent views of a database to all transactions?

Ans:

SQL Server 2000: Only the SERIALIZABLE isolation level will provide a completely read-consistent view of a database to a given transaction. In any of the other isolation levels, you could perceive some/all of the following, depending on the isolation level running in:

    • Uncommitted dependency/dirty reads
    • Inconsistent Analysis/non-repeatable reads
    • Phantom reads (via insert/delete)

SQL Server 2005 and above: Both the SERIALIZABLE and SNAPSHOT isolation levels will provide a completely read-consistent view of a database to a given transaction. In any of the other isolation levels, you could perceive some/all of the following, depending on the isolation level running in:

    • Uncommitted dependency/dirty reads
    • Inconsistent Analysis/non-repeatable reads
    • Phantom reads (via insert/delete)

3. Within the READ_COMMITTED isolation level, during a read operation how long are locks held/retained for?

Ans:

When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis. The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row. Thus, if you run a simple select statement under read committed and check for locks, you will typically see at most a single row lock at a time. The sole purpose of these locks is to ensure that the statement only reads and returns committed data. The locks work because updates always acquire an exclusive lock which blocks any readers trying to acquire a share lock.

4. Within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking, how long are locks held/retained for?

Ans:

Within either of these isolation levels, locks are held for the duration of the transaction, unlike within the READ_COMMITTED isolation level as noted above.

5. Can locks ever be de-escalated?

Ans:

No, locks are only escalated, never de-escalated.

6. What are the different types of lock modes in SQL Server?

Ans:

  • Shared
  • Update
  • Exclusive
  • Schema (modification and stability)
  • Bulk Update
  • Intent (shared, update, exclusive)
  • Key Range (shared, insert, exclusive)

7. Can you explain scenarios where each type of lock would be taken?

Ans:

SHARED: Used for read operations that do not change or update data, such as a SELECT statement.

UPDATE: Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks.

EXCLUSIVE: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

INTENT: Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). (Another question in the Difficult level section expands on this)

SCHEMA: Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

BULK UPDATE: Used when bulk copying data into a table and the TABLOCK hint is specified.

KEY RANGE: Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

8. What is lock escalation and what triggers it?

Ans:

The process of converting many fine-grained locks into fewer coarse-grained locks is known as Lock Escalation.

** Escalation reduces system resource consumption/overhead while increasing the possibility of concurrency conflicts

**To escalate locks, the Database Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), key-range (KEY), or row-level (RID) locks held by the transaction on the heap or index are released. If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.

**Lock escalation is triggered at either of these times:

  • When a single Transact-SQL statement acquires at least 5,000 locks on a single table or index.
  • When the number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
  • If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

9. Name as many of the lockable resources as possible in SQL Server?

Ans:

  • RID (single row on a heap)
  • KEY (single row (or range) on an index)
  • PAGE
  • EXTENT
  • HOBT (heap or b-tree)
  • TABLE (entire table, all data and indexes)
  • FILE
  • APPLICATION
  • METADATA
  • ALLOCATION_UNIT
  • DATABASE

10. What requirements must be met for a BULK-UPDATE lock to be granted, and what benefit do they server?

Ans:

The Database Engine uses bulk update (BU) locks when bulk copying data into a table, and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.

11. What is the least restrictive type of lock? What is the most restrictive?

Ans:

The least restrictive type of lock is a shared lock. The most restrictive type of lock is a schema-modification

12. What is a deadlock and how is it different from a standard block situation?

Ans:

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. In a deadlock situation, both transactions in the deadlock will wait forever unless the deadlock is broken by an external process – in a standard blocking scenario, the blocked task will simply wait until the blocking task releases the conflicting lock scenario.

13. Which 2 isolation levels support optimistic/row-versioned-based concurrency control?

Ans:

First is the READ COMMITTED isolation level.  This is the only level that supports both a pessimistic (locking-based) and optimistic (version-based) concurrency control model. Second is SNAPSHOT isolation level that supports only an optimistic concurrency control model.

14. What database options must be set to allow the use of optimistic models?

Ans:

READ_COMMITTED_SNAPSHOT option for the read committed optimistic model. ALLOW_SNAPSHOT_ISOLATION option for the snapshot isolation level

15. What is the size of a lock structure?

Ans: 

96 bytes

16. In what circumstances will you see key-range locks, and what are they meant to protect against?

Ans:

You will only see key-range locks when operating in the SERIALIZABLE isolation level.

  • Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement. The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.
  • Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

17. Explain the purpose of INTENT locks?

Ans:

The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:

  • To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  • To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

18. Can deadlocks occur on resources other than database object?

Ans:

YES.

19. What are the different types of resources that can deadlock?

Ans:

Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. Here are the resources:

Locks – Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock.

Worker threads – A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result

Memory – When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur.

Parallel query execution-related resources – Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.

Multiple Active Result Sets (MARS) resources – Resources used to control interleaving of multiple active requests under MARS, including:

  • User resource – when a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock
  • Session mutex – The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Before the task can run, it must have exclusive access to the session mutex.
  • Transaction mutex – All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Before the task can run, it must have exclusive access to the transaction mutex.

20. Explain how the database engine manages the memory footprint for the lock pool when running in a dynamic lock management mode.

Ans

SQL Server 2000: When the server is started with locks set to 0, the lock manager allocates two percent of the memory allocated to SQL Server to an initial pool of lock structures. As the pool of locks is exhausted, additional locks are allocated. The dynamic lock pool does not allocate more than 40 percent of the memory allocated to SQL Server.

  • Generally, if more memory is required for locks than is available in current memory, and more server memory is available (the max server memory threshold has not been reached), SQL Server allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application was running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated.

SQL Server 2005: When running in dynamic management mode (i.e. if the server is started with locks configuration option set to 0), the lock manager acquires sufficient memory from the Database Engine for an initial pool of 2,500 lock structures. As the lock pool is exhausted, additional memory is acquired for the pool.

  • Generally, if more memory is required for the lock pool than is available in the Database Engine memory pool, and more computer memory is available (the max server memory threshold has not been reached), the Database Engine allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application is running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error.

21. Describe the differences between the pessimistic SERIALIZABLE model and the optimistic SNAPSHOT model in terms of transactional isolation (i.e., not the concurrency differences, but instead how the exact same transactional modifications may result in different final outcomes).

Ans:

 

  • It is typically relatively simple to understand SERIALIZABLE. For the outcome of two transactions to be considered SERIALIZABLE, it must be possible to achieve this outcome by running one transaction at a time in some order.
  • Snapshot does not guarantee this level of transactional isolation.
  • Imagine the following sample scenario:

Serializable:

There is a bag containing a mixture of white and black marbles. Suppose that we want to run two transactions. One transaction turns each of the white marbles into black marbles. The second transaction turns each of the black marbles into white marbles. If we run these transactions under SERIALIZABLE isolation, we must run them one at a time. The first transaction will leave a bag with marbles of only one color. After that, the second transaction will change all of these marbles to the other color. There are only two possible outcomes: a bag with only white marbles or a bag with only black marbles.

Snapshot:

If we run these transactions under snapshot isolation, there is a third outcome that is not possible under SERIALIZABLE isolation. Each transaction can simultaneously take a snapshot of the bag of marbles as it exists before we make any changes. Now one transaction finds the white marbles and turns them into black marbles. At the same time, the other transactions finds the black marbles – but only those marbles that where black when we took the snapshot – not those marbles that the first transaction changed to black – and turns them into white marbles. In the end, we still have a mixed bag of marbles with some white and some black. In fact, we have precisely switched each marble.

22. What are the different isolation levels available?

Ans:

  • Read Uncommitted Isolation Level
  • Read Committed Isolation Level
  • Repeatable Read Isolation Level
  • Serializable Isolation Level
  • Snapshot Isolation Level
  • Read Committed Snapshot Isolation Level

23. Demonstrate Isolation levels?

Ans:

  • Read Uncommitted: This is the lowest isolation level. It only isolates transactions and activities to ensure that physically corrupt data is never read. It allows dirty reads, nonrepeatable reads, and phantom reads.
  • Read Committed: This isolation level does not permit dirty reads, but does allow nonrepeatable reads and phantom reads. This is the default isolation level for SQL Server, and is used for each connection to SQL Server unless one of the other isolation levels has manually been set for a connection.
  • Repeatable Read: This isolation level does not permit dirty reads or nonrepeatable reads, but does allow phantom reads.
  • Serializable Read: This is the highest isolation level and ensures that all transactions and statements are completely isolated from each other. It does not allow dirty reads, nonrepeatable reads, or phantom reads.

 

New isolation levels that introduced in SQL 2005 based on row versioning:

  • READ_COMMITTED_SNAPSHOT (statement level): READ_COMMITTED_SNAPSHOT is actually a variation of the default READ_COMMITTED isolation level. It uses row versioning, instead of locking, to provide read consistency at the SQL Server statement level. When a statement runs that specifies the READ_COMMITTED isolation level (the default isolation level), and the READ_COMMITTED_SNAPSHOT option is turned on at the database level, all statements see a snapshot of the data as it existed at the start of any current transaction. It uses the row-versioned snapshot of the row to return data, and no locking is needed by the statement, which is normally the case. The biggest benefit of this isolation level is that reads do not block writes and writes do not block reads. Writes can still block writes, but this is necessary to prevent data corruption.
  • ALLOW_SNAPSHOT_ISOLATION (transaction level): ALLOW_SNAPSHOT_ISOLATION is similar to READ_COMMITTED_SNAPSHOT, but it is based at the transaction level, not the statement level. When the ALLOW_SNAPSHOT_ISOLATION is turned on at the database level and the TRANSACTION ISOLATION LEVEL SNAPSHOT isolation level is turned on for the transaction (using the SET command), all statements see a snapshot of the data as it existed at the start of the transaction.

24. Any idea about row versioning?

Ans:

The concept of row versioning is not new to SQL Server, as SQL Server has been using it for years with triggers. For example, when a DELETE trigger is executed for a row, a copy of that row is stored in the “deleted table” just in case the trigger is rolled back and the deleted row needs to be “undeleted.” In a sense, the row is versioned, and if need be, can be reused.

Row versioning for isolation levels is very similar, though not identical to row versioning for triggers. When a row versioning-based isolation level (which includes the two new ones we are now discussing) is enabled at the database level, the database engine maintains versions of each row that is modified (for an entire database). Whenever a transaction modifies any row, an image of the row before the modification is copied into a page of what is called the version store. The version store is located in the tempdb database and is used for temporary storage of versioned rows for all of the databases on a single SQL Server instance.

25. What are the properties of a transaction?

Ans:

There are 4 properties called ACID.

Atomicity: All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.

Example: In an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency: Data is in a consistent state when a transaction starts and when it ends.

Example: In an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation: The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.

Example: in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability: After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.

Example: in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

26. How to find out and prevent Deadlocks?

Ans:

To find Deadlocks

Error:

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There are two popular ways to identifying the deadlocks

  • Enabling a Trace Flag
  • By default Deadlocks are not written into sql server errorlog, to do so we have to enable a trace flag.
  • Trace Flag 1204 – SQL Server 2000 or below
  • Trace Flag 1222 – SQL Server 2005 or above
  • Syntax: DBCC TRACEON (1222, -1)

Note: -1 indicates trace should run for all sessions

  • Using the profiler:
  • We need to capture the Lock Events Lock: Deadlock and Lock: Deadlock Chain along with the ObjectID data column

To prevent Deadlocks:

  • While updating have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Keep transactions as short as possible. To accomplish this when your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Develop your application to grab locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use low level isolation level according to the possibilities
  • Look for other opportunities to improve the efficiency of the queries
  • If both deadlock participants are using the same index, consider adding an index that can provide an alternate access path to one of the spids.

27. What is deadlock priority and how to change this value?

Ans:

  • DEADLOCK_PRIORITY option dictates how the spids are handled when a deadlock occurs. The default deadlock priority is NORMAL.
  • SET DEADLOCK_PRIORITY allows a process to determine its priority for being chosen as the victim using one of 21 different priority levels, from –10 to 10.
  • We can also use LOW(-5), NORMAL (0) and HIGH (5)
  • Default setting is NORMAL (0).
  • DEADLOCK_PRIORITY setting is considered at run time, not at the parse time
  • Syntax: SET DEADLOCK_PRIORITY [HIGH/LOW/[-10 to 10]];

28. Let’s say we have enabled a trace flag to log deadlock information in SQL Server error log. What kind of information we can get from the log?

Ans:

DBID: Database ID from which the transaction initiated

SPID: Which is nothing but connection ID from which the actual query completed/killed

Victim: The process which killed by Victor

Victor: The process which won the deadlock means completed successfully

Proc Name: Name of the procedure if in case the transaction initiated from a procedure

SQL Handle: SQL Handle and also input buffer QUERY also displayed

Page Info:

We can also get page information which causes the deadlock (This we can get when trace flag 1204 is enabled). There is a code to understand about the page.

If IndId = 0:

  • Data Page if there is no clustered index on that table
  • Clustered index leaf page is there is a clustered index available on that table

If IndId = 1:

  • Non-Leaf page of the Clustered Index

If IndId = 255:

  • Text Image Page

If IndId = Other value:

  • Non Clustered Secondary Index

29. Can we include DDL inside a transaction? Does it works if it rollbacks?

Ans:

Yes! We can use DDL commands inside a transaction and these DDL commands also follow the transaction ACID properties. For example if we create a table inside a transaction and the transaction rolled back then the table will also be dropped.

30. Are you using In-Memory OLTP? Any Idea what are the ISOLATION LEVELS supports for memory optimized tables?

Ans:

Yes we tried In-Memory on Pre-Prod (2014) but due to lot of limitations we stopped thinking of it. In-Memory OLTP is improved well in SQL Server 2016. However Memory optimized tables supports ISOLATION LEVELS as below:

  • READ UNCOMMITTED: Doesn’t support
  • READ COMMITTED: Supports only when AUTOCOMMIT mode is on
  • SNAPSHOT: Supports
  • REPEATABLE READ: Supports
  • SERIALIZABLE: Supports

1000 Plus Professional SQL Server Interview Questions and Answers

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , | 1 Comment

MSSQL Security – Interview Questions and Answers

Posted on October 11, 2014 by uday arumilli

sql server security interview questions

 

Database Process Error.jpg

1. What is the Guest user account in SQL Server?  What login is it mapped to it?  

Ans:

The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object.  It is not mapped directly to any login, but can be used by any login.  Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB

2. What is the use of BUILTIN\Administrators Group in SQL Server?

Ans:

Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective

3. We have a list of 3 SQL Server logins which are dedicated to a critical application. We have given all required rights to those logins. Now my question is we have to restrict the access only to these three logins. Means there are two conditions:

a) No other user should be able to access the database except those three logins

b) Even for those three logins they should be able to run their queries only through the application. If someone login through SSMS and trying to run a query should result into a failure.

Finally there should be only way to running a query is from their application using one of those three logins, there should be no other way to run queries on that database. How do you restrict?

Ans:

  • Do not give access to any other login on that database except for those 3 app logins.
  • Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)
raiserror (…..)
Return

4. How to resolve the orphan use problem?

Ans:

Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

  • To find out the orphan users
USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

 

  • To resolve the orphan user problem
USE <database_name>;

GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

 

5. What are the fixed server level roles?

Ans:

  • SysAdmin – Can perform any activity
  • ServerAdmin – Can change server configuration, restart, shutdown server
  • SecurityAdmin – Can manage server level logins, also can manage db level if they have permission on db
  • Granted: ALTER ANY LOGIN
  • ProcessAdmin – Can kill a process on an instance
  • Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
  • DiskAdmin – Can manage the disk files
  • Granted: ALTER RESOURCES
  • BulkAdmin – Can perform BULK INSERT
  • Granted: ADMINISTER BULK OPERATIONS
  • SetupAdmin – Can add and remove linked servers
  • Granted: ALTER ANY LINKED SERVER
  • Dbcreator – Can create, alter, drop and restore any database on the instance
  • Granted: CREATE ANY DATABASE
  • Public – Default role for newly created login

sp_helpsrvrolemember : List out the members mapped with the server roles

6. What are the Database roles?

Ans:

  • db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant option – Connect
  • db_backupoperator – Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
  • db_datareader – Granted – SELECT
  • db_datawriter – Granted – INSERT, UPDATE and DELETE
  • db_ddladmin – Granted – Any DDL operation
  • db_denydatareader – Denied – SELECT
  • db_denydatawriter – Denied – INSERT, UPDATE and DELETE
  • db_owner – Granted with GRANT option: CONTROL
  • db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
  • dbm_monitor – Granted: VIEW most recent status in Database Mirroring Monitor

sp_helprolemember : List out the members mapped with the server roles

Note:

Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role.

7. What are the security related catalog views?

Where the security related information stored on?

Ans:

  • Server Level:
  • Sys.server_permissions
  • Sys.server_principals
  • Sys.server_role_members
  • Sys.sql_logins
  • Database Level:
  • Sys.database_permissions
  • Sys.database_principals
  • Sys.database_role_members

8. What are the extra roles available in msdb?

Ans:

  • db_ssisadmin: Equals to sysadmin
  • db_ssisoperator: Import/Delete/Change Role of own packages
  • db_ssisltduser: Only can view and execute the packages
  • dc_admin : Can administrate and use the data collector
  • dc_operator: Can administrate and use the data collector
  • dc_proxy : Can administrate and use the data collector
  • PolicyAdministratorRole: can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
  • ServerGroupAdministratorRole : Can administrate the registered server group
  • ServerGroupReaderRole: Can view and the registered server group
  • dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor

9. If you lose rights to your SQL Server instance what are the options to connect to SQL SERVER Instance?

Ans:

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTIN\Administrators Group

Option3: Change Registry Values

10. What objects does the fn_my_permissions function reports on?

Ans:

  • SERVER
  • DATABASE
  • SCHEMA
  • OBJECT
  • USER
  • LOGIN
  • ROLE
  • APPLICATION ROLE
  • TYPE
  • MESSAGE TYPE
  • ASYMMETRIC KEY
  • SYMMETRIC KEY
  • CERTIFICATE
  • SERVICE
  • REMOTE SERVICE BINDING
  • FULLTEXT CATALOG
  • ASSEMBLY
  • CONTRACT
  • ENDPOINT
  • ROUTE
  • XML SCHEMA COLLECTION

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);

SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)

11. Name three of the features managed by the Surface Area Configuration tool.

Ans:

  • Ad-hoc remote queries
  • Common language runtime
  • Dedicated Administrator Connection
  • Database Mail
  • Native XML Web Services
  • OLE Automation
  • Service Broker
  • SQL Mail
  • Web Assistant
  • xp_cmdshell

12. What options are available to audit login activity?

Ans:

  • Custom solution with your application to log all logins into a centralized table
  • Enable login auditing at the instance level in Management Studio
  • Execute Profiler to capture logins into the instance
  • Leverage a third party product

 

13. How to perform backup for Certificates in sql server?

Ans:

  • Using Native Backup
  • Using Backup Certificate Command

14. Name 3 of the features that the SQL Server built-in function LOGINPROPERTY performs on standard logins.

Ans:

  • Date when the password was set
  • Locked out standard login
  • Expired password
  • Must change password at next login
  • Count of consecutive failed login attempts
  • Time of the last failed login attempt
  • Amount of time since the password policy has been applied to the login
  • Date when the login was locked out
  • Password hash

15. How can SQL Server instances be hidden?

Ans:

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.

16. Is Profiler the only tool that has the ability to audit and identify DDL events?

Ans:

No. In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.

17. What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?

Ans:

Pros:

  • Any Windows login is by default a SQL Server system administrator
  • This single group can be used to manage SQL Server from a system administrators perspective

Cons:

  • Any Windows login is by default a SQL Server system administrator, which may not be a desired situation

18. What is SQL Injection and why is it a problem?

Ans:

SQL Injection is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner.  It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.

19. How can SQL Injection be stopped?

Ans:

Development\DBA

  • Validate the SQL commands that are being passed by the front end
  • Validate the length and data type per parameter
  • Convert dynamic SQL to stored procedures with parameters
  • Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
  • Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server

Network Administration

20. How to recover from SQL Injection?

Ans:

If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:

  • Shut down the web sites
  • Review the IIS logs to determine the commands issued and which web page\command has the vulnerability
  • Convert the code to determine which tables were affected and the command issued
  • Find and replace the string in your tables
  • Correct the web page\command that has the vulnerability
  • Test to validate the issue no longer occurs
  • Deploy the web page\command
  • Re-enable the web sites

21. How to enforce Security in SQL SERVER?

Ans:

By providing strong Passwords, Limited the access to make sure right people have access to the right data, Creating Customized database roles, server roles and assign privileges and by choosing the correct authentication mode etc.

A DBA should be careful in providing security…..General precautions includes:

  • Minimize the number of sysadmins allowed to access SQL Server.
  • Give users the least amount of permissions they need to perform their job.
  • Use stored procedures or views to allow users to access data instead of letting them directly access tables.
  • When possible, use Windows Authentication logins instead of SQL Server logins.
  • Don’t grant permissions to the public database role.
  • Remove user login IDs who no longer need access to SQL Server.
  • Avoid creating network shares on any SQL Server.
  • Turn on login auditing so you can see who has succeeded, and failed, to login.
  • Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
  • Using server, database and application roles to control access to the data
  • Securing the physical database files using NTFS permissions
  • Using an un guessable SA password
  • Restricting physical access to the SQL Server
  • Disabling the Guest account
  • Isolating SQL Server from the web server
  • Choose either of the service to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)
  • Restrict the remote administration (TC)
  • If SQL Server authentication is used, the credentials are secured over the network by using IPSec or SSL, or by installing a database server certificate.
  • Do not use DBO users as application logins
  • Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
  • Remove the SQL guest user account.
  • Remove the BUILTIN\Administrators server login.
  • Apply the latest security updates / patches

We have plenty of features in SQL SERVER to enforce the security. The major features include:

  • Password policies
  • Encryption
  • Limited metadata visibility (system Tables to Catalog Views)
  • DDL triggers
  • User-schema separation
  • Impersonation
  • Granular permission sets
  • Security catalog views

In addition to these features we have some more added in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security etc.

When we talk about the security we have to consider the bellow

  • Patches and Updates
  • Services
  • Protocols
  • Accounts
  • Files and Directories
  • Shares
  • Ports
  • Registry
  • Auditing and Logging
  • SQL Server Security
  • SQL Server Logins, Users, and Roles
  • SQL Server Database Objects

22. You are delegating permissions on your SQL Server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Ans:

SQLAgentUserRole
SQL Server provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allow the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

23.What is application role in SQL Server database security?

Ans:

Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database. Here it is how it works:

  • Create a login on SQL Server for application user
  • Create an application role on the corresponding database.
  • Give the application role password to the user
  • User will have access to login to SQL Server but doesn’t have any access to the database including public role.
  • He/she just need to provide the password to unlock the access to the database
  • EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
  • Once it is executed successfully the user will get all rights that your app role have on that database. 

24. What are the new security features added in SQL Server 2012?

Ans:

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain\ WinAdmin]. If someone from this group logged in [MyDomain\User1] and tried to create an object then there will be a new schema created like [MyDomain\User1].Table. This issue got fixed in 2012. In 2012 we can assign a default schema for the Windows Group accounts.

User Defined Server Roles: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.

Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits. Ex: We can create an Audit specification to trace all events for a specific login and write all these event details into Audit Log. We can also filter the events.

25.What is the new security features added in SQL Server 2014?

Ans:

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the normal backup and then applies the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.

IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

26. What is the new Security features added in SQL Server 2016?

Ans:

Always Encrypted:

  • This is a new feature which is useful for managing highly sensitive data
  • Unlike TDE it encrypts data at rest means physical files (Data, Log and Backup), data in memory and data in communication channels.
  • TEMPDB is uninvolved from encryption
  • Encryption can be applied to column level.
  • A driver that encrypts and decrypts the data as it is sent to the database server is installed on the client.
  • Application connection string must be changed.

Row Level Security:

  • This is first introduced in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 2016.
  • Data need not be encrypted but we can restrict the users to see the sensitive data. No master keys or certificates required as there is no encryption
  • Row-level security is based on a table-valued function which evaluates user access to the table based on a security policy that is applied to the table.
  • The access levels only applies to SELECT, UPDATE, DELETE operations but anyone who is having INSERT permissions can insert rows.
  • Only problem with this is using user defined functions to control user access which is a huge disadvantage from performance prospect.
  • Dynamic Data Masking:
  • Masks data at select time based on user or database roles (Mainly for Read-only Users).
  • It actually doesn’t change the data but mask data based on the user who access that data.
  • For example I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.
  • But as I said data is not modified only this logic applied and data is masked based on the user/role.
  • A SYSADMIN or db_owner can view the actual data.
  • We can use 4 different types of functions to mask data; Email, Partial, Default, Random

1000 Plus Professional SQL Server Interview Questions and Answers

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , | 5 Comments

Interview Questions

Posted on October 4, 2014 by uday arumilli

sql server experienced interview questions

Hello All, I have been collecting interview questions from the people who given interviews at various organizations. Below are the list of questions. It includes, SQL DBA, MSBI, SQL Developer, SQL Server.

1

​

Organization Microsoft IT
Position MSBI Developer
Location Hyderabad, India

SSRS:
1. How to render a report to a user email?
2. How to join two datasets and use in a single report?
3. How to do paging in SSRS?
4. How to deal with multi valued parameters?
5. I want one page should be displayed in landscape and other pages in different formats in a report. Can you design a SSRS report for this requirement?
6. How to tune the performance of SSRS report?
7. How to design a Drilldown report?
8. What is the difference between Table and Matrix?

SSIS:
1. Design a SSIS package for the following requirement. Create a table, load data and move to the different schema using SSIS.
2. Are you following any framework for ssis?
3. How to execute parent and child ssis packages? Locate 1000 files location?
4. How to add users to a database using SSIS?
5. Which tasks should be used to import a million records txt files?
6. What are the configuration options for parent child packages?
7. What is the new configuration option added in SQL Server 2012?
8. What is the difference between “Execute SQL” and “Execute T-SQL” tasks?

T-SQL:
1. Why we can’t put Order by inside the view?

2

Organization Berkadia
Position Sr. MSBI Developer
Location Hyderabad, India

1. How to call a web service from SSIS?
2. What are the performance issues you have faced in SSIS?
3. Why SSIS? What is the use of ETL?
4. What is difference between SSIS and SSAS?

5. What is a dimensional databases?

6. Difference between Dim and Relational?
7. What is a cross apply and how to use this?
8. How to handle a result set from a webservice or Execute SQL task?

3

Organization Capgemini
Position Sr. MSBI Consultant
Location Hyderabad, India

!. What is a surrogate keys? How to call a unique column in dimension?
2. I have a table
Customer    Book
C1               B1
C2               B1
C3               B1

I need output: All combination of all customers. Only distinct values

Example:

Customers
C1,C2
C1,C3
C2,C3

3. Can we use more than one CTE in a single select query?
4. We have three tables (Customer, Targets and Sale tables)

Customer – CID, CName
Targets – TID, CID, Target_Amt
Sale – SAID, CID, Sale_Amt

I need an output as below:

CID, CName, Total_Target_Value, Total_Sale_Value

;WITH CTE AS (
select t.cid,sum(t.target_amt) as targets
from targets t
group by t.cid)
select    c.cid,
c.cname,
ct.targets AS ‘Total_Target_Value’,
sum(s.sale_amt) as ‘Total_Sale_Value’
from customer c
INNER JOIN SALE S on S.cid=c.cid
INNER JOIN cte ct on ct.cid=c.cid
group by c.cid,c.cname,ct.targets

5. What is drill across through report?

​6. How to implement type 2 SCD using SSIS and queries?
​​

4

Organization Bank of America
Position Sr. MSBI Developer
Location Hyderabad, India

1. I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?

2. How do you know the total transaction count?

3. I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?

4. If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database?

5. Can we fire a trigger manually?

6. What are the magic tables? Do we have “Updated” magic table?

7. What is the difference between UnionAll and Merge?

8. What is Copy Column transformation?

9. I have a requirement that task2 has to be executed irrespective of Task1 result. How can you accomplish this?

10. Basic difference between stored procedure and user defined function?

11. See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?

12. Can we call Dataflow as a container?

13. While using SQL configurations, how you let your package direct to choose the package configuration file?

14. Do you use a single package or multiple packages for all environments?

15. Your dataflow is configured to use event handlers. I wanted one of the transformation inside the dataflow should skip the

16. What are the latest features in SSIS 2012?

17. How to use configurations in SSIS?

18. How to validate data in SSIS? Give some example.

5

Organization HCL
Position Sr. MSBI Developer
Location Bangalore, India

1. How to keep header in all pages – SSRS?
2. How to add custom code to change the colour of a node in report – SSRS
3. I have some .csv files, how to load them into SQL Server explain step by step.
4. How to send a report as an attachment to an email and HTML report should not be added.
5. What is the difference between OLEDB – Provider , ADO.NET and SQL Server destination?

6
​

Organization Liquidhub
Position MSBI Consultant
Location Hyderabad, India

​​1. What is a hash join in execution plan?

​2. What is a sub report?

3. Difference between drill through and sub reports?

3.​What is a shared dataset?

4. Can we use shared dataset with the subreport?
5. What is the deployment mechanism for ​​SSRS deployment?

6. What are the common performance issues in SSRS?
​7. ​Can you tell me top 5 new features in SSIS and SSRS ​Both in 2008 and 2012?​

7
​​​

Organization BARCLAYS
Position Sr.MSBI Developer
Location Singapore

1. What kind of dashboards you have prepared using SSRS reports?

​2. Have you ever tried logging in SSIS? What are the tables on which log information stored? Can we enable logging for only selected items from SSIS package? if yes how?
3. Design a package to load .xlsx files from a folder. File names must include either “Finance_” or “Local_” and load files only from the last week.

​​4. How to capture column name and row number when something failed in dataflow?
5. Design a SSIS package to load a list of excel files. Once a file is loaded that file should be moved / archived to new location.
​6. Write a query to retrieve all employee hierarchy in an organization?
7. Design a SSRS report that can support dynamic columns.

8. Why should we use CTE?

9. How to use partition tables?

10. What is ETL, why specially SSIS?

11. What’s the max parallel value to SSIS?

12. See I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?

 

 

8

Organization Franklin Templeton
Position Sr.Associate – MSBI
Location Hyderabad, India

​Q. SSIS 2008 uses all available RAM, and after package completes Memory is not released. Have you ever faced these issues if yes can you explain how you resolved it?

​Q. Can you give some examples for de-generated dimension?

Q. We have a requirement like this. For every 10 days there is a data load happens in our database using a SSIS package. My requirement is once the ETL finished successfully then a SSRS report has to be emailed to the corresponding group of members. How can you do that?

Q. How to remove PDF from the export options in SSRS report?

Q. What are the different approaches to deploying SSIS packages in development, staging and production environments?

Q. Did you remember any error codes which are occurred while executing SSIS package?

Q. I have executed an SSIS package, usually it takes 3 hours but this time the package has taken 7 hours and the execution is still in progress. Can you describe what all the various approaches to troubleshoot this issue?

 

9

Organization Cognizant
Position Sr.Associate – MSBI + T-SQL
Location Hyderabad, India

​

1. Do you know about BI Symantec Model (BISM)?

2. Have you experienced filter index partitions
3.What is the difference between sub query and correlated query
4. What is the difference between pessimistic locking and optimistic locking?
5. What are the user roles available in SSRS?
​6. Can you write a query to find nth highest salary of an employee?

7. Write a query to delete duplicate records from a table. Note: Write queries that suits the below situation. A table is having a unique key and having duplicates records, table is not having a unique and having duplicate records.

8. What is XACT_ABORT ON?
9. How to filter nested stored procedure code from profiler?

10. How to find the number of transactions in the current scope?

11. What happens when a rollback happens in inside a nested stored procedure?
12. Example for degenerated dimension?

13. What is a bitmap index?
​14. How to avoid bookmark lookup in execution plan?
​15. ​How to avoid sort operation in SSIS?

16. ​When index scan happens?

17. What are the data regions in SSRS?

10

​Organization Tech-Mahindra
Position Technology Lead – MSBI + T-SQL
Location Hyderabad, India

​1. What kind of join happens in lookup transformation?

2. Did you find any issues using Full cache mode?

3. Does temp tables and table variables both stored in tempdb?

4. Why cursors are so costly?

5. Have you ever used custom assembles in SSRS?

6. How to enhance SSRS functionality?

7. Can we move sql server logins and users using SSIS?

8. Which is the most critical ETL task you implemented using SSIS?

 

​11

Organization Datamatics
Position Consultant – MSBI + T-SQL
Location Bangalore, India

1. Can we call a procedure from a function?

2. Can we write DML inside a function?

3. How to design a report to show the alternative rows in a different colour?

4. Write a code to customize the SSRS report. Where the code has to be written?

5. In a SSRS report where to write custom code?

​6. How to troubleshoot SSRS report using ExecutionLog2?

7. Have you ever seen .rdl file? What are the different sections in .rdl file?

​

12

Organization Hitachi Consulting
Position Sr.SQL Developer
Location Hyderabad, India

​1. What is the best value for MAXDOP value?

2. Which is better “Left Outer” Or “NOT EXIST”? Explain.

3. How to find the statistics are outdated?

4. How to find the query running on a given SPID?

5. What is XACT_ABORT ON?

6. Can we use two CTE’s in a single select query?

7. What are the different join operators in SQL Server?

8. Can we use a table variable inside a nested stored procedure if the table variable created in parent stored procedure?

9. What are the limitations on “SET ROWCOUNT”?

10. While creating a “Include” columns on which basis we should consider main column and include columns?

11. How to find the last statistics update date?

13

Organization S&P Capital IQ
Position Sr. Database Engineer
Location Hyderabad, India

 

1. An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table.

2. I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?

3. Does “TRUNCATE” DDL or DML command?

4. I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?

​14

Organization innRoad
Position Sr. SQL Server Developer
Location Hyderabad, India

1. What is index reorganization?

​2. How sql engine knows which index has to be used while dealing with indexed views?

3. How to prevent bad parameter sniffing? What exactly it means?
4. What dll file that handle the transaction logs in logshipping?

5. How to find all dependent objects of a table?
6. How to find the latency in replication?

7. What are the tracer tokens in replication?
8. How to remove articles with generating a new snapshot?

9. I am not able to select some of the articles from publishing list, what might be the reason?

10. ​What is DAC? How it exactly works?

11. On which port DAC runs on?​

12. ​Which agent will take care of database mirroring?​

15

Organization Tech Mahindra
Position SQL DBA
Location Hyderabad, India

 

1. Why can’t we take a backup of a database which is in standby or in recovery mode?
2. How to move a database which is acting as a publisher in transactional replication?
3. What is CROSS APPLY?
4. How to find and remove duplicates from a table which is having 10 million rows?
5. Which is better a CTE or a subquery? Why?
6. Can you tell me replication monitoring tables?
7. How to apply service packs on Active Active cluster?
8. Best practices in applying security patches
9. What is a log reader agent?
10. What is PULL and PUSH subscriptions?

16

Organization IBM
Position SQL DBA
Location Hyderabad, India

1. How to rebuild a master database? Ho to restore a master database?
2. Any alternative to triggers?
3. What are the counters to monitor CPU usage?
4. Top 5 performance tuning tools
5. What events need to be added to capture execution plan in sql profiller?
6. How to add memory to sql server 2005?
7. Which locks are held at the time of snapshot in log shipping?
8. What is the new lock escalation in sql 2008?
9. How to check the log file location for service pack 4 in sql server 2005?
10. What is a filtered index?

​17

Organization Virtusa
Position SQL DBA
Location Hyderabad, India

​1. On which basis merge replication works on?
​​2. How to give a user to run a sql agent job?​

3. Can we install SQL Server using a configure file?

4. What are the top performance counters to be monitor in Performance Monitor?

5. How do you know how much memory has been allocated to sql server using AWE?

​18

​

Organization Microsoft R&D
Position SQL Server Oops Lead
Location Hyderabad, India

1. What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
​​Ans:

​It issues an update lock and upgrades it to Exclusive Lock

The corresponding page would be captured from disk to memory

The modified page will be modified at Memory

The operation “Update *******” will be written to LDF.
Check point happens and the modified page will be written back to Disk and the operation at LDF marked as committed.

Lazy writer is responsible for cleaning the committed transactions from LDF.

2. What is fragmentation? How it happens?

3.  See we have a full backup on Sunday 8 PM, Diff and every day : 8 PM and log bkp on every 15 min. DB Crashed on Saturday afternoon 2:55 PM. How to rebuild the database? If suppose the last Sunday full backup is corrupted then how can you restore the database in current in time?

4. I have an instance on which there are databases in both FULL and SIMPLE recovery models. If I restart the sql service, what is the difference between these databases in recovering or what happens while restarting the services?

5. I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON, and essentially there are no options to allocate new space for the file. What’s your action plan?

6. Can we do replication with mirroring? If yes what are the limitations?

7. Can we perform a tail log backup if .mdf file is corrupted?

8. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?

9. What is the option”Lock Pages in Memory”?

10. How to apply service pack on Active / Passive cluster on 2008 and 2012?

11. Can we configure log shipping in replicated database?

12. How to configure replication on cross domain instances?

13. Let’s say we have a situation. We are restoring a database from a full backup. The restore operation ran for 2 hours and failed with an error 9002 (Insufficient logspace). And the database went to suspect mode. How do you troubleshoot this issue?

14. Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a new log file on other drive

15. Can we add an article to the existing publication without generating a snapshot with all articles?

19

Organization Genpact
Position Lead SQL DBA
Location Hyderabad, India

1. Difference between 32 bit and 64 bit

2. How B-Tree formed for Clustered and non clustered indexes?

3. How B-Tree forms for indexes with included column?

4. Does alzebrizer tree stores in memory for stored procedures, views and constraints?

5. What is WOW and WOW64?

6. How to design TempDB files? And what is the limit?

7. What are the different queues of CPU?

8. Write a query to show what’s happening on instance with description?

9. How VLF’s created for tempDB?

10. When the checkpoint can happen? What it exactly do?

11. When the lazywriter happens and what it’ll do?
12. What is total server memory and target server memory?

13. What is memory grant in SQL Server?

14. Why resourceDB introduced?

15. How to move master database?

16. How to rebuild master database and what are the considerations?

17. What is a boot page?

18. What is fragmentation?
19. What is edition limitation in sql server 2008 for database mirroring with asynchronous mode?

20. Do you know why SQL Browser is?

21. How to upgrade SSRS reports?
22. How do you know that log shipping break down?

23. What is the difference between push and pull subscription?

 

20

Organization E&Y
Position Sr. SQL DBA
Location Kochi, India

1. Can we truncate a table which is participating in transactional replication?

2. How to identify log filling issue and can we issue shrink with truncate on publisher database?

3. How to filter the nested stored procedure or a command from profiler?

4. Can we add articles to the existing publication without initialization of the snapshot?

5. How MAXDOP impacts SQL Server?

6. How distributed transactions works in SQL Server?

7. Full back up size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?

8. What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

21

Organization Infosys
Position SQL DBA
Location Mysore, India

1. ​Are tables locked during generating the snapshot?
2. Does Truncate Works in replication? What are the limitations of Truncate command?
​​3. ​​Causes for slow replication?

4. How to add articles to existing publication without generating a full snapshot?
5. Can we add only new articles to merger publication?

6. What is re-initialization in replication and how it works?
​7. What is the difference between lazy writer and checkpoint?
8. How to move master database?
9. What are the different shrink options?
10. Can you explain the log file architecture?

​22

Organization Factset
Position MSBI Developer
Location Hyderabad, India

1. What is the main difference between mirroring and always on?

2. What is Microsoft Best Practices Analyzer?

3. What is Microsoft Baseline Configuration Analyzer?

4. What are the different database patterns?

5. Any idea types of data warehouse?

6. What is SCD type – 2?

7. What is the difference between “Natural Key” and “Surrogate Key”?

8. What is a junk dimension? Give me an example

9. What is a Degenerate Dimension / Fact Dimension in SQL Server Analysis Services? In what scenarios do you use it?

 ​23

Organization Polaris
Position Technology Lead – DBA
Location Dubai, UAE

1. ​​What the issues you find in migrating databases
2. ​​What is the schedule frequency of your transactional replication?
​​​3. How do check the application compatibility with sql server?​

4. ​How do you know whether statistics are latest or expired?​

5. ​​How to run distributed transactions in sql server?  What are the config changes has to be made?

6. How to give linked server usage permissions to specified logins only?

7. What kind of information that SQL Server keeps in memory?

8. Customer asked to break the mirroring and failover to mirror database. What are the steps to be taken other than a manual failover?

9. Can you give some examples for One to One, One to Many and Many to Many relationships?

 

​24

Organization CA
Position Lead SQL DBA
Location Hyderabad, India

​1. How to find the tempdb contention?

2. What is the sparse file?

3. What is the redo file in log shipping?

4. What are the queues in sql server?

5. How to capture a trace from production without any impact on performance?

6. How to capture the long running queries?

7. What is the migration plan to move 300 databases to  a new data centre? We can have an hour downtime.

8. In mirroring a connection failure happened between principal and mirror and principal server started filling log space (send queue), how do you troubleshoot?

9. What are the phases a database needs to be gone through while restoring?

10. What is the recovery interval?

11. You have any idea on Table Partitions?

​25

Organization Microsoft GTSC
Position SQL DBA
Location Bangalore, India

1. ​We have a log shipping environment. New data file has been added at primary server, what happens and how do you resolve it?

2. See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking, no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons?

3. You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan?

4. See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery plan?

5. Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?

6. What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

7. What are the ITIL basic standards? What are all the phases of ITIL? Explain about incident management, problem management, change management etc?

8. Can you explain sql server transaction log architecture?

9. What are the phases of sql server database restore process?​

​​26

Organization Pythian
Position Sr. SQL DBA
Location Hyderabad, India

1. How many IP’s required configuring a 2 node cluster?

2. How many MSDTC required for a 2 node cluster?

3. What is the basic difference between 2005 and 2008 in clustering?

4. What’s the use of quorum in clustering?

5. Your customer complained that one of the reports is not working, what is your action plan? Note: You just know the database server not anything else about that report.

6. How could you give full access on a database to a user from “Operations-Team”. Remember the user should have all rights except write permission. But our company policy is not to give DB_OWNER rights to any user from “Operations-Team”

​​7. I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?

8. What are the all possibilities that cause the tempdb full issue?

9. What is version store?

10. What is the best configuration for Tempdb?

11. We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot?

12. Do you have any idea about sparse column?

13. Have you ever done any automation in your database environment?

14. What are the critical issues you faced in your career?

15. Did you ever handle any missed SLA’s?

16. How to change the port number for SQL Server?

17. Any idea about capacity planning?

 

27

Organization DST Worldwide Systems
Position MSBI Lead
Location Hyderabad, India

1. How to give estimations to your report requirements?

2. Difference between ISNULL and COAELSCE

3. What is the tough SSIS package you handled?

4. What is the difference between BCP and Bulk insert?
5. On which basis we can give the deadline for a given task?

6. I have a requirement: We need to load a bunch of files on every Thursday at 4:00 AM EST and once the load is completed successfully a pre designed SSRS report has to be triggered and it has to be mailed to the given recipients. Design a ETL package using SSIS

 

28

Organization Yash Technologies
Position MSBI Lead
Location Hyderabad, India

1. Write a query to capture all employee details with salary less than the average salary within each department.

2. I have a table employee in that I have a column empID which is of varchar type. The column may have combination of characters and integers. Now write a query to get all records in which empID are having integers. Exclude rows with EMPID is not having integer values in it.

3. We are doing a ETL operations using stored procedures. Write a procedure to cleansing data in staging table and insert the data into master tables. Remember in staging table all columns are of varchar type

4. What is database unit testing? How to perform unit test for a stored procedure and SSIS package?

5. Assume we need to design a SSIS package to load data from a .csv file to database. In .csv file there are 10 columns. For the first 500 rows data is available for all 10 columns and for the last 400 rows data is available for first 6 columns, not even comma available after 6th column. How do you handle this?

.csv file:

col1, col2, col3, col4, col5

‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’
‘dat’, ‘dat’, ‘dat’
‘dat’, ‘dat’, ‘dat’

6. What are the different types of storage models in OLAP?

7. What is a mini dimension?

8. Design a SSRS report that can support dynamic columns.

9. How to remove PDF option from export options in SSRS report?

10. How to design a SSRS report that shows alternative rows in different colour?

29

Organization L&T Infotech
Position Sr. MSBI Developer
Location Bangalore, India

1. What is connection pooling in sql server?

2. Difference between partition by and patindex

3. What are the database design patterns?

4. What are the storage models in OLAP?

5. What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL?

6. When to use CROSS APPLY over Join?

7. Do you have any idea about table partitions?

8. How to attach configuration files to SSIS package?

9. What is stuff function? Difference between stuff and replace?

10. What is sparse column?

11. We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach?

12. How to manually allocate memory to SSRS service?

13. How to view report server logs and call stacks in SSRS?

14. We know in SSRS by default datasets are executed in parallel. But in our environment datasource is under high load and can’t handle parallel requests. Now can you explain how to disable parallel processing or how to serialize dataset execution?

15. How to pass multi-valued parameter to stored procedure in dataset?

16. How to control the pagination in SSRS?

17. What are the major differences between SSRS 2008 and SSRS 2012?

18. You have any idea about IIF, SWITCH and LOOKUP functions in SSRS?

19. How to get “Total” values at the end of every group in a report?

20. A SSIS package failed how to know the exact row number at which the package failed to process?

 

Posted in High Availability, Interview Q&A, MSBI, Performance Tuning, SQL Development, SQL Server DBA, SSIS, SSRS | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 68 Comments