Hi,

A brief history about our application: We have a statistical application which needs to process large volume of data. We are using .NET 2.0, LLBLGen Pro DAL components. Recently we are facing performance issues and I had posted couple of other queries in this forum. Initially we were trying to do the processing in-memory but as suggested in the answers to those queries, I have changed the code to process one record at a time.

After this when I used the Windows 2003 performance monitor to see the SQL Server performance counters. I was shocked to see the SQL Server:Locks->Lock Requests/sec counter showing the number 556,748 and still more counting. On further analysis, I found that the "key" and "Page" lock counters in the "Lock Requests/sec" counter is showing five hundred thousand and more requests.

The CPU Processor time, Disk time counters are all normal. There is no dead lock or wait time either. So what is the problem here? What is Lock Requests/sec and Page, Key counters actually mean? How to reduce this huge value in Lock Requests/sec counter?

I searched in other websites and it seems the optimum value for Lock Requests/sec should be less than 1000. But I couldn't find out how to reduce this. I am sure if we can solve this, eventually we would get good performance. Kindly help.

Thanks, Srikanth.

So what is the problem here? What is Lock Requests/sec and Page, Key counters actually mean? How to reduce this huge value in Lock Requests/sec counter?

Please refer to the following link: http://msdn2.microsoft.com/en-us/library/ms190216.aspx

In general I think this highly depends on the nature of the database its usage.

Anyway I don't think this is related to LLBLGen Pro.

Are you reading the data inside a transaction? If so, these are read locks.

LLBLGen Pro isn't doing anything odd, it just formulates normal queries and executes them using normal ADO.NET commands, without weird constructs.

Could you also check with the SqlServer profiler to see what # of locks and what TYPE of locks are created? SqlServer places read locks on rows when you fetch them. This is normal.

If you want fetches performed with the NOLOCK hint, please enable NOLOCK using the static property on the SqlServerDQE's DynamicQueryEngine class.

When I run different kind of fetches in bulk, not in a transaction, on our Sqlserver 2000 database, using llblgen pro v2.5 code or normal ado.net code (datareader and datatable), I get 500-800 locks/sec for llblgen pro code and 1100-2500 locks/sec for datareader/datatable code.

This is normal as the overhead for these two types is less than the llblgen pro code so there are more queries executed per second.