Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    4

    Talking Unanswered: Performance issue

    Good morning,

    I need to do the following and I was out of the market for many year, I have a test environment please need help, I would like to clarify that we have three DB seatting in the server, and I need two of them to do this if I can include the third will be awesome but this one is not related to the other one there if just use for integration,,,,some information is pulled from there and go to another server. the wo in question are DYNAMIC and the company, THANK YOU SO MUCH

    2. Performance issues

    a. Database is in full recovery mode but transaction logs are not enabled switch to simple (Microsoft will manage the log file) or start running transaction log back ups (which will manage the log file size)

    b. Database Properties Files LDF and MDF the should be in different drives,

    c. Tempdb should be on its own drive (fastest possible) depending on usage there is typically multiple tempdb, one per core.

    d. Memory setup currently at default setting this should be set to realistic leave enough memory for operating and the remainder for SQL SERVER. SQL will try to use all of the memory if given the opportunity.
    e. SQL Server to setup to notify when errors occur.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A) Whether databases are in FULL or SIMPLE logging mode is dependent only on the needs of the business in regards to recovery of the databases in case of server faults, or user faults. The logging mode has no impact on performance, but FULL recovery mode will need to have sufficient diskspace, and frequent enough log backups, or the users will get errors when the log fills up.

    b) This was (and still is) the case when you had limited local disk spindles to work with. If you are using a SAN, this is less important, as the SAN will likely be striping your volumes across multiple physical disks for you. Talk to your SAN support or sales rep to get specifics. You will want the datafiles and logfiles on fast storage (not nearline or SATA disks), or you will see disk bottlenecks. Again, the sales rep should be able to help you get information on how many I/O ops your particular disks can do, and how to monitor for trouble.

    c) Pretty much the same as above. The original advice on tempdb files was to match the number of CPUs. Now that machines have multiple cores per CPU, and 20 - 30 core machines are not too uncommon, it is better to cap the number of files for tempdb to 4 - 8. Again, monitoring for bottlenecks (excessive waits for pages in tempdb, especially the Allocation Map pages) will determine how many files will be right.

    d) That advice has not changed in the last few years.

    e) Always.

  3. #3
    Join Date
    Mar 2015
    Posts
    4

    Lightbulb Performing issues

    Quote Originally Posted by MCrowley View Post
    A) Whether databases are in FULL or SIMPLE logging mode is dependent only on the needs of the business in regards to recovery of the databases in case of server faults, or user faults. The logging mode has no impact on performance, but FULL recovery mode will need to have sufficient diskspace, and frequent enough log backups, or the users will get errors when the log fills up.

    b) This was (and still is) the case when you had limited local disk spindles to work with. If you are using a SAN, this is less important, as the SAN will likely be striping your volumes across multiple physical disks for you. Talk to your SAN support or sales rep to get specifics. You will want the datafiles and logfiles on fast storage (not nearline or SATA disks), or you will see disk bottlenecks. Again, the sales rep should be able to help you get information on how many I/O ops your particular disks can do, and how to monitor for trouble.

    c) Pretty much the same as above. The original advice on tempdb files was to match the number of CPUs. Now that machines have multiple cores per CPU, and 20 - 30 core machines are not too uncommon, it is better to cap the number of files for tempdb to 4 - 8. Again, monitoring for bottlenecks (excessive waits for pages in tempdb, especially the Allocation Map pages) will determine how many files will be right.

    d) That advice has not changed in the last few years.

    e) Always.

    This advise came from a consultant that is why I would like to test in an test environment, and I just notice that they have created the test environment with replication and/or mirroring and now I guess I have to take this option off

  4. #4
    Join Date
    Mar 2015
    Posts
    4

    Lightbulb Issues

    Thank you MCrowley !

    The advise came from a consultant that is why I would like to test in our test environment, and I just notice that they have created the test environment with replication and/or mirroring and now I guess I have to take this option off

  5. #5
    Join Date
    Mar 2015
    Posts
    4

    Talking Replication

    When I mentioned replication was I have to delete the user activity, and I took the actual user who are working.....I guess that this coming back is going to take my weekend but I will get there ....

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    a. Actually, FULL or even BULKLOGGED is preferred when performance is of concern, because of additional overhead related to checkpoint process that is outside of configuration control when db is in SIMPLE recovery mode. So, - go with FULL, unless imports are expected to be frequent and saturated with a lot of data. And implement tlog backups of course (don't shrink your log devices, just size them so that resizing does not become a daily task).

    b. Put LDF's on redundant storage, and put MDF's/NDF's on fast storage, preferably redundant (it's hard to find non-redundant configuration in today's server boxes). Make sure to have multi-file filegroups, especially on the databases that undergo most reads (see MCrowley's comment on allocation maps).

    c. Number of tempdb files equal to number of cores has been proven several times to be misnomer. For it to be a true benefit you need to ensure that each file sits on the disk that is sitting on a dedicated (NOT SHARED) controller channel, if not a dedicated controller altogether. In SAN environment it's virtually impossible, because DBA's usually are not the ones that control the slicing of the SAN space. And those who are will hardly be able to accommodate your needs because most likely this SAN is also shared with other space consumers.

    d. yep.
    e. yep.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

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
  •