Estimated members, I had a SQL Server 2008 R2 performing poorly with out SharePoint; I did some minor changes (e.g. stopping a weekly maintenance of statistics rebuild statistics, also a rebuild of tempdb and other non-sense of jobs.) the SQL Server performed perfect for about 4 or more weeks. I had more changes or improvements to accomplish but priorities did not give me the time needed. Now I'm hearing that suddenly performance is an issue because a user introduced the use of AutoCAD in the entire mixed.
I am convinced that it is AutoCAD only I don't know where or how to look to fixed this problem.
This is a physical server with 64GB of RAM and I've proven for 2 days that CPU remains idle at 95%,, my queries indicate that disk I/O is also a non-issue. I basically ask for any help or idea on where to look for or how to look for a simple solution. As I said I have more changes to implement but priorities prevent me to do soo even though these changes are 'not' as significant as the first ones I made.
Thanks so much in advance for any help or ideas you can provide..
Other DBAs are sure the problem is RAM maxim out and having to bounce the server weekly, I do not believe that is a permanent solution.
Is it Standard Edition of SQL Server or Enterprise Edition? If Std Edition, the most it can use is 4GB RAM, no matter how much ram the server has, so RAM could in that case very definitely be an issue.
Scott - are you sure that Standard Edition can only use 4GB of RAM?
Now if you meant to ask whether or not the system is 32 or 64 bit - and that 32-bit can only use 4GB then you are partially correct. A 32-bit OS and SQL Server would have to be setup to use AWE memory and PAE for the OS.
Based on what I am seeing from the OP - those 'minor' and 'nonsense' operations that he stopped would be the major issues. If I am reading this correctly he is no longer updating statistics even on a weekly basis and probably not rebuilding indexes if needed (no idea what rebuilding tempdb would even look like).
Now - if the system is x64 and SQL Server is running under local system or a domain account that is a part of the local administrators group then it - by default - will be using locked pages in memory. If that is true - then SQL Server will take every bit of memory available on the system until it starves the OS and other applications...unless you set max memory for SQL Server and leave enough memory for the OS and other applications.
If someone recently installed AutoCAD on this system - and AutoCAD is using a separate instance of SQL Server (which it probably is) and all instances of SQL Server do not have a max memory set - then I could definitely see that these separate instances would conflict and cause issues for performance.
There appears to be a lot more going on that a single instance SQL Server having performance issues.
Sorry, no 4GB limit for Standard. We have almost all Enterprise -- multiple instances on same box -- so I'm not as familiar as I should be with standard memory limits.