Statistics and Fragmentation In SQL Server

  • 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 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.
SET STATISTICS IO ON
  • 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.
FRAGMENTATION
REORGANIZE
AFTER FRAGMENTATION
  • Here I use unused indexes to identify on the database.
UNUSED INDEX

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store