Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
greeneee@sbcglobal.net
Starting Member
1 Post |
 Posted - 2009-04-07 : 13:34:49
|
| I'm not normally a SQL DBA, but I've had to put on that hat recently. I've got 2 standalone vendor app installations at 2 different sites installed using 2 separate local SQL Server 2000 DB Servers, service pack 4 (8.00.2039). DB sizes are relatively equal, about 125GB, running on identical hardware. Tempdb sizes are 2GB DB/500MB tlog in both, not autogrow. There is 1 datafile for each.The issue is a query written against the DB to pull some reporting data. The query is inefficient and uses a server side, scrollable cursor to fetch the data but it works; again, I'm not in control of this application or this reporting job. However, in site A, the reporting job takes about 2 minutes to pull roughly 25,000 rows (about 5MB of data), whereas in site B, the same job takes about 1 hour.With DTS, I can export the data in about 2 minutes in both sites so I know the indexing is the same or similar. The execution plan shows about the same cost on each step of the query.Using profiler, I can see that the cursor fetches about 128 rows of data; in Site A, the cpu/duration is <1 second for each cursor fetch, whereas in Site B, the cpu/duration is about 30 seconds for each cursor fetch, causing, of course, the long duration of the job.So...my question??? I'm wondering what to do to tune Site B such that it performs similarly to Site A. I'm suspecting it has something to do with tempdb, but I don't know where to go from here. Thanks for any help gurus!!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SQL Performance issue