September 19, 2005 at 6:43 am
I have a nightly job that runs on the production server when there are no users. This jobs runs a SP to sync data and takes about 2 hours to run. The production server runs Windows 2000 with SP4, 2.6 Ghz CPU with 3 gigs of RAM. It runs SQL 2000 with SP3.
The same sync procedure runs in the test server in less than 45 minutes. The test server is a Windows 2000 SP4 box, AMD Athlon 3200 with 1 gig of RAM. It has SQL 2000
with SP 3.
Before the Sync jon runs on the productin server, I run a backup database job and then a script to reindex tables. What could be causing the Sync job to take so much time ?
Any help would be appreciated.
September 19, 2005 at 7:59 am
I found that the temp DB has grown to 6 gigs and it has been quite a while since SQL Server has been restarted on the production server. Could that be an issue ?
September 19, 2005 at 8:07 am
How much does it grow to on the dev server after the job has run?
September 19, 2005 at 8:15 am
I don't have that numbers with me, the test server has been restarted since then.
September 19, 2005 at 5:48 pm
I've found rebooting every week helps. If we don't, we have inexplicable performance issues. I've found this true from MSSQL 4.2 all the way through to MSSQL 2000.
Also, for the tempdb, is it increasing in size by a fixed amount or a percentage? I've had some incremental performance gains using a percentage and starting with a fairly large tempdb (~ 20 GB). That way the space it's going to use is already allocated and the hit is smaller when the size needs to increase.
September 21, 2005 at 6:54 am
I rebooted the server yesterday and still no improvement in performance. The tempdb has the intial space set to 500 MB and set to grow in 50MB increments. It is currently using 1MB of the 500 MB allocated.
September 23, 2005 at 4:11 pm
Is this a new issue? Have you seen the threads on this site and others about potential slowness introduced by SP4?
How much time do wait after the reindex completes before kicking off the sync job. Is it a step within the same job or starts soon after? In some cases, a delay between the two is needed, eitehr because (1) the sync job is scheduled at an exact time and expects the reindex to fully complete and end before it starts or (2) the sync job is a dependent job and it starts right away and may encounter some initial blocking or delays because the reindexing is not completely finished.
Otherwise, if you still have issues, I'd recommend taking an inventory of your configuration settings and a trace to show what's going on throughout the sync. Compare the data with similar data on the one that's faster.
September 26, 2005 at 12:01 pm
Shawn - thanks for your input. SQL is still on SP3. There is no overlapping of jobs. Reindex and Sync are two different jobs 1/2 hour apart. Reindex takes less than 2 minutes to complete.
I ran Profiler and found a lot of Scan Started (51) and Scan Stopped (52) events on one of the tables.
Table Definition for TableA
ParentID int
ChildID int
X float
Deleted bit
The query
SELECT @APFamilyXValue = X
FROM TableA
WHERE (ParentID = @xParentID) AND (ChildID = @xChildID) AND (Deleted = 0)
There is a Clustered Index on ParentID + ChildID. Would it help to add an index on the Deleted column ?
This link
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=217539&p=2
shows that the query can be forced to do an Index Seek by changing the above query to
(DELETED = CAST(0 as BIT))
September 27, 2005 at 2:45 pm
Do you have any real time security software running in production that is not on test box? I found that it slows down backup more than 3 times...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply