Statistics and Fragmentation In SQL Server
- Performance Tuning is improve system performance.
- SQL Server Performance Tuning is the process of ensuring that the SQL statements issued by an application run in the fastest possible time.
SET STATISTICS IO ON
- SET STATISTICS IO displays statistics on the amount of disk activity generated by the query.
- SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query.
- When STATISTICS IO is ON, statistical information is displayed, and when OFF, the information isn’t displayed.
- STATISTICS IO ON display information like Table, Scan count, logical reads, physical reads, read-ahead reads, lob logical reads, lob physical reads, lob read-ahead reads.
- Fragmentation has numerous effects that can occur because of data modifications.
- Fragmentation means your physical data and indexes are out of synchronous. Storing data non-contiguously on disk is known as Fragmentation.
- In the below snapshot detect the fragmentation by using system function sys.dm-db-index-physical-stats.
- In PAT_Patient index 98 percent fragmention.
- Using SQL Server Management Studio we can remove fragmentation by following below steps:
→ In SQL Server go to object explorer and open Database as well Table.
→ Right click on fragmented index and select REORGANIZE or REBUILD option.
→ Then click on ok.
- Now we reorganize the index by using query.
- Here we check the fragmentation. And we can see PAT_Patient fragmented index is removed.
- A proper index can improve performance and a bad index can hamper the performance.
- Here I use unused indexes to identify on the database.
- The following example reports disk space information for the Patient Admission table and its indexes.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -