Rebuilding and Reorganizing Indexes
-
Hi all,
I have SQL Server 2017 Edition and see harmful fragmentation for all tables in DB.
I would like to rebuild and reorganize my indexes, but our system works 24/7.
Could I do it with the online option because I read the different articles and see a different meaning regarding this?
-
First, rebuilding indexes online is only available in Enterprise Edition and I don't see that you have specified what edition you have - whether Standard or Enterprise. Second, rebuilding indexes online does take some locks at various points so it isn't guaranteed that zero blocking will occur. Third, fragmentation itself isn't typically the root cause of performance problems. Especially with high performance storage available to database servers these days. You might be better served using a routine to update your statistics versus rebuilding or re-organizing indexes.
-
I have a Standard Edition.
I have poor performance of my SQL Server and saw that a lot of tables had fragmentation more than 90 %.
-
What data do you have that the SQL Server is experiencing poor performance? What issues are you seeing or are being reported by end users?
Is the Query Store enabled for the databases on your SQL Server? If not, I would consider enabling the Query Store and looking at its various reports and data in it to help you determine what queries are having issues, what the plans for those queries are, if there are a lot of different plans for the same query, CPU and duration of the involved queries.
If those indexes that are fragmented are small in size and number of 8kb pages, the fragmentation isn't really going to be enough of a problem to hurt performance enough for someone to notice. Is there an index maintenance routine in place already? Perhaps Ola Hallengren's maintenance solution? If so, that solution already skips re-organizing or rebuilding indexes based on size and number of 8kb pages.
Rebuilding indexes will rebuild the statistics, which can easily cause one or more query plans to be invalidated. The next time a query is submitted that uses that index or statistics, SQL Server is likely to re-compile a new plan. That new plan might be better for the first set of parameters than the current plan is for the set of parameters it is being passed. In other words, out of many things the performance issue could be, it might just be parameter sniffing and updating the stats can trigger a new plan without the work involved in rebuilding an index.
-
ONLINE rebuilds must be fully logged. Make sure you have pre-allocated enough log space to handle the ONLINE rebuild before issuing the command.
Then, try an ONLINE rebuild on an index. SQL Server will return an error if you're not allowed to use ONLINE rebuilds.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Hi Scott. The online index rebuild is, unfortunately, only available in Enterprise Edition based on what I see here. The OP has Standard Edition.
- christi1711 wrote:
I have a Standard Edition. I have poor performance of my SQL Server and saw that a lot of tables had fragmentation more than 90 %.
Are you sure that this is the cause of your performance issues? How have you determined that this is the cause?
Can you elaborate on what maintenance you have in place? Are statistics being updated?
Is this a virtual machine, or a physical machine?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
We don't have any maintenance on DB.
It is a virtual machine. I saw that a lot of indexes take a lot of space:
Everything works very slow. The same queries have different duration.
I don't know which way I need to move. Where I can look for a problem...
-
Since there is no maintenance in place for the databases, I would encourage you to go to https://ola.hallengren.com/ and download the maintenance solution. Run the .sql file you get from the download. It will create a number of jobs related to running CHECKDB, Backups and index maintenance. I do hope you have backups being taken by some method already and CHECKDB running by some means. If not, I would encourage you to set that up as well ASAP. Ola's scripts are known worldwide and support for them is easily attainable.
For your question, I would start by creating a schedule for the Index maintenance job that the .sql file creates. I would set it up to run weekly, off hours and then create a second job configured daily to update the statistics. That second job to update the statistics you would create manually and put the code in the second code block in that step.
I would then turn on the Query Store and familiarize yourself with the data it collects and how to use it to find troublesome queries.
https://azure.microsoft.com/en-us/blog/query-store-a-flight-data-recorder-for-your-database/
USE [master]
GO
ALTER DATABASE [MYDatabaseNameHere] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 2000, QUERY_CAPTURE_MODE = AUTO)
GOEXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL' - christi1711 wrote:
We don't have any maintenance on DB. It is a virtual machine. I saw that a lot of indexes take a lot of space:
Everything works very slow. The same queries have different duration. I don't know which way I need to move. Where I can look for a problem...
Also, since you mention this is a VM but are saying there is no maintenance in place, I would ask your System Administrators if they have a 3rd party app, like Zerto, that is taking backups of the entire VM. There might be backups happening that you're not aware of. You don't want to implement a native backup solution, like the backup jobs in Ola's SQL Server Agent job list, if there is already something taking backups. If Zerto, or regular VMWare backups are happening, you should be able to see that in the SQL Server ErrorLog. It will indicate that a freeze is taking place on the databases when the VM backup is happening.
Also, you can grab Adam Machanic's sp_whoisactive from the internet and run that to see a lot of good information about what is currently running on the server and the performance of those queries.
-
For starters, I would download Glenn Berry's diagnostic scripts to attempt to pinpoint what the specific issues may be.
https://glennsqlperformance.com/resources/
Ola's scripts are fine, I use them. But if you do not know what these specifically do, you can get into trouble with them. For example, the default is to reorganize if the fragmentation is > 30%. Personally, I never re-org. I also do not re-index except in limited cases.
I would suggest updating statistics first on each table in the system.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Glenn Berry's scripts are also another diagnostic tool for this scenario as Michael is suggesting. You will need to know which specific queries to run to find the problem though and there are a lot of queries in there. Queries 42, 43, 48, and 49 in the SQL Server 2017 scripts will cover top queries by CPU, duration, etc. Inside the downloaded .sql file you can search by query number in SSMS.
There is controversy and debate about index maintenance, for sure. Ola's defaults follow Microsoft guidance, which I know is sometimes suspect. I agree with Michael, as a I said earlier, that updating stats may get you what you want without the work involved in the index rebuilds that will happen with the default settings in Ola's job. The script I provided from Ola's website for statistics maintenance is an option that just updates the statistics.
It sounds like you are having, or were having a live production issue of most/all queries being slow. Is that correct? If so, are you still having that problem currently? If yes, Glenn's queries can look at the plan cache and tell you what expensive queries have ran recently. From there, perhops you can do some performance tuning for those queries. The sp_whoisactive tool will provide you similar info for the queries that are running as of the moment the sp_whoisactive procedure was ran. It has a number of parameters to help you dig deeper.
- christi1711 wrote:
We don't have any maintenance on DB. It is a virtual machine. I saw that a lot of indexes take a lot of space:
Everything works very slow. The same queries have different duration. I don't know which way I need to move. Where I can look for a problem...
Before going off and trying to rebuild everything - confirm that these are actually tables and not HEAPS. If there isn't a clustered index defined on the table then you would see 'massive' fragmentation (like this).
Next step would be to identify one or more queries with bad performance. Identify the tables being queried - review the execution plan and see what indexes are utilized for the query. At this point...update the statistics for those indexes and see if that changes the performance. If the performance improves enough then you can focus on setting up a job to update statistics...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
In general you should always review the clustered indexes first, to make sure the single best clus index key(s) was(were) chosen for each table. In particular, that an identity was not just slapped on every table as the default clus key.
And, with fragmentation that extreme, I would rebuild the tables first. It can't hurt. Rebuild from smallest to largest.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
First of all, I saw that we have Amazon RDS for SQL Server.
Regarding script for index maintenance (https://ola.hallengren.com/) it looks like the same as what I want to do: (rebuild or reorganize indexes).
I have a clustered index on each table (for the most part, this is the default id).
Also, I look at the sys.indexes, sys.dm_db_index_usage_stats, sys.dm_db_partition_stats, and found which indexes don't use.
For which period we have such results?
And what means update statistics? Can it block my DB?
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply