SQL Server Performance

Hardware Limitation or Software Iimitation?? Need your perspective...everybody

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by h_tere2000@yahoo.com, Dec 3, 2007.

  1. <p>Hi all,</p><p>Company sql server 2000 has been hanging constantly. 20+ Users have been complaining of that my Ms. Server 2003 hang quite often. RAM is 1 G.<br></p><p>Tried upgrading to SP4 but vendor says that their some issues. &nbsp;</p><p>My performance graph screenshot is below. Note the green line in the graph indicates that it could be the harddisk. But I checked. Our harddisk is Seagate RPM 10 K and i even ran the disk tool diagnositic all passed.<br></p><p>Here is the link. Further down is my server specs. Any thoughts on this! My email is&nbsp; h_tere@yahoo.com<br>&nbsp;</p><p> <img src="http://img102.imageshack.us/my.php?image=performance213pm4decembkk2.jpg" mce_src="http://img102.imageshack.us/my.php?image=performance213pm4decembkk2.jpg" height="300" width="500"></p><p><img src="http://img162.imageshack.us/my.php?image=performance213pm4decembnd9.jpg" mce_src="http://img162.imageshack.us/my.php?image=performance213pm4decembnd9.jpg" align="middle" border="0" height="300" hspace="0" vspace="0" width="400"></p>Please click the thumbnail for larger resolution.<p><font color="#000000" face="arial"><table border="0" cellpadding="5" cellspacing="0" width="100%"><tbody><tr align="left"><td><p><a href="http://www.cpuid.com/" target="blank"><img src="http://www.cpuid.com/pics/logo-2.jpg" border="0" height="55" width="172"></a></p><p><b>CPU-Z 1.40.5 report file</b></p></td></tr><a href="http://img116.imageshack.us/my.php?image=performance213pm4decembcg3.jpg" target="_blank"><img src="http://img116.imageshack.us/img116/421/performance213pm4decembcg3.th.jpg" alt="Free Image Hosting at www.ImageShack.us" border="0"></a><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr bgcolor="#e0e0ff" valign="top"><td width="30%"><small><b>Processor(s)</b></small></td><td valign="center"><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr valign="top"><td width="30%"><small><small><b>Number of processors</b></small></small></td><td><small><small><font color="#0000a0">1</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Number of cores</b></small></small></td><td><small><small><font color="#0000a0">1 per processor</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Number of threads</b></small></small></td><td><small><small><font color="#0000a0">2 (max 2) per processor</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Name</b></small></small></td><td><small><small><font color="#0000a0">Intel Xeon</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Code Name</b></small></small></td><td><small><small><font color="#0000a0">Prestonia</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Specification</b></small></small></td><td><small><small><font color="#0000a0">Intel(R) Xeon(TM) CPU 2.80GHz</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Package</b></small></small></td><td><small><small><font color="#0000a0">Socket 604 mPGA</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Family/Model/Stepping</b></small></small></td><td><small><small><font color="#0000a0">F.2.9</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Extended Family/Model</b></small></small></td><td><small><small><font color="#0000a0">F.2</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Brand ID</b></small></small></td><td><small><small><font color="#0000a0">11</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Core Stepping</b></small></small></td><td><small><small><font color="#0000a0">D1</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Technology</b></small></small></td><td><small><small><font color="#0000a0">0.13 um</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Core Speed</b></small></small></td><td><small><small><font color="#0000a0">2791.8 MHz</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Multiplier x Bus speed</b></small></small></td><td><small><small><font color="#0000a0">21.0 x 132.9 MHz</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Rated Bus speed</b></small></small></td><td><small><small><font color="#0000a0">531.8 MHz</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Stock frequency</b></small></small></td><td><small><small><font color="#0000a0">2800 MHz</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Instruction sets</b></small></small></td><td><small><small><font color="#0000a0">MMX, SSE, SSE2</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>L1 Data cache</b></small></small></td><td><small><small><font color="#0000a0">8 KBytes, 4-way set associative, 64-byte line size</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Trace cache</b></small></small></td><td><small><small><font color="#0000a0">12 Kuops, 8-way set associative</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>L2 cache</b></small></small></td><td><small><small><font color="#0000a0">512 KBytes, 8-way set associative, 64-byte line size</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr bgcolor="#e0e0ff" valign="top"><td width="30%"><small><b>Chipset &amp; Memory</b></small></td><td valign="center"><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr valign="top"><td width="30%"><small><small><b>Northbridge</b></small></small></td><td><small><small><font color="#0000a0">Intel E7501 rev. A1</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Southbridge</b></small></small></td><td><small><small><font color="#0000a0">Intel 82801CA (ICH3-S) rev. 02</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Memory Type</b></small></small></td><td><small><small><font color="#0000a0">DDR</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Memory Size</b></small></small></td><td><small><small><font color="#0000a0">1024 MBytes</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Memory Frequency</b></small></small></td><td><small><small><font color="#0000a0">132.9 MHz (1:1)</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>CAS# Latency (tCL)</b></small></small></td><td><small><small><font color="#0000a0">2.0 clocks</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>RAS# to CAS# (tRDC)</b></small></small></td><td><small><small><font color="#0000a0">3 clocks</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>RAS# Precharge (tRP)</b></small></small></td><td><small><small><font color="#0000a0">3 clocks</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Cycle Time (tRAS)</b></small></small></td><td><small><small><font color="#0000a0">6 clocks</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr bgcolor="#e0e0ff" valign="top"><td width="30%"><small><b>System</b></small></td><td valign="center"><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr valign="top"><td width="30%"><small><small><b>System Manufacturer</b></small></small></td><td><small><small><font color="#0000a0">Intel</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>System Name</b></small></small></td><td><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>System S/N</b></small></small></td><td><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Mainboard Vendor</b></small></small></td><td><small><small><font color="#0000a0">Intel</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Mainboard Model</b></small></small></td><td><small><small><font color="#0000a0">SE7501BR2S</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>BIOS Vendor</b></small></small></td><td><small><small><font color="#0000a0">Intel Corporation</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>BIOS Version</b></small></small></td><td><small><small><font color="#0000a0">SBR20.86B.0082.P23.0504071730</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>BIOS Date</b></small></small></td><td><small><small><font color="#0000a0">04/07/2005</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr bgcolor="#e0e0ff" valign="top"><td width="30%"><small><b>Memory SPD</b></small></td><td valign="center"><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr valign="top"><td width="30%"><small><small><b>Module 1</b></small></small></td><td><small><small><font color="#0000a0">DDR, PC2300 (142 MHz), 512 MBytes, Infineon</font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>Module 2</b></small></small></td><td><small><small><font color="#0000a0">DDR, PC2300 (142 MHz), 512 MBytes, Infineon</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr bgcolor="#e0e0ff" valign="top"><td width="30%"><small><b>Software</b></small></td><td valign="center"><small><small><font color="#0000a0">&nbsp;</font></small></small></td></tr></tbody></table></td></tr><tr><td><table bgcolor="#f0f0f0" border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr valign="top"><td width="30%"><small><small><b>Windows Version</b></small></small></td><td><small><small><font color="#0000a0">Microsoft Windows Server 2003 Standard Edition Service Pack 1 (Build 3790) </font></small></small></td></tr><tr valign="top"><td width="30%"><small><small><b>DirectX Version</b></small></small></td><td><small><small><font color="#0000a0">9.0</font></small></small></td></tr></tbody></table></td></tr></tbody></table></font></p><p>&nbsp;</p><p>&nbsp;</p>
  2. martins New Member

    Hi,
    I can't see all the images, but I would suggest that you go to SP4 as soon as possible. Your hardware also looks a bit weak, especially if you have more than 20 users using it at the same time. If you can, try and upgrade your hardware...
  3. satya Moderator

    Agree with Martin on the hardware, as the memory seems very low 1GB which may be not efficient to cater the SQL queries and load. You may go upto 2gb if the SQL is a standard edition and as recommended you should test and apply latest service pack that includes lots of bug fixes.
  4. How can that be? if you look closely at the thumbnail. you can see that the memory's graph is always stable... it has never increased much .
    Compared to physical memory,

Share This Page