SQL Server Performance Forum – Threads Archive

2005 index much slower than 2000 (??)

I have an identical database (with identical indexes) on two different machines. The hardware for both machines is similar, and both are running Windows Server 2003. One machine is running SQL Server 2000, while the other has SQL Server 2005. For nearly all queries, the machine with SQL Server 2005 is faster. But for a particular type of query involving clustered indexes, the older SQL Server 2000 machine is much faster — 7x-8x as fast. Actually, this is not just on one database, but rather three different databases where the architecture and queries are similar to that described below. So bottom line is — I’m trying to determine why the clustered index and table on the SQL Server 2005 machine is so slow. Briefly, the crucial query is the following one: select b.w1,b.c1
from temp1 as a
left join xID as b
on a.ID-1 = b.ID The xID table has 100 million rows, with the following columns. The clustered indexes are on the ID column in both the [xID] table and the temp table (described below). Table [xID] (b)
ID word
…. ….
23145290 the
23145291 tall
23145292 man
23145293 saw
23145294 the
23145295 old
23145296 man
…. …. Imagine I want to find the most common words preceding <man> in the 100 million rows. I store ID values for all of the rows with <man> in a temp table (temp1) above. For example: Table [temp1] (a)
ID
23145292
23145296 Then I run the query shown above, to find the words where the ID value is one less than the ID’s for <man> (e.g. 23145291 = <tall>, 23145295 = <old>, etc). Again, exactly the same databases and indexes for the databases on the SQL Server 2000 and the SQL Server 2005 machines, but for this query the SQL Server 2000 machine is about 7x-8x as fast. I’ve looked at the Query Execution plan and it shows that 99% of the work is being done in accessing the clustered index for the [xID] table, but it’s this way on both machines (well, 86% on the SQL Server 2000 machine). Finally, I’ve run the Database Engine Tuning Advisor on the SQL Server 2005 machine, but it doesn’t suggest any changes. Any suggestions for the super slow performance with SQL Server 2005? Thanks in advance.

---

Is autoupdate statistics on on both servers?
Is query is using parallalism in any one of the servers?
What is the I/O STATS comparision from both executions? Mohammed U.

---

Check whether it meets following:
You use a keyset-driven cursor to execute a FETCH statement.
The SELECT query contains a condition in the WHERE clause.
The columns in the condition have both a clustered index and a nonclustered index.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Yes

No

Slow 2005 machine (18 seconds; 23446 rows affected) Table ‘xID’. Scan count 23446, logical reads 206352, physical reads 133, read-ahead reads 17492, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘col_1’. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘col_2’. Scan count 0, logical reads 23519, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Fast 2000 machine (4 seconds; 23446 rows affected) Table ‘xID’. Scan count 23446, logical reads 164340, physical reads 156, read-ahead reads 16035.
Table ‘col_1’. Scan count 1, logical reads 78, physical reads 0, read-ahead reads 0.
Table ‘col_2’. Scan count 0, logical reads 47717, physical reads 0, read-ahead reads 0. (This is from just one identical query from among a series of identical queries on the two machines. The others are quite similar) I’m not very good at analyzing these stats, but it doesn’t look like the 2005 machine is doing much more physical disk access than the faster machine. Anything obvious here?

No, it only meets #2 of the preceding conditions, but not #1 or #3. I think I know where you’re going with this, though (the 2005 patch:http://support.microsoft.com/kb/917738). But I think that doesn’t apply in this case.

---

– Did you dbreindex the clustered index on both tables on both servers ?
– Are the Clusterratios equal ?
– do you have a clusterd index on your temptb ? – how about disk-spindle-speeds on both servers ? Are they equal (15K / 10K ??)

---

Part of my answer agrees with what you’re thinking, I guess or intended to say that memory contention is another throughput-related issue that can cause problems when an application runs with many concurrent connections. In SQL Server, queries reserve memory for the duration of their execution. This prevents some queries from failing on a memory allocation after they have completed only a portion of the query. If the system does not have any more memory to give, not-yet-started queries will be blocked and wait for previous queries to complete (and release their memory reservations). In some applications, the total system throughput is limited by the available memory necessary to enable operations like hash joins. I believe creating an index can cause the system to pick loops joins or merge joins in cases where hash joins would be picked otherwise. Creating an index or two on such a system can reduce memory contention and improve overall system throughput. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Are both server query plans are using the same indexes?
and row count is same on both servers?
Mohammed U.

---

]]>