I am far from being a SQL expert and not really sure where to start for troubleshooting perfomance isses with SQL and FactorySQL.
I have an assembly line which has been running full production (24x6) for a little while, but they have consistently had problems with slow response or timeouts. Here is the setup:
5 Siemens PLCS handling 4 to 8 stations each
FactorySQL (latest version)
SQL Server 2005
All three software packages running on Windows Server 2003 R2 (dual core Siemens IPC hardware)
About 60 transactions happening simultaneously
Primarily the transactions save data, so the Recovery model is still in Full mode. I read Colby’s article on bogging down due to the log, but was not sure if I should make that change.
My Server is seeing a constant bouce from 40% to 100% utilization with SQL showing 50% of that)
I am now seeing a lot of this error, too, in FactorySQL: Transaction (Process ID ??) was deadlocked on lock …
Each table has 100 - 800 thousand records, which I don’t think is too bad. But, I realized I don’t have very good indexes, so I will start trying to add these, but any suggestions or reading would be appreciated.