Solved

The ULTIMATE CHALLENGE:  SQL Database performance issues with Goldmine CRM

Posted on 2010-08-16
49
2,055 Views
Last Modified: 2013-11-15
We have an issue with a customer that's going on over a year now.
For the past year, they've been having terrible performance issues with Goldmine installed on 2 client PCs that use a SQL database on their server.  I'm going to provide as much info about their setup as possible to help whomever reads this get the best idea of our setup.

Goldmine connects via ADO.NET to this DB.  This DB is very large (4GB for 2 users).  This is because they've been using Goldmine for 15 years and are in graphic design, so their email attatchments are large.  
They're running MS SQL 2005.
The company has a dedicated Goldmine technician who's also been unable to pin-down the issue.  We've contacted Frontrange, and they of course can find no issue.  He has completely re-created the DB from scratch, run performance tests and consulted others.  According to him, the DB is performing fast.  

At completely random times throughout the day, performance slows to a crawl.  It may take 10 minutes for them to switch between emails when they click one in their inbox.  Sometimes it's 10 seconds, and for days it will work fine for both users.  One user can be running fine, while the other is at a standstill.  Both users could have days w/o problems, then both will experience the issue at the same time.  There is no rhyme or reason to it, and it's starting to jeopardize our relationship with them.

We have tried literally everything to try to resolve this.  We've replaced their switch w/ a new gigabit Netgear, rewired their CAT5, replaced NICs, installed new desktops, moved the Database to it's own  own Windows server 2008 R2 box, ran ping tests from clients to the dedicated SQL server over 3 days which dumped to txt file w/o a single dropped packet.
We've turned off AV software for a week at a time on both the client PC & server with no change.  They have 2 WAN connections so they won't loose connectivity.  They're of course always running the latest version of Goldmine & all servers have the latest OS updates.  I can't find anything that may be related in event logs, or program logs.

I know we've done even more things to resolve this, but as it's been an entire year I can't recall everything we've done anymore.


The only thing we've been able to say for certain is that Goldmine is loosing connection to the database when this happens, somehow.  Sometimes it will wait up to 10 minutes & reconnect.  Sometimes it gives an error that it's dropped the connection.  
RANDOM RANDOM RANDOM!
I've never experienced this level of difficulty resolving an issue before.  We highly value this customer and are at our whits end with this.  We will be posting for help from Rent-A-Coder for some kind of SQL expert to have a look at this, but I wanted to post here as well, now that I have more information about how Goldmine works.

PLEASE, I need the best of the best on this issue & will be awarding as many points as possible for any kind of help to resolve this.  The problems are killing their productivity.

Also, please don't respond with help-desk level suggestions like "try rebooting the server."  We're not SQL knowledgeable, but we're not idiots either.  
[b]I NEED REAL ANSWERS![/b]
I'm not sure of the exact rules here, but we're willing to compensate someone beyond points if they can provide real help on this.

Thanks for taking the time to read this and for any help you can provide.
0
Comment
Question by:bryanchandler
  • 11
  • 10
  • 9
  • +6
49 Comments
 
LVL 8

Accepted Solution

by:
russell124 earned 63 total points
ID: 33450402
Have you investigated hardware usage issues, such as memory usage, disk usage, memory paging etc via performance logging?  Have you tried using the SQL performance monitoring tools?  

How much memory is your server running? What is your hard drive layout on the server?  What RAID are you using?  Is the database on a separate raid volume from the system partition?
0
 
LVL 9

Assisted Solution

by:the_b1ackfox
the_b1ackfox earned 63 total points
ID: 33450451
Every couple of years or so I come across a DB that is performing horribly.  It seems as though everything is running fine, but the App running on top of the DB runs slow, off and on (Sound familiar?  I think it does)   Anyways, what I usually find is that the DB has grown to the point it is ready to overrun the alotted space (and the db is adjusting itself), or the log file has grown to the point where it is bigger than the MDF file.  Shrinking the log file usually clears this up.    Can you go into the management studio and databases, select the DB in question -> properties, and tell us (under general the space available, size, last db backup and last db log backup), and under files (database file type and initial sizes fir the files listed?), and under Options, what is your recovery model and compatibility level?

