Hi experts,
i have been using the forum forever and has been very helpful all the time. This is the first time i am posting though. since mid last week we seem to get terrible SQL performance for some reason and i haven’t been able to figure out what caused it. An updatable BAQ/Dashboard we have been using since go live that pulled data in about two minutes now taking about 30 minutes.
Some background on the issue:
On the host server two VMs were created by consultants. ERP (48 Gb RAM 8 processor) and SQL(48 Gb RAM 8 processor). After watching resources for about a year I changed to ERP (32 Gb RAM 8 processor) and SQL(72 Gb RAM 8 processor). SQL server - windows 2016 and SQL server 2016. Everything running fine until last thursday when users started complaining about slow epicor. i checked sql server, it was consuming most of the memory. i thought i will do app recycle or restart SQLSERVER service off hours but sql crashed in the afternoon. Since then sql is super slow. MRP is taking 2.5 hours, before it was 20-30 minutes. SQL server resource utilization seems ok. Enterprise search index rebuild is taking over an hour everyday since then, before it was like 16 minutes.
i have been pulling my hair for about a week and still havent figured out what would be causing it and how to fix it.
What do you mean SQL crashed?
You can start by running sp_blitz at https://www.brentozar.com/blitz/ to make sure your server is configured correctly. Brent Ozar has tons of other tools and hundreds of videos on Youtube also.
Then you will want to check your scheduled index rebuild. The popular one on here is Index Optimize at: SQL Server Index and Statistics Maintenance
Lastly, if you are near a big city and want to learn more about MSSQL then PASS has really big SQL meetups. https://www.sqlsaturday.com/Events.aspx You can find a lot of people there who work with MSSQL all day everyday and they can be very helpful with any problems or questions.
SQLSERVER service stopped. later i checked the logs and turned out be security update installed for SQL which killed the SQL service. KB4532097. I dont know if anyone else who installed the update having any issues
thanks for the resources you mentioned. I will take a look.
That makes sense. MSSQL should never crash. Ever. If it does then I would report it to Microsoft because that is a really big problem
I would disable any auto updates and make sure you schedule downtime at least once a month to update the servers and restart them.