?
Solved

SQL Server 2005 disk/memory performance.

Posted on 2007-04-02
3
Medium Priority
?
622 Views
Last Modified: 2012-08-14
SQL Server 2005 disk/memory performance.

OS: Windows 2003 Server (32)
Memory installed: 3,6 GB
Pagefile location: C:
Pagefile minimum: 2 GB
Pagefile maximum: 4 GB
SQL Databasefiles on seperate partition and seperate physical array than drive C:
SQL logs on seperate partition but same physical array as Database files

Memory status: 1.4 GB free physical memory
Pagefile usage: 2 GB

SQL Server memory:
AWE: no
Minimum mem = 0
Maximum mem = 2147483647
Index memory = 0
Minimum memory pr. query: 2048

The Issue:
Some queries makes disk activity peak. Using perfmon I can see that the heavy disk activity is on drive C: for 10 seconds. I think i could be the pagefile og tempdb that is working heavily.

How do I monitor the pagefile activity to be sure that it's the bottleneck?

How to set up the SQL server 2005 memory handling properly to avoid pagefile activity hven doing heavy queries. It's a SQL Server 2005 standard edition.
0
Comment
Question by:TANGLAD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18839601
complex queries can "store" intermediate results in Tempdb so u are probably correct in assuming the 10 second activity is a write to tempdb rather than the pagefile.

one "quick" way to "prove" this is to move tempdb onto a different sector to see if that sector gets more hits!!
/Richard
0
 
LVL 1

Author Comment

by:TANGLAD
ID: 18848564
Moved tempdb and YES that is the one causing heavy disk-activity.

How can I avoid that. Is tempdb and installed memory related? I mean will more memory avoid disk access to tempdb.
0
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 1000 total points
ID: 18849155
the *only* way to avoid tempdb being loaded is to examine the query that is running and see if there are ways to limit the paging out to tempdb.

u can icrease the amount of memory to avoid some disk actiovity; the relationship between memory and disk write to tempdb is a bit of a dark art; there are other variables that SQL will take into account before deciding wether to write out to disk or not.

/Richard
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 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