Posted by Erland Sommarskog on 06/22/06 22:26

(BTabios@gmail.com) writes:
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
>
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.

Now, that's so amazing, that I would double-check.

The only thing I can think of beside faulty hardware is severe
ragmentation, but in such case the fragmentation would be carried over with
the backup. I note your post that you mention defragmening the
primary hardware. That sounds like your running defrag on file-system
level. This can be a good thing, but the most important is to run
defragmentation with DBCC DBREINDEX or INDEXDEFRAG within the database.

Of course, it could be the case that the production machine is completely
swamped with work, but I guess you would have noticed this.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]