1- Nondeterministic functions are not allowed (ex getdate() not allowed in ITVF) 2- cannot call an UDF (also and Inline table-valued functions) via Linked-server (does'nt work : Select...... from linked-server.BD.owner.ITVF(...,...,...,)

performance ; Chose the best provider (ex. SqlClient offers high-performance access to SQL Server natively)

security : use Integrated security=SSPI : use application role (.sp_setapprole)

CPUTime + ResourceWaitTime + SignalWaitTime= Total Query Response Time

from : http://msdn.microsoft.com/en-ca/library/cc281954.aspx

.....SQL Server 2008 introduces support for Windows PowerShell. Windows PowerShell is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. The Windows PowerShell language supports more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts. Windows PowerShell scripts can also be used to administer other Microsoft server products. This gives administrators a common scripting language across servers........ .

From http://msdn.microsoft.com/en-us/library/ms190692(v=SQL.105).aspx

……Under the bulk-logged recovery model, if a log backup covers any bulk operations, the 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 very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost. To track the data pages, a log backup operation relies on a bulk-changes bitmap page that contains a bit for every extent. For each extent updated by a bulk-logged operation since the last log backup, the bit is set to 1 in the bitmap. The data extents are copied into the log followed by the log data. The following illustration shows how a log backup is constructed. ….

One (1) plan if two different sessions use the same SET option

Two (2) plans if two different sessions Not use the same SET option

For each table that contain GUIDs as primary keys , ask the application team to create a new field (identity, integer) and set the clustered index on that new field (instead of primarykey).

limited downtime/maintenance window ---- Reorganize ONLY Index of highly defragmented table (see DBCC SHOWCONFIG) with high FILLFACTOR

There has been NO change in the workload, NO new development changes have been deployed and there are NO other configuration changes to the system. I must try to resolve rapidely the problem without impacts on others users

1- check for LOCKING, check event viewer for error, monitor the network for bottleneck, check what processes are running

2- If the quey is a Stored-Proc, try to generate a better plan t (drop , create, execute SP withh the best parameter I can)

3 - Update Statistique and/or reorganize indexes

From : http://blogs.microsoft.co.il/blogs/dannyr/archive/2008/09/21/filtered-indexes-in-sql-server-2008.aspx

“A new kind of index is presented in sql server 2008; a filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data…........….If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. A SOLUTION is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison…”

Tabulate the RAID Level considerations based on - Reliability, Storage Efficiency, Random Read, Random Write, Sequential Read, Sequential Write and Cost etc. http://www.pcguide.com/ref/hdd/perf/raid/levels/comp.htm

callout any specific workloads or deployment you do on specific RAID Levels

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel1-c.html RAID Level 1 Recommended Uses: Applications requiring high fault tolerance at a low cost, without heavy emphasis on large amounts of storage capacity or top performance. Especially useful in situations where the perception is that having a duplicated set of data is more secure than using parity. For this reason, RAID 1 is popular for accounting and other financial data. It is also commonly used for small database systems, enterprise servers, and for individual users requiring fault tolerance with a minimum of hassle and cost (since redundancy using parity generally requires more expensive hardware.)

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel2-c.html RAID Level 2 Recommended Uses: Not used in modern systems.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel3-c.html RAID Level 3 Recommended Uses: Applications working with large files that require high transfer performance with redundancy, especially serving or editing large files: multimedia, publishing and so on. RAID 3 is often used for the same sorts of applications that would typically see the use of RAID 0, where the lack of fault tolerance of RAID 0 makes it unacceptable

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel4-c.html RAID Level 4 Recommended Uses: Jack of all trades and master of none, RAID 4 is not as commonly used as RAID 3 and RAID 5, because it is in some ways a "compromise" between them that doesn't have a target market as well defined as either of those two levels. It is sometimes used by applications commonly seen using RAID 3 or RAID 5, running the gamut from databases and enterprise planning systems to serving large multimedia files.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel5-c.html RAID Level 5 Recommended Uses: RAID 5 is seen by many as the ideal combination of good performance, good fault tolerance and high capacity and storage efficiency. It is best suited for transaction processing and is often used for "general purpose" service, as well as for relational database applications, enterprise resource planning and other business systems. For write-intensive applications, RAID 1 or RAID 1+0 are probably better choices (albeit higher in terms of hardware cost), as the performance of RAID 5 will begin to substantially decrease in a write-heavy environment.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel6-c.html RAID Level 6 Recommended Uses: In theory, RAID 6 is ideally suited to the same sorts of applications as RAID 5, but in situations where additional fault tolerance is required. In practice, RAID 6 has never really caught on because few companies are willing to pay for the extra cost to insure against a relatively rare event--it's unusual for two drives to fail simultaneously (unless something happens that takes out the entire array, in which case RAID 6 won't help anyway). On the lower end of the RAID 5 market, the rise of hot swapping and automatic rebuild features for RAID 5 have made RAID 6 even less desirable, since with these advanced features a RAID 5 array can recover from a single drive failure in a matter of hours (where without them, RAID 5 would require downtime for rebuilding, giving RAID 6 a substantial advantage.) On the higher end of the RAID 5 market, RAID 6 usually loses out to multiple RAID solutions such as RAID 10 that provide some degree of multiple-drive fault tolerance while offering improved performance as well.

http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel7-c.html RAID Level 7 Recommended Uses: Specialized high-end applications requiring absolutely top performance and willing to live with the limitations of a proprietary, expensive solution. For most users, a multiple RAID level solution like RAID 1+0 will probably yield comparable performance improvements over single RAID levels, at lower cost.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+1 (01) and 1+0 (10) Recommended Uses: Applications requiring both high performance and reliability and willing to sacrifice capacity to get them. This includes enterprise servers, moderate-sized database systems and the like at the high end, but also individuals using larger IDE/ATA hard disks on the low end. Often used in place of RAID 1 or RAID 5 by those requiring higher performance; may be used instead of RAID 1 for applications requiring more capacity.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+3 (03 or 53) and 3+0 (30) Recommended Uses: Not as widely used as many other RAID levels. Applications include data that requires the speed of RAID 0 with fault tolerance and high capacity, such as critical multimedia data and large database or file servers. Sometimes used instead of RAID 3 to increase capacity as well as performance.

http://www.pcguide.com/ref/hdd/perf/raid/levels/mult.htm RAID Levels 0+5 (05) and 5+0 (50) Recommended Uses: Applications that require high fault tolerance, capacity and random positioning performance. Not as widely used as many other RAID levels. Sometimes used instead of RAID 5 to increase capacity. Sometimes used for large databases

RAID Levels 1+5 (15) and 5+1 (51) Recommended Uses: Critical applications requiring very high fault tolerance. In my opinion, if you get to the point of needing this much fault tolerance this badly, you should be looking beyond RAID to remote mirroring, clustering or other redundant server setups; RAID 10 provides most of the benefits with better performance and lower cost. Not widely implemented.