Solved

slow newid

Posted on 2004-08-23
10
804 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:marscpa
10 Comments
 
LVL 34

Expert Comment

by:arbert
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.
0
 
LVL 12

Expert Comment

by:ill
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.
0
 
LVL 4

Expert Comment

by:davehilditch
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:marscpa
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)
0
 

Author Comment

by:marscpa
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
0
 

Author Comment

by:marscpa
ID: 11882035
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
 
LVL 4

Accepted Solution

by:
davehilditch earned 500 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.

0
 
LVL 12

Expert Comment

by:kselvia
ID: 11889134
Any chance there is a trigger on users table?
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

789 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