Tanks for your useful explanation.
As you mentioned and if I understood correctly,if there is a shared table-lock , the INSERT would be blocked until the whole operation(select) completed .Is there any recognizable reason for the shared table-lock?
In a select statement i use WITH (HOLDLOCK) to see the locks.
BEGIN TRANSACTION
SELECT COUNT(*) AS [Count],
[t1].[h] AS [Hour],
[t1].[Transk] AS [Kind]
FROM (
SELECT DATEPART(Hour, [t0].[Date]) AS [h],
[t0].[Transk]
FROM [dbo].[Trans] AS [t0] WITH (HOLDLOCK)
WHERE ([t0].[Success] = 1)
AND ([t0].[Type] = 1)
AND ([t0].[Date] >= '2012-11-05 00:00:00')
AND ([t0].[Date] < '2012-11-06 00:00:00')
) AS [t1]
GROUP BY
[t1].[h],
[t1].[Transk]
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
COMMIT
and this is the output:
resource_typerequest_moderesource_description
OBJECT S
hence ,the INSERT would be blocked?how can i avoid this problem?
I suppose the time out that I have in Inserts is because of this,am i true?
in this case I even check indexes but they are properly created and there isn't any unused index.
How can i monitor these time out a part from SQL Profiler(because of low performance) to see the locks either in the past(i mean a history) or at the moment?