Fox
0
 
LVL 9

Expert Comment

by:the_b1ackfox
ID: 33450461
PS, your DB is not large at all, I have Databases over 30 GB in size.  Still I would like to know thw answers to russells questions too
0
 
LVL 3

Assisted Solution

by:mnachu
mnachu earned 63 total points
ID: 33450522
My $0.02.

We had a similar problem were our DB would slow down once in a while. We figured that our main database and our temp DBs file (I think log file) was on the same SAN disk so whenever the temp DB grew our main database crepped because there was not enough space in the SAN for both together. The problem you describing could be that too. But, we never had a connection issue.

Experts before me definetely sound more knowledgeable so I would go they route before even thinking what I mentioned.

Regards,
Nachi
0
 
LVL 17

Assisted Solution

by:sgsm81
sgsm81 earned 63 total points
ID: 33452156
* Starting with the basics, are you running the latest versions of SQL, patched etc
* Are there any other services running on the server that could be creating a bottleneck
* If you save the event logs then clear them (so you can easily identify problems) does anything show up in  
  them when the problem occurs ?

Have you tried temporarily installing SQL 2005 express onto a workstation, copying the goldmine database there, making it live and running that to see if the problems remain (this will help isolate the problem or at least identify it)

I had an instance whereby a SQL database would fall over if installed on the server but if it was on an XP workstation with a cut down version of SQL it ran fine
0
 
LVL 12

Assisted Solution

by:GMGenius
GMGenius earned 62 total points
ID: 33452991
My question is , what is the version of GoldMine you are running and do you have any sync users?
If you have no syncronizing users you could try truncating the TLOGs tables , this is where GM will record all field level changes and can grow VERY large and cause bottlenecks because of the volume of rows
to truncate them - open SQL managment studio and execute the following commands
truncate table GMTLOG
truncate table CONTTLOG
you could also truncate the SYSLOG table - this holds system logs, these usually are copies of the process monitor window (group building, email collection etc)
Once truncated the database will have some free space so you could manually shrink it or leave until next backup.
Another question. do you have a maintenance plan running at the wrong times? or even at all for that matter.
0
 
LVL 11

Assisted Solution

by:stevengraff
stevengraff earned 62 total points
ID: 33453276
Ask the users if either of them are running reports in GoldMine, partiularly History reports. The built-in reports have been known to cause hangups for all users even if just one user runs a report. If so, the work-around is to re-do the report using Crystal.
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33453586
You mention 4 GB -- which happens to be the max, I think, for SQL express. Could it be that they're using SQL Express, rather than "full" SQL? And could this behavior occur if they were bumping into the 4 GB limit?
0
 

Author Comment

by:bryanchandler
ID: 33458999
@russell124:
The DB is currently running on a dedicated Dell Poweredge T-105 Windows 2k8 R2 server w/ 4GB of RAM, on a 350 RAID-1 single C: drive.  It's sole function is to host the DB.  The problem is that we've moved it to about 5 different boxes (virtual & real) without a single change in behavior.  It neither improves or gets worse.  I don't think it's an issue with the server not having enough mojo to do the job.

@stevengraff:
We've been hyper-observant for any kind of cause/effect events when these slowdowns happen.  There are none.  There's no AV scans, backups, Volume Shadow Copies being created, and no scheduled tasks running on any system during the day when it happens.
The DB runs on MS SQL 2005 FULL, not express, so that's not the problem.  

I've always had the latest updates installed on the servers we've used.
This issue keeps happening NO MATTER WHAT we've done.  If it were a missing update, something would of changed when I installed it later.

For everyone else who's replied, thanks a lot.  Unfortunately, I'm completely ignorant about SQL (or databases in general), so I can't answer any of these technical questions for you.  I will have the Goldmine tech review your replies & see if it gives him any ideas or answers to your questions.


 
0
 
LVL 8

Expert Comment

by:russell124
ID: 33459378
I would still be interested to hear if you have enabled any performance logging items.  Maybe you need to hear back from your Goldmine tech, but the performance counter logs will be able to tell you if you suddenly run out of memory and experience a large number of pages, if you have a high number of queued disk reads/writes, various SQL performance items, etc.  These counters, when enabled, will run and record data, and they can be very helpful when you experience your performance issue.  You can go back and check the performance logs for any issues at the same time the data issues happen.  If you need help enabling the performance logging, let me know.  

I can give you some good performance items to log from a Server/Hardware standpoint, and I'm sure the SQL guys can recommend specific SQL items to be monitored as well.
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33459477
Bryan, you answered one of my questions, but not the other. I understand it's full SQL, not Express. But what about users running reports? From within GoldMine, many reports are possible to be run. It seems like a completely innocent activity to the user. Yet, since the introduction of SQL versions of GoldMine, the internal reports, especially History reports, have caused the system to behave as if it's been "brought to its knees," which is exactly the symptom you describe.

If there's no other reason apparent for this, let's ask the question. If the answer is "yes" then that's probably the smoking gun you're looking for. If not, well, I guess you keep looking.

Also, for specific clarification, you said they're running the latest version of GoldMine... that's 9.0 -- confirmed?
0
 

Author Comment

by:bryanchandler
ID: 33459636
@stevengraff:
Yes, they just upgraded a few weeks ago to 9.0.  We were hoping beyond hope this would be a magical fix, but it wasn't.
I asked in an email to both users if they run any kind of reports in GM.

russell124:
I would like some help setting up these performance monitoring logs.
Are these to be run on the server or the client PCs?
It just seems odd that it'd be some kind of hardware-based performance issue, as we've replaced all the hardware involved except for the SBS 2k3 DC which the DB formally hosted. (the DB has been off of the DC for months now.) and haven't seen any changes.
0
 
LVL 8

Expert Comment

by:russell124
ID: 33460781
You can enable the performance monitoring on both the server and the client machines.  I would start at the server level.  In Server 2008, you can access this via "Control Panel -> Administrative Tools -> Reliability and Performance Monitor".

Drill down through Monitoring Tools to the Performance Monitor.  Right click on the Performance Monitor Icon on the left and select "New Data Collector Set".  Name the data collector set something that makes sense, like Goldmine Performance monitoring, and select where the log data will be stored.

After you have created the Data Collector Set, you need to customize what items it logs, and when/how long to run the data collection.

On the left pane, Drill down to the Goldmine Data Collector Set, select it, then on the right pane you will see the "System Monitor Log" performance counter already there.  Now you can either add different items to the existing Data Collector (System Monitor Log), or you can create new Data Collectors, and title and group them by Disk Monitoring, Network Monitoring, SQL Monitoring etc.  

Once you have created your Data Collectors in the right pane, you add or remove the Performance Counters (the actual items being monitored) by right clicking them, and you should see where you can add/remove them, and specify collection intervals etc.  There should be some pre-defined templates, which cover most of the important items, but some of the performance counters I would look at are:

Processor:
  % Processor time

Memory:
  Available MBytes
  Pages/sec

PhysicalDisk:
  %Disk Time
  Avg. Disk Queue Length

 
These are just a few to do some baseline logging, you should be able to get an idea of what the average baseline usage is, and then use that to compare to the times when you are running into problems.  You should be able to see any spikes in Disk, Memory or Processor usage.  

Hopefully some of the SQL experts can recommend some SQL performance counters to add as well.

Lastly, make sure to start the Data Collector Set, and set the log files to "Append" so you don't have to over write them.  
 
0
 

Author Comment

by:bryanchandler
ID: 33465857
@russell124:
I will review & implement these performance checks today.

