How does a business owner solve a memory leak?

Torn between hosting and programmer

[Roxster]

I am the site owner with dedicated web and database servers. My website performance is back and forth between insanely fast, and crawling slow sometimes needing a reboot. How do I get resolution? How do I test to see what is even wrong?

The webpages with static content still load pretty fast.
Sometimes I have to reboot the database server to get items to add to cart.
Sometimes pulling up orders in the backend and scrolling over to the next page of 25 orders drags too.

[LifeinAsia]

It sounds like you need to do some monitoring of your database server. The specific tools depend on your DB.

Some things to look at:
- memory usage
- CPU usage
- disk drive access
- query indexes
- long-running queries

Another possibility may be on the network side. We've had problems before with improperly configured network posts causing a lot of collisions on the network.

[Roxster]

Under perfomance:
% Processer time spikes from 0-50 often, and occasionally spikes up to 100, but doesn't stay maxed out.

Average disk que length is similar 0-50 and spiked up to 96.

Pages per sec can spike, but for the most part hangs out at 0.

[Roxster]

Now it looks like the same grapgh is upside down, but things are mostly at 100, rather than returning to 0.

[Roxster]

Taks manager goes from 50-100% on CPU usage.
But it did come back down to 2-35% when re-logging in later.

[Ocean10000]

Is it possible that you are running into traffic spikes which is causing your site to slow down? This might explain your problem. And if you are using IIS Log you might be able to determine which pages are taking the longest to load and examine those pages for performance tweaks.

[LifeinAsia]

You should really take a look at what queries are taking a long time on the DB. If you're running MS SQL, run SQL Server Profiler. Also, the Database Engine Tuning Advisor can give some great recommendations.

[Roxster]

I only get about 5,000 uniques per day. I am suspicious of bad code, like connections not being closed and memory leaks. How do I test?

[LifeinAsia]

What does memory usage look like? Is it maxed out? Is it spiking?

[Roxster]

How do I look at memory instead of CPU used?

Also my programmer is looking at adding triblock to prevent the page from crashing and finally to close connections in the event something in the code is broken. He didn't know that when a page breaks the connection remains open. We are also looking into running a garbage collector.

All of these sound like elegant bandaids but how do I find the source of the problem?

[haggul]

Does sound like db hits - as this is the IIS part of the forum I'm assuming SQL Server?

Can you get access to see what the locks are like on the db (sp_lock) - if they are mounting up and not releasing it could be bad indexing / design leading to long running queries and dealocking.

Get the developor to check his indexing, and also the nature of the columns that are being searched / sorted etc. If your keys are based on char fields not numerics the indexes will be bigger and slower.

Also can be worth using locking hints where you are only doing straight selects etc and don't care about the consistency of the data - stops the db applying locks if not needed.

Of course get all connections closed down at the end of each page, tidy up after yourselves etc.

We got a site that gets 25k uniques a day, site and db all running on one (beefy) box with no slow down so it should be fine.

Let us know how you get on.

[LifeinAsia]

I assume you're using Task Manager to look at the CPU usage? TM also lets you look at memory usage.

[marcel]

Also my programmer is looking at adding triblock to prevent the page from crashing and finally to close connections in the event something in the code is broken. He didn't know that when a page breaks the connection remains open

Yes, you should always use a try - catch - finally block [msdn.microsoft.com] to close DB connections (or the using statement [msdn.microsoft.com])

I would begin there, most likely the problem will be as good as solved after implementing this.

[txbakers]

There are many issues at play here.
1) COM objects that you are using are leaking memory.
2) insanely BAD sql statements - with missing joins and no indexes.
3) not closing objects opened in Classic ASP.

If you need a GREAT IIS expert, send me an email - I've called on this person for help before when I had the same problems a few years ago.

For sql help, download the free Jet Profiler and install it on your server. This will show you the sql problems.

If you are the owner, you cannot be at the mercy of your programmer. You need to learn how to deal with these issues. Just like the captain knows everything on the ship. You don't have to do the work, but you do need to know what's going on.

[Roxster]

Today when the site locked up my programmer was available to look at it. Turns out there was an SQL block feature.
The order ID part of the database was fine, but the item detail when adding to the cart was getting blocked.

We suspect there are still connection leaks. The memory itself was not eaten up and there were about 70 connections used.

[haggul]

How is it inserting into that table? Stored procedure / direct SQL from the code? What is the table structure like - indexes etc?