asked
Rebuild Index job is causing all memory to be used up.
Hi All Can anyone please provide any suggestions for setting maximum Server Memory? or my task settings to help alleviate the following issues? I have a Rebuild Index task with following settings: All User databases Reorganise pages with default amount of free space. Both sort results in tempdb and keep index online are off. Things begin to go bad about 40 minutes into this task. As it uses up most of the memory, applications can't connect. I've even had one of the databases left in the state where it could not be connected to even from ssms with sa credentials. For example I found this log: MemoryLoad 96% TotalPhysical = 20479MB Available Physical 667MB Total Page File 40957MB My maximum server memory setting was the default value (exceeding the actual server memory) Tasks are run away from any backups at times of low usage. Regards
Thanks for your replies and the resources. I've only got 20GB or ram on the server and have since changed the configuration to use 17GB at most. The recovery mode is simple as we just don't have the space to keep large ldf files. I have to admit that I'm pretty much just re organising all in one that is using the following basic settings: All User databases (3) Reorganise pages with default amount of free space. Sort results in tempdb off and keep index online off. I'm not sure how to make stops? that is split this job up onto stages? The index rebuild appears to make a significant difference. We don't have a reporting database yet and are trying to run reps from the transactional db. I'm doing my rebuilds on Sunday night at times when there's little going on, having said this it is actually possible some data is being added while the index rebuilding is going on. There are 3 separate databases. So I wonder if there's a simple mechanism I could use to stop the loading service just before the index rebuild starts on the corresponding database. And subsequently to re start it when the rebuild finished on the corresponding database?
So just to clarify, are you rebuilding or reorganizing the indexes? Those are two different operations. Reorganizing is always an online operation whereas rebuilding can be either or depending on what edition of SQL Server you are using. Same with sorting. You can't specific to sort in TempDB with reorganizing but you can with rebuilding. Rebuilding is pretty much dropping the index entirely and rebuilding it from scratch. Reorganizing basically fixes the physical ordering of the leaf level pages. A good primer:
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ How are you implementing the process? With a maintenance plan? Sounds like it. What is the sigificant difference on the rebuild? Can you clarify? Can you do the rebuild after the ETL load?
That's correct it's a maintenance plan with a "Rebuild Index Task": All user databases, Reorganize pages with the default amount of free space, Sort results in tempdb off and keep index online off. Here I wonder how this task is actually executed does it attempt to rebuild indexes on those databases in parallel or sequentially? Is it worth configuring a separate task for each database and run them one after another? It's SQL Server 2008 R2 Standard 64bit. What I mean by significant difference is that we are able to run our web app without queries timing out. There is not ETL load. We have a windows services running on separate servers (one per database and there's 3 databases). These services read files as they are uploaded to the servers and write data to the database. As I said the rebuild index task is executed during quaiet time when no date tends to arrive, but I'm not actually stopping those services. So I'm wondering if there's a way for me to run some batch files from the SQL server to execute net stop and net start commands on those services before and after the index rebuilds?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
HI, server memory: right click on your server go to: Server Properties -> Memory: here you tell seql server how much memory to allocate to SQL from OS. Dion't reorganize all ine one, make steps so you can free the ldf. What recovery mode are you in? If in Full or Bulk, do you take log backups?
SQL Server will happily consume all available memory, essentially starving everything else on the box. I would definitely adjust it. What version of SQL Server are you running? If you are looking for guidance on what to set it to, I suggest this article:
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/ If you want a script way,
http://sqlrus.com/2014/03/configuration-validation-max-memory/ Are you just blindly rebuilding indexes? If so, I'd suggest a maintenance plan such as the one from Ola Hallengren.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html. His solution is widely used. Just curious, have you confirmed that the index rebuild is indeed the culprit of the bottleneck? There isn't anything else doing on during that time frame? An ETL load maybe? Hope that helps.
21 People are following this question.