FYI: The users replied back that they DO NOT run any reports in Goldmine.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33475695
easy ... put the wole frickin' DB on a commercial class SSD. It will cost under $1000 for something that can sustain 25,000 RAndom IOPs.  why beat yourself up on this?

sometimes a fix is to just treat the symptoms with a pill rather than cure the disease.


 
0
 

Author Comment

by:bryanchandler
ID: 33475708
@dlethe:
We've spend thousands already.  I couldn't get an IDE cable approved at this point.
0
 
LVL 47

Assisted Solution

by:dlethe
dlethe earned 62 total points
ID: 33475971
Now that i am back, i re-read.  You can get an appropriate SSD this small that you can image the entire system onto, O/S & Database for under $500 that will still be 100X faster than your current disk config.  I bet you could even get an eval if you pay a premium and go with a VAR.  Your database is smaller then what I thought, so you don't have to spend as much money.  It will only take a few hours to do this, heck, use an old backup and a similar machine.

Seems to me that it is well worth the time.  You're out a few hours worth of time, this can be done in parallel with backup data, end-user knows nothing if it doesn't work ... but if it does, you are a hero.  Let's face it.  We KNOW it can't be CPU, memory, or motherboard. 99.9% of time it is I/O.  If it is network bottleneck, it would be easy to confirm.  That leaves disk I/O.    Fix the problem, give yourself a storage solution that can handle 100-1000X what you can deliver now for $500.  If you can't get even this test approved, then you need to jump chain-of-command and take it to your boss's boss.

Based on the end-user's pain, seems that it would be a no-brainer, even if that meant slipping suggesting directly to end-user.  I've been in the storage diagnostic and I/O biz for 25 years doing internals and such, even based on the minimum info you gave me, the odds favor disk I/O problem.  Yes, can spend a lot of time looking at the hardware config, makes/models of RAID controller (assuming you have hardware vs software), and specific models of disk.

So in interim, what exactly is disk controller and exact make/model of disk?   I'll throw you an easy one.  If you have consumer class disk drives, then this looks like TLER issue.  Consumer class drives are unacceptable for use with numerous RAID controllers, and are certainly not designed for 24x7x365.  ECC rates are unacceptable & error recovery times are 10-60 seconds rather than 2-3 secs max compared to enterprise storage disks.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33482859
I still see no answer to the Question i asked here,
What version of GoldMine is it.
Some versions have had some bad performance issues which is an application problem NOT an SQL problem.
There where some serious issues with early versions of GoldMine Premium so please let me know what version you are on.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33483879
One of these issues was the excessive use of the coalesce statment to smooth the nulls, this caused some really bad speed issues.
There have been other performance issues also.
 
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33484562
@dlethe, performance boost of 100 - 1000x? Sign me up! What SSD can I buy for $500 that would do this? Presently I'm using an Adaptec 3405 with 4 consumer-grade drives in a RAID-5 configuration.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33485036
Well, as an example, the OCZ SSD2-2VTXE180G Vertex2 (I love cut & paste),  will do 50,000 random 4KB I/Os per second.  Max read speed is 285MB/sec, write is only 275MB/sec.  MTBF 2 million hrs, 180GB. LIST is $540, so with an online seller who offers ever a small discount, you can get it for under $500.

You'll be lucky to get 50 random writes/sec in your current config.

Mileage may vary, don't expect 50000 on a benchmark, but real-world, it will knock your socks off.  Highest relative performance gain is random read/writes, then sequential read/writes. Put scratch table spaces, index files, anything that gets pounded to the SSD.

Of course, far be it from me to remind the author that it is just plain stupid not to entertain the idea that $500 (you can get a smaller SSD for less money) could very well make this year-long issue go away, and that it is worth even a test ... I'll let other experts beat the horse to death.
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33485107
Thanks. Like (I suppose) many small-business owners trying to pinch a nickel, I gravitate (far too) frequently toward the inexpensive (i.e. consumer) end of the product spectrum, i.e. the Crucial  Technology 128 GB Crucial RealSSD C300 Series Solid State Drive  CTFDDAC128MAG-1G1 (copy/paste back at ya!) which is going for $275 at Amazon. The 100 GB version of the OCZ drive you suggest goes for $330.

Is there enough difference in performance, durability, power consumption, or other features to make the more expensive drive the best choice? (Aside from the adage "you get what you pay for.")
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33485215
Yes, there are lots of differences, also inherent incompatibility issues with controllers & motherboards ... unfortunately.   Here is a great site that has current reviews & specs.  Stuff added almost every day.  Bookmark it!

http://ssd-reviews.com/
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:bryanchandler
ID: 33485920
@GMGenius
In my post above I said they're running version 9.0.
I'm running the initial performance sets & will report back when I get a full day of data and they get a definite slowdown event.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33491298
Sorry missed that :-)
Are you seeing the slow down in the GM UI or are you using the new Outlook client?
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33491421
@gmgenius... what's your speculation regarding performance improvement using SSD vs. rotational disk in the specific case of GoldMine, for most user functions? Do you think we'd see dramatic or noticeable difference? I mean, if a single lookup takes 0.1 seconds instead of 0.3 seconds, well, it's three times faster, but would anyone care? On the other hand, maybe some operations in GoldMine take a few seconds to complete, and that could be cut to .25 seconds. Which would be a nice improvement.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33491514
I cannot comment on performance improvments based on hardware, i can however comment on the fact that GoldMine has been prone to many performance issues in the past and GM9 being a new release it is highly possible it has some problems still
I have been recommending customers to NOT upgrade to 9 for at least 6 months to enable them to iron out the wrinkles.
There are some serious performance issues when using the outlook client  and there is a new patch due soon
I would be looking at what SQL queries are being issued using the SQL Profiler when using GM and see what these queries report speed wise when run in the SQL Managment studio before looking at upgrading hardware.
I have seen GM work perfectly well on sub standard setups including where the SQL server / GM  and exchange is used on the same platform.
0
 

Author Comment

by:bryanchandler
ID: 33503535
I'm still running the logs.  Haven't heard of any major slowdowns from the users yet, but that's how it works.  Fine for days & then BAM.
I'll update w/ the performance data shortly.
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33509818
A non-scientific (but informative) digression... I bought a 120 GB SSD for about $300, just to run some tests on my GoldMine on SQL. I cloned my existing notebook drive onto it. Running the query "select * from contact1" returns 10000 records in about 10 seconds. On my server, a 2 x Dual-Xeon with RAID5, it takes 15 seconds! As a brilliant "side-effect" the notebook boots pretty quickly as well. For general use, i.e. looking up individual contacts, etc., it seems to give the program a little more "snap," but it's hard to generally quantify it as being 50% better or faster.
I know SSD is not new, but the way the price points keep lining up... it's an excellent performance boost for the buck.
0
 

Author Comment

by:bryanchandler
ID: 33511204
All,
We're NOT buying new gear just to "see" if it helps.  We've sunk $1000s into this and can't justify another cent until we can directly show it will resolve the issue.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33512760
See my comment above. I really do suspect its GoldMine not SQL thats your issue.
On a non sync system, truncating the GMTLOG and CONTTLOG can improve performance especially if you have never cleared them before. I have seen improvements from doing this and you could setup as part of the maintenance plan to run the SQL Queury to clear them once every few weeks.
Is the DB set to Simple and not FULL recovery mode, Simple is all that is needed if you do a full backup everyday (recommended)
 
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33534013
You stated they have run GoldMine for years, they obviously recently upgraded to 9.0, where there performance issues before they upgraded?
You also stated 2 users, I highly doubt a 2 user system would cause the DB to become 4gb.
Email attachments are not stored in the database , they are extracted to the HD to a folder.
I do feel that there are bloated GoldMine TLOGs and maybe even a large volume of temporary tables left from previous versions (seen this in GM8.02)
Would you be willing to let me look at the database tables via a remote session?
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 33549202
Bryan,

Try the Microsoft® SQL Server® 2008 R2 Best Practices Analyzer:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

It will find all nature of "bad" or sub-optimal conditions on your SQL Server.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33549311
"We've sunk $1000s into this and can't justify another cent until we can directly show it will resolve the issue."

Sorry for being so blunt, but this means your time is worth nothing, meanwhile your throwing your customer (inevitably a former customer) under the bus.   Quality/Server-class SSDs will directly improve I/O performance for a few hundred dollars, and Goldmine is I/O intensive. I recognize that you have spent 1000s of dollars over a year trying to resolve an issue the wrong way, so you have reached a limit in capital purchases.  

Your time is worth money as well, and it has been 2 weeks since the original post, and have continued along a path that continues to be try-this-try-this with software settings.   Migrate the entire O/S and application to a QUALITY SSD will remove the I/O bottleneck.  It will also remove any issue relating to physical device time-outs due to queue limits, bad blocks, deep recovery, and a host of other HDD-related issues that could be lurking and are difficult to detect w/o specialized software.

This is my last comment in the thread. Good luck to you. I give up.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33683771
Your company is giving up supporting this issue?, thats a very defeatest attitude and if we did that with our clients we would soon loose them
Have you looked at the details I posted about TLOGs
select count(*) from conttlog
select count(*) from gmtlog
I bet they are huge
Its a shame to delete the question because the information provided here by experts should of given plenty of options and some there have been many
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33683904
I also disagree with deleting with refund.  Lots of good info.  The author is basically asking to close the issue because they don't want an answer anymore.  The time to do that was before a rich database of reasonable options and things to try was built by numerous experts over a month.

For the good of EE readers, this thread is valuable, and it is not the fault of the experts, or for that matter, the author that company management doesn't want to pursue it.  That is like ordering a steak, taking a bite, and asking for a refund because you're not hungry any more.


0
 

Author Comment

by:bryanchandler
ID: 33684104
If deleting the question isn't the correct procedure I apologize & will do what's required.

We cannot spend anymore time chasing ghosts.  We've meet & exceeded all requirements of Frontrange regarding hardware/software.  We HAVE to move on. 100's of hours & thousands of dollars have been spend w/o a single improvement.  We're not experts on SQL operation or Goldmine.  We have a Frontrange Certified technician on contract with our client who has been there since the beginning, and he can't figure out what the problem is.  We've replaced or changed every piece of hardware at this site, and have moved the DB to 5 separate Hardware/VM servers. New gigabit switches, rewired networking.  All of this, plus 100s of hours spent by the Goldmine technician tweaking SQL this & that.... ALL OF THIS AND NOTHING HAS CHANGED.  WE ARE DONE.  If the client leaves, we'll be disappointed, but we can't stop supporting our other maintenance clients to endlessly chase a problem that no one on the frikkin earth can seem to figure out.

@!dlethe: An SSD will solve nothing! How can you claim an SSD will solve the issue when no one can say that this is the problem (I/O performance)!?  It wasn't before the upgrade.  Did v9 quadruple the I/O requirements? No? THEN THAT'S NOT THE PROBLEM!
How is this any different than what you claim I've been doing, trying this or that with software.  This is the same exact approach.
@GMGenius:  I'm sorry, but I don't know what the hell TLOGS are in the first place.
Here's what I do know.
Until they upgraded to Goldmine v9 last year, there wasn't a peep.  The SQL DB was running on the DC.  After the upgrade occurred, all hell broke loose.
I realize that ExEx is the best collection of technicians on earth, but should I have to take a SQL class just to figure out what the Experts are asking me to do?   I did look at your link @GMGenuis, and I don't know what any of it means or how it relates to my issue.
Please realize this issue confounds me & for all the answers I've received I simply can't take the time to learn entire new scopes of computing that it seems I'll need to do in order to continue this thread.
HELP!
0
 

Author Comment

by:bryanchandler
ID: 33684238
I just want to make a point of saying thanks to everyone who's tried to help me on this.  THANK YOU!  I'm sorry I've not been more helpful.
2 years ago I was a lowly tech removing viruses & teaching old ladies the difference between left/right click on a mouse.
The owner and lead admin sold the company w/o warning & I was thrusted into managing 6 separate networks with 100+ users knowing absolutely nothing about DCs, Exchange, SQL DBs etc...  He was about as verbose as a used piece of chewing gum & I've had to carry the entire company on my shoulders since.  I think I've done an exceedingly good job adapting & keeping my companies operating well, but things like this are beyond me.  I have no one on earth whom I can consult regarding these things, which I why I joined ExEx.  Again, I'm sorry I can't be of more help, and I'll do right by this thread.  Just let me know what I should do.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33684364
Your system is I/O bound. By your logic, since a faster disk will solve nothing, then a slower disk will also have no effect.  So why not image the computer on a USB flash drive.  If the system runs more slowly, then I have proved my point, that I/O performance is a factor.  It is not a fair test, but just back up one of the databases and run it on USB flash, w/o migrating the O/S.  Again, by your logic, you will see no performance degradation.


0
 

Author Comment

by:bryanchandler
ID: 33684598
@dlethe
You're entirely missing my point.  I realize if I load the DB on a floppy drive it will effect performance regardless of the real issue being present.  I cannot say that  if I did move the OS/DB to a SSD that it will fix the problems, because I have no data pointing to I/O being the problem.   In fact I DO have data pointing that I/O ISN'T the problem.
If it were, wouldn't moving the DB to different hardware make some kind of change?  Moving from a SBS box also running Exchange to a dedicated VM didn't help, moving it to a dedicated server didn't help.  Both of these would improve I/O because the DC OS no longer has to split it's time between the DB, Exchange, AD etc.  
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33684704
Fair enough, I am somewhat oversimplifying things.  This is a way to confirm.  Run perfmon, graph queue depth and I/Os per second both reads and writes on all physical devices.   If queue depth is > 1 then it means the app is waiting for I/Os to complete, and, well, I/O is a bottleneck.   Watch it during normal use, and also during a spike, any kind of operation that takes time.  What does it reach?  Do the logical disks,  i.e, C & D, not physical hard drives.

Moving to a VM will not improve performance unless it is tuned properly. In fact, it will almost always negatively impact performance.  I/O on a VMWARE server is going to show up as 64KB chunked random I/O requests.  There is a great deal of work that has to be done to properly set up VMWare. In fact, it is almost  unheard of that you will get a performance increase unless your storage was grossly incorrect at the beginning.
0
 

Assisted Solution

by:bucball2007
bucball2007 earned 62 total points
ID: 33685370
dropping the connectivity to the database--that jogged my memory of an issue I had between SQL and CRM.
Did you verify that the client doesn't have any Windows scheduled tasks or anything running?  We had a scheduled task that performed an iisreset--caused the CRM db to lose connectivity.  Also, in SQL, did the client look into any maintenance plans running against that database?  Redinexing or some such thing scheduled?  
Also, I kept a log of exact days/ times/ issues --which pointed me to a pattern and helped me ID what seemed random actually had a pattern.
these may seem like trivial things, but worth a quick look to verify.  
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33685837
My statement about the TLOGS would be more geared towards the goldmine technician, I am sure he/she would know what they are.
Open the SQL Managment studio, start a new query window and paste in the 2 statements

select count(*) from conttlog
select count(*) from gmtlog
What record counts do you have?
On a another note, you have stated that they upgraded to v9.... Not surprised there are performance problems as v9 is total rubbish . I have been recommending our clients to stay well away from it.
If you could roll back to v8.5.2.9 I would suspect improvement
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33709781
I think all the experts who contributed should be awarded an equal split
0
 

Expert Comment

by:bucball2007
ID: 33724889
agree w/ GMGenius.  
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33725071
Its especially important to leave the content of this question in tact so do not delete.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 33725106
Agreed. This is good stuff here, split points to all experts who responded.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now