Home Forums Discussions Problems SQL help

I know this has come up before, but I can't seem to find it. We have 4 providers and a total of 9 concurrent users. One of our IT guys suggests that this is pushing the limits of the stripped down version of SQL that ships with AC, and we should invest in the full blown version for better speed. I seem to remember some memory issues with the full blown version discussed here. Can any of the Wizards here offer a quick opinion, or at least point me back to that thread?

Bert will weigh in, and I look forward to his take.

From a DB performance perspective, a full SQL engine will allow more of the memory in your server to be allocated to the database. Keep in mind that unless your sever is a 64 bit install, your machine will not be able to use more than 4 GIGs.

Also, with a full SQL install there are several configuration values that can be modified to improve the DB performance. Admittedly, tinkering with the server config is not for the weak of heart.

Overall, you should see some significant performance improvements in the responsiveness of the DB.

Plus you'll also have a new subject for light bedtime reading on the long nights - database administrator tuning theory.

If I may weigh in a bit.

Amazing Charts, as we know, comes with the 32-bit version of SQL Server 2005 Express. SQL Server 2005 Express can utilize only:

* 1 CPU
Now, let's stop for a moment. Let's say your one CPU has four cores - which means you really have four CPU's but they take up on ONE socket on the motherboard. SQL 2005 Express will utilize all four cores on that one CPU. If you add a second four core CPU, then it will sit idle.

* 1GB of RAM.
So you have 9 concurrent users. Even if those users are entering information as fast as they can, how much power do you think they will consume of your server? Better question, even with 9 users, how much do you think your server sits IDLE out of the day???

* No database can be bigger than 4GB.
How big is your database now? How long did it take you to get there?

From a performance standpoint, the engine that runs on the Express Edition is the same one used in Standard and Enterprise editions of SQL Server. Therefore, the idea that Express is a "stripped down" version is suspect once you get past the above limitations.

Have you tried the "gut" test? When you use AC in the middle of the day with everyone, what does your gut tell you about performance? Is it slow? Acceptable? What has your staff said?

If AC is slow, are we sure it isn't because of a slow network?

Think about it before you plunk down serious coin on a new server.

JamesNT

Responses to Indy:


If the server is running Windows Server 2003/2008 Enterprise or Datacenter editions, you can address more than 4GB using Physical Address Extensions (PAE) up to 64GB. Standard editions can only use 4GB regardless of PAE. SQL Server 2005 Express can use only up to 1GB regardless of how much your server has.


Quite correct.


Assuming his server isn't some refurbished p.o.s. that he totally skimped on when buying it, probably not. If his server has a decent dual or quad core proc, 4GB of RAM, and at least 10,000 rpm SCSI drives, he's good to go.

JamesNT

James, good points. As to our system, the one part I'm very confident in is the network. The issue is that some things are slow and some fast. It does not seem to be an issue of number of concurrent users. Certain processes can be slow at different times and for different users, to the point of being an issue. It had been suggested to me that our version of SQL might be limiting us. I can only say for certain that between all users perhaps 15 minutes a day are spent waiting for the computer to do something.

Agreed with everyone. I don't think you will see a ton of a performance issue by moving up for SQL Express. You just don't have enough users. SQL utilizes memory by storing pages. The amount of pages you will need for your users just isn't that much.

I have as many users as you. I also have SQL 2005 Express, Workgroup, Standard and Enterprise along with 2008. They all sit in my desk. I just stay with Express.

James said it best and I have been saying this ad nauseum, do you think this is a slow network. My guess is you would see a much better increase in performance if you hired a Microsoft MVP in networking and IT who could come to your place and test your network. It isn't just plug and play. My friend uses a connection meter that costs over $15,000 and finds huge issues at places such as huge telephone companies and banks. It is well worth it to have someone look at your network.

dgrauman,


This would tell me to take a look at your workstations. What other programs do users have running at the same time as AC?

The next time a workstation gets slow, find out what all the user is running. That should give you a spring board to start narrowing things down.

JamesNT

All we have on the workstations is AC and UpDox in Chrome. I've checked task manager and there are all the little windows processes but nothing else.

What kind of hard drives does your server have? I'm specifically looking for the connection (SATA, SCSI, SAS) and the RPM (5400, 7200, 10000, 15000).

JamesNT

I'll have to ask the IT guys. That question is above my pay grade. I'll post back, but we bought new Dell equipment in April and did not go for cheap.

David,

I have used three computer system information programs in the past.

Lavalys Everest was bought out by AIDA64 and, frankly, I see no difference.
Belarc is free and should be.

I used to like the above until I found

SIW PC Inventory http://www.gtopala.com/

It is the only one that will show you your drives, let you know if they are SCSI, SATA or SAS and the speed. It lets you know a lot of things including what type of video and sound cards, etc. which can be helpful if you forget.

Unfortunately, you used to be able to get them as standalones for about $9.95 a license (life long). I bought one for each of my computers. Now, you have to get a 10-user license. They have a free version. Not sure how good it is. But, if it shows you the info on your drives, what the heck. You can wow your IT guys.

I am sure there are other ways just using your PC, but I can't think of any off the top of my head.

Even not going cheap, I wouldn't be surprise if you don't have SATA 7200 drives. (which are fine -- obviously 15K are faster but pricier).

Not going cheap could mean how much overhead your IT person charged you for the server. In my opinion, when an IT person helps you purchase a server from Dell/HP/etc. the price you pay should be the price Dell charges. There should be no markup from the IT person.

JamesNT

I was not speaking so much of money as of philosophy, James. The marching orders when the system was set up were "do it right.". If it is a hardware issue, it sure wasn't because we tried to cut corners.

But, back to the original question, I think you answered it for me. We'll stay with what we have and look to the workstations. Thanks.

dgrauman,

Apologies if I rubbed you the wrong way. I'm just trying to look at all endpoints. You can probably imagine the setups I've seen where people paid good money but got taken for a ride.

JamesNT