SQL Server DB Administrator Questions And Answers

1⟩ What is a correlated sub-query?

A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the TimeEntry table to see if they've entered their time or not. I can't do a straight join here because I'm looking for the absence of time data, so I'll do a correlated sub-query similar to this:

SELECT FirstName, LastName

FROM EMPLOYEE e

WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te

WHERE te.EmpID = e.EmpID

AND te.WeekID = 35)

Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

148 views

2⟩ What are the steps to take to improve performance of a poor performing query?

► Maximum use of indexes, stored procures should be done.

► Avoid excessive use of complicated joins and cursors.

► Avoid using conditional operators using columns of different tables.

► Make use of computed columns and rewriting the query.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

154 views

3⟩ What is blocking and how would you troubleshoot it?

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

Troubleshooting blocking:

► SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server

► The common blocking scenarios must be identified and resolved.

► The scripts output must be checked constantly,

► The SQL profilers data must be examined regularly to detect blocking.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

155 views

4⟩ What authentication modes does SQL Server support?

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It's important to note that if you use Windows Authentication, you will not be able to log in as sa.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

147 views

5⟩ Why would you call Update Statistics?

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

148 views

6⟩ What is database isolation in SQL Server?

Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:

► Serializable

► Repeatable read

► Read committed

► Read uncommitted

Is this answer helpful? 0 Yes | 0 No

Answer This Question

146 views

7⟩ How to control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

139 views

8⟩ How to create a Scrollable Cursor with the SCROLL Option?

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence

Example:

DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

145 views

9⟩ What is DBCC?

DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don't fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

147 views

10⟩ Explain Databases and SQL Server Databases Architecture?

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It's only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

142 views

11⟩ What happens on checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.

One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

136 views

12⟩ How do you test proper TCP/IP configuration Windows machine?

Windows NT: IPCONFIG/ALL,

Windows 95: WINIPCFG,

Ping or ping ip.add.re.ss

Is this answer helpful? 0 Yes | 0 No

Answer This Question

143 views

13⟩ Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

149 views

14⟩ How to define testing of network layers?

Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

157 views

15⟩ What types of replication are supported in SQL Server?

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.

Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.

Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

137 views

16⟩ How many layers of TCP/IP protocol combined of?

Five. (Application, Transport, Internet, Data link, Physical).

Is this answer helpful? 0 Yes | 0 No

Answer This Question

141 views

17⟩ How to trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

141 views

18⟩ How many bits IP Address consist of?

An IP Address is a 32-bit number.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

138 views

19⟩ What purpose does the model database serve?

The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

142 views

20⟩ Tell me what is blocking and how would you troubleshoot it?

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

122 views

SQL Server DB Administrator

Home MS SQL Server SQL Server DB Administrator

84 SQL Server DB Administrator Questions And Answers

1⟩ What is a correlated sub-query?

2⟩ What are the steps to take to improve performance of a poor performing query?

3⟩ What is blocking and how would you troubleshoot it?

4⟩ What authentication modes does SQL Server support?

5⟩ Why would you call Update Statistics?

6⟩ What is database isolation in SQL Server?

7⟩ How to control the amount of free space in your index pages?

8⟩ How to create a Scrollable Cursor with the SCROLL Option?

9⟩ What is DBCC?

10⟩ Explain Databases and SQL Server Databases Architecture?

11⟩ What happens on checkpoint?

12⟩ How do you test proper TCP/IP configuration Windows machine?

13⟩ Why would you use SQL Agent?

14⟩ How to define testing of network layers?

15⟩ What types of replication are supported in SQL Server?

16⟩ How many layers of TCP/IP protocol combined of?

17⟩ How to trace the traffic hitting a SQL Server?

18⟩ How many bits IP Address consist of?

19⟩ What purpose does the model database serve?

20⟩ Tell me what is blocking and how would you troubleshoot it?

Quick Links:

SQL Server DB Administrator

Home MS SQL Server SQL Server DB Administrator

84 SQL Server DB Administrator Questions And Answers

1⟩ What is a correlated sub-query?

2⟩ What are the steps to take to improve performance of a poor performing query?

3⟩ What is blocking and how would you troubleshoot it?

4⟩ What authentication modes does SQL Server support?

5⟩ Why would you call Update Statistics?

6⟩ What is database isolation in SQL Server?

7⟩ How to control the amount of free space in your index pages?

8⟩ How to create a Scrollable Cursor with the SCROLL Option?

9⟩ What is DBCC?

10⟩ Explain Databases and SQL Server Databases Architecture?

11⟩ What happens on checkpoint?

12⟩ How do you test proper TCP/IP configuration Windows machine?

13⟩ Why would you use SQL Agent?

14⟩ How to define testing of network layers?

15⟩ What types of replication are supported in SQL Server?

16⟩ How many layers of TCP/IP protocol combined of?

17⟩ How to trace the traffic hitting a SQL Server?

18⟩ How many bits IP Address consist of?

19⟩ What purpose does the model database serve?

20⟩ Tell me what is blocking and how would you troubleshoot it?

BE THE FIRST TO KNOW

Quick Links: