sp_helpuser blocking

Question

sp_helpuser blocking

Brad Wilson

SSC Rookie

Points: 42
More actions
April 24, 2006 at 9:34 am

#111918

Hi,
For the past couple of weeks the performance on our SQL Server has been much worse than normal, with very high CPU utilisation. There is constantly blocking on the systen and the spid at the top of the tree is always executing the sp_helpuser stored proc.
I understand what output this stored proc produces but does anyone have an idea why it would suddenly start taking a lot longer to execute, especially when there are a lot of users on the system? The users are logged in with Windows authentication - does sp_helpuser contact the Active Directory or does it just take the info from the sysusers table in the current db?
Thanks,
Brad

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I don't really understand why you would see heavy use of sp_helpuser and I don't know why it should block, if at all. You don't say how you're getting your blocking info, but I suggest you pick up one of the many procs that record blocking info and run to make sure.

I'm not aware of sql server going to the AD, is sp_helpuser being called with a parameter?

I take it you have regular jobs to rebuild indexes and updatestats on your database(s) .. degraded performance can cause problems by causing sql to run slow - it looks like the sql is the problem but really it's the slowness of the system.

Check for ntfs fragmentation as well as database fragmentation.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Brad Wilson SSC Rookie Points: 42 More actions · Answer 2

I ran a defrag of the file system on the weekend, and also a database reindex of each table.

I get the blocking info by using sp_who2 and dbcc inputbuffer (spid). Sometime sp_helpuser is called by itself, and other times it is called as sp_helpuser;1.

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 3

Run a profiler for 30 minutes or so and see if you can step through events and find out what app is calling the sp. (log to a table - its much easier than using the profiler app/ files)

There have been times that I will see a long running query, but will have to use the statements previously executed from the same host to determine what module, application, or user is connecting and executing a SQL block.