Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2005 disk/memory performance.

Posted on 2007-04-02
3
Medium Priority
?
625 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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 …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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