SQL Server Performance Forum – Threads Archive

Caching in SQL SERVER 2000 on WIN2K3

Hi, Thanks in advance for your support. We have recently purchased a Dual Core, Dual processor(XEON) server with 4 GB installed RAM, which we are using exclusively as our SQL SERVER Database server. We have installed SQL SERVER 2000 on this machine and we are now facing serious performance issues. The Server is configured in the following fashion. 1) OS is installed in a disk with RAID 1 configuration [36 GB] 2) SQL DATA is stored in a disk array of RAID 10 configuration[67 GB] 3) SQL LOGS are stored on a disk array of RAID 1 configuration.[135 GB] Our database size is 3.2 GB (MDF file). One of the interesting issues,which we are facing is the following; After we started getting poor performance, I had installed the same Database on a desktop (Pentium IV / 512 MHz machine) and when I use the query analyzer, I can find that the queries are cached as expected. (For example, If I query a table for the first time, I get the result in 6 seconds and in consequent attempts, I am getting the results in 2 secs or even 0 secs) But on my NEW super powered, Dual Core Dual Processor machine dedicated for my database activities,the same "select query on the same table" constantly takes 6 secs, regardless of the number of attempts. If I run a DBCC MEMORYSTATUS I am getting the following values.<img src=’http://66.155.212.68/palm/image/DBCCMEMORYSTATUS.jpg’ border=’0′ /> I am not sure whether the following information helps you in suggesting your views.

1) How many processor do you have for paralellism?
2) How do you set SQL Memory?
3) Some counters will help: pages/sec, Avg. Disk queue lenght.
4) Did you run maintenance plan like Reindex or/and Update Statistics? BTW:Welcome to the forum. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

i would run the query with the OPTION (MAXDOP 1) at the end also, run the two below, then run the query and post the messages output
SET STATISTICS IO ON
SET STATISTICS TIME ON

Hi,
We have two dual core processors availible for paralleism, and we have also reindexed the tables It did not help.

There are known issues with SQL Server using parallellism when it shouldn’t, and your query may be one of them. Look at Joe’s suggestions.

we got an sql expert today but we dont have any progress on this, guys if you have anything to we can try, please help URGENTLY!!

Have you tried Joe’s suggestion to "run the query with the OPTION (MAXDOP 1) at the end"? The point is that parallellism may be the cause of the problem, rather than an improvement. The MAXDOP 1 option disables parallellism for the query.

SQL DATA is stored in a disk array of RAID 10 configuration[67 GB] why RAID 10? (mirror with stripping) ? why not go with raid 50 (raid 5 stripped) ?

]]>