Posted by Stu on 06/23/06 04:19

Building on Erland's suggestion that the production server might be
swamped, are you pulling data that might be affected by query
contention? In other words, are any of the tables you reference in
your query also involved in other complex queries? If someone were
updating that table, for example, your query could have been impacted
by blocking.

Just a thought,
Stu


Erland Sommarskog wrote:
> (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]