ITVF´s can´t be indexed.

in ITVF´s you dont have to write the WHERE statement, just pass parameters to it.

in ITVF´s you must pass them the number of parameters specified in function, when selecting data from view´s you could omit to write a WHERE clause.

Mmm, one example of performance improvement is setting the packet size = 32767 in the connection string for SSIS data pumps.

In terms of security, changing the connection to an integrated one, using the trusted connection=yes or Integrated security=SSPI parameters, is a best practice.

signal wait + resource wait + query execution time = Total Query Response Time

Powershell / shell programming is a great and easy tool for executing tasks that you couldn´t do so easy , or can´t do, with sql server maintenance plans/jobs...etc.

It can interact with all the .NET platform and get access to tools like WMI , registry, hard drive.

You can schedule a powershell script that can do near anything.

For example: You can read the status of all of your sql servers and publish it throught a webservices in form of interactive report.

Bulk logged recovery model offers a smaller log size, NOT a smaller log backup size. Transactions minimally logged are writen in log backup as a full logged transaction in both bulk_logged and full recovery model, with a small difference:

Due to bulklogged recovery model does not support point-in-time recovery, bulklogged log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup much larger than full recovery one.

Raid 0 relays on stripping. No Fault Tolerance. If a disk fails data can be recovered using special tools, however, this data will be incomplete and most likely corrupt, and data recovery is typically very costly and not guaranteed. Performance is sightly better than other Raid systems in random and sequentials read/writes.

Raid 1 relays on mirroring. If a disk fails, the data is available in the other disk. RAID 1 can be an effective protection against physical disk failure, it does not provide protection against data corruption due to viruses, accidental file changes or deletions, or any other data-specific changes. Raid 1 is great in sequentials writes/read, cause of mirroring. Raid 1 is not good as Raid 5 in random operations.

Raid 5 relays on stripping with parity. It saves the partity of one disk into the others. Raid 5 is very powerfull in random reads. Is not good as Raid 0 or 1 in sequential ones and random writes. Raid 6 is similar to Raid 5 xcept for an additional parity block. If a disk fails, the data can be recover througth the other disks. It has an performance penalty on write operations because of the overhead associated with parity calculations.

Raid 10 relays on stripping with mirroring. All but one drive from each RAID 1 set could fail without damaging the data. However, if the failed drive is not replaced, the single working hard drive in the set then becomes a single point of failure for the entire array In most cases RAID 10 provides better throughput and latency than all other RAID levels except RAID 0 (which wins in throughput).

If query plans are identical ,only 1 plan are retained at the end.

This can be checked saving the result of this query

select planhandle,COUNT(*) as a into #tmp from sys.dmexeccachedplans group by plan_handle order by COUNT(*) desc

Executing the two sentences simultaneously and saving the query again in #tmp2 for example.

As we can see, only one query plan has been added (Tested in SQL Server 2008 SP3)

1: Add an int(smallint if possible, bigint if required) column as primary key.

Remove the index from GUID column (and remove the column if posible).

Create clustered index on the new Int column with fillfactor=90. Doing this we Rebuild all the indexes and remove the actual fragmentation.

2: Since there is a little write activity and maintenance window is small, schedule Weekly Alter index reorganice and Monthly Rebuilds to remove frag and maintain the fillfactor. Take a look daily to index fragmentation and pagesplits and accomodate alter index's schedules and fillfactor value.

1. Check for locks
2. Check for heap/index fragmentation
3. Check server activity ( IO, memory, disk counters)

Trigger is perfect for this situation.

A job would work too, but you need to depend of SQL Agent.