I wonder if anyone can shed som light on SQL-server and index fragmentation?
Problem is that I have one customer in particular that experience sever index fragmentation, to the point where we now rebuild indexes two times a week. If we don’t, the application becomes almost unresponsive. Other customers do not experience this.
There are differences in both size of database and which features are used. Also, one of them have a .NET web application connected. The large customer has a 100GB database now, where about half is documents and images, we have deleted any information older than 2018. The smaller customers has all data back to 2006, no problems.
What is weird to me, is that any index is fragmentated to 99%, even on tables that are rarely updated.
I would like to help the customer, but I can’t see that what we do with regular applications should cause this. I assume an enviroment issue, but not sure.
Anyone had similar issues and how did you solve it?
That was useful. I have played with the “First responder kit” and it seems to me that I have at least two issues
- First issue is that I have 1:M relations that are not indexed. They have a key, but index, when processing using SET/NEXT, it might scan entire tables to create the result.
- Second issue is that Clarion is not parameterizing PROP:FILTER, so a new execution plan must be created each time a new cursor is created (I think?). Not sure how to work around that in regular views or if it necessary as long as it is a cursor. I can do it where prop:SQL is used though, but I don’t have too many of those anymore.
The problem is if you have relations and keys in your DCT but they do not exist in SQL or the other way round, SQL then build these indexes at runtime from many users. Sync your DCT with the DB!
I am uncertain what you respond to. I am missing some indexes,and clearly, missing an index would make SQL-server have to work more to retrieve data, but that should not affect writing and updating other indexes.
Please explain how an existing index can get fragmented because other indexes are missing?
I’ve found once a table gets large enough, the thresholds for automatically updating statistics don’t fire often enough. The percentage of change that needs to occur to force the stat update isn’t frequent enough, so that the users don’t experience performance issues.
When new data is being added to a table and that new data are the rows being accessed, they are not included in the stats until the stats are updated. For example, you are retrieving the items for a job. The item stats for the link to the job table do not reflect values for newer jobs IDs. SQL estimates for the rows to return can be way off and it picks a poor execution plan to achieve the query.
Check out Ola Hallengren database maintenance scripts. They allow you setup jobs to perform this kind of thing using your own thresholds.
And another article talking about setting up the jobs.
https://www.sqlshack.com/ola-hallengrens-sql-server-maintenance-solution-index-statistics-maintenance/
Roelf, if you are talking about MS SQL Server, it does not build indexes automatically based on workload.
Hi Rick,
For Oracle, not MSSQL, but possibly related. When I have a query that performs really poorly and I look at the explain plan to see what the problem is, the query could probably be expressed as:
with x as
(select *
from a bunch of tables
that are expensive to link together
due to lack of proper indexes
)
, y as
( select x.id,b.column
from x
join b on x.something = b.something
)
, x as
(select x.id, b.column
from x
join c on x.something_else = c.something_else
)
select x.*,b.column,c.column
from x
left join y on …
left join z on …
The way the optimizer deals with this is to construct a materialized view first from the things that are expensive to join together, and then treat that as a table for putting together the rest of the query. So, no it doesn’t create indexes, it basically creates a temporary table When I end up with one of those, the solution (if the slowness is a real problem) is generally either:
– create a table temporarily that mimics the part that that the optimizer felt it had to create the mat view for, or
– add indexes so that first part is not so expensive.
It’s a bit off-topic. I have to say that fragmentation of indexes must be a MSSQL thing. There is virtually nothing in Oracle-related info that talks about it. Also the data I mostly work with is recopied from the production database every week, so my indexes are rebuilt from scratch every week. So that means I would be very unlikely to run into it.
Jon
Hi Jon,
I was not going to get into examining execution plans here. 
Interesting about no fragmentation in Oracle. Obviously, the engines themselves are very different!
I haven’t done Oracle work in nearly 20 years now.