Performance Tuning FAQ's

Performance Tuning

1)   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.

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

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.

3)   You are 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?

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

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

RAID 1 for the OS / Applications

RAID 1 for the page file

RAID 10 for the Data file (or RAID 5 for few writes)

RAID 1 (or 10) for the transaction log

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

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 live lock 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 live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks"  in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

6)   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.

Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.

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

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:

If there is significant change in the key values in the index

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

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

8)   Write SQL query to find the products which have continuous increase in sales every year considering the following Schema and tell which optimized query?

Table Structure

CREATE TABLE PRODUCTS

(

       PRODUCT_ID     INTEGER,

       PRODUCT_NAME   VARCHAR(30)

);

CREATE TABLE SALES

(

       SALE_ID        INTEGER,

       PRODUCT_ID     INTEGER,

       YEAR           INTEGER,

       Quantity       INTEGER,

       PRICE          INTEGER

);      

This table, contains the following rows,

Solution

SELECT PRODUCT_NAME

FROM

(

SELECT P.PRODUCT_NAME,

       S.QUANTITY -

       LEAD(S.QUANTITY,1,0) OVER (

                            PARTITION BY P.PRODUCT_ID

                            ORDER BY S.YEAR DESC

                            ) QUAN_DIFF

FROM   PRODUCTS P, SALES S

WHERE  P.PRODUCT_ID = S.PRODUCT_ID

)A

GROUP BY PRODUCT_NAME

HAVING MIN(QUAN_DIFF) >= 0;

9)   How to read DEADLOCK Information in SQL Server ERRORLOG ?

In our previous posts, we learned how to trace dead lock information in SQL Server Errorlog. Once the this information is logged inside SQL Server Logs, then how can we use that information for deadlock analysis.

10)In case you want to build your test scenario for DEADLOCK, then refer my article, DEADLOCK example with SQL Table and Script.

One we enable trace for DEADLOCKS, then on every event of deadlock, a detailed information about deadlock is  being locked in SQL Server ERRORLOG.

In our case, (refer my previous Article)following error message can be seen in SQL Server error log related to deadlock. I am highlighting some of the key information

2012-02-23 22:18:09.12 spid6s      Deadlock encountered …. Printing deadlock information
2012-02-23 22:18:09.12 spid6s      Wait-for graph
2012-02-23 22:18:09.13 spid6s
2012-02-23 22:18:09.13 spid6s      Node:1

2012-02-23 22:18:09.18 spid6s      RID: 2:1:184:7                 CleanCnt:2 Mode:X Flags: 0×3
2012-02-23 22:18:09.18 spid6s       Grant List 0:
2012-02-23 22:18:09.18 spid6s         Owner:0x000000008017CE40 Mode: X        Flg:0×40 Ref:0 Life:02000000 SPID:57 ECID:0 XactLockInfo: 0x0000000084E79440
2012-02-23 22:18:09.19 spid6s         SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 6
2012-02-23 22:18:09.19 spid6s         Input Buf: Language Event:
– Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
– Session 1 (Query Window 1) — Statement 3

BEGIN TRAN
update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;

2012-02-23 22:18:09.19 spid6s      Requested by:
2012-02-23 22:18:09.19 spid6s        ResType:LockOwner Stype:’OR’Xdes:0x0000000084E5F950 Mode: U SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000084EC2540) Value:0x85005c80 Cost:(0/120)
2012-02-23 22:18:09.19 spid6s
2012-02-23 22:18:09.19 spid6s     Node:2

2012-02-23 22:18:09.19 spid6s      RID: 2:1:157:3                 CleanCnt:2 Mode:X Flags: 0×3
2012-02-23 22:18:09.19 spid6s       Grant List 0:
2012-02-23 22:18:09.19 spid6s         Owner:0x000000008017BF40 Mode: X        Flg:0×40 Ref:0 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x0000000084E5F990
2012-02-23 22:18:09.19 spid6s        SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 6
2012-02-23 22:18:09.19 spid6s         Input Buf: Language Event:
– Step 4 update Orders Table and update the Sales OrderDate for Order 108
– Session 2 (Query Window 2) — Statement 4

