Qs on locking/isolation

http://www.mssqltips.com/tip.asp?tip=1253

Table variable indexes

Two types of indexes may be specified on a table variable - primary key (clustered) and unique. However, they need to be included in the table variable definition.

DECLARE @test TABLE
(
a INT PRIMARY KEY,
b varchar(50),
UNIQUE (b)
)


Generally, temp tables perform better in situations where an index is needed.

RAID for SQL Server

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

RAID 0 (disk striping)
This RAID level offers the highest throughput from a write perspective, but there is no redundancy built in. Since your backup files are your last line of defense and there is not a redundant set of data, I would not recommend this option.

RAID 1 (disk mirroring)
This level also offers a high level of I/O throughput as well as a redundant copy. The downside is that the size of the data partition using RAID 1 will be limited based on your physical disk size. One option would be to write your backup file to multiple RAID 1 arrays that are independent of each other and, possibly, to use different controllers or channels to further increase the I/O throughput.

RAID 5 (striping with parity)
With RAID 5 there is a write penalty because of the need to keep the parity in check. If you are really looking for faster backup processing, this RAID level does not make sense.

RAID 10 (mirroring with striping)
With RAID 10, you get the advantage of both RAID 0 and RAID 1. The downside is that it becomes very expensive to implement. But, the upside is your I/O throughput should be very fast. Since RAID 10 uses all of the drives in the array, to gain higher I/O rates, the more drives in the array will increase performance.


Recommended: Consider using independent pair of RAID1 volumes over RAID10

In addition to RAID levels, here are other things to take into consideration:

Controllers
In setting up your RAID arrays, try to isolate the different types of I/O traffic to different controller cards or channels on the controller cards. Also, the better the controller card, the better the performance.

Disk speeds
Disk drives come in multiple speeds. SCSI drives currently run as fast as 15 K rpm and are faster than the IDE drives. In most cases, enterprise-wide servers will probably be using SCSI technology which is the better choice.

Disk size
You can now get drives that are several GBs in size. This is great if you want to store a lot of data that is not accessed very often. But for writing out a large amount of data as fast as possible, large disk size doesn't help. What will be more advantageous are more drives in the array versus a couple of very large drives.


Refer: http://blogs.zdnet.com/Ou/?p=484



Tempdb - Separate RAID0
Data/Log- RAID 10 but pricey, or go for independent RAID1 arrays

Performance counters

Performance Object	Counter	Instance	Remarks
Memory	Pages/Sec		Rate of hard page faults; Average value <>
Physical Disk	Average Disk Queue Length		Average number of queued disk requests; Average value <>
Processor	% Processor Time	_Total	Average value <>
SQL Server: Access Methods	Freespace scans/sec		The areas where the count increases need to be monitored and proper indexes introduced to avoid such scans.
SQL Server: Access Methods	Full scans/sec		The areas where the count increases need to be monitored and indexes revisited.
SQL Server: Buffer Manager	Buffer Cache Hit Ratio		% of requests served out of the buffer cache; Average value >= 90%
SQL Server: Locks	Lock timeouts/sec	_Total	Should be 0
SQL Server: Locks	Lock wait time (ms)	_Total	Should be low
SQL Server: Memory Manager	Memory Grants Pending		Number of processes waiting for workspace memory grant; Average value = 0
SQL Server: SQL Statistics	SQL Re-compilations/sec		Should be 0 or near it
System	Processor Queue Length		Number of requests outstanding on the processor: Average value <>
System	Context Switches/sec		Rate of switching processors from one thread to another: Average value <>