Astuces SQL Server
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)
Advertisements
