slow newid

Hello,

I have written an application that keeps track of users logged in.  Eash user is assigned a id (GUID type) via a stored procedure which uses the NEWID function.  Once a day or so now my application does not allow anyone to login, the reason is a timeout error on the following code:

set nocount on
declare @id as uniqueidentifier
set @id = newid()
insert into users (userGuid) values (@id)

It is that simple.  The reason for the timeout is that my code would like to see a return of the above within 30 seconds, when the login problem occurs the above can not finish within 30 seconds.  To fix it, I open query analyzer and run "SELECT NEWID()".  That takes the server about 40+ seconds the first time, the next time it returns in less than a second.  Once it runs (the 40+ second time) users can then login again.

I'll correct my app so that it waits and does not time out, but does anyone know why SQL server is taking so long to generate the GUID at certain times?

Thanks

Mike
marscpaAsked:
Who is Participating?
 
davehilditchConnect With a Mentor Commented:
I just ran:

select newid()
from master.dbo.sysobjects
cross join master.dbo.sysobjects b

which produces 1.5 million guids - did it in 19 seconds on my box so no question of poor speed for newid().  Your problem like you said is at first request, so it sounds to me like it's not specific to newid().  When it happens again, can you try opening query analyzer and running some other query like:

select rand()

and see if it comes back just as slowly. If so it's something to do with your setup - maybe low disk space, low memory, or some other setting.

Incidentally, if you really do believe that it's newID, you could always create your NewIDs in advance.


create table NewIDs (new_id varchar(36))
insert newids
select newid() from sysobjects
cross join sysobjects b

declare @newid uniqueidentifier

update NewIDs
set @newid = new_id,
new_id = ''
from newids where new_id in (select top 1 new_id from newids where new_id <> '')

print @newid

;-)

Hope you like it!

Dave Hilditch.

0
 
arbertCommented:
Any reason you're using a GUID--there is a LOT of overhead for a GUID...

When the "timeout" appears, have you ran SP_WHO in query analyzer to see if there is any locking/blocking.

Also, make sure you don't have the "auto close" option enabled for the database.
0
 
illCommented:
are you using full SQL server or MSDE?
one more to @arberts hints. also check for memory reallocation.it can cause timeouts too, when database starts.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
davehilditchCommented:
Yeah this is pretty unusual!! I've never experienced it and have used guids a lot.  You might want to make sure that you have the latest service pack for your SQL Server installation.

Dave Hilditch.
0
 
marscpaAuthor Commented:
I should have included the version information:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
0
 
marscpaAuthor Commented:
I just grabbed the settings for the db in question:

off      ANSI NULL default
off      Recursive triggers
on      Auto Update statistics
on      Torn page detection
off      Auto close
on      Auto shrink
on      Auto create statistics
off      Use quoted identifiers


Database Compatability Level 80
0
 
marscpaAuthor Commented:
arbert:

i chose a GUID because i needed a identifier (token) that is held on the client and on the server to control licence /  login access.  It just seems the right choice, although in hindsight I see it has overhead and issues.  But they are the only client with this problem.  I have this app installed at 17 other companies, so 16 other SQL boxes deal with this fine.

i should have checked for locking / blocking, I'll check it out the next time it happens.

although I'm not familiar with auto close....it does appear "auto close" is off.  just licky i guess, probably the default?

ill:

this particluar server has 4 GB of ram and SQL getts everything it wants....SQL controlled memory allocation.  Is this what you mean.  Sorry, I'm not familiar with "memory reallocation".
0
 
Ken SelviaRetiredCommented:
Any chance there is a trigger on users table?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.