[NEED ADVICE] SQL Server 2005 - Page File Size

Hello,

System Information:
------------------------
We've a SQL Server 2005 with SP4 running on Server 2003 R2 x64 Standard Edition with SP2. All Software is Up to Date. The server is Dell PowerEdge 1950 with 2 Processors (4 cores each) with 73GB of primary SAS and 500GB of NAS Storage Array (Connected via SCSI) and contains 16GB of RAM. The SQL Server 2005 is a 64-bit Standard edition.

The number of concurrent connections, users are same from past couple of years and nothing changed in terms of either software, hardware and users.

Problem:
----------
From past few weeks, the Memory usage is goes to 100% as per Performance monitor. I verified the pagefile size and it is set to 2GB. I heard that I need to set pagefile size to minimum of 1.5 times RAM.

I already specified maximum memory usage in SQL Server properties to 12GB a long time back. I even tried to increase and decrease it, but the result is same!

Since this server is running on Server 2003 Standard edition, it only supports 32GB of RAM. I already ordered another 16GB modules from Dell.

Question:
-----------

1.) [250 Points] What is the recommended pagefile size for this kind of environment? Do I need to specify 24GB (1.5 x 16GB RAM) of pagefile?
2.) [250 Points] How to determine the SQL Server Performance Degradation? i.e., RAM is sufficient or not?

Any further help would be greatly appreciated. Thank you.
SrinathSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
That is a pretty small database for that amount of RAM.  My first guess is that there needs to be some tuning done on the SQL server.  Check to be sure your statistics are being updated.
Check for missing indexes (See this article) http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

If you have a bad query, then more ram isn't going to help much.

0
SrinathSAuthor Commented:
I spoke with my our application (database) provider. They already configured SQL Server maintenance plan which index the database daily in addition to regular backup.

C:\ --> SQL Server 2005 + Windows Server 2003 OS
D:\ --> SQL Server Database + Backups

I regularly defragment both the hard drives. Nothing helps!
0
millardjkCommented:
You definitely want to increase the page file for the system; you might not need 1.5x, but you definitely want to get above 2GB on a 16GB box. I would suggest 1x (16GB), and you want it on a volume that isn't being used by the SQL instance.

For the memory issues, you need to drill further into the problem before throwing more RAM at the box. You indicate that perfmon shows high utilization, but you don't indicate that you have been able isolate sqlsrvr as the culprit: it's quite possible for other system processes to have a problem, including sqlagent, antivirus, etc. Whether you use perfmon or some other tool (like Sysinternals Process Explorer), that would be my first tactic.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SrinathSAuthor Commented:
I used the tool Sysinternals Process Explorer.

I created a sample trace for 2-3 minutes in SQL Server Profiler which creates 4MB trace file (with Tunning template.) And then I ran the Database Tunning Advisor tool and it gave me stunning result!

It shows that the database performance can be improved by 73% and it also shows that 9% incorrect syntax usage.

After that I created a test maintenance plan with "Database Integrity Check", "Reorganize", "Rebuild Index" and Cleanup.

In addition to that our application provider provided us a custom script which regularly reindex the database on every day. The script is given below:

USE TOPSDATA

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
GO
EXEC spx_reindexalltables 90
GO
EXEC spx_setalltablekey
GO

However in SQL Server 2005 Properties, the reindex tables compatibility is set to: 80
0
millardjkCommented:
Comparability mode indicates the DB was originally intended for SQL2000; you'll want to check with your vendor to see if that's still appropriate; as long as none of the app's calls are using 2000-specific constructs or expectations, you can safely change the mode without restarting the instance.

Reindexing is a great idea, but you don't want to simply reindex everything: with SQL Standard, it won't do them online, so tables & indexes will be locked while the system goes through and recreates indexes. That could be perceived as a huge performance hit.

Use the dynamic management views to find the indexes with the biggest fragmentation problems and manually recreate them, one at a time.

Finally: were you able to confirm that SQL is using all the RAM, or is another process contributing to the problem?

Finally, the DTA may have some great recomendations, but you'll want to check with your app vendor before implementing any of them: you may be changing the schema in ways that could cause unintended consequences.
0
SrinathSAuthor Commented:
I just spoke with my application vendor. They recommended to set the comparability level to 80. Their Level 3 tech is going to call me back later today.

Use the dynamic management views to find the indexes with the biggest fragmentation problems and manually recreate them, one at a time.
- How can I do that? Any references?

Finally: were you able to confirm that SQL is using all the RAM, or is another process contributing to the problem?
- I increased page file from 2GB to (16 min, 20max) and specified 14GB of RAM for SQL Server in properties. The SQL Server Process starts taking RAM at 1GB and ends at 14GB. Other system processes are taking less than 1GB RAM.

Both the page file and actual database is on different 15K RPM drives. I'm able to quickly defragment both drives on last night and configured to defragment on every day after hours.

Finally, the DTA may have some great recomendations, but you'll want to check with your app vendor before implementing any of them: you may be changing the schema in ways that could cause unintended consequences.
- Thanks for your advice. I won't implement those recommendations until my vendor asks.
0
millardjkCommented:
SQL will take memory for its own uses--up to the maximum you set--and typically not return it. All it takes is a single high-resource operation to get that to happen; after that, the memory it "owns" will be used to cache plans, record sets, indexes, etc. as long as nothing else is happening on that system, it's not a problem.

Always reserving 2GB for the system is a good practice.

As for the DMVs: see http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx

It may be a bit dated, but is totally appropriate when applied to a SQL2005 instance hosting a SQL2000 database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.