Databases
StackOverflow2013
Postsdbo
POSQL Server Performance Problem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Performance Problem
    primarykey
    data
    text
    <p>Our primary database server is an 8 core box with 8GB of RAM. The CPU is a Xeon E7330 @ 2.4GHz. It runs Windows Server 2003 R2 (x64 edition) and SQL Server 2005</p> <p>I wanted to do some testing so I set up SQL Server 2005 on another brand-new server which is an 8 core box with 4 GB of RAM. It has a Xeon X5460 @ 3.16GHz and runs Windows Server 2003 R2 Standard. I Installed SQL Server 2005 out of the box and restored a backup of the primary database on to it, and did an UPDATE STATISTICS on all the tables.</p> <p>The process I was testing executes the same stored proc many times. I was astounded to find from the profiler that this proc which executes with duration=0 or 1 on the primary server, was consistently executing with durations in excess of 130. This essentially makes the secondary server useless for testing, because it's just too slow.</p> <p>No other apps run on either of these two boxes, just SQL server. And unlike the primary database server, the test server only had me accessing it.</p> <p>I can't believe the difference in spec between these two machines explains this colossal difference in performance. Can anybody suggest any settings I may need to change? </p> <p>Updates in answers to questions:</p> <ol> <li>Second server is 32 bit Windows</li> <li>I'm inquiring now about the disk arrays and how comparable they are</li> <li>On the primary server, the data and logs are on the same drive (!) and it works fine</li> <li><p>Looking in task manager on the test server, the CPU is running at like 10%, only one core even showing activity</p></li> <li><p>Task manager on the test server (4GB RAM) shows "PF Usage 2.01GB" with SQL Server running. On the primary server (8GB RAM) it shows "PF Usage 6.67GB". How would I make SQL Server on the test box use more of the RAM? Maybe that would make a difference</p></li> </ol> <p>Another update:</p> <p>The primary server has a RAID-5 with 15,000 RPM drives. The test box has a RAID-5 with 10,000 RPM drives.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrc1
    1. USrc1
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Performance Problem
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow do the disk systems compare? RAID-?? Number of spindles? How about how where the data files are vs logs? Is the new box set to use all procs? (maxes at 4 for std ed I think) Also check the execution plans on both servers, make sure they are the same.
      singulars
      1. POSQL Server Performance Problem
      1. USSqlACID
    2. COYup, smells like a bad disk setup, e.g. data and log on the same platter or something like that...
      singulars
      1. POSQL Server Performance Problem
      1. USmarc_s
    3. COIs the second, test, machine 32 bit OS?
      singulars
      1. POSQL Server Performance Problem
      1. USRemus Rusanu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload