Blog

SQL Server Interview Questions For Experienced - 5+ Years

  • (4.0)

If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, The average salary for  SQL Server ranges from approximately $69,682 pa. So, You still have opportunity to move ahead in your career in SQL Server. Mindmajix offers Advanced SQL Server Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SQL Server Developer.

Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo!

SQL Server Interview Questions and Answers

Q) What is blocking and how would you troubleshoot it?

A) Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Q) What are the steps you will take to improve the performance of a poor performing query?

A) This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

  • SET SHOWPLAN_ALL ON,
  • SET SHOWPLAN_TEXT ON,
  • SET STATISTICS IO ON,
  • SQL Server Profiler,
  • Windows NT /2000 Performance monitor,
  • Graphical execution plan in Query Analyzer.

Q) You are being you being assigned a task to move 5 million rows from one server to another using T-SQL with a linked-server. What will you consider to avoid transaction log fill up at destination server?

A) Will prefer to use SET ROWCOUNT and a while loop to commit data in batches.

Q) What is the optimal Disk configuration for a database server and what RAID configurations would you use if budget is not a constraint?

  1. RAID 1 for the OS / Applications
  2. RAID 1 for the page file
  3. RAID 10 for the Data file (or RAID 5 for few writes)
  4. RAID 1 (or 10) for the transaction log

Frequently Asked SQL Server Interview Questions & Answers

Q) What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

A) Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a  request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Also Read: Advanced SQL Server Interview Questions

SQL Server Interview Questions And Answers For Experienced

Q) What is blocking and how would you troubleshoot it?

A) Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Q) What are statistics, under what circumstances they go out of date, how do you update them?

A) Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

  1. If there is significant change in the key values in the index
  2. If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
  3. Database is upgraded from a previous version

Look up SQL Server books online for the following commands:

UPDATE STATISTICS,

STATS_DATE,

DBCC SHOW_STATISTICS,

CREATE STATISTICS,

DROP STATISTICS,

sp_autostats,

sp_createstats,

sp_updatestats

Q) Could you please some items which you may see in an execution plan indicating the query is not optimized.

  1. Index Scan or Table Scan
  2. Hash Joins
  3. Thick arrows (indicating large work tables)
  4. Parallel streams (Parallelism)
  5. Bookmark lookup (or key lookup)

Q) What structure can you implement for the database to speed up table reads?

A) Follow the rules of DB tuning we have to:

1] properly use indexes ( different types of indexes)

2] properly locate different DB objects across different tablespaces, files and so on.

3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules