Solved

Using perfmon to monitor memory bottlenecks

Posted on 2013-01-25
6
456 Views
Last Modified: 2013-05-02
We have a Windows 2003 SBS server with 3 GB of memory.  Users have been complaining that running the backup on their large SQL database has been slow, as their program also uses an extensive documentation folder that contains 29k PDFs (3.0 GB of docs) and has to compress and back these up as well into a zip file.  

The vendor told us that since we recently reduced our memory from 4 GB to 3 GB, backups are going to take much longer (users are reporting it as three times as long).  Is there a way to use performance monitor to verify this shortage in memory?  If so, what counters should I look at?
0
Comment
Question by:epichero22
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 38820303
In my opinion if SQL backups take longer you need to look at IO not just memeory. It's easy to check in task manager how much memory SQL Server is using and I suggest chanage SQL MAX memory so it won't take all that it can from that box. My guess is the ZIP takes most of the memory and SQL will consume most of the IO during backup.


More details below:

Optimizing Server Performance Using Memory Configuration Options: http://msdn.microsoft.com/en-us/library/ms177455.aspx

How to determine proper SQL Server configuration settings: http://support.microsoft.com/kb/319942/EN-US/

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup as follow:

• If you have 3-4 GB memory, include the /3GB switch in the startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

• When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

• The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38820395
Hi,

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Now here is the great part - if you follow all the above, at the bottom Brent makes this outstanding offer, of taking a 5minute or so look at your results and giving a gut feel type recommendation. As Brent is a MVP and Certified Master this is a great offer!

HTH
  David

PS Do the monitoring - too easy to just jump to a wrong conclusion as everything is inter-related.

PS2 What is the issue with the backups taking 3 times longer - is it moving outside agreed maintenance window? Is backup a user initiated activity? If so, why isn't it automated via a schedule and run overnight?
0
 
LVL 11

Author Comment

by:epichero22
ID: 38820529
The SQL server backup is taking the normal amount of time.  It's the documentation that's taking forever.  Does that change your answers?


Also, to answer the questions, backup is a user initiated activity and the vendor doesn't allow this to be part of an automated task.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Expert Comment

by:David Todd
ID: 38820592
Hi,

Changes my answers a little.

Is the documentation folder inside or outside the database? Is it using the filestream function of sql at all?

Do run the perfmon counters and post the top 7 or 8 header rows from excel. They may need some tuning to pick up on other things.

SBS - what else is running on the server? SBS isn't the most proformant of platforms. Is it time that SQL was broken out into its own server and away from exchange and the fileserver and the domain controller.

If it is the filesystem backup that is taking the time, when was the last time the disk was defragmented? How full are the disks? Performance drops off rapidly as the disk goes over 80% used. (Just like you don't fill your coffee cup to the brim, treat 80% full as at capacity. In someways the burns from sloshing an overfull coffee cup can be less painful than overfull hdds.

No idea why the vendor wont allow automated/scheduled backups - this is the complete opposite to most disaster recover thinking.

Just to go back a couple of steps - memory is generally one of the least expensive parts of the server - why was 1GB removed? In this day and age of 64bit OS's, there is generally a performance increase for memory hungry services like SQL and Exchange in increasing from 4 - 8GB of ram.

HTH
  David
0
 
LVL 11

Accepted Solution

by:
epichero22 earned 0 total points
ID: 38886001
Hi Everyone,


I actually ran a backup on the software and was watching Windows task manager, and it turns out that the available memory was staying put around 1.5 gigabytes.  It was actually the processor that was being worked the hardest (they have an Opteron 1210 dinosaur).  I can't really answer the detailed questions that were asked regarding the SQL functions since I'm not an engineer for it.  But I think I discovered that the CPU is what's slowing down the backup process.

I did speak with the vendor and discovered a work-around, so thanks for everyone's help.
0
 
LVL 11

Author Closing Comment

by:epichero22
ID: 39131088
Thanks everyone :)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL - How to declare table name 26 29
HP Printer on Windows 2003 Terminal Server 4 33
Update a text value in another table 10 39
Sql Query 6 64
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question