Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Backup database taking unrealistic time

    Hi,

    I have my DB server with following configurations.

    OS Name: Microsoftr Windows Serverr 2008 Enterprise
    OS Version: 6.0.6002 Service Pack 2 Build 6002
    OS Configuration: Member Server
    OS Build Type: Multiprocessor Free
    System Manufacturer: HP
    System Model: ProLiant DL580 G5
    System Type: x64-based PC
    Processor(s): 4 Processor(s) Installed.
    [01]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
    [02]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
    [03]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
    [04]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
    Total Physical Memory: 32,764 MB
    Page File: Max Size: 68,661 MB

    and SQL SERVER version is

    Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64) May 14 2009 16:08:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


    I have a DB of size 225 GB when i started to backed the DB it took 10 hrs 7 mins to get complete.
    Which is too much i guess in comparision to DB size.

    Can anyone please guide me what could be possible reason and how i can improve performance?

    Posted - 07/29/2010 : 04:49:18
    --------------------------------------------------------------------------------

    thanks YelloBug for reply
    Here is the info
    1. I am doing native backup using 'backup database' command
    2. I am writing the backup file on SAN disk
    3. There was no any other activities was performing when i started backup but earlier one SSIS package was running and memory utilization was around 31 GB out of 32 GB but when package executed the memory utilization doesn't goes down and it was still showing 31 GB and process which was taking high memory was msdsrv.exe.
    4. CPU utilization was normal(10-20%) during this period


    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can tell you that 10 hrs is too long for a 225GB db.

    I do not know what the figures are for the new native backup compression (we use third party backup compression) however compressed backups are substantially quicker than uncompressed. The reason is that the main bottleneck is the IO. Compression is in memory and CPU intensive, but reduces the IO substantially. As such, this is one option to investigate.

    Also, I am not very good with SANs but it appears that these are very, very often the cause of bottle necks if not configured absolutely perfectly. There are a lot of potential bottlenecks between your database and the SAN arrays.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, I haven't heard of msdsrv.exe. Google doesn't show much either. Can you confirm the name?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2010
    Posts
    2
    sorry for typo..it is msmdsrv.exe which taking high memory

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - this is Analysis Services. I'm afraid I know little about this too!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the SAN disk you are writing the backup to the same disk where the database datafiles are? If so, then 10 hours is probably right. The disk is likely getting choked. Backup tends to be more of a disk activity, than a memory activity, so the memory usage of MSMDSRV.exe is likely irrelevant. If I recall, Analysis Services 2005 allocates memory based on a percentage of physical memory, rather than a set limit. You may want to look around for whitepapers about SQL 2008 Analysis Services to see if that has changed at all.

  7. #7
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    10 hours is definitely too long, I'd have to agree with MCrowley here, one thing to remember is that SAN does not necessarily mean fast. There could be configuration problems, you could be reading and writing to the same LUN simultaneously, you could be on slow disks, there could be network issues, and on and on...

    Without knowing the full story on your setup it's hard to say exactly where the bottleneck might be.
    drew
    ------------------
    http://wjtechnologies.com

  8. #8
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    If possible , attempt to do the backup , on a drive using a different LUN .You may also want to consider other layers that SQL Server originating packets need to go through prior to writing on disk

  9. #9
    Join Date
    Aug 2010
    Posts
    1

    IO Bottleneck

    I agree with the comment above that you might be experiencing an IO Bottleneck, which might be the result of your HDD not being able to process quickly enough highly fragmented data or small random blocks of data (4K, 8K, 16K).

    The solutions you got in this case are:

    1) Upgrade RAM
    2) Upgrade to SSD
    3) Upgrade to PCIe SSD

    Upgrading RAM is probably not a bad idea, but one would need to evaluate the amount of data needing quick access to determine the amount of RAM needed to store this data on (and whether it can be applied)

    Here is a quick explanation of some solutions we evaluated before to enhance our SQL database performance:

    HDD 15000 RPM

    1. Max. speed of no-frag. data transfer - 220 MB/s (uncompr. HD video)
    2. Max. speed of frag. data transfer (4k random) - 1.2 MB/s (databases)
    3. Price 500GB - 420 USD


    SSD usable in RAID (not all SSDs work in RAID)
    for example 2.5 SATA II : SSD Europe
    http://runcore.com

    1. Max. speed of no-frag. data transfer - 285 MB/s (uncompr. HD video)
    2. Max. speed of frag. data transfer (4k random) - 30 MB/s (databases)
    3. Price 500GB - 2000 USD


    PCIexpress cards
    for example PCI-Express : SSD EuropeHome :: Fusion-io

    1. Max. speed of no-frag. data transfer - 1450 MB/s (uncompr. HD video)
    2. Max. speed of frag. data transfer (4k random) - 500 MB/s (databases)
    3. Price 500GB - 18000 USD


    So you would get the following picture:

    SSD are 35x times faster than HDD but cost 5x the amount
    PCIe cards are 500x times faster than HDD but cost 40x the amount


    The life expectancy of the SSD with SLC NAND flash came up to 8 years, given that we use it roughly 80% read, 20% write 24/7.

    Hope this helps,

    Sergey

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •