Thursday, August 23, 2012

The “NOT FOR REPLICATION” option in SQL Server Replication:

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

The NOT FOR REPLICATION option allows you to specify that the following database objects are

treated differently when a replication agent performs an operation:


* Foreign key constraints

The foreign key constraint is not enforced when a replication agent performs an insert, update, or

delete operation.


* Check constraints

The check constraint is not enforced when a replication agent performs an insert, update, or delete

operation.


* Identity columns

The identity column value is not incremented when a replication agent performs an insert

operation.


* Triggers

The trigger is not executed when a replication agent performs an insert, update, or delete
Get Oracle Certifications for all Exams
Free Online Exams.com

What is filtered index

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A filtered index is an optimized nonclustered index, especially suited to cover queries that select

from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.


A well-designed filtered index can improve query performance, reduce index maintenance costs,

and reduce index storage costs compared with full-table indexes.


Filtered indexes can provide the following advantages over full-table indexes:


Improved query performance and plan quality

Reduced index maintenance costs

Reduced index storage costs

Get Oracle Certifications for all Exams
Free Online Exams.com

How to set the instance to utilize one thread for searches which owns an estimated execution cost less than 6.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Change the sp_configure cost threshold for parallelism option.

 

Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

 

Only set cost threshold for parallelism on symmetric multiprocessors.

Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate the plan. The cost threshold for parallelism option is actively used when a mix of short and longer queries is executed. The short queries execute serial plans while the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, thus executing only serial plans.

 

In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This is because the decision to use a parallel or serial plan, with respect to cost threshold for parallelism, is based on a cost estimate provided before the full optimization is complete.

 

The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

 

If your computer has only one processor, if only a single CPU is available to SQL Server because of the affinity mask configuration value, or if the max degree of parallelism option is set to 1, SQL Server ignores cost threshold for parallelism.

 

cost threshold for parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and

restart).

Get Oracle Certifications for all Exams
Free Online Exams.com