May 9, 2020 at 12:35 pm
Dear friends:
SQL Server physical machine, critical services, overloaded with several requests, some partitioned tables, although non aligned indexes exist, other indexes appears that are not wiseley choosen, my question is how indexing could help alleviate memory pressure counters or make worst the problem.
Thanks for your help and ideas
May 9, 2020 at 7:25 pm
Indexing could help a lot! Or not at all, all depending on what your memory pressure is due to. It could be that you are scanning a 100 GB table and you have 128 GB of RAM, but with the correct index, you only need to read some 10 MB of that table. Or it could be that you only have 16 GB or RAM and desperately need to upgrade your hardware. And there are plentiful of other options as well.
That is, with the minimal amount of information you have provided, it is impossible to say anything useful at all.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 11, 2020 at 5:00 pm
To add to Erland Sommarskog's reply, adding indexes could make things worse too. Not likely to make things worse in terms of memory pressure, but increased disk I/O, larger databases, larger backups, slower INSERT/UPDATE/DELETE and in some cases SELECT performance.
The other thing is creating an index requires memory so while the index is being created, you may end up with extra memory pressure.
Jumping back in the process, what is the exact problem are you trying to solve? Jumping from the initial problem (memory pressure) to solution (adding indexes) my be the wrong solution to the problem. As Erland said, if your server has very little memory to begin with, adding indexes likely won't make that big of a difference. On the other hand, if your server has 128 GB of RAM and you have 3 SQL instances installed on the server, all with max memory of 2 PB, you are going to have a bad time. If the server has 128 GB and you have your SQL instances (one or more) configured to use 128 GB total, you are going to have a bad time. The OS needs some memory too. If SSIS and/or SSRS is on that instance, they need additional memory. Every additional thing installed on the server (antivirus, monitoring tools, users (I really hope no users are on that system), etc) will need its own memory.
And if this is a VM, it could be misconfigured. You could have the VM host software sharing memory or shuffling memory around. memory ballooning on a VM is a potential misconfiguration for a VM hosting SQL Server.
There are a lot of unknowns here (again as Erland pointed out) so it is all guesswork on our side.
But to answer your questions - indexing could help memory pressure or make no difference and during the index creation, it may cause more memory pressure.
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply