Community
Showing results for 
Search instead for 
Do you mean 
Reply

URGENT!!! SQL slowdown

Tuned Listener
Posts: 35
Country: Belgium

URGENT!!! SQL slowdown

I've posted this item a e few weeks ago but I repost it because it's getting worse. Our SQL server peaks with everything we do in ACT.

This since a few months, after upgrading to ACTv10.0.3182 hotfix 1. But in the beginning it was not that high.

When we schedule a todo, meeting, etc the SQL peaks to 100%. When we sent a mail the SQL starts peaking to 100%. Attach a shortcut, delete a shortcut.

I've made a few printscreens.

http://www.europowergenerators.org/ACT/20090331-ACT peak 01.bmp

http://www.europowergenerators.org/ACT/20090331-ACT peak 02.bmp

http://www.europowergenerators.org/ACT/20090331-ACT peak 03.bmp

 

We changed the amount of processors true ACTdiag to 2 processors, but the SQL keeps using al processors. We need urgent help about this topic.

 

Can we go back to the previous version? Is it recommended? What can we do?

 

Thanks for the quick response

 

kind regards

 

Jurgen Poelmans

 

Moderator
Posts: 4,395
Country: USA

Re: URGENT!!! SQL slowdown

How many users are accessing the database?  How large is the database?  Try running through the repairs from actdiag. You can also try reinstalling SQL.  The steps are in the second KB.

 

Database Rebuild and Repair Procedures

Error: "Reset Error: System.InvalidOperationException: Not allowed to change the 'ConnectionString' ...

Tuned Listener
Posts: 35
Country: Belgium

Re: URGENT!!! SQL slowdown

We're using ACT with 25 to 30 users. We ran all the repairs from actdiag, no change. Our dbase is around 1Gb, with 12000 contacts and 280.000 attachments. The mapsize of the attachments is 15Gb. Every night we run a maintenance true act scheduler. We have also done a test on a testnetwork and there we get the same problem. Is it possible that our dbase and map attachments is getting to big? On our testnetwork we reïnstalled the SQL because we thought the problem was with the SQL but this also didn't resolved the problem.

We also removed all the cleared activities except from the last 30 days, but there are still activites cleared from 2006 which we can't remove because act wont delete them.

Are there 3rd party tools to fix (check) a dbase trouble or what are the remaining options?

We are not saying that the dbase is corrupt, but we wont to exclude that this is the problem.

The dbase is converted from act2000 to act2007 to act2008.

 

kind regards

 

Jurgen Poelmans

Nickel Contributor m4y
Nickel Contributor
Posts: 515
Country: Belgium

Re: URGENT!!! SQL slowdown

It is obvious that you have a lot of attachments and a heavy attachments folder.
I'm surprized that your database is 1Gb for only 12.000 contacts. How much companies? Do you have a lot of fields? Is your structure optimized?

Run the "field usage analysis" in actdiag and see if all fields are optimized.

Is it possible to think about deleting some histories? You could keep a database with old histories and cleanup the production database.

If you make a full copy of your database (file > save as), is the size of this new database a little smaller?

Maybe you have some integrity issues with your multiples upgrades, especially since the 2000 > 2007 migration. A strong and meticulous migration could take a long time and is recommanded to avoid future issues...

Xavier Nyssen

ACT! Certified Consultant @ Intuitis | Belgium
{ +32 64 45 08 00 | xavier@intuitis.be | http://intuitis.be }
Tuned Listener
Posts: 35
Country: Belgium

Re: URGENT!!! SQL slowdown

We already deleted everything till 2 years ago, but the problems remains. We ran the "field usage analysis" and there were 15 fields to long. We get for a lot of fields the following discription: "Betalingstermijn, CUST_Betalingstermijn_113931566, Character, NVARCHAR(50), 372, 2%, 31, 5, 13, Low column usage, Review need". What does this means? Do we have to shorten these fields also? 

 

You're talking off a strong and meticulous migration, what do you mean by that? Is there a tool to do such migration? When we save our dbase, it becomes a lot smaller. How is this possible? When we reindex the size is almost the same?

 

Jurgen Poelmans

Nickel Contributor m4y
Nickel Contributor
Posts: 515
Country: Belgium

Re: URGENT!!! SQL slowdown

"Low column usage" means that you don't really use this field. For example, your field "betalingstermijn" is not empty for only 2% of your database, 240 contacts of the 12.000! So, you have to define if its content is so important that you require a field for that, of its content could be recorded as a note or a history...

Regarding the meticulous migration, as ACC for long years, we developed our own migration process and it includes several steps. There is no tool for that, this is the result of our experience with the product and all its versions since ACT! 3.0.


Xavier Nyssen

ACT! Certified Consultant @ Intuitis | Belgium
{ +32 64 45 08 00 | xavier@intuitis.be | http://intuitis.be }