Memory Handling in Windows 2003 Server/SQL

[DigitalFool]

I have a client that just migrated to a new 'beefy' system for a SQL 2000 application, SQL 2000 Standard. The issue is that basic SQL queries are now taking 10x as long to generate results. What once took 10 seconds takes 100 seconds. Overall performance generally seems to be sluggish.

The old system was dual/dual, Windows 2003 Standard, 4GB, SQL 2000 Standard. (had /3GB and /PAE switch in boot.ini)
The new system is dual/quad, Windows 2003 Enteprise, 32GB, SQL 2000 Standard.

I would imagine at the very least it would have similar performance if not better. I understand the limitations of 2GB of memory for SQL Standard 2000, and was curious if anyone had suggestions to where the bottleneck might be? Any utilities that might shed some light?

It would be great to move to SQL 2005 Standard at the very least, but that is not in their budget at the moment.

Page Life Expectancy has a moving average of 150 whereas the previous server was well over 300, so it really seems like a memory problem. CPU is barely being touched.

Thanks in advance!

 

[technome]

What once took 10 seconds takes 100 seconds."

This better be running on hardware raid. What type?, Is the cache policy set to write back vs write thru.


........................................
Chernobyl disaster..a must see pictorial

http://www.kiddofspeed.com/default.htm

 

[xmsre]

1. How was the new server built? Was a vendor autobuild utility used? Often these utilities start with a FAT partition which is later converted to NTFS resulting in an allocation unit size of 512 bytes. That in turn causes excessive transactional overhead and decreased performance (you want 4K which is the size of a read/write to the page file). Check:

a. The perfmon physical disk counter split IOS/sec for the boot partition (if there are a lot you have a problem)
b. The allocation unit size of the boot partition ( use the disk deframenter mmc snapin - analyze then read the report - no need to actually defragment)

2. What RAID type is the OS on? Many vendors ship with the old 3 drive RAID 5 set, which has 1/4 the write capacity of a plain old single spindle. RAID 5 has a write penalty of 4. THis means that each write operation requires 4 actual IOs to the disk. You'd be far better off with a mirror.

3. How is the page file set? If you set a small initial size and allow the page file to grow, this can lead to page file fragmentation which degrades performance. You'd be better off setting a fixed size in accordance with

http://support.microsoft.com/kb/889654/en-us

for 64bit versions and 1.5 times RAM max 4096MB unless /pae is enabled then as in

http://support.microsoft.com/kb/237740/en-us

4. SQL is PAE aware. If you have more than 4GB of RAM use /PAE but monitor fixed memory resources listed in

http://support.microsoft.com/kb/555223/en-us

