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
Siemens OPC
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.
The deadlock error happens mainly on tables that have many reads occurring at the same time as writes, and is caused by how the indexes are set up. If you can figure out which query it is (perhaps by posting more of the stack trace), we might be able to figure out how to improve it.
At any rate, problems like that are made worse by high load or otherwise inefficient queries. I don’t have a lot of experience with the SQL Server performance troubleshooting tools, but I believe they’re pretty good, so hopefully Travis or someone can post with a bit more info.
Creating proper indexes on your tables can make a huge difference (like, 30 seconds down to 10 ms). However, you must be careful not to make the beginner mistake of just thinking you can index everything. Too many or incorrect indexes can range from wasting disk space to actually hurting performance.
Step 1: Identify where the load is coming from. Most likely, it is from the querying of data. Who queries the tables, how are the queries written, and how often are they running?
Of course, you should also look at how long it’s taking to insert/update data. The easiest thing to do is pick a couple groups and look at their statistics from Connection>System Status>Statistics, and then “DB Writes” under the group. Looking at the overall execution cost of the group can be useful as well, in case the group is performing any selects.
Step 2: Once identifying what’s slow, we can work to figure out what the best indexes would be.
Before you touch anything I’d profile your existing system. You probably installed SQL Server Profiler when you installed SQL Server. You can quick start on the MSDN page: msdn.microsoft.com/en-us/library/ms181091.aspx