My initial response to your interesting question was to look at the answer(s) and think: Hey, good answers.....
However, after mulling it over for a bit, I came to a slightly different conclusion.
While there are legitimate reasons to believe that in a modern SAN (Storage Area Network) infrastructure there is no need for a DBA (Data Base Administrator) to perform index reorganizations and/or index rebuilds or possibly initiate statistics updates, there still might be certain technical limitations / reasons to do so nonetheless.
This is possibly contrary to what is currently (April 11th, 2022) believed to be state of the art, so I'm formulating my answer as a thesis (to be continued).
List of Interesting Articles Over The Years
Observations Over The Years
Recommendations come and go, just like the seasons of the year. Eventually somebody will find a reason not to rebuild an index or why not to reorganize an index, ....
The bottom line today seems to be: Forget rebuilding your indexes. Let SQL Server sort it out. The SAN is fast enough to cope with it...
...but I disgress.
Over the past 5 to 10 years disks have evolved from being spinning platters read by a thin wire led over the surface of the disk by the reading arm steered by a controller to position itself in the inner or outer rings of a spiral data path, to being an array of chips which are written to in a semi-random manner in order to reduce the ageing of the chips. The controller decides where to write and once written can re-arrange the position of the data if the controller decides it has been too long in one space and has been modified.
The era of sequential reading and optimizing the sequential reading of an extent (64 kb) of SQL Server database date seems to have been lost with the emergence of SSD (solid state disks/drives).
You used to format your disk with 64 kB clusters so as to align the reading of the extents with the disk geometry. In the beginning you even had to cope with disk not having a 1024 kB offset after formatting, which could result in reading two 64 kB clusters to retrieve one extent. To reduce the impact you might have larger read-aheads (up to 1024 MB - Enterprise Edition) which would reduce the impact of not having an 1024 kB offset.
Aligning the disk geometry and the formatting with the 64 kb extent size resulted in optimal performance. One Disk (Cluster) Read = One Extent.
*Reference: Disk Partition Alignment Best Practices for SQL Server (Microsoft Downloads .DOC)
Block Sizes on SSD
Even with today's SSD technology where the reads and writes are steered by a controller that distributes the writes evenly over the entire NAND (or other technology) chips, there is still a certain amount of data that will be read from or written to a disk.
According to the article How Do SSDs Work? writing a single page of data (4 kb, 8 kB, 16 kB, ...) will actually write 4 MB of data to the chip.
The entire grid layout is referred to as a block, while the individual rows that make up the grid are called a page. Common page sizes are 2K, 4K, 8K, or 16K, with 128 to 256 pages per block. Block size therefore typically varies between 256KB and 4MB.
If you make a change to a 4KB file, for example, the entire block that 4K file sits within must be updated and rewritten. Depending on the number of pages per block and the size of the pages, you might end up writing 4MB worth of data to update a 4KB file.
But How Does This Impact My Indexes?
Well your indexes are stored in SQL Server pages, which are roughly 8 kB in size. This might correspond with the page size of your SSD, but might not depending on the disk architecture of your SSDs. If you're lucky, then the extent size of 64 kB will correspond with the disk geometry of your SSD. Or it might not.
Because you can possibly fit more than one index item (leaf level, intermediate level, root node) into an 8 kB page you are eventually going to hit some form of fragmentation inside the index, even though the index is stored physically on an SSD.
Reference: SQL Server index structure and concepts (SQLShack 2018)
You have index fragmentation on your SSDs.
And How Does This Impact Performance?
While the distribution of the data on the SSDs is managed by the controller, and the access to the data is almost instantaneous, you will still have thousands of instantaneous access to data which in the end will sum up to some amount of disk access time.
SQL Server will be asking for much more data form the data (SSD) pages to satisfy the queries need to read through indexes root levels and intermediate levels to get to the data. If the data isn't ordered inside the data (SQL) pages / extent, then that will result in more reads from the SQL Server side and SSD side of the equation.
Answering Your Questions
However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL server is running on a SAN infrastructure and I have read that there is no benefit to setting fill-factor to less than 100%, or to perform index reorganize or index rebuilds when the data files are on a SAN storage. Any advice will be appreciated.
There will be a benefit or performing index maintenance tasks, but you might be better off setting different fragmentation levels which trigger either a
REORGANIZE INDEX ... or a
REBUILD INDEX .... (e.g. 30% for REORG and 60% for REBUILD).
You don't want the levels too low or you will be impacting the lifespan of your SSD NAND (or other technology) chips, because of the additional wear due to the re-writing of the data.
The statistics are a totally different story. Having up-to-date statistics will greatly improve the cardinality estimation of the SQL Server Query Optimizer:
The SQL Server Query Optimizer is a cost-based Query Optimizer. This means that it selects query plans that have the lowest estimated processing cost to execute. The Query Optimizer determines the cost of executing a query plan based on two main factors:
- The total number of rows processed at each level of a query plan, referred to as the cardinality of the plan.
- The cost model of the algorithm dictated by the operators used in the query.
The first factor, cardinality, is used as an input parameter of the second factor, the cost model. Therefore, improved cardinality leads to better estimated costs and, in turn, faster execution plans.
Cardinality estimation (CE) in SQL Server is derived primarily from histograms that are created when indexes or statistics are created, either manually or automatically. Sometimes, SQL Server also uses constraint information and logical rewrites of queries to determine cardinality.
Reference: Cardinality Estimation (SQL Server) (Microsoft SQL Docs 2021)
So you could benefit from having a separate OLA job that basically optimizes the statistics of your data.
As you have pointed out, the Database Engine will eventually update the statistics for you, but there have to be a certain amount of data modifications (UPDATE, INSERT, DELETE) before this action is triggered.
Auto Update Triggers
Up until SQL Server 2014 the value of modified data that would trigger an automatic update of the statistics was calculated as:
[rows_modified] * 1.0 / 100 * 20 + 500
... or simply put: If 20% of the rows + 500 rows have been modified, then update the statistics.
This value changed after SQL Serve 2014 to:
MIN([rows_modified] * 1.0 / 100 * 20 + 500, SQRT(1000 * [rows_modifed]))
... or put differently: As soon as the either square root of (1000 times the modified data) has been reached OR the older calculation of 20% of the rows + 500 rows have been modified, then update the statistics.
Reference: Statistics (Microsoft SQL Docs 2022)
Then you can have a situation where the data that the users want most is the data that has been added an hour ago. Consider having a table that contains historical tax data. Which data will be accessed the most? Possibly the data that has been added in the last month. Now if you have 10 years of data for 20 Million people and add just 2% of new data, then the Query Optimizer might not use the correct index to query the data or perform an index seek instead of index search because of outdated statistics. The values that would trigger an updates of the statistics might never be reached.
The solution would be to have a separate OLA job that periodically updates the statistics for these special tables.
- You mileage will vary.
- You might need a
REORG INDEX... or
REBUILD INDEX... job.
- You might need an additional
UPDATE STATISTICS... job.
- There is no ONE SOLUTION to suit them all.
Reference Reading Summary