April 19, 2023 at 3:20 pm
I have Index Maintenance Job
I have stand alone secondary server when I run on secondary server it takes only 10 minutes to complete
When I run in Production it takes 2 hours to finish
I copied the same DB to secondary stand alone server and still it takes 2 hours to complete but server has same windows configuration.
Any suggestion how to track this?
April 19, 2023 at 4:54 pm
Index maintenance can only work on databases which are online.
What technology are you using to keep primary and secondary syncing? AOAG, Mirroring, Log Shipping, Replication?
If AOAG, mirroring, log shipping the DB is never actually online so index maintenance will be skipped for those databases.
April 19, 2023 at 5:02 pm
Its just a stand alone server which we do backup every night and restore every night. No Log shipping or mirroring or Always on.
I hope that answer your question
April 19, 2023 at 5:55 pm
Just so I understand the issue:
LIVE system - 2 hours to do index maintenance
TEST system - 10 minutes using the backup of live that is restored nightly
TEST system - 2 hours if restored from live
My GUESS as to why it takes 10 minutes to complete is that index maintenance is happening as part of the nightly restore. If the indexes are already in a good state, reorg/rebuild of them will happen very quickly.
You could verify this by checking the fragmentation on the indexes on the test system first thing in the morning when you get in.
Now another thing you MAY want to look at is if you really need all the indexes you have AND that they are set up appropriately. You MIGHT have it set up properly (I am not trying to second guess your DBA skills), but 2 hours to do index maintenance sounds like a long time. Sounds like either a lot of indexes or a lot of fragmentation and you may benefit from reducing that footprint.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 19, 2023 at 5:56 pm
And what solution do you use for index maintenance?
For me I would be using Ola’s solution and ensuring the LogToTable parameter is set to Y so I could track the indexes and databases being maintained.
It sure does sound like something is being skipped on the secondary.
Additionally is this a DR box and is a nightly restore so 24 hours out of date data sufficient enough to meet your RTO and RPO requirements?
April 19, 2023 at 7:30 pm
Got it thanks I will do more research on it
It has 205 Indexes as I said when I do this in Prod it takes 2 hours to reorg and rebuild depends upon fragmentation. I did backup before Index job so both copy are identical and when I restore on Test server it finishes in 10 minutes.
In production we have maintenance window so no application is running.
So I am not sure why it finishes early in TEST and why it is taking long in PROD.
April 19, 2023 at 7:49 pm
Are you doing a reorg of all indexes followed by a rebuild of all indexes? I REALLY hope not as that is redundant.
But reasons it could be slow that come to mind (there are more, just these are things that are easy to test/check) are:
1 - live system is still in use and there is blocking or other operations happening causing it to slow down
2 - live system is on slower disk
3 - live system has less free resources available (CPU/RAM/disk)
4 - live system index maintenance is configured to run across ALL databases and live has more databases than test
5 - some system process is causing slowdowns (antivirus for example)
My advice, if you are using maintenance plans for this, stop. They are buggy and not very configurable. If this is a home-grown script, verify the script is IDENTICAL on test and live. If it is Ola's scrip, verify it is identical on test and live. Also, make sure the parameters are 100% identical on the 2 systems. Basically, you want to verify that you are doing a 1:1 comparison. If, for example, on LIVE you are rebuilding and reorganizing all indexes once fragmentation is greater than 1% but on test you only rebuild if it is less than 15% and only reorg if it is greater than 20%, those will greatly impact the times as on live you are doing 2 operations on (likely) ALL indexes while on test you are being selective.
Also saying that you have "maintenance window so no application is running" doesn't ALWAYS mean that people are out of the system. I know at my workplace we have scheduled downtime and I will find stragglers in the system. USUALLY it is because they forgot to close the app before the maintenance started, but sometimes they just didn't read the email or they thought that their tool would be fine to use during a maintenance window because it was read only (SSRS or Excel for example).
My advice is to never assume anything. If SOMETHING could be causing a problem, verify it before ruling it out. SAYING you have a maintenance window is a VERY different thing than VERIFYING you have no users in the system.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 20, 2023 at 6:09 pm
Is there a reason you are reindexing? What is the reason?
Are you using online reindexing?
Have you considered the difference in activity on one vs. the other server?
What about configuration? What are the processors/disks/memory on the two servers?
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply