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 |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2004-03-04 : 21:20:36
|
| I have one instance of a server (2gb ram and 5 instances), 2 Xeon CPUs. When we run a stored procedure, it runs in 5 seconds on this server.But on all other servers, it runs for 17 minutes.The CPU spikes the moment we click on the run button. similar hardware configurations. may be one or two instances lesser than the first one.What are the possibilities?What is the first place I should look? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-04 : 23:41:52
|
| First thing to look at is if the data in the databases are the same. Same rowcounts? Same data distribution? Same table structures? Even minor differences can add up when performing millions of operations per second.The next thing would be to check statistics and update them, on both machines, for all indexes on all tables. Also run DBCC UPDATEUSAGE on the entire database. Might be a good idea to also run DBCC DBREINDEX or INDEXDEFRAG on both machines. One of them could very likely have out-of-date stats or be seriously fragmented.Last thing would be to flush the procedure and data caches (DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE, respectively) You cannot make valid comparisons of two different machines' performance without running these before and during your tests. Caching has an enormous effect on performance. It could very well be that one machine has all the data it needs in cache while the other has none and has to do a lot of I/O to get it. The only way to determine the real performance of both machines is to start with a clean slate.You can also run Profiler while doing your tests and checking the reads, writes, CPU time and duration values. If there is a bottleneck somewhere it will be easy to spot when comparing the two. It could be a hard drive problem, or a difference in the type of RAM being used. |
 |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-03-05 : 00:30:40
|
| I would also look at the execution plan of the query on each server.Lance Harra |
|
|
|
|
|
|
Long Running Stored Procedure