Day: June 23, 2015

Astuces SQL Server

Posted on Updated on


This note aims to put the focus on database issues

How to increase the performance of a view by saving the data to disk?

Create a cluster view (clustered).

What is XACT_ABORT?

cancel or not a transaction inside stored procedure, for example, if an error occurs at a treatment

SET XACT_ABORT ON

How to solve the fragmentation problem index?

Use Rebuild or Reorganize index

The result is obtained with function sys.dm_db_index_physical_stats.

Below results  :  

avg_fragmentation_in_percent : The percent of logical fragmentation (out-of-order pages in the index).

fragment_count : The number of fragments (physically consecutive leaf pages) in the index.

avg_fragment_size_in_pages : Average number of pages in one fragment in an index.

If avg_fragmentation_in_percent is greater than 5% and less than 30%

                => ALTER INDEX REORGANIZE

If avg_fragmentation_in_percent value is greater than 30%

                => ALTER INDEX REBUILD WITH (ONLINE = ON)