Concurrency problem in SQL Server

We have very large site which will have more then 500 users online at a time. Our site runs on asp.net 3.5 and SQL Server 2005. We have some shopping cart application which is having payment transactions and lots of data insertion. Our problem is with concurrency like we have some large transactions in code as well as SQL Server. The problem with our site when 500 users shop at a same time. Our tables are getting locked and having larger response time. WE have already implemented 开发者_StackOverflow社区the snapshot replication but still tables data insertion ratio is so high that our response time is greatly increases when everybody hitting same thing.

What should we do to have better response times with our queries like 1 users is inserting data at same time others are in waiting state until first one completes. Is there any other a sync way to that please advice. Due to this some time we have are having cpu 100%.

Thanks in advance for your answers.

First, you need to determine where the problem is.

Run this query (from Glenn Berry) to find out the most expensive cached SPs in terms of logical reads:

-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

What is your Page Life Expectancy (under load):

-- Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

You should post your drive information:

• SAN or Directly Attached Storage (DAS)?

• RAID placement (and number of spindles in each group) and type for logs and Data