BEGIN TRAN
update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.19 spid6s      Requested by:
2012-02-23 22:18:09.19 spid6s        ResType:LockOwner Stype:’OR’Xdes:0x0000000084E79400 Mode: U SPID:57 BatchID:0 ECID:0 TaskProxy:(0x0000000084DC2540) Value:0x8017bec0 Cost:(0/196)
2012-02-23 22:18:09.34 spid6s
2012-02-23 22:18:09.34 spid6s      Victim Resource Owner:
2012-02-23 22:18:09.34 spid6s       ResType:LockOwner Stype:’OR’Xdes:0x0000000084E5F950 Mode: U SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000084EC2540) Value:0x85005c80 Cost:(0/120)
2012-02-23 22:18:09.36 spid23s     deadlock-list
2012-02-23 22:18:09.36 spid23s      deadlock victim=process4d3048
2012-02-23 22:18:09.36 spid23s       process-list
2012-02-23 22:18:09.37 spid23s       process id=process4d3048 taskpriority=0 logused=120 waitresource=RID: 2:1:184:7 waittime=459 ownerId=356117 transactionname=user_transaction lasttranstarted=2012-02-23T22:17:56.510 XDES=0x84e5f950 lockMode=U schedulerid=3 kpid=10368 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2012-02-23T22:18:08.860 lastbatchcompleted=2012-02-23T22:17:56.590 clientapp=Microsoft SQL Server Management Studio – Query hostname=DBATAG  hostpid=968 loginname=DBATAG isolationlevel=read committed (2) xactid=356117 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2012-02-23 22:18:09.37 spid23s         executionStack
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=58 sqlhandle=0x02000000e49d262c4e7a570ab8a24babad5eb2842e2e869c
2012-02-23 22:18:09.37 spid23s     UPDATE [Orders] set [OrderDate] = @1  WHERE [OrderID]=@2
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=276 sqlhandle=0x0200000022f3a72386c7e56f1e90e8cd21461721a3e0a67a
2012-02-23 22:18:09.37 spid23s     update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.37 spid23s         inputbuf
2012-02-23 22:18:09.37 spid23s     — Step 4 update Orders Table and update the Sales OrderDate for Order 108
2012-02-23 22:18:09.37 spid23s     — Session 2 (Query Window 2) — Statement 4
2012-02-23 22:18:09.37 spid23s     BEGIN TRAN
2012-02-23 22:18:09.37 spid23s     update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.37 spid23s        process id=process43f048 taskpriority=0 logused=196 waitresource=RID: 2:1:157:3 waittime=7212 ownerId=353074 transactionname=user_transaction lasttranstarted=2012-02-23T22:17:18.967 XDES=0x84e79400 lockMode=U schedulerid=1 kpid=6448 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=4 lastbatchstarted=2012-02-23T22:18:02.093 lastbatchcompleted=2012-02-23T22:17:49.167 clientapp=Microsoft SQL Server Management Studio – Query hostname=DBATAG hostpid=968 loginname=DBATAG isolationlevel=read committed (2) xactid=353074 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2012-02-23 22:18:09.37 spid23s         executionStack
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=58 sqlhandle=0x020000005d7ef62e1c25a6069814688d37c8893e368f37ac
2012-02-23 22:18:09.37 spid23s     UPDATE [SalesPerson] set [Region] = @1  WHERE [SalesPersonID]=@2
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=294 sqlhandle=0x02000000b9aa5a33ee4c7af1c2eeb2cd8572c35f2638e203
2012-02-23 22:18:09.37 spid23s     update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;
2012-02-23 22:18:09.37 spid23s         inputbuf
2012-02-23 22:18:09.37 spid23s     — Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
2012-02-23 22:18:09.37 spid23s     — Session 1 (Query Window 1) — Statement 3
2012-02-23 22:18:09.37 spid23s     BEGIN TRAN
2012-02-23 22:18:09.37 spid23s     update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;
2012-02-23 22:18:09.37 spid23s       resource-list
2012-02-23 22:18:09.37 spid23s        ridlock fileid=1 pageid=184 dbid=2 objectname=tempdb.dbo.Orders id=lock8012e600 mode=X associatedObjectId=1224979099300986880
2012-02-23 22:18:09.37 spid23s         owner-list
2012-02-23 22:18:09.37 spid23s          owner id=process43f048 mode=X
2012-02-23 22:18:09.37 spid23s         waiter-list
2012-02-23 22:18:09.37 spid23s          waiter id=process4d3048 mode=U requestType=wait
2012-02-23 22:18:09.37 spid23s       ridlock fileid=1 pageid=157 dbid=2 objectname=tempdb.dbo.SalesPerson id=lock826ba480 mode=X associatedObjectId=1297036693379874816
2012-02-23 22:18:09.37 spid23s         owner-list
2012-02-23 22:18:09.37 spid23s          owner id=process4d3048 mode=X
2012-02-23 22:18:09.37 spid23s         waiter-list
2012-02-23 22:18:09.37 spid23s          waiter id=process43f048 mode=U requestType=wait

