SQL DBA Interview Q & A (Part3)

what is checkpoint process in the sql server?

Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

Dirty pages: Dirty pages are data pages which are reside in buffer cache and have been modified but not yet stored in data disk.

How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?

A non-clustered index and tempdb can be created on a separate disk to improve performance.

How to Find the List of Fixed Hard Drive and Free Space on Server?

EXEC master..xp_fixeddrives

why can there be only one clustered index and not more than one?

Clustered Index: Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically. Hence there can only be one clustered index per table.

what is a hint in sql server?

Sometimes your query executes very slowly. One of the ways to increase the queries performance is using the SQL Server table hints. The table hints specify a locking method to be used by the query optimizer with this table

what is aggregate functions in sql server?

Aggregate functions are built in sql server functions.
Aggregate functions uses to summarize data.
Aggregate functions perform a calculation on a set of values and return a single value.

What is the Difference between Index Seek vs. Index Scan?

Index Scan:Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
Index Seek:Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

What is the Maximum Size per Database for SQL Server Express?

4GB

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

GRANT:
USE master;GRANT VIEW ANY DATABASE TO username;GO

WITH GRANT:
USE master;GRANT VIEW ANY DATABASE TO username WITH GRANT OPTION;GO

The difference between these options is very simple. In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users.

How to Create Primary Key with Specific Name while Creating a Table?

CREATE TABLE [dbo].[TestTable]([ID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [varchar](100) NULL,CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED([ID] ASC)
)GO

What is T-SQL Script to Take Database Offline – Take Database Online

-- Create Test DBCREATE DATABASE [myDB]
GO-- Take the Database OfflineALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO-- Take the Database OnlineALTER DATABASE [myDB] SET ONLINE
GO-- Clean upDROP DATABASE [myDB]
GO

How to Enable/Disable Indexes?

USE AdventureWorks
GO----Disable IndexALTER INDEX [IX_StoreContact_ContactTypeID] ONSales.StoreContact DISABLE
GO----Enable IndexALTER INDEX [IX_StoreContact_ContactTypeID] ONSales.StoreContact REBUILD
GO

Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?

I don't  think so because there is no significant difference in transferring of 1 byte and a null value.Other than that there is no difference between these two queries.

What are the basic functions for master, msdb, model, tempdb and resource databases?

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

what is the maximum number of index per table in sql server?

For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index

For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index

Explain Few of the New Features of SQL Server 2008 Management Studio?Explain?

1. IntelliSense for Query Editing
2. Multi Server Query
3. Query Editor Regions
4. Object Explorer Enhancements
5. Activity Monitors

IntelliSense for Query Editing

This is my favorite feature so far. If you have used Visual Studio before, this feature may not be new to you. Implementation of IntelliSense is not as advanced as in Visual Studio but it is still very useful and well implemented.

After implementing IntelliSense, DBAs and developers will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the world. You can see those option from SSMS Menu >> Edit >> IntelliSense >> (See the available options)

There are a total of five options available in Edit Menu. I suggest to experiment with all of them while playing with IntelliSense.

1. List Members
2. Parameter Info
3. Quick Info
4. Complete Word
5. Refresh Local Cache

If your IntelliSense does not bring up recently created objects, try “Refresh Local Cache” as described above, or press CTRL + SHIFT + R.

IntelliSense is a new feature and it will take some time to get adjusted to it. If any developer does not like this option, it can be turned off from Menu >> Query >> (deselect) Enable IntelliSense.

MultiServer Query

Usually DBA don’t manage only one database; they have many servers to manage. There are cases when DBA has to check the status of all the servers. I have seen one of the DBA who used to manage 400 servers, writing query using XML_CMDSHELL where he wanted to find out what the status of fullback up on all the servers was. In one of the recent consultancy job, when I had to find out if all the three servers were upgraded with Services Packs (SP), I ran a query to find version information on all the three instance separately in three windows.

SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all make sure that you registered all the servers under your registered server. Once they are registered Right Click on server group name and click New Query as shown in the image below.

Now in the opened query window run the following query (you can find it in the sample code for this article):

SELECTSERVERPROPERTY('Edition') AS Edition,SERVERPROPERTY('ProductLevel') AS ProductLevel,SERVERPROPERTY('ProductVersion') AS ProductVersion

Query above will give the result shown in the image below. Note that we have only three columns in the SELECT but our output contains four columns. The very first column is the “Server Name” and it is added by SQL Server to indentify rows belonging to a specific server.

If all of the above servers are registered with “central server” – the option which is right below it, other administrators can also register to those entire servers by simple registering one central server.

Query Editor Regions

This feature exists in many programming languages already but now it is newly introduced in SSMS 2008. The reason I am highlighting this feature is because there are cases when T-SQL code is longer than hundreds of lines and after a while it keeps on getting confusing.

The regions are defined by the following hierarchy:

1. From first GO command to next GO command.
2. Statements between BEGIN – END, BEGIN TRY – END TRY, BEGIN CATCH – END CATCH
3. Multiline statements

In the following code snippet I have reported an example which has five coding regions (you can find it in the sample code for this article). I have made small comment besides them describing their region type.

CREATE PROCEDURE MyTest -- Region Type 1AS
BEGIN -- Region Type 2SELECT 1BEGIN -- Region Type 2SELECT 2;SELECT * -- Region Type 3FROM sys.objects;SELECT * -- Region Type 3FROM sys.columns;SELECT 3;END
ENDGO 


Regions can be collapsed as well as expanded by clicking the small sign of ‘-’ or ‘+’ besides them. The following image shows a fully expanded region code snippet and a fully collapsed code snippet.

If there is any single-line statement, it does not qualify for a coding region.

Object Explorer Enhancements

Object Explorer Detail initially looks the same as the previous version but when right clicked on the bar with labels it reveals what it can do. This feature looks the same as the Vista OS folder option but when looked at how it is implemented for SQL Server data, it is really amazing. Object Explorer Detail view can be enabled by either going to Menu >> View >> Object Explorer Detail or pressing F7.

In Object Explorer Detail the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.

Additionally, there are new wizards which help you perform several tasks, from policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue.

Activity Monitors

This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

There are four graphs 1) % Processor Time, 2) Waiting Tasks, 3) Database I/O, 4) Batch Requests/Sec

Additionally, there are four tabs which also reflect some statistics of the above four graphs 1) Processes, 2) Resource Waits, 3) Data File I/O, 4) Recent Expensive Queries. Clicking on the tables will expand the tabs as shown in the following image.

All the four tabs provide very important information, however the one which I refer most is ‘Recent Expensive Queries’. Whenever I find my server running slow or having any performance related issues my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment.