Let me start by saying that performance is a tricky thing.

FYI: normally, you would only set to recycle worker processes every 5 min if you are debugging a code issue.

Here are some quick IIS suggestions.

Checked: Recycle worker processes - we normally set ours to a large number like 1740 min.
Checked: Shut down worker process after being idle for 20 min
Checked: Limit the kernel request queue - 4000
Checked: Enable pinging - every 30 sec, Failures - 5, Time period - 5 min, Startup time limit - 90 sec, Shutdown time limit - 90 sec

For your unique sitution, you could also search on Microsoft's website concerning best practices for turning on Memory Recycling (In IIS 6/7 right click on your application pool, choose "Properties" and tab "Recycling". At the bottom of the opened tab you will see "Memory recycling").

NOTE: The above are only suggestions. Our lead architect doesn't think this is related to your problem so we will need more information before we can help you.

How did the 3 million records w/ null values in the MetaAtribute table get there in the first place?

Can you describe how you set up your meta data at a high level (how many meta classes, how many meta attributes in each meta class, etc.)?

Are there other websites running on your web server or is it dedicated to only one instance of the ECF?

We still need clarification on your SQL server. What version of SQL server are you running including what SP level? You said this server has 4 GB of RAM, but only 1.5 GB was used by the SQL process - are there other things running on this server or is this a dedicated SQL server? 

Mediachase Software

We have SQL Server 2005 and Windows 2000 Server.

There are 4 main meta classes, each with about 15 meta attributes. And about 5-6 meta attributes are string dictionary. To me, it doesn't seem that much, or complicated. But that's just me...

I still have a question that nobody doesn't seem to have an answer to. It is possible to batch re-save some (or all) of the products ? Because i think it has something to do with the data serialization.

Hi,

i would avoid any meta dictionary attributes since those are known to cause performance issues (you have to do extra query to look up values).

It is possible to resave the products, but you will need to write a console application that goes through all products and saves them. Should be pretty easy to write. That will affect front end site performance and not backend.

Did you run SQL Profiler to see how many and what kind of queries are executed?