Interpretation from above mentioned Error Information

SPID 53 is running an update statement “UPDATE [Orders] set [OrderDate] = @1  WHERE [OrderID]=@2″.  It holds an U lock on ridlock fileid=1 pageid=184 dbid=2 objectname=tempdb.dbo.Orders id=lock8012e600 mode=X associatedObjectId=1224979099300986880.  This lock is blocking SPID 57, which is waiting to acquire an X lock on that key resource.

SQL Server has chosen SPID 53 as the deadlock victim, it’s batch will cancelled and it’s transaction will be rolled back.

To make you more clear, graphically, I enabled the profiler and capture the Deadlock graph, which given me this information.

If I would have enabled the profiler to capture Deadlock Graph then this what I am going to get

This summaries, how can we READ Deadlock from SQL ERRORLOG for Deadlock Analysis.

IS there a way I can stimulate a deadlock in SQL Server, so that I can use that as an example of deadlock

Let’s take an example, where we will result / cause a dead lock to SQL Serve?

To stimulate the Deadlock, let’s create two tables which we are going to use as an example of Deadlock

STEP 1 – Create Sample Tables which we will use Deadlock Example

-- Sample Table, which will track individual Orders entered by

-- Various Sales Persons

create table Orders

(

OrderID int,

OrderDate datetime,

Amount money,

SalesPersonID int

)

go

-- Sample Table, which will track all individual Sales Persons details

create table SalesPerson

(

SalesPersonID int,

Name varchar(100),

Region varchar(100)

);

-- Insert Date in SalesPerson

insert SalesPerson Values (1,'Dave Lawlor', 'California');

insert SalesPerson Values (2,'Mark F', 'Atlanta');

insert SalesPerson Values (3,'Vivek Pundit', 'India');

insert SalesPerson Values (4,'Chi Li Hu', 'China');

insert SalesPerson Values (5,'Mat Ross', 'London');

-- Insert Date in Orders Table

insert Orders values (101,'2011-01-01', 343,1)

insert Orders values (102,'2011-02-01', 35,2)

insert Orders values (103,'2011-03-01', 33,3)

insert Orders values (104,'2011-04-01', 445,1)

insert Orders values (105,'2011-05-01', 865,3)

insert Orders values (106,'2011-06-01', 23,5)

insert Orders values (107,'2011-07-01', 673,1)

insert Orders values (108,'2011-01-01', 343,1)

insert Orders values (109,'2011-02-01', 135,2)

insert Orders values (110,'2011-03-01', 373,3)

-- select Table Data

select * from Orders;

select * from SalesPerson;

STEP 2 – Open TWO new Query Windows where we will run 4 update statements

1 – Execute the following Query on Query Windows 1

-- Step 1 update a Order Table and update the Sales Person Id for Order 108

-- Session 1 (Query Window 1) -- Statement 1

BEGIN TRAN

update Orders set SalesPersonID=4 where OrderID = 108;

2 – Execute the following Query on Query Windows 2

-- Step 2 update Sales Person Table and update the Sales Person Name for SalesPersonID 4

-- Session 2 (Query Window 2) -- Statement 2

BEGIN TRAN

update SalesPerson set Name='Li Hu Chu' where SalesPersonID = 4;

3 – Execute the following Query on Query Windows 1

-- Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4

-- Session 1 (Query Window 1) -- Statement 3

BEGIN TRAN

update SalesPerson set Region='Beijing' where SalesPersonID = 4;

4 – Execute the following Query on Query Windows 2

-- Step 4 update Orders Table and update the Sales OrderDate for Order 108

-- Session 2 (Query Window 2) -- Statement 4

BEGIN TRAN

update Orders set OrderDate='2011-11-11 11:11:11.000' where OrderID = 108;

OUTPUT at STEP 4

Once you execute the 4th statement, you will get the following error message

Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

NOTE : Currently this DEAD LOCK Error is displayed at session level, I mean to say either at query window or at user front end. But this information in not get logged either in SQL Server Error Logs or Event Viewer by default.

As a preventive. we should keep an active eye on deadlocks and take necessary actions to avoid DEADLOCK issues.

But if these events are not getting logged anywhere, there how we are going to check these?, So in next topic I am going to discuss, how to track DEADLOCK information, so that these can be analyzed.

Please check My previous article, "How to Keep track of DEADLOCK in SQL Server", where we learn how to trace DEADLOCK in SQL Server

Backup SQL table, have you ever tried to backup a single SQL table inside a database? Let’s see How to backup SQL table | SQL Table Backup Restore

11) Which Tools are used for Performance Tuning?

There are many tools are used for Performance tuning

a)    Windows tools for monitoring applications:- Performance monitor, Performance Counters and Logs, Task manager, Network manager

b)    SQL Server tools for monitoring components:- SQL trace, SQL Profiler, DMVs, System Stored procedures, Graphical show plan, Activity Monitor, DBCC, Built-in Functions, Trace flags

12)How to identify longest running queries?

There are two ways to identify slow running queries

1)    Profiler (By using duration of the query)

2)    sys.dm_exec_query_stats and sys.dm_exec_requests DMVs

3)    DBCC OPENTRAN

13)Reasons for Slow Running Query?

There are a number of common reasons for slow-running queries

a)    Lack of useful indexes, Lack of useful data striping (RAID).

b)    Blockings, Table scans, Lack of useful partitioning

c)    Missing or out of date statistics (update statistics)

d)    Slow network communication.

e)    Insufficient memory available for SQL Server.

f)     Insufficient disk space.

g)    Excess recompilations of Stored Procedures

h)    Procedures and Triggers without SET NOCOUNT On

14)How to analyze query performance?

We can analyze query performance in three ways

1)    T-SQL:- SET SHOWPLAN_ALL ON/OFF, SET SHOWPLAN_TEXT ON/OFF

2)    SSMS:- Estimated Execution Plan & Actual Execution plan

3)    Profiler:- To display text and XML execution plans, see Displaying Execution Plans by Using SQL Server Profiler Event Classes

15) How to increase Query performance?

We can improve query performance in the following ways

1)    Add indexes if required

2)    Run Update statistics for out of date statistics

3)    Resolving Blocking issues

4)    Add space to DB files or TempDB, if that are not having enough space

5)    Reduce the too much normalization

6)    Using Temporary tables instead of Cursors

7)    SPs and Triggers are with Set NOCOUNT On

8)    Unnecessarily complicated joins

16) Explain about Profiler? What are the Uses of Profiler?

SQL Profiler can capture SQL Server events from the server to analyze or troubleshoot performance problems such as Finding Slow-running queries and Monitoring performance etc. Profiler is useful for maintaining security, troubleshooting, monitoring and optimization.

Uses of SQL Profiler:-

a)    Find the worst-performing queries.

b)    Identify the cause of a deadlock.

c)    Monitor stored procedure performance

d)    Audit SQL Server activity.

e)    Monitoring T-SQL activity per user.

f)     Collect a representative sample of events for stress testing.

g)    Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.

17) What are the Events is captured SQL Profiler?

We can capture the events such as

a)    T-SQL Statements, Stored Procedures

b)    Cursors,  Locks (deadlocks)

c)    Databases objects and auto growth of size of data & log files

d)    Errors & warnings (syntax errors)

e)    Performance (show plan)

f)     Table Scans

g)    Security audits (failed logins, password changes)

h)    Monitor server control, memory changes (CPU, Reads, Writes)

i)     Sessions, Transactions, Tuning

18) Explain about Database Tuning Advisor? What is Workload?

Database Tuning Advisor can analyze the performance effects of workloads run against one or more Databases or a SQL Profiler trace (they may contain T-SQL batch or remote procedure call). After analyzing, it recommends to add, remove or modify physical design structures such as clustered and non-clustered indexes, indexed views and partitioning.

Workload:- A workload is a set of Transact-SQL statements that executes against databases you want to tune

19) What is Dynamic Management Views?

DMVs return server state information that we can use to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of DMVs:

