Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-22 : 23:20:56
|
Andy writes "I have SQL 2000 (Final Release Version) Enterprise Edition running on NT 4.0 SP 6 Enterprise Edition with 3GT support enabled.
Hardware is a Quad 550 with 4 gigs of RAM, Fiber Channel RAID 5.
I have a table that is about 164 columns wide and 4 million rows deep.
I have created a duplicate of that table that is exactly the same but only has 1% (40,000) of the records.
The problem is when I run a SIMPLE query on the BIG table it takes LONGER then running a complex query. When looking at the query plan the simple query <> use parallel processing, whereas the complex one does. To further complicate the problem on my 1% sample it ALWAYS uses parallel processing....
The only difference in the Simple and complex query is that I link in another table with 4 million rows (I don't use this extra table for anything and the join does not exclude anything, I was just doing it for testing). Although the results are identical it processes faster due to parallel processing.
Anyway here are the stats for running the more complex query Vs Simple
Complex Simple
Scan count 12, 2
logical reads 1432953 846716
physical reads 5890 5397
read-ahead reads 1421900 838911
CPU time = 505013 352984
elapsed time = 227269 397031
Notice that it took nearly twice as long to run the Simple query even though it used only 60% of the resources.
So the upshot is that it's quicker doing a scan of an additional 4 million rows. I really don't think that the cost of parallel setting has anything to do with it since I set it all the way down to 1, and it works on the smaller table anyway. (Please notice this since everyone that has suggested something on it has suggested setting this. I really don't think that has anything to do with it since the identical query run on 1% of the rows uses the parallel feature.)
The performance monitor shows when running the complex one all 4 processors are maxed out the simple one shows only 1 processor maxed out.
FWIW this box is not used for transactional processing. We only use it to run these reports on every week or so. So having a query that uses 100% of the available resources is what we want.
I have all the query plans and graphs of CPU usage if you want to see them.
Thanks for your time,
Andy" |
|
|
|
|
|
Parallel Optimization Failure