Using Magic with SQL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Using Magic with SQL

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
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.
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
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.
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
[email protected] When life gives you a lemon, fire the DBA.
]]>