a)    Server-scoped DMVs:- Require the VIEW SERVER STATE permission on the server.

b)    Database-scoped DMVs:- Require the VIEW DATABASE STATE permission on the database.

20) What is DAC? How to connect DAC?

DAC is stands for Dedicated Administrator Connection. This diagnostic connection allows an administrator to access running instance to troubleshoot problems or execute diagnostic queries on the server - even when SQL Server is not responding to standard connection requests. This connection uses 1434 port and can connect only one connection per instance.

To connect DAC:-

1)    SQLCMD:- -S admin:<instance_name>

2)    GUI:- SSMS Query Editor by connecting to ADMIN:<instance_name>

21) Explain about Database Console Commands (DBCC)?

DBCC Commands are used to check the consistency of the Databases or Database Objects. While executing DBCC commands the DB engine creates a database snapshot and then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

22) What is Stored Procure? What are the types of stored Procedures available in SQL server and explain each?

A stored procedure is a precompiled executable object that contains one or more Transact-SQL statements.

a)    User Defined Stored Procedure:- Stored procedures are modules or routines that encapsulate code for reuse. A stored procedure can take input parameters, return tabular or scalar results and messages to the client

b)    System Stored Procedure:- System stored procedures are used to perform many administrative and informational activities.

c)    Extended stored procedure:- Extended stored procedures are used to create own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run.

23) What is Activity Monitor and use of that? What are the permissions required to use Activity Monitor?

Activity Monitor is used to get information about users connections to the Database Engine and the locks that they hold. Activity Monitor is used to troubleshooting database locking issues, and to terminate a deadlocked or unresponsive process.

To use activity monitor:- VIEW SERVER STATE permission on Server and SELECT permission to the sysprocesses & syslocks tables in the master database.

To Kill a Process:- sysadmin and processadmin database roles and permission are required to KILL a process.

24)    What is Execution Plan and explain it?

Execution Plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and queries in SQL Server. This graphical approach is very useful for understanding the performance of the query.

1)       What is Trace flag? Give some Trace flags?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. There are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

(1) 260 (2) 1204 (3) 1211 (4) 1222 (5) 1224 (6) 2528 (7) 3205 (8) 3625 (9) 4616 (10) 7806 (11) 1400

To set on/off Traceflag:- (1) DBCC TRACEON (2) DBCC TRACEOFF

To Enable Trace flag globally :- DBCC TRACEON with the -1 argument (Ex:- DBCC TRACEON 2528, -1)

-T startup option:- Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect.

To Determine trace Flags are currently active:- DBCC TRACESTATUS

25)What are the common failures occur in SQL Server 2005?

There are three common failures occur in SQL Server 2005.

1)    Database Failures

2)    Physical Server Failures

3)    SQL Server Service failures

26) What are the causes of Database Failures?

There are three common issues will causes Database failures. Log File viewer is very useful to diagnose these problems that will occur in SQL Server 2005.

1)    Database has run out of Disk space:- 

a)    If a Database is online, and running out of disk space the data cannot be inserted into the database.

b)    If the Database during recovery, and the data file becomes full the Database engine marks the Database as “Resource Pending”.

2)    T. Log is full:-

a)    If the Database is Online, and the T.Log becomes full the Database Engine issues 9002 error and it is in read-only state and will not allow updates.

b)    If the Database during recovery, and the T.Log becomes full the Database engine marks the Database as “Resource Pending”.

3)    TempDB has run out of Disk space:- TempDB stores User objects, Internal Objects and Version stores. If the TempDB database runs out of space, it causes significant problems for SQL Server 2005. The errors are written to SQL Server log and these errors  (1101, 1105, 3959, 3967, 3958, 3966) indicates TempDB has insufficient space.

27) How to understand Database Engine Errors?

If any error occurs, the server writes error messages to logs (Database mail, SQL Agent, SQL Server & Windows NT). These logs having Error number, Error message, Severity, State, Procedure name, Line number. We can easy to understand errors by viewing Log file viewer.

28) What is Severity level in Event log? At what severity levels are written to the SQL Server log?

The Severity level tells how bad the error is. Above 19 Severity level errors are written to the SQL Server log.

29) What is State Attribute of an error message in Event log?

The state attributes provides details of what caused the error.

30) What are the Causes of Physical Server Failures?

There are five common issues will causes Physical Server failures.

1)    Disk failures

2)    Memory failures

3)    Processor failures

4)    Network card failures

5)    RAID failures

31)If Log file is full what you will do?

If the T.Log file is full the Database issues 9002 error and it is in Read-only state and will not allow updates.

The following tasks will responding T.Log file

1)    Backing up the log

2)    Adding one or more log files

3)    Moving the Log to another disk

4)    Increasing log file size or enabling auto growth

5)    Teminating long-running transactins.

32) If Data file is full what you will do?

If the primary data file is full we can add secondary data files.

Adding a file to database: - Right click on database > Properties > Files > click add > give the values for logical name, file type, file group, initial size, auto growth, path and file name .

33) If a Database is under Suspect mode? What you will do?

sp_resetstatus:- Resets the status of a suspect database (sp_resetstatus ‘Adventureworks’)

34)  If the server has completely failed, and how to all the backups restored onto a new server?

a)  Build the Windows server and restore the domain logins to support Windows authentication.

b)  Install SQL Server and any service-pack upgrades.

c)  Put SQL Server in single-user mode and restore the master database.

d)  Restore the msdb database.

e)  If the model database was modified, restore it.

f)   Restore the user databases.

What is Lock Escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.

What is a bookmark lookup?

When a non clustered index is used for the seek and the data needed was not

at the leaf level.

What is a key lookup?

Same as bookmark lookup, when a non clustered index is used for the seek and the data needed was not
at the leaf level.

What new indexes are introduced in SQL Server onwards ?

- Spatial
- XML

Could you please describe some properties / Facts about NULL during comparisons.

NULL can not used with any comparing operator, NULL

• NULL will never be true or false
• NULL can not compared as =,<>,<,>, <=,<= etc.
• NILL is always equates will NULL only

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the resultsets.

Types of cursors:

Static,

Dynamic,

Forward-only,

Keyset-driven.

See books online for more information.

Disadvantages of cursors: Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy. Moreover, there are restrictions on SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 — 5000 hike

Salary between 40000 and 55000 — 7000 hike

Salary between 55000 and 65000 — 9000 hike

In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =

CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000

WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000

WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000

END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILEloop, as long as there is a unique key to identify each row.

Could you please explain, how to use Cursors ?

1.    Declare a CURSOR

2.    OPEN a CURSOR

3.    FETCH data in CURSOR

4.    finally close CURSOR

Suppose, we are building a UAT environment and we need to build a algorithm to export exact 50% of the rows (I means to say alternative rows either even or ODD). So write a query to select prime number rows from table.

         select profileid,name,friend_name

  from (select f.*, row_number() over (order by profileid asc) rn

         from Friend f) a

where rn%2=1;

Testing Script

create table Friend

(

ProfileID uniqueidentifier,

Name varchar(50),

Friend_Name varchar(50)

)

insert Friend values (NEWid(),'RAM', 'Shyam')

insert Friend values ('FFCB96AD-0F16-4A77-B634-3CE1F697A3D1','RAM', 'Tony')

insert Friend values ('FFCB96AD-0F16-4A77-B634-3CE1F697A3D1','RAM', 'Vibha')

insert Friend values (NEWid(),'SHYAM', 'RAM')

insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'SAM')

insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'Vibha')

insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'John')

insert Friend values (NEWid(),'VIBHA', 'RAM')

insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'SHYAM')

insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'George')

insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'TOM')

insert Friend values (NEWid(),'TOM', 'RAM')

insert Friend values ('DE86E5EC-9748-47A0-936E-0BB6BCBCA1A0','TOM', 'DNATAG')

insert Friend values ('DE86E5EC-9748-47A0-936E-0BB6BCBCA1A0','TOM', 'Reddy')

go

         select profileid,name,friend_name

  from (select e.*, row_number() over (order by profileid asc) rn

         from Friend e) a

where rn%2=1;

What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like

USER_NAME(),

SYSTEM_USER,

SESSION_USER,

CURRENT_USER,

USER,

SUSER_SID(),

HOST_NAME().

What is precedence constraints and can you name some?

Precedence constraints are used in DTS / SSIS packages to move from one

task to another. We have three type of precedence constraints

1.    Success

2.    Failure

3.    Completion

What sort of resource contention we can have, which can cause database to run slow?

1.    CPU bottleneck

2.    Memory bottleneck

3.    Network IO bottleneck

4.    Disk IO bottleneck

5.    Paging File (process trimming)

6.    Lock contention

7.    Corrupt index

8.    Recompilation

Can you have a nested transaction?

Yes, SQL Server do support nested transaction up to 32 levels. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

Which is faster a Table Scan, or a Clustered Index Scan?

Same speed in case a table has a clustered index that it’s always show index scan instead of table scan.

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using theEXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreatestored procedure.

Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.

What is recompilation?

When the cached execution plan for a query cannot be used so the procedure
recompiles.

What is parallelism?

SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly. When a single query runs of multiple CPUs is known as query parallelism.

What is the default query threshold for parallelism?

The query optimizer decides to utilize multiple SPIDS running on different processors to query / transfer data. Default threshold is 5 seconds.

What are the main reasons for statement recompilation ?

Recompilation happen mainly because of

• underlying statistics change
• DDL changes within the procedure.
• The parameters the procedure was compiled with vary from the recently passed in parameters.
• The query plan was flushed from cache.

How will you handle exceptions in SQL Server programming

By using TRY-CATCH constructs,

What is difference between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Let’s assume, you are working for online product selling company like AMAZON, now you need to write two alternatives query to return the firstname, lastname and the most recent OrderID for all customers.

-- OPTION 1

SELECT o.OrderID,c.FirstName,c.LastName FROM Orders o

JOIN Customers c

ON o.CustomerID = c.CustomerID

WHERE OrderDate = (SELECT MAX(OrderDate)

FROM Orders

WHERE CustomerID = o.CustomerID

-- OPTION 2

SELECT c.FirstName,c.LastName,o.OrderNumber FROM Orders o

JOIN

(

SELECT MAX(OrderDate) AS MaxOrderDate,

custid

FROM orders

GROUP BY custid

) o_2

ON o.custid = o_2.custid

AND o.orderdate = o_2.MaxOrderDate

JOIN customers c

ON c.CustID = o.CustID

What tools do you use for performance tuning?

Query Analyzer, Profiler, Index Wizard, Performance Monitor

What are extended stored procedures?

How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?

exec xp_cmdshell 'dir c:\*.exe'

What sp_MSforeachtable  does ?

You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”

How do I prevent SQL injection in my applications?

Check my previous post, “How to secure against SQL injection“.

What you do when a particular query is slow?

1.    Run SQL profiler and determine if abnormal amounts of IO or CPU is used.

2.    Run profiler to determine if recompilation is a factor.

3.    Update the statistics.

4.    Check the execution plan

Can we convert the column datatype in to different datatype in a exiting table ?

YES, we can change data type of column for an exiting table too but we need to make sure the new datatype is compateble with old datatype. For Example, we can not convert a column which store varchar values and has textual data  in it to a int type data type. Following TSQL can be used to modify (alter) a exiting column

alter table tablename alter Column Columnname newdatatype

What is a live lock?

A live lock 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 live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What are Checkpoint In SQL Server ?

When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired.

What is an execution plan? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since  the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query  drop-down menu). If this option is turned on it will display query execution plan in separate window  when query is ran again.

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

·  Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Explain different isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key does not allow NULLs, but unique key allows one NULL only.

Difference between Stored Procedure and Trigger?

• we can call stored procedure explicitly.
• but trigger is automatically invoked when the action defined in trigger is done.
  
  ex: create trigger after Insert on
• this trigger invoked after we insert something on that table.
• Stored procedure can’t be inactive but trigger can be Inactive.
• Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is Service Broker

Its a message queuing technology in SQL to helps developer to develop fully ditributed applications.Its helps to send asynchronous, transactional message.Its also helps to send message to another database.

What is SQL Profiler

SQL Profiler is a graphical tool thats helps administrator to capture events in instance of Microsoft Sql Server. We can get all the events that done on file or on SQL Table.  We can filter the events that we need for us. We can also get the subset of event that we need.

Which recovery model gives a best sql server database performance ?

·        Simple Recovery Model  – It allows for the fastest bulk operations and the simplest backup-and-restore strategy.

·        Bulk Logged Recovery Model  – allows recovery in case of media failure and gives best performance using least log space for certain bulk operations like BULK INSERT, bcp, CREATE INDEX , REBUILD INDEX etc.