Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?

Question

I have configured Ola Hallengren's backup and integrity check scripts.

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.

Statistics maintenance is done automatically by SQL server so I don't see any benefit of configuring Ola's scripts for statistics maintenance.

Note that SAN storage means multiple disks are involved, and data (irrespective of whether the table has no index [heap] or clustered index, or non-clustered index) is scattered across multiple disks.

Answer 1

The "index optimize" job created by Ola's install script focuses on defragmentation, not statistics update. It will do:

• Nothing if the fragmentation level is below 5%
• REORG if it is between 5% and 30%
• REBUILD if it is above 30%

You get new statistics with rebuild, but not reorg. Note that you can have indexes with lots of modification without those indexes being fragmented, hence not causing rebuild to happen, leaving you with out-of-date statistics.

As you might know, I highly doubt the value of defragmentation, which I also wrote about here.

I recommend that you create one more job, in addition to the ones that Ola's install script created, and that job focuses on update statistics. It can look something like below:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'

Answer 2

It is true that index fragmentation is mostly a thing in the past, if your database is architected properly, making index maintenance mostly moot these days, especially with modern hardware. One example where I have seen index maintenance necessary was on a third party application's database, where they didn't believe in clustered indexes, so all of their tables were heaps which would constantly get over 90% fragmented and perform poorly. But this is why I added the disclaimer "if your database is architected properly" to my first sentence. (Note this may have been on mechanical storage at the time, which could've exposed the problem of a fully fragmented heap even more so.)

Statistics maintenance is a different objective and still relevant today. Having up to date statistics helps lead to better cardinality estimates when the SQL Engine is choosing an execution plan to process your query. Poor cardinality estimates (generally when they're off by an order of magnitude or more) can result in really poor performance (regardless of what kind of hardware your server is on) due to a number of different improper decisions made in the execution plan.

For example, when there's an underestimate for the number of rows the Engine thinks is going to be returned for a given query, it may under-request how much Memory is needed to process the query, which will starve the execution plan of adequate resources to run. Conversely, an overestimate can result in over-provisioning of resources to your query's execution plan which also takes time to allocate, and then hogs those resources from the rest of the server. If I recall correctly, a single query can take up to 25% of the instance's allocated Memory.

Another type of performance bottleneck that can result from poor cardinality estimates is improper operations being used in the execution plan that was chosen, particularly when data is being joined together. For example, an underestimate may result in the Nested Loops Join operator to be chosen, because it is usually more performant for smaller data sets, but a Hash or Merge Join operator would've been actually more performant in this scenario, had the cardinality estimates been accurate.

Statistics aren't the end all be all for guaranteeing always having good cardinality estimates, but it's one factor that can help (or harm when the statistics of your tables are outdated) those estimates. Updating statistics at a frequency that makes sense relative to how large your data is and how frequently those tables change is key. It is not a blocking operation to update statistics, but it can utilize a decent amount of server resources to do so.

Regarding Ola Hallengren's scripts, I'm not an expert on them as I don't personally use them (yet) but my understanding is they provide better granularity and configurability to schedule maintenance more appropriately for your use cases. They're also supposed to be more reliable than scheduling maintenance with the out of the box Maintenance Plans. For these reasons, I believe they are the more popular choice for maintenance.

Answer 3

Your point about Statistics Maintenance is down to preference. SQL Does it automatically using its one-size-fits-all t-shirt. But if your statistics get too out-of-date on a small table, then it can be like a snowball starting an avalanche through your execution plan.

But it's large tables that get caught out. With some large tables i.e. [dbo].[CustomerComplaints] can get so large that they can accumulate 10 million rows before the T-shirt spots that a given percentage of it has changed.

It's your choice to trust in the t-shirt.

The fill-factor depends on the data-type and read-write activity of your database. There are cases where 100% is acceptable but it isn't SAN storage that is a factor.

The only debate I can see on Fill-Factor with a SAN is how you limit Network traffic.

SQL stores/retrieves data in 64k extents. You want a single (4 byte) row of data, you've got to retrieve the 64k in which that row lives. If you're doing a sequential read and the pages are heavily fragmented with poor page fullness (due to a poor choice of fill-factor) then you could (at worst) be transmitting a 64k extent over the network in order to get your hands on 32k of data....ouch!

Some SAN controllers are smarter (and more expensive) than others, when you ask for a Block, it will read the Block next to it and have it ready in anticipation. This give a performance edge on sequential reads.

Not sure what else to add... J.D. said it all..

Answer 4

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

• Index Maintenance Madness - Brent Ozar (2017)
• Good Reasons to Rebuild or Reorganize Indexes - Brent Ozar (2017)
• DBA Training Plan 10: Managing Index Fragmentation - Brent Ozar (2020)
• Meme Week: Setting Fill Factor to Fix Fragmentation - Brent Ozar (2022)
• You probably don't need to reorg & rebuild your indexes so often... - Brent Ozar on Twitter (2021)
• Optimize index maintenance to improve query performance and reduce resource consumption - Microsoft SQL Docs (2022)
• How can I quickly detect and resolve SQL Server Index fragmentation for a database? - Stack Overflow (2017)
• Why do I have to rebuild the indexes on a table after inserting >150,000 records? - Stackoverflow (2014)

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.

Disk Architecture

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.

and

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)}

Other Considerations

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.

Summary

1. You mileage will vary.
2. You might need a REORG INDEX... or REBUILD INDEX... job.
3. You might need an additional UPDATE STATISTICS... job.
4. There is no ONE SOLUTION to suit them all.

Reference Reading Summary

• Index Maintenance Madness - Brent Ozar (2017)
• Good Reasons to Rebuild or Reorganize Indexes - Brent Ozar (2017)
• DBA Training Plan 10: Managing Index Fragmentation - Brent Ozar (2020)
• Meme Week: Setting Fill Factor to Fix Fragmentation - Brent Ozar (2022)
• You probably don't need to reorg & rebuild your indexes so often... - Brent Ozar on Twitter (2021)
• Optimize index maintenance to improve query performance and reduce resource consumption - Microsoft SQL Docs (2022)
• How can I quickly detect and resolve SQL Server Index fragmentation for a database? - Stack Overflow (2017)
• Why do I have to rebuild the indexes on a table after inserting >150,000 records? - Stackoverflow (2014)
• How Do SSDs Work? - Extreme Tech (2021)
• Disk Partition Alignment Best Practices for SQL Server - Microsoft Downloads *.DOC (May 2009)
• SQL Server index structure and concepts - SQLShack (2018)
• Cardinality Estimation (SQL Server) - Microsoft SQL Docs (2021)
• Statistics - Microsoft SQL Docs (2022)

Answer 5

Erin Stellato wrote an excellent article on using the Ola Hallengren scripts for statistics maintenance, even if you discover that index defrags are less important than they used to be when storage was weaker. She explained how Ola's scripts offer more precise control than SQL Server features.

Answer 6

Sorry for adding to a 6 month old question thread, but this came up when I was doing a search for something else and has 1000 views and, frankly, the answers so far are riddled with misinformation and a lack of product understanding so I felt compelled to add a few comments in case someone else happened across this page.

Firstly, Clustered Indexes are important. A SAN will not protect you against a Heap. Imagine you have a phone book and it's in no logical order whatsoever. Someone says "Find me all the Fergusons". Remember, it is in no order. You have no option but to scan the entire phonebook from front to back looking for all instances of the surname. Imagine if you have that phonebook ordered in surname order. You now can skip quickly to the F, then Fe, then Fer, then read the names and know you're done. Therefore the difference is maybe reading 5 pages of a phonebook as opposed to 50,000 pages. In a database we could be talking billions of records. If you think a SAN or NVME can read 100GB of table as fast as an Index being used to read just 5 or 6 8Kb pages, then you're sadly mistaken. This is why indexing is still important.

Index maintenance is also still necessary. Yes, a SAN or NVME drives mean that you can access data in random reads at a fast rate and therefore the sequential part of an index is moot, however, there are other factors in play. SQL Server stores data in 8Kb pages and let's imagine we have an 8Kb page full of data and there are 4 records making up this page, with surnames beginning A, D, H, K and a clustered index on surname. We want to insert a new record with surname Ferguson. This can't fit on the page and so SQL Server has to split the page into 2, then add the record to the first page. So we now have 1 data page with 3 records and 25% free space, the other data page with 2 records and 50% free space. Imagine this happening at a high rate of knots on a very large table throughout the working day. We could end up with huge fragmentation. This isn't an issue for the SAN in terms of finding the data as it can do random reads... but you might end up with a table showing 100GB when it only has 50GB data in it. Again, reading 100GB is slower than reading 50GB. Also, SQL Server reads all data from disk into memory and therefore you're using 50GB memory for no reason. This adds huge overhead and churn to your system. Doing index maintenance would remove this fragmentation and make the entire SQL Server more efficient and performant no matter what disks you may have. (Also disk space in a SAN or on NVME is far from cheap, using it for blank space is not ideal)

Lastly, in terms of statistics... yes, SQL Server has Auto Stats Update and always has. This has been a setting for nearly 20 years now. However, it is massively flawed (as one of the replies above correctly stated). It only fires based on an amount of records in your table changing. Therefore, chances are, in most situations you will be working with out of date statistics that are not going to be updated by autostats. This is critical to your system performance. In finance most new data is latest day and, as this data is a small fraction of the overall tables, auto stats will not kick off and not tell SQL Server that this latest data exists. However, nearly all reports run are latest date. Therefore all reports run appallingly slowly as SQL Server doesn't know the data exists and returns an estimate of 1 record. This leads to poor execution plans, heavy CPU usage, an increase in disk IO, memory churn etc etc. BUT if we were to run stats updates ourselves (or via Ola's scripts) then correct estimates would be returned and performance improve. This will NOT happen with auto stats no matter how much you'd like it to.

Therefore, in summary, you DO still need to consider and maintain databases in terms of indexing and statistics. Not doing so will hurt performance badly in the long run, is not sustainable for larger databases, and will cost a lot of money in unnecessary hardware as a result. Yes, a SAN can alleviate the pain of random reads, but nothing else.

Hope this helps anyone else who stumbles across this and, again, apologies for bouncing an old question. I know it's bad form, but the answers worried me enough to willingly face the backlash.