No execution plan in profiler

I'm trying to track down a problem with a very simple sql insert statement.
INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
tblRecordingId
This statement has recently begun to cause timeouts (but not all the time).
When it does work, I get this plan:
Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
[tblRecordingId].[AccountId]=RaiseIfNull(82440)))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
|--Stream
Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
|--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
CPU=16, Reads=121, Writes=0, Duration=10860.
When it causes a timeout, there's no execution plan in the profiler. CPU=15,
Reads=40, Writes=0, Duration=40000.
tblRecordingId contains 2 int columns and 590 rows. There are no indexes or
keys.
I did add deadlock to the profiler but didn't get any entries. The server is
not doing much other work (cpu usage is very low.)
I also tried it in the query analyzer. The query usually executes in less
than a second, but sometimes it takes more than a minute.
Any ideas on what the problem is or hints on what to search for?
PeterMost probably a locking problem. The subquery that gets the MAX value does a
table scan. Can you add
an index on the RecordingId column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Laan" <plnews2000@.yahoo.se> wrote in message news:e7S7Uly0FHA.3780@.TK2MSFTNGP12.phx.
gbl...
> I'm trying to track down a problem with a very simple sql insert statement
.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM tblRecord
ingId
> This statement has recently begun to cause timeouts (but not all the time)
. When it does work, I
> get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler. CPU=1
5, Reads=40, Writes=0,
> Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no indexes o
r keys.
> I did add deadlock to the profiler but didn't get any entries. The server
is not doing much other
> work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in less
than a second, but
> sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
>|||Peter Laan wrote:
> I'm trying to track down a problem with a very simple sql insert
> statement.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
> tblRecordingId
> This statement has recently begun to cause timeouts (but not all the
> time). When it does work, I get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream
> Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no
> indexes or keys.
> I did add deadlock to the profiler but didn't get any entries. The
> server is not doing much other work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in
> less than a second, but sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
Tibor is correct. The table scan is the problem. Do you not have an
index on the RecordingID column? If not, you need to add one. Also, are
you using the MAX command to return the next key value for inserting? If
so, you really need a unique index on the column and the statement may
not guarantee unique results. I would recommend that you either use an
identity column to have SQL Server auto-generate the next key value or
you use an another table that holds the next value and update it and
grab the key when needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||[posted and mailed, vnligen svara i nys
Peter Laan (plnews2000@.yahoo.se) writes:
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
This is because the timeout is something that occurs in the client that
causes it to cancel the batch, and the plan event is not generated until
the query completes.
Beware that when you get command timeouts, you must issue something
like "IF @.@.trancount > 0 ROLLBACK TRANSACTION", as any transaction
started by the batch is not rolled back automatically.
Or even better, set the command timeout to 0 which means wait forever.
Does make life simpler.

> tblRecordingId contains 2 int columns and 590 rows. There are no indexes
> or keys.
As Tibor and David said, for this type of query you must have an index.

> I did add deadlock to the profiler but didn't get any entries.
A deadlock is not a timeout. That's when two processes are waiting for
each to other release resources. As Tibor and David said, you probably
had a blocking scenario.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks to all who replied! As you correctly guessed it was a blocking
problem.
Petersql