Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
washaq
Starting Member
2 Posts |
 Posted - 2014-01-10 : 04:15:00
|
| Hi,i have a problem that sql server working so slow,we have only one server, both ERP software and SQL working on this device, and most of the clients use this ERP software via RDP and very few use on local, but RDP users complain about Slow response time, because of this we renew our Network Infrastructure, nothing changed, for RDP clients, so we think it is SQL makes them suffer, is there anything that you can suggest us??thanks in advance |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-01-10 : 05:52:29
|
If you really need to run SQL with other software on the same box it is a good idea to restrict the amount of memory sql can use otherwise it will try and grab it all.eg If your box has 16GBytes of memory you may want to restrict SQL to 8GBytes with:EXEC sp_configure 'show advanced options', 1;GORECONFIGURE;GOEXEC sp_configure 'max server memory', 8192;GORECONFIGURE;GO |
 |
|
|
washaq
Starting Member
2 Posts |
Posted - 2014-01-10 : 08:08:23
|
| Thanks for reply,another question; does restricting memory has any bad effects on sql, i mean will sql work flawless?, and some says licensing type effect performance of SQL, as far as i know there are 2 types of licensing; CAL and CORE, mine is CAL licence about 100, but only 30 in use, but they say if you use CORE licence SQL work-speed will increase, i didnt believe this, isn't it pointless??where will i run this code in SQL or in PowerShell?if i'm annoying please forgive me, i'm trying to learn ;)Thank you very much. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-01-10 : 08:48:05
|
| The code I posted should be run from SQL Server Management Studio. It is just an exampe as you will need to work out the relative amounts of memory each app requires. If you do not feel confident doing this yourself, your suppliers should be able to help. For proof of concept you could start by assigning 70% of the server memory to SQL.I have never really understood, and have never had a need to understand, Microsoft licencing. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-01-13 : 16:09:46
|
| I suggest trying to narrow the issue down and identify the bottle-neck.Try to find out a query or stored proc call that is associated to where the users are complaining. You may need to run SQL Profiler, ensure you are finding the most time-consuming call.Try running this call on the Server directly and via the RDP. I assume the ERP is a separate Database or Server Software. If that is true, when the ERP RDP people are complaining, what is SQL Server doing? If you need a query to check, let me know. If the ERP doesn't not run on SQL server, runs fine without RDP and runs slowly with RDP, doesn't that eliminate SQL Server from the equation? There could be a few different parts that could be causing the performance issues: SQL Server itself, poor SQL query, Server Application (including the ERP software), Client application, Network (they claim the Network is fix but is that true). |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-15 : 04:33:30
|
| Guys: Isn't there a way of detecting if the server is trashing memory - using PerfMon perhaps? Then O/P can restrict SQL's Memory Land Grab until server stops trashing - would that work?Once the Memory Allocation is sorted then any performance is probably down to SQL Tuning - SQL Profiler could be used to fine "Slow Running Queries" (although beware of queries that run incredibly often, and don't take "very long" but tuning them to "even faster" will have a dramatic effect of course, because collectively they use a lot of CPU/resources). |
|
|
|
|
|
|
|
|
Sql server working very slow