SQL Server 2000 SP3a and HyperThreading 1

[Kiehlster]

I'm having huge problems with SQL Server 2000 with SP3a installed, and running on a dual xeon HT 3.6ghz system with 3.5gb of ram. We've had some very complex statistical data queries running on an older system and just bought a new server to handle the queries at a faster rate.

The problem is, when we allow SQL to run on more than 1 of the hyperthreads, the SQL server slows down and in some cases it never seems to finish the queries. We know that the SQL Server is to blame because we pointed the old web server to the new box so that it would just use the database off the new box, but even the old website has the problem when accessing the new database.

If we limit it to one of the 4 hyperthreads, it runs fun and about the same speed as the old SQL server. But if we have one hyperthread from each physical processor running SQL or even both hyperthreads on only one physical processor, the SQL server just hangs while running our queries.

We are thinking about trying SQL Server 2000 SP4 Beta because that may fix things, but it takes too much time to get enrolled in the beta test. We need this up and working by tomorrow. And right now, it looks like we're forced to run it on one hyperthread.

Have any of you had this problem? Is there a trick to getting SQL running reasonably on all 4 of the hyperthreads? And do any of you have a link where we can download SP4 beta to test it out? Or is SP4 Beta too unstable?

Steve Kiehl
Web Page Designer - Nanovox Productions
Fantasy Artist - Zeadi

 

[Kiehlster]

1 1/2 days after trying to isolate this issue.... I found out that it has something to do with degrees of parallelism. But using the OPTION (MAXDOP 1) doesn't work. This may be because we use views in some of our queries.

This query in a breef psuedo code looks something like this:

SELECT 	
r.field1 AS rfield1,
...,
r.field14 AS rfield14,
e.field1 AS efield1,
...,
e.field14 AS efield14,
view.*
FROM r
INNER JOIN e ON r.id = e.id
LEFT OUTER JOIN view
	ON e.name = view.name
	AND view.date >= (
		SELECT MIN(date)
		FROM r
		WHERE id IN	(
			SELECT TOP $lines view2.id
			FROM view2
			WHERE view2.name = e.name
			AND view2.field1 <> 0
			AND view2.field2 <> 'S'
			ORDER BY view2.date DESC
		)
	)
WHERE r.date = '$date'
AND e.field2 <> 'S'
ORDER BY r.field3,
e.name,
view.date DESC

When I set the server's parallelism to 1 the query runs fine, but it takes twice as long as our old server which runs on a dual PIII. So somehow it's taking 4 logical processors twice as long to run this query as it does two slower processors with parallelism of 2.

Disabling HyperThreading only made it run three times as slow as the old system, and it still wouldn't run in parallelism.

The only difference now is that the old system has Windows Server 2000 standard where as the new system runs on Windows Server 2003 standard. Both servers are running SQL Server 2000 SP3/SP3a.

Steve Kiehl
Web Page Designer - Nanovox Productions
Fantasy Artist - Zeadi

 

[Kiehlster]

Resolved my own problem. If anyone's interested.

After a week or two of scratching my head, I looked once more through the output from SQL profiler. Seeing how the query spit out somewhere around 1.5 million page reads I decided to search google for "SQL 2000 profiler million reads" and found this page about sql server statistics:

http://www.sql-server-performance.com/q&a74.asp

I read through it and did a little more research on statistics and found the UPDATE STATISTICS query. So we ran that and our queries dropped from 500+ seconds to 16 seconds. And that's all that was needed to fix it. The site's working at blazing speeds now. The old server would run the same queries in 25-60 seconds each. And now our customer is very happy about their new server. And we now have the server updating stats regularly with some of the built in stored procedures.

Steve Kiehl
Web Page Designer - Nanovox Productions
Fantasy Artist - Zeadi

 

[vongrunt]

Always interested... and sometimes surprised with simple answers.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]