Solved

SQL Server 2005 disk/memory performance.

Posted on 2007-04-02
3
612 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
  • 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

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.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now