| Author |
Topic |
|
mjschwenger
Starting Member
10 Posts |
 Posted - 2007-07-28 : 02:00:03
|
| I have a problem with a new install of SQL server 2005 Standard SP2 on Win 2003 server, x86_64, 8GB, 4 CPU. There's no RAM limitation for the SQl server as 64b.The server behaves very strange. Compare to install of SQL 2000 on the same box, it is a disaster. The same code, on the same data like the SQL 2000 instance runs 10 times slower, picks all wrong plans with FTS (like the indexes do not exists)although we have rebuild the indexes, shrink the DB, updated all stats. The Sqlserver exe shows up to 7.5 GB memory usage in task monitor, although if we sum the memory used by the several processes running on this SQL server, there will be no even 1GB (in this case Sql server 2000 is not running). We do not see locks, or anything unusual, but the users are complaing of slow running queries and hard to connect. No hardware problems encountered either. Made a trace, but again, nothing that looks suspicious there.What can I do to troubleshoot and resolve the problem?Thanks a lot, mj |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-28 : 04:42:55
|
What about parallellism? I have seen this behaviour before.Try to add OPTION ((MAXDOP 1) to a query and run. See what difference it gives.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
mjschwenger
Starting Member
10 Posts |
Posted - 2007-07-28 : 12:13:09
|
| Thanks a lot for the advice - I changed the server to use single procesor - it is high volume OLTP, the parallellism will not help us. That cleaned up a little the plan, but it did not help in picking up the indexes. The FTS for no reason are still there - on the same places sql server 2000 picks the existing indexes with no problem and run much faster.Thanks again, mj |
|
|
|
mjschwenger
Starting Member
10 Posts |
Posted - 2007-07-28 : 12:46:54
|
| After lookign carefully at the plans, it looks that the differences between the 2000 and 2005 instances is due to the usage of Bookmark lookup in 2000 (I believe it doesn't exist in 2005) and the index spool-eager spool that SQL 2005 uses - the whole cost is comming from there. As this is related to the tempdb, I start ready about it but so far cannot determine a problem there.Any idea on how to optimize/prevent the index Spool?Thanks a lot, mj |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 13:42:01
|
| Tried database tuning advisor? It may tell you what kind of index you need. |
|
|
|
mjschwenger
Starting Member
10 Posts |
Posted - 2007-07-28 : 14:47:04
|
| Thanks a lot, but the wizard did not offer anything more then what I already have. it suggested though to combine some fo the indexes I have already have as single.I think the poblem is in the tempdb used by the index spool/eager spool, but when followed the steps given by MS I did not get any solution.Somethign I could do for the tempdb? Or a way to optimize the spools?Thanks a lot, mj |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-28 : 16:17:54
|
| Check if you can add the non key columns to the index (as covering columns) using the INCLUDE keyword and see if it makes any difference. We've seem weird query plans when we were load testing 2005 with current prod DB in 2000. Some query plan issues got resolved by including these columns in the index.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-28 : 17:01:04
|
Bookmark lookups indicate that you are heavily using a column in your query, and the column is not indexed.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
mjschwenger
Starting Member
10 Posts |
Posted - 2007-08-02 : 20:57:44
|
| I still have the same problem.Generally, when there is an index on column 1,2,3, but the query includes columns 4 and 5 as well, SQL server 2005 makes full table scan. Sql 2000 on the same query bookmarks it and row id look up is performed.If I create an indexs on all 5 columns - SQl server 2005 is happy and do not scan.(same code, same data loaded, indexed recreated and stas run on 2005)Any idea on how to fix that?Thanks a lot, mj |
|
|
|
|
SQL 2005 problem