Post reply

Need guidance...

  • May 1, 2016 at 7:01 pm

    #326062

    Hi guys,

    I need guidance on a situation I have with a customer... I'm not sure where this post should fit, please feel free to indicate a better place if this one does not fit.

    I have a customer having massive issues with it's ERP system and database: performance, locking, etc.

    When I look at it, they have:

    - Excel connected to the live system doing queries for users

    - A BI tool plugged to the live system doing queries in with dirty reads

    - Triggers on tables (insert/update) triggering heavy stored procedures.

    - Netapp RAID DP storage wit 10K RPM disks (I didn't even bother looking at the aggregates etc.)

    - There are probably even more things that I'm not aware of

    I'm not sure where to start there... At this point, I feel like the IT is trying to sabotage the system...:crazy:

  • May 1, 2016 at 10:31 pm

    #1875824

    I would start by separating the transactional data from the reporting data if that hasn't already been done.

    For example, take this:

    - Excel connected to the live system doing queries for users

    - A BI tool plugged to the live system doing queries in with dirty reads

    If Excel is connecting to the same place where the data is being inserted/updated/deleted from (e.g. the OLTP layer) then that's bad.

    This too...

    - Triggers on tables (insert/update) triggering heavy stored procedures.

    No need for triggers in your data mart or reporting DB.

    Set up ETL or Replication and extract a copy of the data to a new DB . Use that data for stuff like Excel and your BI tool.

    - Netapp RAID DP storage wit 10K RPM disks (I didn't even bother looking at the aggregates etc.)

    That's what tools like SQLIO are for. Run some tests, do some research on what good results should look like, compare them to the results you get... then you can say, "According to this article (or book) you should get these results but you are getting much worse performance." That's called providing measurable data that decision makers can use to make the right decision (which is better disk in your case).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 1, 2016 at 10:40 pm

    #1875825

    Thanks a lot Alan.

    I wanted to try using SQL Snapshots for the BI and Excel, but I'll try using replication.

    I'm in middle of using Diskspd to get actual figures, but like you said, I need to find a system that works properly first in order to get reference figures to compare with.

    I'll post the end result, If I ever see the end of it...

  • May 2, 2016 at 5:10 am

    #1875848

    You might also consider whether or not you can use READ ONLY files / filegroups for the data they are reporting on. And if you can get rid of Excel by using SSRS Report Builder for the end users (which gives them the power but doesn't do the same sort of locking that Excel or Access does).

    This sounds like quite a mess. You may need some help to straighten it out if they have the resources to hire another DBA.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • May 2, 2016 at 5:45 am

    #1875854

    Broniz (5/1/2016)

    Thanks a lot Alan.

    I wanted to try using SQL Snapshots for the BI and Excel, but I'll try using replication.

    I'm in middle of using Diskspd to get actual figures, but like you said, I need to find a system that works properly first in order to get reference figures to compare with.

    I'll post the end result, If I ever see the end of it...

    If snapshots are an option, that might be a better choice than replication. If you go the replication route consider snapshot replication if you don't need up to the minute reporting data. Transactional replication can get complicated.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 2, 2016 at 6:19 am

    #1875863

    Thx, I'll get all the information from them before trying to do anything. Not even sure they have EE to be honest.

  • May 2, 2016 at 9:45 am

    #1875931

    First, look into implementing snapshot isolation (SI) for the underlying db(s). If nothing else, if will reduce contention issues while you tune the indexes. Be sure to adjust as needed for the extra table overhead of SI.

    Then, if you can, end querying from Excel. It will be a never-ending nightmare.

    Finally, go after the real solution, if there is one, by analyzing all indexes on all (significant) tables, starting with the clustered indexes. Getting the best clustered index on every table is the most critical factor for performance. Do not assume that an identity is automatically right for the clustered index! You'll need to consider at least missing index stats, index usage stats and index operational stats; sometimes cardinality must be considered as well.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • May 2, 2016 at 5:28 pm

    #1876132

    Thank you for the guidance, that is helpful.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply