MINIMISE DEADLOCKS

1. I have a dot net windows application which can be download from the website. I want to use the database for the application by using public IP address. I am not able to connect SQL server 2005 from remote connection it is giving me the error, SQL server does not allow remote connection.

I changed my server remote connection configuration to

Local and remote connection

using both TCP/IP and named pipes

but still I am getting the error.

Sol:  Login to SSMS using windows authentications, right click on your server name, click properties, on left select CONNECTIONS, and on right select option for ALLOW REMOTE CONNECTION FOR THIS SERVER

2. How you can minimize the deadlock situation?
To minimize deadlocks:
Access objects in the same order.
If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. Use stored procedures for all data modifications, to standardize the order of accessing objects.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
When several long-running transactions execute concurrently in the same database, chances of having a deadlock increase. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.
Use a low isolation level.
Read committed holds shared locks for a shorter duration than a higher isolation level such as serializable.
Use bound connections.
Two or more connections opened by the same application can co-operate. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa, and therefore do not block each other.

3. What is the importance of concurrency control?
Managing records so that no more than one person can update a record at any time.

4. There is a table with 100 rows of data. You want to add a new column to the table using the ALTER TABLE command. Which of the following is true?
A.This column can allow Null values
B.This column can be restricted to Not Null
C. This column can be an identity column
The answer is both A and C. You can add an identity column to a table that already has existing data. You can add a column, which allows null. If you want to add a column which does not allow null then you must define a default value for the column.

5. You want to check the syntax of a complicated Update SQL statement without executing it. What command should you use?

SET NOEXEC ON
When SET NOEXEC is ON, Microsoft SQL Server compiles each batch of Transact-SQL statements but
does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.
The execution of statements in SQL Server consists of two phases: compilation and execution. This setting
is useful for having SQL Server validate the syntax and object names in Transact-SQL code when
executing. It is also useful for debugging statements that would usually be part of a larger batch of
statements.
SET NOEXEC is set at execute or run time and not at parse time

6. What is RAID, and how it can influence database performance?
RAID is a redundant array of inexpensive (or independent) disks.
RAID
Configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID
1)Hardware disk array improves I/O performance because I/O functions, such as striping and mirroring,are handled efficiently in firmware. Conversely, an operatin g system-based RAID offers lower cost but consumes processor cycles. When cost is a consideration and redundancy an d high performance are
required, Microsoft Windows NT(r) stripe sets with parity are a good solution. Data striping (RAID 0) is the RAID configuration with the highest performance, but if one disk fails, all the data on the stripe set becomes inaccessible. A common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1). You can get the best disk I/O performance for the database and maintain data recoverability (assuming you perform regular database backups) through a mirrored transaction log. If data must be quickly recoverable, consider mirroring the transaction log and placing the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array, allowing a single disk to fail and be replaced in most cases without system downtime. RAID 5 offers lower performance than RAID 0 or RAID1 but higher reliability and faster recovery.

7. How can u fix a poorly performance query?

Some general issues that would cause a query to perform poorly that you could talk about would be: no
indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures,
having procedures and triggers without the SET NOCOUNT ON directive, unnecessarily complicated
joins, excessive normalization of the database, or in-appropriate or un necessary usage of cursors and
temporary tables.
Some of the tools and techniques that help you troubleshoot performance problems are: SET
SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler,
Windows NT /2000 Performance monitor, and the graphical execution plan in Query Analyzer

                                            
                                           Mirroring
12. How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)
Transaction safety is the attribute that controls the operating mode of the session. At any time, however, the database owner can change the transaction safety. By default, the level of transaction safety is set to FULL (synchronous operating mode).
Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance. If the principal becomes unavailable, the mirror stops but is available as a warm standby (failover requires forcing service with possible data loss).

Note: For more information about operating with safety on, see Synchronous Database Mirroring (High-Safety Mode). For more information about operating with safety off, see Asynchronous Database Mirroring (High-Performance Mode).

Turn  on transaction safety(synchronize communication)

·  Connect to the principal server.
· Issue the following Transact-SQL statement:

ALTER DATABASE <database> SET PARTNER SAFETY FULL

To turn off transation safety(Asynchronize communication)

·  Connect to the principal server.
·  Issue the following statement:

ALTER DATABASE <database> SET PARTNER SAFETY OFF

Turn off the witness server:

1.      Connect to either partner.

2.      Issue the following statement:

ALTER DATABASE <database_name> SET WITNESS OFF

where <database_name> is the name of the mirrored database.

Note:

The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, see

Manual failover database mirroring:

·  Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
·  Expand Databases, and select the database to be failed over.
·  Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
·  Click Failover.
A confirmation box appears. If you confirm that you want to fail over to the mirror database, failover proceeds, and principal and mirror server roles are swapped. The mirror database becomes the principal database and the principal database becomes the mirror.

Important:

If you have modified any properties since opening the Mirroring page, those changes will not be saved.

              How to pause or Resume database Mirroring:

1.      During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.

2.      Expand Databases, and select the database.

3.      Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

4.      To pause the session, click Pause.

A prompt asks for confirmation; if you click Yes, the session is paused, and the button changes to Resume.

For more information about the impact of pausing a session, see Pausing and Resuming Database Mirroring.

5.      To resume the session, click Resume.

    How to view the status of the database Mirroring:

          ·  After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
·  Expand Databases, and select the database to be mirrored.
·  Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
·  After mirroring begins, the Status panel displays the status of the database mirroring session as of when you selected the Mirroring page or clicked the Refresh button. The possible states are as follows:

States and Explanation

<blank>
No database mirroring session exists and there is no activity to report on the Mirroring page.
Paused
The principal database is running but is not sending any logs to the mirror server. The mirror copy of the database is not available.
No connection
The principal server instance cannot connect to its partner or to the witness server instance (if any)
Synchronizing
The contents of the mirror database are lagging behind the contents of the principal database. The principal server instance is sending log records to the mirror server instance, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the mirror and principal databases are in the synchronizing state.
Failover
On the principal server instance, a manual failover (role swap) has begun but has not yet accepted by the mirror.
Synchronized
The mirror database contains the same data as the principal database. Manual and automatic failover are possible only in the synchronized state.