SQL Server using 15GB of Virtual Memory

adminknight
adminknight used Ask the Experts™
on
3rd party app running on SQL 2005, 2003 Ent server on R610 hardware, 48GB of ram, 4 sep disk RAID volumes. The SQL server process is using 15GB of virtual memory. 3rd party company says this is normal but the app is terrible slow and I think this has something to do with it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President
Top Expert 2010
Commented:
You're going to have to do some homework first and give us at least something to go on.   Run performance monitor and find the bottlenecks.  (perfmon from the DOS prompt)

Here are the biggies for SQL Server. Hopefully something will stand out that can be commented on further..

Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.

Network Interface — Bytes Total/sec: To see how much network activity is going on.

PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.

PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.

System — % Total Processor Time: To see how busy all the CPUs are as a whole.

System — Processor Queue Length: Also see how busy the CPUs are.

SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.

SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.

SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.

SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.

SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.
Distinguished Expert 2017

Commented:
How much of the memory resources are you allowing for SQL ?
The answer to the well thought out questions by delthe will answer whether the resources are properly allocated.
What else does the server do?
Web, exchange, terminal services, etc.?

Author

Commented:
No other roles what so ever,

Will do what Dlethe suggested tomorrow and see what I can find out.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

DavidPresident
Top Expert 2010

Commented:
Note, I foolishly didn't cite the source of my post above in #33109153.  This is from the http://www.sql-server-performance.com site, that has a great deal of tuning information. Give credit to them, not to me.   Really, you could just go to that site and spend hours going over proper tuning procedures.  I highly recommend just spending time on one of the tutorials.

Author

Commented:
Memory — Pages/Sec: 0.000

Network Interface — Bytes Total/sec: Averages between 5KB and 50KB/sec

PhysicalDisk — % Disk Time — _Total: stays under or around 1%

PhysicalDisk — Current Disk Queue Length — _Total: stays at 0

System — % Total Processor Time: Averages around 5%

System — Processor Queue Length: Stays at 0

SQLServer: General Statistics — User Connections: Around 30

SQLServer: Access Methods — Page Splits/sec: Stays around 0 but jumps to 8 or so on occasion

SQLServer: Buffer Manager — Buffer Cache Hit Ratio: Stays around 99+

SQLServer: Memory Manager — SQLServer: In the ball park of 40GB

SQLServer: Memory Manager — Total Server Memory (KB): In the ball park of 14GB
DavidPresident
Top Expert 2010

Commented:
The system looks pretty idle, so do you have a network problem?  Drill into the network controller & look for problems.

Author

Commented:
The application speed is the same on the server as it is on the client. I ran my speed tests, and using the application on the server it self to leave the network out of the issue.

Distinguished Expert 2017
Commented:
What is the configuration of the server? Check the properties of my computer\advanced\performance settings\advanced\ make sure that the processor scheduling and memory usage is set to programs rather than background and system cache..
This will configure the server as an application server versus as a file share server.
The other thing, check the properties of the active LAN connection.
within the listed protocols, get the properties of the file and printer sharing for microsoft windows and make sure that the settings there reflect, "maximize data throughput for network application"

The last thing, using SSMS login into the sql server.  Get the properties of the sql server (top of the higherarchy.) go to processor and make sure that boost SQL priority is checked.

Which version of SQL 2005 are you using? Is your system X64?  If the option is availalbe you may want to consider configuring AWE if not already configured.  This should improve the performance of your system/responsiveness.

How big is the database?

Author

Commented:
64bit OS yes
As for SQL:
Microsoft SQL Server Management Studio                                    9.00.4035.00
Microsoft Analysis Services Client Tools                                    2005.090.4035.00
Microsoft Data Access Components (MDAC)                                    2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML                                    2.6 3.0 6.0
Microsoft Internet Explorer                                    8.0.6001.18702
Microsoft .NET Framework                                    2.0.50727.3607
Operating System                                    5.2.3790
DB is about 3.5GB
Distinguished Expert 2017

Commented:
Do you have auto-shrink on under the properties/options of the database?

I think someone has suggested that you run the  server profiler/engine tuner to see whether adding an index may speed up performance.

The issue you are dealing with is subjective such that it is nearly impossible to determine whether it is really slow or you have complex/unindexed queries.

You can with SSMS get an execution plan for a query to see whether that is the cause for the slwo responses.

Author

Commented:
Auto shrink isn't turned on
Where can I get more info on running the Server Profiler/engine tuner and adding an index?
Top Expert 2012

Commented:
Unless you set a maximum memory to be used SQL Server will attempt to use all it supports for that edition.

The real problem is that you have a query or queries that are taking a long time.  Your first task is to identify the queries that are slow.
Distinguished Expert 2017

Commented:
start\programs\sql server 2005\ has both.  You first have to use the profiler to record a snippet of transactions.  Then you use the captured data as input to the engine tunner.

The SQL books online should have this information as well as other suggestions on tunning your sql server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial