SQL Server Performance

Want to minimise the size of backup DB and increase the performance.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by SumitSV, Dec 26, 2008.

  1. SumitSV New Member

    Hi,
    I dont know if i am putting the post in write section.
    The senario is
    I have a huge Database where huge number of tables and millions of records in table. We have a maintainence plan of having a daily backup of database with hourly transational backup.
    Recently we had added nonclustered index for Tables to increase the performace and this did reflect the huge change in our site performance. But the major problem is, the size of the DB is getting huge day by day with transational logs.
    Could you suggest me some solutions so that we can minimise the size of DB backup and also should not hamper the performance of the DB.
  2. Mahmoud_H New Member

    use the index option sort_in_tempdb =true to maintain all indexes in tempdb not in the database.

    increase the size of tempdb
  3. SumitSV New Member

    I am sorry, but i am not an much experienced guy in SQL. Can you please give me steps or in detail knowledge on this, surely this will add up in my knowledge bank
  4. Mahmoud_H New Member

    exampleUSE [AdventureWorks]
    GO
    ALTER INDEX [IX_Employee_ManagerID] ON HumanResources.Employee
    REBUILD WITH ( SORT_IN_TEMPDB = ON )
    GO
    increase the size of datafile tempdb from the SSMS tool (server managemnt tool) to be
    25% of your user database(to enhance performance).
    Try to do the following tasks:
    1- shrink your database database
    DBCC SHRINKDATABASE(N'AdventureWorks' )
    2- rebuild index
    note the size of database after , before rebuilding index
  5. satya Moderator

    It seems we are going opposite ways, as Sumit is after size of backup and you are referring SORTING in TEMPDB for indexes which may cause additional issues on TEMPDB sizing.
    Sumit
    Can you explain what is the size of database and backup file size?
    What kind of problem you are having at the moment with this backup size, any disk free space issues?
  6. SumitSV New Member

    Hi Satya,
    Thanks for quick response[:)]
    We currently having a backup size of 6GB and transactional backup of 12 GB to 15GB. Before adding indexes to our site, we use to have the backup size up to 2 to 4 GB, but after applying indexes for performance size is expanding daily.
    No we do not have disk free space issue, but if it continues to grow in this way it will surely encounter this problem in future.
  7. Hi Sumit,
    What version of SQL Server you are using,
    One point to note here is, though you need less storage space because of backup compression but it takes more CPU cycle. So you would need to do a trade-off between these two.
    Hope it helps.
  8. SumitSV New Member

    Hi Arshad,
    Thanks for reply.
    We are using SQL Server 2005.
    It is nothing to do with CPU cycle. We have jobs running for daily db backups and for transational backups, so i dont think there is any thing to do with CPU. I am only concerned with the backup size of DB and the performance of the site(not hamper the sites performance).
  9. Hi Sumit,
    Can I have these details to understand your scenario in details:
    • Recovery model for the database
    • Time and frequency of Full backup
    • Time and frequency of Differential backup if any
    • Time and frequency of Transactional backup
  10. SumitSV New Member

    Thanks Arshad.
    See my comments below.
    • Recovery model for the database : Full
      • Time and frequency of Full backup : After every 24hrs
        • Time and frequency of Differential backup if any : No
          • Time and frequency of Transactional backup : After every hr.

Share This Page