SQL Server Performance

Windows 2000 / SQL 2000 : Slow Data Copying Process

Discussion in 'General DBA Questions' started by mrf1226, Aug 8, 2007.

  1. mrf1226 New Member

    We are running SQL 2000 on a Windows 2000 Std Server. Whenever my log shipping fails, I perform the tasks manually. Manually, due to the limited disk space we have on the server. What I normally do is I backup the database and copy it over to the target SQL server. The DB size is about 22GB. Copying it to the other SQL server normally takes less than 20minutes to finish. After copying I perform some tasks to complete the log shipping process.
    This month however, I noticed a huge difference in the copying process. What normally takes less than 20mins to complete takes forever, more than 8 hours to complete. There were no activities done on both the OS and SQL level that may have caused this performance issue. In fact, I tried copying large amount of files between other servers (not SQL servers) and found no problems on performance. The performance issue is only noticeable on the SQL servers.
    I run a trial version of IDERA Diagnostic Manager to see what may be causing it. Processor usage, network and I/O are all low. Except for memory which is normally high.
    I also tried restarting the server over the weekend to see if this will help, but to no avail.
    Any suggestions.

  2. ghemant Moderator

    Hi Mark,
    Welcome to the forum.
    It is default behavior of SQL Server, it occupies the max memory it can and release upon the request from OS/other applications.
    Have you check Windows event viewer!!! Is their anything that looks suspicious!! Is their anything installed on your SQL Server boxes that eats up more memory, have you check if their is AV setup to check runtime activity and db files are not excluded!!

  3. mrf1226 New Member

    Thanks for response.
    Nothing much on the event viewer. There is also no AV in our SQL server.
    I don't suspect it to be a bad NIC since this is happening in our other SQL server as well and only on SQL servers. A week before everything seems to be fine. In fact I rebooted our SQL Servers a couple of time, made some adjustments on the network settings and see if that helps, but to no avail.
    Not unless though something is causing it to slow down, like a virus maybe or a vulnerability that probably came out this month.
    Our SQL 2000 is only at SP3a.
    Is there any tool that I can use to test, scan and check particularly SQL servers to rule out any attacks or vulnerability issues.
    Any suggestions.

  4. i2lovefishing New Member

    A few suggestions,
    (1) Try different ways to perform the copy operation, e.g. on the source server, on the target server, or on a third computer.
    (2) Try the operations in (1) from your source sql server to a different server.
    (3) Try the operations in (1) from a different server to your target sql server.
    (4) If you have multiple NICs on your target and/or source sql servers, disable all but one on each server.
    (5) You mentioned "limited disk space", how low is your disk space?
    (6) Check virus
  5. mrf1226 New Member

    I tried all these already before and noticed that the large file copy that is involving SQL servers are really slow which is not the case a week ago. Any large copy involving any client or server that are not SQL servers are fine though.
    Unfortunately, we only use one NIC per SQL server.
    We do have enough disk space to accommodate the file copy of about 23 GB on the source SQL server. There is still about 4GB disk space left after the copy. The target SQL server on the otherhand has no problem on disk space.
    I was also planning to do a scan for virus on the SQL server, any recommendation on what tool to use?

  6. i2lovefishing New Member

    Based on your source server disk usage, it appears you have less than 5% disk space available after making a copy of your db. Even if this is not the reason of your slow copy process, you need to address it in the near future to make free space to at least 20% - 30% level after all usage.
    Taking 8 hours to copy 22G data should be considered slow, but 20 minutes, in my opinion, is very fast. Are you sure it was 22G data for 20 minutes? I can live with a 1 - 3 hour process. What copy utility are you using for such a large file?
    What else could be happening during this 8 hour process on both your source and target server? Do you have backup process starting on any of your sql servers during the 8 hour process?
    On Antivirus, I would use the same tool you are using on other servers. I always have Antivirus on all my servers, including sql servers. Not having Antivirus will not be my option. The most I can live with is to exclude virus scan from a few db files.

Share This Page