SQL Tags, MS SQL, Deadlocks

Archived Topics
1

First off I want to say sorry for writing so much but with the recent issues that have come to be I felt it best to obtain expert opinions.

Next I dont know how to do it on this forum but I would like if other integrators and designers would be willing to post answers to the following questions:

  1. How many tags do you have recording data to/from the PLC’s?

  2. How many SQL tags do you have configured?

  3. What is the frequency you are collecting data from item 1?

  4. What is the scan rates set for your SQL tags?

I ask these because I have about 7500 tags from Kep to FSQL that are logging at random times, sometimes at the same time depend on circumstances. We also have about 1200 SQL tags and they are firing at random intervals but even they have the option of all firing at the same time depending on circumstances.

Our install is: One server (at this time) - On this server is everything - FSQL, FPMI, MS SQL, and Kep.

While doing some testing recently we noticed a large sluggishness in some of the queries. We ran some after market products on the SQL server we found that at times we were getting what was refered to as deadlocks. We are thinking we are overtaxing the MS SQL. I mean we have the FSQL dumping into the historical table, then we have all the reports comming off of these tables, we also have the SQLTags going to the same instance, and all the data for the PMI comming out of the instance as well. We are thinking this is why all the dead locks are occuring and also why we periodically peg the CPU usage on the server while running queries and why there are random missed records in the historical data base.

One of the things we are looking at is having the SQL tags go to a different server. The reason being is when we ran the product DEADLOCK DETECTOR a lot of the red balls there were occuring when deadlocks occurs most seemed to be off of the SQLT_* table. Are there any other recommendations? especially from some of the larger scaled project out there?

Thanks and have a great day.

2

There’s no reason that you can’t get a project of your size to perform well. My thoughts:

  1. I doubt that the SQLTags tables are the cause of your deadlocks, even though they may be flagging. The reason is that the SQLTags system frequently performs a fast query - look at the gateway page for numbers. My guess is that external problems will indicate problems in the SQLTags queries.

  2. One thing that can hammer database systems is really slow queries, typically written without “LIMIT” or proper “WHERE” clauses. This can often happen if you don’t have appropriate fields keyed. Places to worry here are with reports and graphs. FSQL logging is lightweight on an SQL server, but poorly formed large reads are not.

  3. Another thing to look at with SQL server is the transacation log that allows undo-ing queries. It’s a great feature for low volume, high importance applications, but irrelevant when you have lots of little updates (like temperature changes). Look to disable this feature on tables where appropriate.

  4. All that said, I’m a huge fan of the “Scaling out” approach of moving databases to other computers. This is totally seamless to the user. I suspect that your history table is a good candidate to move to a separate server. SQLTags maybe…

Bottom line - you need to determine exactly what’s slowing down your system. Check RAM, the drive where data is stored, etc. Excessive paging is bad. The best place to investigate is by watching queries including how long they take to run. Do any other applications use that database?

Figure out where your bottleneck is, then you can expand to accomodate. IT should be able to help you - be sure to “de-mythify” the software~there’s nothing magical going on in the background.

3

To answer your questions and provide feedback here is what I can say:

  1. The reason the IT dept is looking at the SQL tags is the fact that there has been tried two products, one is SQL diagnotic and the other is SQL Deadlock Detector. From using these two product it was noticed that there was a lot of deadlocks and deadlock wait timing on this server. When we compared the performance to other SQL servers there was no deadlocks nor deadlock wait times on those servers. When we utilized Deadlock Detector the vast majority of what we seen, and these were the red flagged queries, were queries that contained the table SQLT_CORE. Upon looking at this forum site the IT dept and I seen that this was the core table for SQL Tags. So it was beleived that this was causing the deadlocks since this is what was reported.

  2. There are a number of Tables and Views in this instance of SQL. Some of these tables “Jump” across to other SQL instances to get data from there. One view that is on this is the JobTask view. We have a SQL server dedicated to MTSoft which is our MES system. All order entry and such goes into this system, so all mail jobs are placed here. Then the information is filtered over to our datacollection for use there. Of course another good example would be the employee database, this is fed from the CeleriTime Server which is our timeclock system. Now as for the views, tables and other yes there are quite a few queries that use 3 or more view and/or tables. These are the really sluggish queries and while running these this is when you get TIMEOUTS and SQL SERVER NOT CONNECTED errors on the IA software system. As I mentioned there are quite a few reports that come off of this data contained in this SQL Instance, some of the reports are in Crystal and some are in IA but all use extensive joins and unions and there are some that use the where clause, some on the server and some in the FPMI depending on what is needed out of the table. One thing we did do was to see what all data was needed and where and then instead of creating many, many, many views, we would take the information into one table then separate what we needed at the PMI level since most of the data is the same.

  3. As for the transaction log, I know our IT staff has been watching it closely, mainly because of what happened before we went live with the system. If oyu look on this forum site you will a posting many months back where our transaction log grew so large it caused all kinds of problems.

Now onto the paging and Ram issues, the software products we ran on the SQL instance also have nice little graphs depicting memory usage, hard drive usage, etc… One of the things was that there was no paging and all processes were using memory allocation. Right now the server is running Windows server 2005( i think it might be 2003), SQL 2005, it has plenty of hard drive space, and although it only sees 4 gig of memory the system actually has 6 gig available.

As for the bottle necks, dont these deadlock create or were created by bottlenecks? and do you have any better software products that might be faster or more efficient than the ones we are currently evaluating? because if the SQLT_CORE is not the root cause then why is it that any query pertaining to that table is red flagged with deadlocks?

Thanks and have a great day.

[quote=“nathan”]There’s no reason that you can’t get a project of your size to perform well. My thoughts:

  1. I doubt that the SQLTags tables are the cause of your deadlocks, even though they may be flagging. The reason is that the SQLTags system frequently performs a fast query - look at the gateway page for numbers. My guess is that external problems will indicate problems in the SQLTags queries.

  2. One thing that can hammer database systems is really slow queries, typically written without “LIMIT” or proper “WHERE” clauses. This can often happen if you don’t have appropriate fields keyed. Places to worry here are with reports and graphs. FSQL logging is lightweight on an SQL server, but poorly formed large reads are not.

  3. Another thing to look at with SQL server is the transacation log that allows undo-ing queries. It’s a great feature for low volume, high importance applications, but irrelevant when you have lots of little updates (like temperature changes). Look to disable this feature on tables where appropriate.

  4. All that said, I’m a huge fan of the “Scaling out” approach of moving databases to other computers. This is totally seamless to the user. I suspect that your history table is a good candidate to move to a separate server. SQLTags maybe…

Bottom line - you need to determine exactly what’s slowing down your system. Check RAM, the drive where data is stored, etc. Excessive paging is bad. The best place to investigate is by watching queries including how long they take to run. Do any other applications use that database?

Figure out where your bottleneck is, then you can expand to accomodate. IT should be able to help you - be sure to “de-mythify” the software~there’s nothing magical going on in the background.[/quote]

4

Do those tools happen to show you the queries that were executing when the deadlock occurred? We’ve had various scattered reports about problems with deadlocks on SQL Server, so we’re trying to gather more info in order to see if there’s any pattern.

Also, check the FactorySQL error log to see if there are any messages about deadlocks. If so, export the log and send it to support AT inductiveautomation.com

Regards,

5

Yes the one does show the queries but not in a format I am use to seeing. That is how we know it is the SQLT_CORE table. When one of these deadlocks would occur it flags a red flag and then you click on it and there are other details where is would should what was attempting to run at the time. I think our IT dept still has a copy of the file they created using this program and if they do I might be able to get them to forward it to IA as well. They created this file to send to the company that wrote the software so that they could help us decipher what all information was being presented. The name of the software is called DEADLOCK DETECTOR.

As for the FSQL I was only able today to get thru about 20 of the groups, but none of the groups had any error messages in the groups status. They all reported no problem or what ever that message is.

As for today one of the things we are trying is we place mySQL on a seperate server. Took all the SQL tags and redirected them to that server. Then we monitored the MS SQL where the tags origionally resided. There was still a couple of yellow flags which are warning flags but the red flags were not present. So we might still have some issues but at working thru it as best we can.

Hope this answers all your questions. Have a great day.

[quote=“Colby.Clegg”]Do those tools happen to show you the queries that were executing when the deadlock occurred? We’ve had various scattered reports about problems with deadlocks on SQL Server, so we’re trying to gather more info in order to see if there’s any pattern.

Also, check the FactorySQL error log to see if there are any messages about deadlocks. If so, export the log and send it to support AT inductiveautomation.com

Regards,[/quote]

6

Hi-

If you just go to Help->Log Viewer, you can see any errors reported by the groups, and more importantly in this case by the SQLTags system, where the errors are occurring. I would guess that there would be some errors mentioning deadlocks, and just wanted to confirm that they were similar to what we’ve seen before.

So far, it seems to be a problem with how SQLTags “updates external tags”, that is, gets values from the database for db static tags and tags driven by other tag sources. There’s nothing inherently wrong with FactorySQL, it’s just something about the query it uses that SQL Server doesn’t like from time to time. When you research these deadlock errors, the common suggestion is to “catch them, and retry until it doesn’t happen”, which in essence is what happens, but to me doesn’t sound like the right solution. I’d like to know why it happens and prevent it.

Anyhow, all you need to worry about is trying to get some confirmation as to the query being run at the time.

Regards,