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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s