slow newid

Posted on 2004-08-23
Medium Priority
Last Modified: 2008-02-01

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?


Question by:marscpa
LVL 34

Expert Comment

ID: 11877746
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.
LVL 12

Expert Comment

ID: 11878873
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.

Expert Comment

ID: 11881635
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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

ID: 11881844
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)

Author Comment

ID: 11881935
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

Author Comment

ID: 11882035

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?


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".

Accepted Solution

davehilditch earned 2000 total points
ID: 11882080
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.

LVL 12

Expert Comment

by:Ken Selvia
ID: 11889134
Any chance there is a trigger on users table?

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question