Solved

SQL Server 2005 disk/memory performance.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Following an example - removing duplicate strings 4 61
Query 14 57
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 66
Loops and updating in SQL Query 9 55
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…

830 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