0

We are using Windows Server 2008 R2 and SQL Server 2012 and sqlservr.exe is always low priority and after we change it to high priority. But immediately it goes back to low priority. I have select Boost SQL Server Priority checked, but the problem still exists.

Is there anyway we can make it high priority for sqlservr.exe?

Boost SQL Server Priority

Dale K
  • 25,246
  • 15
  • 42
  • 71
BeginnerNewbie
  • 1
  • 2
    What are you trying to solve for? Is your server not serving adequately? Also, 2008R2 and all 2012 before SP3 are end of life. See https://tcscs.co.uk/table/microsoft-operating-system-end-life-3/ – zedfoxus Jun 06 '20 at 03:18
  • currently sql server is very slow and not utilizing fully of RAM and CPU resource – BeginnerNewbie Jun 06 '20 at 03:39
  • Are you using SQL Server heavily? If you have heavy load, SQL Server will use the RAM and CPU it needs. If it doesn't need resources, it won't use it. Even busy servers that are running well tuned queries will not ending up using CPU...and that's OK. Is your server performing reasonably well? – zedfoxus Jun 06 '20 at 03:42
  • i am deleting 150 million records. but still sql server is not using resources fully. – BeginnerNewbie Jun 06 '20 at 03:51
  • Is your delete working or is the delete query taking forever? – zedfoxus Jun 06 '20 at 03:52
  • deleting is working but taking longer. now not even half of RAM, sql server is using. I am hoping use 80% of RAM and hopefully sql is faster. – BeginnerNewbie Jun 06 '20 at 03:55
  • It probably doesn't need to shove all that data into the RAM before deleting. Delete is going to be a heavy IO operation more than a heavy memory operation, I'd tend to believe. If you try breaking up your deletes in chunks of 1MM, does deletion of 150 chunks go faster? – zedfoxus Jun 06 '20 at 03:58
  • Also, try using this guide from Brent Ozar and check your memory size settings: https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/ – zedfoxus Jun 06 '20 at 04:00
  • Memory section- Maximum Server Memory already set maximum size. i am deleting in chunks of 500k record. – BeginnerNewbie Jun 06 '20 at 04:10
  • Great. 150MM record deletion will take a bit of time, I am sure. You can experiment with 100 chunks of 50K records (5MM total deletes) vs. 50 chunks of 100K records vs. 5 chunks of 1MM records vs. 10 chunks of 500K records. Do share what you found was the sweet spot. If all of them take roughly the same time, you won't see much gain in splitting anymore. Just go with 150 chunks of 1MM records in that case. – zedfoxus Jun 06 '20 at 04:15
  • 1
    https://stackoverflow.com/questions/24785439/deleting-1-millions-rows-in-sql-server gives some good ideas on deletion of records by chunk. – zedfoxus Jun 06 '20 at 04:16
  • 3
    You need to restart the service after setting this option for it to take effect but **this option is deprecated and warned against**. It is not the solution for your query performance issues anyway. If you need help optimising a `DELETE` you should ask a question about *that*. Bottle neck is likely to be transaction log not CPU or memory – Martin Smith Jun 06 '20 at 09:25

0 Answers0