September 7, 2005 at 1:13 pm
I have a situation where we are changing a DSN on a Windows 2003 Server (MDAC 2.8) to point from one SQL Server to another and we are getting a query timeout after the change (using the same query).
The first server(A) runs Windows 2000 and SQL Server 2000 SP2. The second SQL Server (B) runs Windows 2003 and SQL Server 2000 SP4.
We ran some testing and this is what we came up with:
Here’s what I saw in the traces we ran. On the old server, this query did 266,544 reads. On the new server, the same query did 5,408,110.
When I ran it through Query Analyzer, the plans on both servers were identical. The number of reads to satisfy the query was still off but not by nearly that much. I got 265,513 on the old server, and 796,666 on the new server when using Query Analyzer rather than ODBC. The number of rows returned by the queries was identical.
Any ideas of why server B is producing so many more reads through ODBC? Server B has much larger hardware and more memory than server A.
September 8, 2005 at 2:02 am
Have you tried running optimizations?
September 8, 2005 at 5:50 am
I'm going to be checking if the indexes are fragmented on server B, but I have never heard of a fragmented index(es) that would cause the number of reads to go from 266,544 to 5,408,110. I'll post results from the index fragmentation check.
Any other ideas?
September 8, 2005 at 5:55 am
If you've just restored the database then it is likely that SQL has no idea about how your data is organised. When moving a DB from one server to another i generally perform some sort of optimisations before using it, and the DB is then generally quicker. As with all things in this wonderful land of MSSQL DB's YMMV
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply