Solved

SQL Server 2005 disk/memory performance.

Posted on 2007-04-02
3
621 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 250 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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