SQL Server XML Function exist() December 14, 2009

  07.DECLARE @xml XML

     08.DECLARE @id SMALLINT

     09.DECLARE @value VARCHAR(20)

     10.  

     11.SET @xml=

     12.'

     13.<ninjaElement id="1">SQL Server Ninja</ninjaElement>

     14.<ninjaElement id="2">SharePoint Ninja</ninjaElement>

     15.<ninjaElement id="3">ASP.NET Ninja</ninjaElement>

     16.'

     17.  

     18.-- this is what we will look for

     19.SET @id    = 2

     20.SET @value ='SQL Server Ninja'

     21.  

     22.-- note exist() will return only either :

     23.-- 1 (true) or 0 (false)

     24.  

     25.-- check if a node called ninjaElement exists

     26.-- at any level in the XML snippet

     27.SELECT @xml.exist('//ninjaElement')

     28.  

     29.-- check if a node called bar exists

     30.SELECT @xml.exist('//bar')

     31.  

     32.-- check if attribute id exists anywhere

     33.SELECT @xml.exist('//@id')

     34.  

     35.-- check if attribute id exists within a ninjaElement tag

     36.SELECT @xml.exist('//ninjaElement[@id]')

     37.  

     38.-- check if the id attribute equals to what we saved

     39.-- in the @id variable

     40.SELECT @xml.exist('/ninjaElement[@id=sql:variable("@id")]')

     41.  

     42.-- check if the node text equals to what

     43.-- we saved in the @value variable

     44.SELECT @xml.exist('/ninjaElement[text()=sql:variable("@value")]')

     45.

      Source: http://www.sqlmusings.com/2009/04/08/sqlxml-how-to-use-sql-server-xml-function-exist/

Minimizing Deadlocks April 7, 2009

Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
  Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
  Use snapshot isolation.
• Use bound connections.
• Access Objects in the Same Order

If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.

Avoid User Interaction in Transactions
Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

Keep Transactions Short and in One Batch
A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

Use a Lower Isolation Level
Determine whether a transaction can run at a lower isolation level. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

Use a Row Versioning-based Isolation Level
When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

Use Bound Connections
Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

(source: http://msdn.microsoft.com/en-us/library/ms191242.aspx)

See also: Detecting and Ending Deadlocks
Handling Deadlocks
Using Row Versioning-based Isolation Levels
Using Bound Sessions

Deadlocking April 7, 2009

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

• Transaction A acquires a share lock on row 1.
• Transaction B acquires a share lock on row 2.
• Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
• Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

Using certain coding conventions in applications reduces the chance that applications will cause deadlocks. For more information, see Minimizing Deadlocks.

Deadlocking is often confused with normal blocking. When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is set. The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

Deadlocks are sometimes called a deadly embrace.

Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. The waiting thread is said to have a dependency on the owning thread for that particular resource. In an instance of the Database Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the Database Engine to lock table partitions at the HoBT level instead of at the TABLE level. However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

(source http://msdn.microsoft.com/en-us/library/ms177433.aspx)

Database Fragmentation with Visual Defrag March 26, 2009

Visual Defrag is a defragmentation tool, designed specifically for Microsoft SQL Server database administrators and developers to defragment their indexes.
Visual Defrag allows DBAs to view all the indexes in a database, see how heavily fragmented each index is, and choose whether to defragment one index, a selection of indexes or all indexes.

What is Database Fragmentation?
From time to time, depending on how your data is used, you will need to reorganize the indexes on your tables. Indexes don’t maintain their fill factor and become fragmented and user queries will suffer the effects of reduced performance.
The tables in your database become fragmented when data modifications are made, such as inserts, updates and deletes. These changes to your tables are not usually made equally among the rows, so the page fullness will vary according to the characteristics of the data, creating hot spots within your indexes.

Fragmentation happens when the logical order of the pages does not match the physical order of the pages, or when pages that are logically sequential are not located on the same extent. This means your indexes become less efficient and queries will take longer to return results to your users.

How Does Fragmentation Affect My Databases?
When you run queries that scan part or all of a table, if you have fragmentation on that table, it causes additional page reads, slowing down parallel data scanning and reducing the performance and speed of your database.
For example, for SQL Server to retrieve all the pages of a fragmented table in their logical order, the disk drive’s heads have to jump around on the physical disk, rather than performing just contiguous read-only operations. This ‘latency’ causes decreased performance on your database and frustration and complaints from your users. Using Visual Defrag to regularly remove fragmentation will make your databases run faster.

Why is Fragmentation a Problem?
When fragmentation is present, and particularly if the fragmentation is heavy, perhaps on a table with data that is constantly being modified, users will find the performance of the table is lower than it should be and queries will be slower.
With internal fragmentation, if SQL Server needs to insert a new row on a full page, it will allocate a new page and split the full page so that half the rows are on one page and the other half on the other page.

Splitting can be an expensive I/O operation and leads to external defragmentation because more often than not, the new page will not be stored contiguous with the original page which has been split. The longer SQL Server takes in splitting pages, or jumping around the disk to return query results, the longer your users must wait.

This is how Visual Defrag can help. By using the built-in scheduler you can plan defragmentation jobs to take place on heavily used tables as often as you need, and at times when users will not be affected.

What is Defragmentation?
User modifications, such as inserts and deletes on the data in your tables, create pages splits. Performance will be lost as it takes SQL Server longer to return results of queries as these splits mean your indexes become less effective. Defragmentation is the process of putting your table indexes back in order and removing the page splits, so queries run at the speed you would expect and your users are not kept waiting.
Defragmentation should be a major part of your database maintenance plan to make sure frequently modified tables are regularly defragmented to keep them running at optimum speed. Visual Defrag enables you to regularly defragment your database tables at times when your users are not using the database by creating scheduled defrag jobs. This keeps your databases performing at their optimum speed and your users will benefit from faster data access.

Source: http://www.norbtechnologies.com/products/visualdefrag/faqs-defrag-sql-server.aspx

About index defrag: http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx

http://sqlfool.com/2009/03/automated-index-defrag-script/

Creating an index with included (non-key) columns March 11, 2009

CREATE NONCLUSTERED INDEX IX_TableName_ColumnName
ON TableName (ColumnName)
INCLUDE (ColumnName2, ColumnName3);

Best way to get identity of inserted row? March 11, 2009

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it’s across scopes. You could get a value from a trigger, instead of your current statement.
Another way said: @@IDENTITY returns the id of the last thing that was inserted by your client’s connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don’t know about, and you’ll get the ID from this new row, instead of the one you want

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
Another way said: SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won’t cause the wrong value to get returned.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren’t quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.
Also said: IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you’ll get the ID of that row instead of your one.

If you want to play it safe, always use SCOPE_IDENTITY(). If you stick with @@IDENTITY and someone decides to add a trigger later on, all your code will break.

More information:

http://blog.beyondrelational.com/2009/03/sql-server-identity-columns.html

http://msdn.microsoft.com/en-us/library/ms175098.aspx

Introduction to Locking in SQL Server December 10, 2008

Locking is a major part of every RDBMS and is important to know about. It is a database functionality which without a multi-user environment could not work. The main problem of locking is that in an essence it’s a logical and not physical problem. This means that no amount of hardware will help you in the end. Yes you might cut execution times but this is only a virtual fix. In a heavy multi-user environment any logical problems will appear sooner or later.

Lock modes
Shared locks (S) Shared locks are held on data being read under the pessimistic concurrency model. While a shared lock is being held other transactions can read but can’t modify locked data. After the locked data has been read the shared lock is released, unless the transaction is being run with the locking hint (READCOMMITTED, READCOMMITTEDLOCK) or under the isolation level equal or more restrictive than Repeatable Read. In the example you can’t see the shared locks because they’re taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks.
Update locks (U) Update locks are a mix of shared and exclusive locks. When a DML statement is executed SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can’t modify the underlying data. It has to be converted to an exclusive lock before the modification takes place. You can also force an update lock with the UPDLOCK hint.
Exclusive locks (X) Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level. Because DML statements first need to read the data they want to modify you’ll always find Exclusive locks accompanied by shared locks on that same data.
Intent locks (I) Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. Thus the name. Their purpose is to assure proper data modification by preventing other transactions to acquire a lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on the page or the row level an intent lock is set on the table. This prevents other transactions from putting exclusive locks on the table that would try to cancel the row/page lock. In the example we can see the intent exclusive locks being placed on the page and the table where the key is to protect the data from being locked by other transactions.
Schema locks (Sch) There are two types of schema locks:
                - Schema stability lock (Sch-S): Used while generating execution plans. These locks don’t block access to the object data.
                - Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed.
Bulk Update locks (BU) Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import. This allows for multiple fast concurrent inserts by disallowing data reading to other transactions.
Conversion locks are locks resulting from converting one type of lock to another. There are 3 types of conversion locks:
            - Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock
            – Shared with Intent Update (SIU). A transaction that holds a Shared lock also has some pages/rows locked with an Update lock.
            - Update with Intent Exclusive (UIX). A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock.
Key – Range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction. In the example we can see that there are two types of key-range locks taken:
            - RangeX-X – exclusive lock on the interval between the keys and exclusive lock on the last key in the range
            - RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

Lock Granularity
Lock granularity consists of TABLE, PAGE and ROW locks. If you have a clustered index on the table then instead of a ROW lock you have a KEY lock. Locking on the lower level increases concurrency, but if a lot of locks are taken consumes more memory and vice versa for the higher levels. So granularity simply means the level at which the SQL Server locks data. Also note that the more restricted isolation level we choose, the higher the locking level to keep data in correct state. You can override the locking level by using ROWLOCK, PAGLOCK or TABLOCK hints but the use of these hints is discouraged since SQL Server know what are the appropriate locks to take for each scenario. If you must use them you should be aware of the concurrency and data consistency issues you might cause.

Spinlocks
Spinlocks are a light-weight lock mechanism that doesn’t lock data but it waits for a short period of time for a lock to be free if a lock already exists on the data a transaction is trying to lock. It’s a mutual exclusion mechanism to reduce context switching between threads in SQL Server.

(sursa si exemple: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server )

Information about currently active lock manager resources December 10, 2008

sys.dm_tran_locks (Transact-SQL): Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

(sursa: http://msdn.microsoft.com/en-us/library/ms190345.aspx)

Lock Modes: http://msdn.microsoft.com/en-us/library/ms175519(SQL.90).aspx

The following query will display lock information. The value for should be replaced with the database_id from sys.databases.
SELECT * FROM  sys.databases OR if you already know the Id you can use SELECT db_name(<dbid>)

SELECT resource_type,request_mode,request_lifetime,resource_associated_entity_id,
(SELECT [name] FROM sys.objects WHERE object_id=(SELECT object_id from sys.partitions
WHERE hobt_id = resource_associated_entity_id)),
(SELECT [name] FROM sys.objects WHERE object_id= resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE resource_database_id= <bdid>

The following query returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.

SELECT object_name(object_id), *
FROM sys.partitions
WHERE  hobt_id= <resource_associated_entity_id>

The following query will also show blocking information.
SELECT t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address

sp_configure – Displays or changes global configuration settings for the current server December 2, 2008

Limit SQL Server memory usage: Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Enable advanced options:
EXEC sp_configure ’show advanced options’, 1
RECONFIGURE WITH OVERRIDE

Set the maximum amount of memory to 64 MB:
EXEC sp_configure ‘max server memory (MB)’, 64
RECONFIGURE WITH OVERRIDE

Display the newly set configuration:
EXEC sp_configure ‘max server memory (MB)’

Set ’show advanced options’ back to default:
EXEC sp_configure ’show advanced options’, 0
RECONFIGURE WITH OVERRIDE

The configuration options table can be found at: http://msdn.microsoft.com/en-us/library/ms189631.aspx

SQL Trigger November 28, 2008

Un UPDATE pe o tabela poate afecta intre 0 si N rows. Indiferent cate row-ri sunt afectate trigger-ul se apeleaza o singura data pe UPDATE.
In trigger ai access la 2 pseudo-tabele numite “inserted” si “deleted”. Sunt 2 pseudo-tabele disponibile numai in trigger. Au aceeasi structura cu tabela pe care e def. triggerul si contine noile valori ale row-urilor updatate respectiv vechile valori.
Prin aceste 2 tabele se stie ce anume s-a updatat in statement. Ele sunt disponibile in toti trigerii doar ca in cei pe INSERT “deleted” e goala si pe DELETE “inserted” e goala.

exemplu cu “inserted”
CREATE TRIGGER [dbo].[UpdatePendingCalculationLockFlag]
ON [dbo].[PendingCalculation]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @transactionId uniqueidentifier
DECLARE @lockFlag int

DECLARE c1 CURSOR FOR
SELECT TransactionId, LockFlag FROM inserted
OPEN c1
WHILE 1 = 1
BEGIN
FETCH FROM c1 INTO @transactionId, @lockFlag
IF @@FETCH_STATUS != 0
BREAK

IF (@lockFlag = 2)
UPDATE LoadTest_PendingCalculation
SET TimeStampBLF2 = GETDATE()
WHERE TransactionId = @transactionId

IF (@lockFlag = 3)
UPDATE LoadTest_PendingCalculation
SET TimeStampBLF3 = GETDATE()
WHERE TransactionId = @transactionId
END
CLOSE c1
DEALLOCATE c1
END

Ce urmaresc in acest exemplu: la fiecare update care se intampla in tabela parinte (PendingCalculation) vreau sa modific ceva in tabela secundara (LoadTest_PendingCalculation). De aceea printr-un cursor parcurg eventualele randuri modificate (pseudo-tabela inserted).