>Hi im running on SATA RAID level 1 software level in Windows 2003. I have a physical table and more logical tables (views) and updates and insertion are done in views. All of the routine were done using ATOMICITY method using TRANSACTION. Lately our data is just 700MB (mdf) file on a raid 1 harddisk and i notice there is a very slow performance every time a call is made to the procedure with BEGIN Transaction call. The procedure manipulate the view (update, insert , delete) and when running on multiuser mode on a terminal server, the disk queue reaches 100% (Performance Monitor). Does this means when using TRANSACTION i really need to use Ultra SCSI and run it on RAID 5 ? Is this a normal issue when using BEGIN TRANSACTION? I also assume that when we issue BEGIN TRANSACTION there will be a TABLE LOCK so since I have different table views with 1 source of physical Table I assume that this is the problem. Should I change my table view to physical table to seperate the locking? Also
> in the SQL Enterprise manager I notice sometimes that there are Blocking Process.
>
>Thank you for your response
>
>Simplicio

SQL Server does not use a table lock - even for views - unless the query optimizer thinks that a table lock will perform better than page or row locks. The view is not the problem.

Have you looked at the execution plans for your update statements? If SQL Server is choosing a table lock then it is probably performing a table scan and you may need better indexes.

Transactions in SQL Server are very lightweight. It is highly unlikely that the transaction is the cause of the poor performance. When you start a transaction, SQL Server writes a transaction marker to the log file before the data. It's only a few extra bytes written to disk.

And processes blocking other processes is normal as long as it is only for a moment. You still want to minimize the blocking, but that is done by tuning up all of the queries so that they only read/write the minimum data needed and execute very quickly.