SQL Server Memory Usage - Is this normal?

Hi,

I have noticed over the past couple of weeks that the performance of my SQL
Server has been going down significantly. My database is around 7 or 8 GB,
with a Transaction Log of about 500 MB. Over the past few weeks, running
reports off of the data has been taking longer and longer, and now I have had
to wait several minutes for reports to even start loading. (I am using
Crystal Reports). The server itself has 2 GB of RAM on it, and if I look in
the "Processes" tab in the Task Manager, it is showing that the sqlserver.exe
Process alone is using up 1.4 GB of Memory.

Is this normal? Would increasing the RAM make a difference in the
performance? Would using a Fixed Memory Size make things better or worse?
(The memory is currently being set dynamically).

Any help would be greatly appreciated.

SQL Server memory usage increases up to the size set either dynamically or
configured to a specific number and does not come down from there until the
SQL Server service is restarted.

Does performance improve after a reboot/restart of SQL Server?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

Are you regularly updating statistics on your tables?
What kind of activity occurs in this db? Lots of inserts/deletes?

Could be that your queries are executing off of an old query execution plan,
you want to update stats regularly since the query optimizer uses these to
determine
the most efficient method executing the sql.

T

By default SQL server takes all the memory available unless you have not
allocated amount of memory to the SQL Server.

In addition to the other posts, here's some reading about SQL Server memory usage:

INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363

http://www.mssqlserver.com/faq/troub...memoryleak.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

No - After rebooting it is just as slow as it was before, and the
sqlserver.exe is still using 1.4 GB of memory.

Most of the activity is just retrieving data through Crystal Reports.
Relatively small amounts of data are imported on a weekly basis (usually no
more than 100,000 records), and larger amounts are imported monthly (usually
around 500-600,000 records), but other than that, updates and deletes are not
common.

I checked my maintenance plans and saw that I actually was not updating
statistics. I'm in the process of doing that now, I'll see if that helps.

Thanks for the info

I just ran an update statistics using the DB Maintenance Wizard and sampling
10% (the default) but it didn't appear to make any difference in the
performance. Would increasing the sample size make a difference?

Possibly. That might make the statistics reflect reality more accurate. If you can repro the old
execution plan (or of you saved it, I'd compare the execution plans to see what the difference might
be...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode test 33Switch to Hybrid Mode Switch to Threaded Mode

3 17th August 15:45 dfspfd! External User Posts: 1	SQL Server Memory Usage - Is this normal? Are you regularly updating statistics on your tables? What kind of activity occurs in this db? Lots of inserts/deletes? Could be that your queries are executing off of an old query execution plan, you want to update stats regularly since the query optimizer uses these to determine the most efficient method executing the sql. T

4 17th August 15:45 javed iqbal via sqlmonster.com External User Posts: 1	SQL Server Memory Usage - Is this normal? By default SQL server takes all the memory available unless you have not allocated amount of memory to the SQL Server.

5 17th August 19:53 tibor karaszi External User Posts: 1	SQL Server Memory Usage - Is this normal? In addition to the other posts, here's some reading about SQL Server memory usage: INF: SQL Server Memory Usage http://support.microsoft.com/default...;en-us;q321363 http://www.mssqlserver.com/faq/troub...memoryleak.asp -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/

6 18th August 00:46 stujÛŠÇ.²È¨žÉ¢rº,¡û\¢ External User Posts: 1	SQL Server Memory Usage - Is this normal? No - After rebooting it is just as slow as it was before, and the sqlserver.exe is still using 1.4 GB of memory.

8 18th August 00:46 stujÛŠÇ.²È¨žÉ¢rº,¡û\¢ External User Posts: 1	SQL Server Memory Usage - Is this normal? I just ran an update statistics using the DB Maintenance Wizard and sampling 10% (the default) but it didn't appear to make any difference in the performance. Would increasing the sample size make a difference?

9 18th August 01:53 tibor karaszi External User Posts: 1	SQL Server Memory Usage - Is this normal? Possibly. That might make the statistics reflect reality more accurate. If you can repro the old execution plan (or of you saved it, I'd compare the execution plans to see what the difference might be... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/