SQL Server Performance

Vendor asked set parameters to boost performance

Discussion in 'Performance Tuning for DBAs' started by eramgarden, May 22, 2006.

  1. eramgarden New Member

    We have a sql server2000 database designed by the vendor. No FK. No PK. We have lots of indexes..some are unique indexes...

    They told us to do this to boost performance:
    1.Set the memory property (very important). Select the SQL server , right click, click start up properties, type in –g512 and press ADD.

    2.From the same right click as above, select the processor tab, click “Boost SQL Server Priority on Windows”

    3.Change logging from full to simple, remove old logging files if possible.
    4.Keep unused disk space at 25%
    5.Use SETRANGE commands, get rid of all SETLL, READ, CHAIN.

    --- I couldnt find "setrannge" command in BOL. Not sure what they mean by #5.
    #4: is that because of the back-up logs?? Should we remove logging files ( do they mean the transaction logs and stuff)?

    #3: is that a good idea for a production environment?
    #1 and 2: what is that "-g512"? is it ok to click the "boost" option?

    --- I think the problem is how the database is designed that's making it perform poorly.
    ANy thoughts?

  2. Luis Martin Moderator

    Yes, you can add to the vendor list: Buy the more powerfull server you can find in the planet!!!!!!

    Luis Martin

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte

    All postings are provided “AS IS” with no warranties for accuracy.

  3. eramgarden New Member

    So you think instead of fixing their DB issues, they just want to make it work by asking us to do those 5 steps? This is what they said:
    "setting up the attached parameters substantially improves performance"
  4. joechang New Member

    1. boost priority should generally not be used.
    i think your vendor is 1) getting advice from an idiot or 2) this is their remedy for people who run multiple apps on their SQL Server, or even 3rd party monitoring tools, which frequently cause problems rather help solve any problems

    2. if they need the -g512, then it is my opinion that they seriously f*cked up the design and architecture of their application,
    this is a bandaid that reserves memory for uses other than the data cache, and the only reason they need so much is that they misused certain SQL Server capabilities

    I would take a look to see if there are other good app that do not need -g512 in 32-bit mode.
    of course, just because its not mentioned does not mean they did not still screw it up

    ie. tell them to fix their apply to not squander mem to leave
  5. Luis Martin Moderator

    Well, I think Joe give you a very good picture[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  6. eramgarden New Member

    They're not getting advice from idiots..THEY ARE IDIOTS <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />But seriously...still not clear about that -g512 and why not use the "boost" stuff:<br /><br />"boost" is for running a 3rd party tool or multiple apps??<br /><br />-g512 is to reserve memory for other things than data caching?<br /><br />I need to explain it to my manager and still not sure "why we shouldnt do as vendor is saying". How about that full and simple thing?<br /><br />I totally agree that the db design is not good but i need more info to tell my manager why.<br /><br />
  7. joechang New Member

    priority boost sounds impressive, it really is for twits who cannot configure their server properly, ie, prevent low priority processing from consuming too much cpu at critical times,
    so if you need then priority boost to compensate for this, then what does thaat say about your sysadmin skills

    on mem to leave (-g), this is the way i would put it.
    if SQL Server was built by performance engineers for a performance task,
    it would only have limited functionality, and no frills

    but customers ask for everything under the sun, kind of like the difference between a race car, which may not have air conditioning, compared to an SUV stuffed to gills,

    if the app needs -g512, then thats an indication they put too much crap into their app, then needed to apply band-aids to fix it.

    now the use of mem-to-leave by itself should not be an issue, in a 64-bit system it is not an issue.
    however, the fact that is used is an indication they did not have the discipline to not do dangerous things in the database engine
  8. eramgarden New Member

    Thanks and I found some info on google as well.

    They've created user defined stored procs in the Master database and started them with "sp_". I also see custom tables they've created in the Master DB.

    I know DB design is wrong..i'm sure they're app is doing funcky stuff too.
  9. Luis Martin Moderator

    "They've created user defined stored procs in the Master database and started them with "sp_""

    Another mistake. Think if you want to change, in the future, to 2005. All store procedure must (usually design) store in vendor databases. In this way is easier to migrate.

    Luis Martin

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte

    All postings are provided “AS IS” with no warranties for accuracy.

  10. mmarovic Active Member

    quote:Originally posted by eramgarden
    3.Change logging from full to simple, remove old logging files if possible.
    I see this recommendation is not commented by peers yet.

    Are you aware of consequencies of downgraded recovery model? Besides, I don't see it helps performance at all. You should just schedule frequent enough transaction log backup instead.

    quote:#3: is that a good idea for a production environment?

    No, it is not good idea unless you don't care loosing transactions after last full database backup.
  11. eramgarden New Member

    I'm new at this job and told my manager about the findindgs and this is what he said:

    "Yes, I can not explain or defend setting those like that other than for whatever reason it makes the vendor app work much better. I think the –g512 is the one that really made things better."

    I wonder how "works better" is going to last? At some point..i think..even those settings wont fix the crap the vendor is doing...

    any thoughts on that?

  12. satya Moderator

    As long as the queries are fine tuned to perform well this may stand as short term fix, so try to fix the queries and advice the vendor to fix on that areas.

    Also consider what Mmarovic suggested and do not attempt to change until you've tested the data recoverability by changing the recovery models.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. eramgarden New Member

    my manager wants me to make the same changes on sql2005!

    I havent installed sql2005 yet. Not sure if sql2005 has those settings or not.
  14. joechang New Member

    It is true that -g512 can make things better,
    it is the fact that they do not understand why they needed this that is of concern,
    so they were probably perplexed with strange drastic performance degradation for no apparent reason until they somehow stumbled on to this setting

    which means they do not have serious performance analysis skills, and are likely to be hit with future problems for which they will have stumble around,
    and never fix the underlying problems

    but the other question: is this a critical application? if not, don't worry about it
    there are also legitimate applications that do not need transactional recovery,
    some apps bulk load data from files,
  15. cmdr_skywalker New Member

    who hired to id..ts anyway!? <img src='/community/emoticons/emotion-1.gif' alt=':)' />. First, try to determine where is your accountability lies. Don't throw a wrench on a running engine (because its already mess up) cause if somethings goes wrong, who's in the firing squad? <img src='/community/emoticons/emotion-1.gif' alt=':)' />. I don't mean not to find another vendor or suggest something to your boss. By all means, go ahead. You can start capturing the query using profile analyzer. Then create a separate database in your workstation (or test server) with the database improvements you made (indexes,etc.) and show the difference to your boss. That way, you have a smoking gun and in better position to go against your vendor <img src='/community/emoticons/emotion-1.gif' alt=':)' /> (without something to compare, apple-to-an-apple, you are considered second guessing).<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  16. FrankKalis Moderator

  17. eramgarden New Member

    The orig database is AS400, RPG stuff. Seems like the vendor has applied the same logic from AS400 to SQl Server. For example, they've made all the fields "chars" and "numeric" and created a default date of "1753" even tho SQL has a default date of 1900. No PK and No FKs.

    I will do what Skywalker suggested. Try making the database locally and then show my manager the difference.
  18. eramgarden New Member

    One more thing I'm noticing also is they've used different names for the same key.
    For example, xCode is a key to a table but that key is also in other tables. Instead of refering to xCode thru out the tables , they've created yCode, nCode, whatever.

    Now, i think that's bad design but how about performance wise? would creating extra field names bad for performance?
  19. spacemonkey New Member

    You not having any primary keys means all you have are heaps. In order to find anything your server must fully scan the tables and use no shortcuts. This will kill your performance in some areas.

    For no foreign keys, I would be most concerned about data integrity. Foreign keys are helpful to make sure the data you are entering is accurate and matches other data in the other reference tables for your queries. (Plus Normalization blah blah...)

    I would be worried about the lack of checks on tables. Without PKs and FKs, (and probably not unique constraints) you could have some serious problems with what is returned by queries. You could potentially loose records and never know it until it starts affecting your company and you loose clients.

    As you said, there are other problems but these are some big ones. In the end, it boils down to loss of profit for your company.

  20. eramgarden New Member

    They have unique indexes..instead of..for example..PKs.
  21. cmdr_skywalker New Member

    You might be seeing a temporary denormalize loading table. Again, traverse with care, look at the captured queries of profiler, then you can decide if that particular table is normalize or denormalize. Most of the time, an OLTP table is in 3rd Normal Form and thus, no repeating columns. You are probably seeing the tip of an iceberg. The flaw may also include the application and breaking unknowingly the assumptions between application and supporting database is dangerous, if you don't know exactly what to break <img src='/community/emoticons/emotion-1.gif' alt=':)' />. Again, gather the SQL codes first and analyze them. <br /><br />By the way, Unique allows null, PKs dont. Changing the other may break things in your app that you don't want to do at the peak of production run.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  22. eramgarden New Member

    I turned on Profiler. I see "encrypted text" and some sql that seems like doing some sort of cursor stuff.
  23. joechang New Member

    if you see calls like sp_cursoropen, sp_cursorexecute
    those are API server cursors

    this is because the client app does not fetch the entire result set, but a gets 1 or a few rows per call.
    this is very bad client side programming,
    i would advise ditching this application if practical

    in any case, its probably ok to have this app on its own dedicated SQL Server system
    do not share it with an important application
    don't waste money on a expensive machine for this, it will not scale, a 2 cpu box at most,

  24. eramgarden New Member

    Yes, the company is planning on rewriting the app in .Net but I think the database needs to be fixed as well..

    One more thing I see which I think is wrong but need to make sure before I say anything to my manager:

    I see, for example, TableA.
    2.Then the vendor has created 2 views: TableA1_view and TableA2_View.
    3.They've created indexes on TableA and named them "TableA1_view" and "TableA2_view".
    4.In the app, they use the 2 views to call TableA. Their thinking is that..they're using the indexes when they use the views!!

    I think in this case, the views are NOT indexed. Naming an index after a view and then using that view..doesnt mean they're using that particular index associated with the view.

    Am I right?
  25. Luis Martin Moderator

    It depends what thery wrote in a view. May be the view use indexes in table.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  26. cmdr_skywalker New Member

    or they are using the old principle in Access, converting the select query to views <img src='/community/emoticons/emotion-1.gif' alt=':)' />. Check what's the condition on those views.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  27. eramgarden New Member

    Some of them have conditions but mostly straight select statements.
  28. cmdr_skywalker New Member

    SELECT * OR SELECT col1, col2, ...? IF as simple as SELECT * FROM TABLE1 then you may have smoking gun. However, if it is SELECT COL1, ... FROM .. WHERE.., don't be so quick to destroy those walls unless you know why they were created. <br /><br />My suggestion to you if your company will overhaul the application and database, is to finalize the requirements and design of the data model first. Requirements identifies the business objectives. The design helps build the business objects/entities, attributes and their relationships. Once you have the design nailed down, you can then look at what are the views, indexes, etc. that should be developed/dropped to support the application, reports, other requirements, etc. Then you can look at the application, reports, codes, etc and see what needs to be enhanced/replaced/etc. You start from the requirements -&gt; data model because changing the data model is much harder compared to changing the application/reports (i.e. there is always an EMPLOYEE table but not necessarily and EMPLOYEE module). Although you should avoid doing design changes when your on the testing phase/turn-over because they all cost money (and at times, someone's head)<img src='/community/emoticons/emotion-1.gif' alt=':)' />. So remember, <br />1. know what you want. business objectives/requirements drives the technical solution. <br />2. know your data. most company don't know what data do they have and end up developing applications/procedures/etc. that can't be reused. Tsk, tsk, tsk, what a waste of time, people, and money.<br />3. names are destiny. choose the names well. good naming convention helps more than explaining what up doc.<br />4. have a plan. from project plan to enterprise architecture to technical infrastructure. It helps in buy-in.<br />5. Use good tools, keep your people informed, and remember that communication is a two way street. DBA/SysAd/BusinessAnalysts/SystemAnalyst/Developer, all have roles to play and should play it well <img src='/community/emoticons/emotion-5.gif' alt=';)' />. <br /><br />Don't be too hard on your vendor, smile <img src='/community/emoticons/emotion-1.gif' alt=':)' />. Its better to know that even the bridge is burned, you can still walk across it if you need to (because only you knows that the bridge is burned <img src='/community/emoticons/emotion-1.gif' alt=':)' />). <br /><br />I think the rest if for your project manager and your team to deal. You probably heard about key project sponsors/champions, risk/qa/config/etc. plans, adv. planning doc, ..., which is way out of league on this thread <img src='/community/emoticons/emotion-5.gif' alt=';)' />. <br /><br />Wish you the best and good luck.<br /><br /><br /><br /><br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  29. eramgarden New Member

    I opened up the 198 views the vendor has created. Of those, only maybe 10 have "where" clauses like this:

    WHERE [A1MODE] = '00000' OR (NOT( (1=1) ) AND((1=1)))
    WHERE (NOT([D2DEL] = 'I') AND( (1=1) ))

    For example, on one table, 18 indexes are created. Of those, 17 "index names" match the name of 17 views. Then of the 17 views:
    4 have "where clause "like this:
    WHERE (NOT([D1DEL] != ' ') AND([D1COLL] = '99999' OR (NOT( (1=1) ) AND((1=1)))))
    WHERE (NOT([D1TOT] <= 000) AND( (NOT([D1COLL] = '99999') AND( (NOT([D1XDAT] = '0001-01-01') AND( (1=1) ))))))
    WHERE (NOT([D1TOT] <= 000) AND( (NOT([D1COLL] = '99999') AND( (1=1) )))
    WHERE (NOT([D1TOT] <= 000) AND( (NOT([D1COLL] = '99999') AND( (1=1) ))))

    The rest of the views on that table are just straight "select allcolumns from table".

    just doesnt make sense at all . I think those indexes with the same name as views have no meaning..it does NOT mean the view is using the index.

    am I right?
  30. FrankKalis Moderator

  31. eramgarden New Member

    I'm new at this job. The vendor is IBM/AS400 vendor and now they're supporting SQL Server. I think they're trying to emualte AS400 functionality to SQL Server.

    For example, in AS400, there's something called "logical file". Vendor told them this translates to "views" in SQL Server. AS400 defines "logical files" as :

    "The logical file contains no data records. It contains the corresponding record number of the data record in the physical file. The logical file will contain the index to the physical file. Logical files provide the path to the physical file"

    So they got happy go lucky with the views and just created whatever they wanted.

    That's the only explanation I can come up with.

    My manager should ask for his money back!
  32. joechang New Member

    you have already mentioned enough items to say that this application should not share the same server as any critical applications

    however, does this application do its intended function adequately?
    if so, there may be no point complaining to the vendor as they will have no incentive to fix anything
    if not, is there any indication that they will fix things
    or is your company willing to switch vendors or build a custon app
  33. spacemonkey New Member

    Does your boss have a good understanding of what this vendor has delivered to you? It hurts to look at some of their code.

    Best case scenario, the vendor is inexperienced in this area and little good can come of continuing to do business with them. If there is room for advancement, maybe you can outshine the vendor to the point of a raise/promotion?

  34. cmdr_skywalker New Member

    It seems you have the following problem: <br />1. optimization/logic<br />2. naming convention -&gt; maintainability<br />etc., etc., etc. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />For instance, I tried to convert them and see what its like:<br />WHERE [A1MODE] = '00000' OR (NOT( (1=1) ) AND((1=1))) =&gt; WHERE [A1MODE] = '00000'<br />WHERE (NOT([D2DEL] = 'I') AND( (1=1) )) =&gt; WHERE [D2DEL] &lt;&gt; 'I'<br /><br />WHERE (NOT([D1DEL] != ' ') <br />AND([D1COLL] = '99999' OR (NOT( (1=1) ) AND((1=1)))))<br /><br />equivalent to<br />WHERE [D1DEL] = ' ' AND [D1COLL] = '99999' <br /><br />since NOT (1=1, Always True ) = Always False<br /><br />The '0001-01-01' must be the default format of the AS400.<br /><br />At anyrate, you really have to find another vendor. You may translate some of the views but overall, I am not sure how much good you can make <img src='/community/emoticons/emotion-1.gif' alt=':)' />. You might be better off spending the money developing the new system instead of spending X dollars to patch new cloth into an old rag (not dress).<br /><br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  35. FrankKalis Moderator

    I would be very careful. Depending on how "political" this system is, it might turn out a boomerang for you. You have to document everything in order to be able to prove your points and I guess you have to operate sensibly when talking to your boss and the vendor. Don't know, if I can express myself so that you understand me, but you know there's a huge difference when you say to someone:<br /><br />"Your application is completely sh*t. It is nothing but crap and not worth the money!".<br /><br />or when you say:<br /><br />"I think you can improve your application in one or two aspects by doing...."<br /><br />In the first case, if I were the vendor I would completely block everything you say, while in the second case I might have an open ear for your issues. <br /><br />Hope I've made myself clear. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Share This Page