SQL Server Performance

Using Magic with SQL

Discussion in 'T-SQL Performance Tuning for Developers' started by kramer, Sep 27, 2005.

  1. kramer New Member

    Sorry not the Penn and Teller kind but the development tool Magic edeveloper 9.x

    Has anyone used this and if so what kind of techniques did you use for batch tasks etc...

    i am currently involved in a project but having a number of problems with CPU usage on SQL server machine

    I think it is because of the cursor approach the magic suite is using to do batch tasks.

    The SQL machine is
    SQL 2000 sp2
    2GB ram (1gb allocated sql)
    1 x Xeon 3.0 Ghz
    Windows 2000 sp4

    CPU usage hits 100 % a lot of times during the day... and at these times queries start timing out and rollback start and user machines crash.

    The Magic application is tasked with doing a number of automated data tidy ups etc during the day constantly... a number of key tables have client cursors travelling down them looking for specific flags and processing across joins should the flags dictate.

    I have suggested a more set based approach would mean a less cpu intensive mechanism but have been told this is the best way for Magic to do these tasks ...

    Can anyone throw some light on this...

    Thanks in advance


    K

  2. satya Moderator

    It is not a good application as per my experience, as I have had lots of troubles when used with SQL because of poor written queries from Magic.

    Anyway in your case try to increase more physicial RAM and keep memory to dynamic for SQL Server.
    Ensure the index optimization jobs are running regularly to keepup optimum performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. kramer New Member

    Hi thanks for your reply

    When you mention index optimiztaion jobs can give me a bit more information.

    I occassionally run an index defrag job when i notice any problems but they seem fine and then once a week a maintenance plan index optimization routine...but this is out the box.
    Do you think this is sufficient?

    Thanks
  4. satya Moderator

    We used to run DBCC INDEXDEFRAG to keeup the intermittent performance, but with DBCC DBREINDEX the performance used to be stable for a day or two. Due to the poorly written queries it couldn't help much further.

    In your case you might look into adding up more physical memory to tackle resource intensive queries. If possible get hold of Magic developers and optimize the queries. You can use PROFILER, PERFMON and PSSDIAG tools to collect the information on slower performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    I hate Magic and have had several problems with it as well. Anytime a vendor, or anyone for that matter, tells you cursors are the best way to do something, they don't know what they're talking about. What are the exact batch processes you're having trouble with on the system?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page