5. Use your physical disk counters sec/read sec/write to check response times. You should see 4-20ms for drives hosting databases and preferrably 1ms or less for logs. Setting appropriate hardware options like write back cache (only if it's battery backed) and adaptive read cache, and choosing an appropriate RAID type for write intensive applications can improve performance as well.

6. make sure your memory is tuned for applications not file server.

7. use an allocation unit size of 8K or greater when formatting disks that will hold databases or logs.

put the OS logs and databases on seperate spindles.


Generally, an out of the box server from a major vendor will have all the drives in a RAID 5 and be built using an automated build program resulting in a 512 byte allocation unit on the boot partition, the page file will grow automatically, and memory is tuned for file servers. All the disks will be logical drives sharing the same spindles formatted with a 4K allocation unit size (except the boot which is 512 bytes because it was converted). THIS IS THE WORST POSSIBLE SERVER CONFIGURATION FROM A PERFORMANCE PERSPECTIVE. I personnally believe it is a conspiracy on the part of server vendors to get you to buy unneeded hardware ....

 

[mrdenny]

What's the execution plan look like for the old server and the new server? If the page life expectancy has changed that much, then SQL is flushing data from memory more often than it needs to.

Why the purchase of all the RAM if only using SQL 2000 Standard, as 2 Gigs will be your limit no matter what.

Is the system x86 or x64?

Check perfmon and see what the queueing at the disk is looking like. How large is the database?

My gut says that we need to start with the execution plans. If you aren't sure how to read these you can screenshot them from both servers, by running the query in Query Analyzer, and posting them so we can take a look at them. That will probably tell us the problems, or at least point us in the right direction.

Ideally get the execution plans in SQL 2005 so that you can save the XML of the execution plans and post that, as it will provide more info than the screenshots.

How much memory is SQL actually taking up?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[DigitalFool]

Thanks for the replies...I'll expand info and hopefully touch on suggestions.

Both systems are x86. OS loaded local on local SAS disk RAID 1. DATA/LOGS on separate LUNS carved from an Equallogic iSCSI SAN RAID 5. Same setup as before.

The plan was to move to SQL 2005 Standard when the new hardware was purchased. It didn't make the budget.

Cache policy is write back.

I personally didn't build the system so I don't know if util disks were used, but the allocation unit size is 4KB. Perfmon counters look fine regarding disk read/write.

Pagefile is set to 2GB (no different from old server) and % usage is 2.5%.

According to

http://support.microsoft.com/kb/875352

I don't have to set /PAE if hardware DEP is enabled which it is.

The SQL instance is using 1.65GB

mrdenny - you are right, the execution plans are different between the servers even though they are a 'simple' select * from a view. I'm running enterprise manager, but the option to save is grayed out. I'm not a DBA, so why would there be a change since the only thing was a database restore from the old server?

 

[mrdenny]

It could be any number of things. The optimizer has found some reason to handle something differently.

Run the select statement within Query Analyzer. Before you run the query press <CTRL>K which will turn on the showing of the execution plan.

Without the SQL Server 2005 tools you'll have to take a screenshot. There doesn't appear to be anyway to save the execution plan.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[DigitalFool]

Again, I'm only querying from a view. Maybe this information will help regarding the IO stats:

OLD SERVER

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CIRSUB_MX'. Scan count 15, logical reads 161411, physical reads 0, read-ahead reads 0.
Table 'CDSCVI_MX'. Scan count 1, logical reads 3247, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3969 ms, elapsed time = 5011 ms.

(323173 row(s) affected)

SQL Server Execution Times:
CPU time = 3969 ms, elapsed time = 5011 ms.

(8 row(s) affected)

SQL Server Execution Times:
CPU time = 3969 ms, elapsed time = 5012 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

NEW SERVER

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CDSCVI_MX'. Scan count 8, logical reads 3205, physical reads 2446, read-ahead reads 3048.
Table 'CIRSUB_MX'. Scan count 120, logical reads 161683, physical reads 2808, read-ahead reads 158818.

SQL Server Execution Times:
CPU time = 4405 ms, elapsed time = 33874 ms.

(322048 row(s) affected)

SQL Server Execution Times:
CPU time = 4405 ms, elapsed time = 33874 ms.

(9 row(s) affected)

SQL Server Execution Times:
CPU time = 4405 ms, elapsed time = 33875 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

 

[mrdenny]

Your new server is definetly going to the disk. There are thousands of physical reads, where the old server had all the data in cache so there were no reads to the disk.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[xmsre]

I'd have to agree with that. Compare:

Table 'CIRSUB_MX'. Scan count 15, logical reads 161411, physical reads 0, read-ahead reads 0.
Table 'CDSCVI_MX'. Scan count 1, logical reads 3247, physical reads 0, read-ahead reads 0.


with

Table 'CDSCVI_MX'. Scan count 8, logical reads 3205, physical reads 2446, read-ahead reads 3048.
Table 'CIRSUB_MX'. Scan count 120, logical reads 161683, physical reads 2808, read-ahead reads 158818.




Secifically, look at table 'CIRSUB_MX' read-ahead reads.

What SQL Service Pack are you at?

 

[DigitalFool]

I know that it is not being read from the cache as opposed to the old server, that is why am I puzzled. I was hopeful the types of reads would help lead me towards a solution. Again, same database/'better' hardware (even though I understand the limitations of SQL 2000 Standard)

SP4 (8.0.2039)

 

[xmsre]

The difference in physical reads seems about right for creating an index. Take a look at:

http://support.microsoft.com/kb/897284/en-us

Why don't you run the Index Tuning Wizard and see what it says?

 

[DigitalFool]

I ran the index tuning wizard both on the query in question and on a profile trace of activity. Both came back with 'No index recommendations for the workload and the chosen parameters.'

 

[xmsre]

If you rerun the query, what happens? I'm guessing the cache will be populated and it will run faster.

 

[mrdenny]

Can you post the screenshots of the actual execution plans? I fired up Query Analyzer and apparently QA doesn't have the option to save the Execution plan.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[DigitalFool]

In my process of getting the screenshots, I wasn't able to access the forum this morning. I dug a little deeper focusing on the execution plan and decided to remove parallelism which seemed to be the difference on the new server.

sp_configure 'max degree of parallelism', 1
go
reconfigure with override

Everything seems to be working out great now. The old server is still configured with 0, which is why I discounted it originally since it was the same on the new server when I was cross referencing settings.

PLE is a little better with a moving average of 275, but it could be just todays load. I'd like it to be better, but I think that is more related to the SQL 2000 bottleneck. Just happy queries are zippy again.

Thanks all!

 

[mrdenny]

The parallelism can cause you problems some times.

Basically what's happening is the work is being done by more than one thread. While everything is running, one of the threads gets hung up on something, and all the other threads now have to wait for that the hung thread to finish.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog