Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Talking Unanswered: involuntary DBA needs your expert advice

    dear forum members,

    i am a sap business objects administrator (BI reporting tool), lately i have been asked to maintain the SQL Server 2008 R2 (standard edition) database which holds a datawarehouse DB.

    So far there were no such activities done on the DWH and we never hada DBA here so you can assume how bad things are for me at the moment.

    To be honest my skills in SQL server are just average, but i have fair knowledge on Oracle administration so this makes me understand/grasp a couple of concepts easily.

    my tasks for the next few weeks is to plan a maintenance activities on the DWH to address performance issues.

    Anyway, to explain the current situation. we have 2 databases within a single SQL server standalone instance.

    one is a staging DB and other is the DWH. We don't do any kind of database / logs backup on a daily basis because its a virtual machine and we do a virtual backups daily.

    Also there are no clusters, no log shipping or no mirroring so i am assuming my tasks would be a bit straight forward.

    getting a little more technical, i found that each DB has 8 data files and each datafile is above 200GB in size. Also none of the DB has undergone a statistics and index rebuild or update in the last 3 years.

    What is expected from me is to come up with,

    1. a maintenance plan for the DWH
    2. find out performance bottlenecks and address them
    3. improve the performance of SSIS packages on the server (yes we have SSIS packages running on the same server)

    Please can help me how and where do i begin with, any suggestions and ideas from experts would be really helpful.

    Though I am reading threads and posts online to learn things but I am really searching for a right direction to proceed with.

    Thanks for reading and looking forward to hear from you.

    Raj

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First order of business, and I can't stress FIRST enough... Make valid SQL Server backups! Do this now, before you read the rest of this response.

    VM Backups are only useful for SQL Server if you quiesce (turn off) the virtual machine before you take the backup. Too much data is "in transit" and if the data and log files are not flushed to disk and quiesced, then the backup can't be restored intact. You may be able to save a large percentage of the data, but especially for a warehouse this is how you build a disaster!

    Next, get sp_Blitz installed, run it, then carefully review its output. The items are clear, actionable, and prioritized. They'll help you get the server "under control" in fairly short order.

    After that, you'll probably have questions before you tackle your existing priorities, but these really need to be addressed NOW, and the things you learn in the process may well positively effect your plans.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    5
    thank you pat, thank you very much.. highly appreciated.

    the problem is, the DWH DB is 1TB in size. Most of our previous backup attempts turned into disasters. In the past performing a backup of DWH DB made SQL server unstable and unusable thus we had to turn back to VM backups.

    The problem is this particular DWH DB has 8 database files and 1 log file. Out of the 8 , 3 of them are approx 200 GB, 1 is 500 GB and 4 are between 10 to 50 GB in size. The transactional log is 15GB in size. i can't really think of any strategy to get everything backed up.

    in fact the DWH server itself doesn't have sufficient space to back things up. It got less than 800GB free space.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A VM snapshot backup can work, but only if you stop the SQL Service for the duration of the snapshot. Depending on your VM host and its load, that could be a long time. If that is the only backup that you can make work, you might need to live with it... but I wouldn't!

    Check the Microsoft Product End of Life to see how long your version of SQL Server is supported. Keep in mind that unless you have a support contract (either for your business or for a specific instance of SQL Server), that a SQL Server that has had no oversight for three years will probably be out of support by August. If the business doesn't consider the data warehouse important enough to require support, that's a cue for you to relax a bit.

    If you follow the link I provided in my previous post for sp_blitz, you will find another link toward the bottom of the page for sp_BlitzIndex. It provides a very similar list that is prioritized, actionable, and pretty clear describing what needs to be done for the database indexes.

    As far as figuring out the process for SQL Server Maintenance Plans, I like Brad McGehee's book. You can buy the book from Amazon (for about $10), or download the PDF for free from Red Gate.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Pat, but the VM backup would not contain any transaction that occurred since the last backup. Correct? Why not a backup to a SAN server? Provided they have one.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Can the backups go directly to tape? Or even a file share? You can also backup the databases in a piecemeal fashion with filegroup or file backups (similar to tablespace backups). This would, of course, require you to run your database in FULL recovery mode (archivelog mode). How did the backups make the SQL Server "Unstable", anyway? You might not be able to backup (and truncate) the transaction log while the backup is running, but that should not be a problem for a system that is (I assume) loaded nightly.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by corncrowe View Post
    Pat, but the VM backup would not contain any transaction that occurred since the last backup. Correct? Why not a backup to a SAN server? Provided they have one.
    As long as the SQL Server is stopped and the snapshot is a full copy of the VM, they ought to be Ok... Hyper-V, VM-Ware, and VirtualBox are all able to cope with taking a snapshot of a running VM, each has its own methods with plusses and minuses.

    A snapshot taken with SQL Server running might be of academic interest, but of no practical value in case of a problem. A snapshot of only the system state would not have any data, but could rebuild most or all of the machine proper.

    Backups are something that you need to understand, not just run. If you don't know what you have and at least occasionally test the restore then the backups aren't valuable to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    we have our BI solution ( staging db, dw, analysis, reporting ) all on one VM.

    my suggestions are :-

    1) simple recovery model for all databases. no need to maintain tlog. not oltp.
    2) ensure database files/log files are on separate disk partitions.
    3) lots of memory allocated. use performance monitor to track memory consumed by sql server and ensure enough buffer remain for the OS. if analysis services run on the same box, track memory usage for this process as well. Then cap the max memory for sql server/analysis services.
    4) reason backup not important for us is we can re-run the entire process to rebuild staging/dw/cube ~ 6 hours.

    thong

  9. #9
    Join Date
    Mar 2014
    Posts
    5
    Dear Friends,

    Many thanks to all your response and sharing your knowledge. this is highly appreciated.

    @wthong: Great thanks for your invaluable inputs,

    1. We have 2 databases, one is the DWH itself and the other is Staging DB. The DWH is massive (1TB), its tlog of 15GB in size. Do you think its necessary to backup the tlog and truncate it or just leave it as is? yes this is OLAP and not OLTP.

    2. Argh, this system was setup in 2001. the logs and data files are on the same machine itself but on a separate disk which is 2 TB in size. The server spec was upgraded recently and is good though , its 16 CPU and 28 GB RAM. i am pretty sure NO fragmentation of disk and also NO statistics and indexes were run in the last 3 years.

    3. Thanks for this advice, i will use performance monitor and check memory of Analysis services.

    4. Our SSIS packages take ages to run, i think rebuilding statistics and de-fragment of hard disk will improve performance.? I read somewhere that rebuilding statistics will also rebuild indexes so i dont have rebuild statistics separately .. is this true?



    @Pat, Many thanks for the sp_blitz information and Brad McGehee's book. Thanks for the end of date link, We are running SQL Server 2008 R2 SP2, i was assuming this was still supported by MS? But yes we have a support contract with MS and the DWH is an important aspect of the business. Thanks for your priceless inputs.

    I am still not completely sure how the VM backups are taken daily, ill speak to infrastructure today to get more insight on this.

    @MCrowley, this is a good idea, i will try this, we have a network file share. The backups were scheduled to run early in the morning, it took too long and interfere with the SSIS jobs run-times which lead to memory bottlenecks. We had to abort the packages and cancel the backups.

    The one last question i had was, does an OLAP DB need to have a 15GB worth transactional log? should i do something to truncate it or just leave it as is?

    obviously i can only backup one database file at a time to avoid memory bottlenecks, so at the end when i have a complete backup of all the database files will they be up to date and sync with each other?

    Also any idea how long will it take to backup a 500GB database file?

    At the moment i am also confused if i need a simple or full recovery backup, i do understand simple will not backup the transaction log.

    Thanks in advance for all your help.

    Raj

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As for the index statistics, you get a free set of index statistics when you rebuild or create an index, but not on a re-organize. If auto update statistics is set to ON for the database, you will get a free index statistics update when 20% of a table changes (for very large tables, this could be a very long time). To look over the index statistics last updated date, you can run this query:
    Code:
    select object_name(object_id), name, stats_date (object_id, index_id)
    from sys.indexes
    where object_name(object_id) not like 'sys%'
      and index_id not in (0, 255)
    As for how long it will take to back up a 500 GB file, that will come down to how you are backing it up, and how spry of a system you have. Obviously, backing up the file to the same disk it is currently sitting on will cause disk queues, as the system tries to read and write to the same physical device. And copying across a network will be at the mercy of the slowest link. We do one weekly full on the weekend, when traffic is low, and daily differentials for databases in simple recovery mode. It's a nice feature that I think Oracle has not quite caught up to, as Oracle requires the archive logs for almost any restore.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keep in mind that a 15Gb log is only 1.5% of a 1024Gb database. I'm not sure that you can or should try to get smaller than that, and even if you do I think that the exercise will be pointless.

    I think that you REALLY need to read Paul Randal's 30 SQL Server Backup myths. SQL Server is a quite different beast than Oracle especially where backups are concerned, so many of your past database backup experiences are actually leading you into blind alleys... I've been doing this stuff for decades, but have to constantly remind myself that different database engines and different hardware/OS platforms sometimes need completely different backup approaches. Each engine has its own strengths and weaknesses, and the backup needs to be matched to the engine and platform.

    For a system where data changes are batched (like an OLAP system), the SIMPLE recovery model is appropriate. FULL recovery is useful because it maintains a journal in the log file allowing a backup to be restored down to sub-second levels of precision. SIMPLE recovery fully supports transactions (BEGIN TRANSACTION through COMMIT TRANSACTION) but once the transaction either commits or rolls back the log detail is no longer useful (at least for the most part).

    If you have a lot of disk that you can spare for a day or so, try using SQL Profiler to log the activity of your SSIS packages. Be sure to include CPU time, and disk I/O statistics. The resulting file will be enormous, but it will give you concrete information about poor performing queries that you can then use to find/fix the underlying problems. You can do a lot without this data, but knowing which queries are performing poorly and why is like taking your car to the mechanic with a specific list of problems you want them to address instead of dropping off the car with a note that says "fix it"... The detailed list allows you to get something specific done faster and cheaper than the "fix it" approach!

    As MCrowley pointed out, the "how long will my backup take?" question is almost unanswerable. I can build two different systems in the same virtual farm and have one backup in minutes and the other require hours... There are too many variables to give a specific answer. FWIW, I just did a 763 Gb backup for a client that took 53 minutes but that doesn't have much correlation to your backup running on your system.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rajsin View Post
    To be honest my skills in SQL server are just average, but i have fair knowledge on Oracle administration so this makes me understand/grasp a couple of concepts easily.
    As Pat noted, there are substantial fundamental differences between SQL Server and Oracle. Your knowledge of Oracle could actually hinder your ability to administer SQL Server.
    Pretend you are starting at the very beginning, and make no assumptions based on what you know of Oracle.
    The good news: SQL Server is MUCH easier to administer than Oracle.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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