sp_who reports all processes running in master database

Question

sp_who reports all processes running in master database

bwilliams-1049831

Mr or Mrs. 500

Points: 532
More actions
January 9, 2014 at 12:46 pm

#396082

I have a 3rd party application that has a user database installed on SQL Server 2008 with all tables, views procedures etc in it.
This server/application is performing very poorly.
When I run sp_who or sp_who2 or any other monitoring programs I have, they all report that all processes are running in the master database not the actual database installed with the application.
There are no user defined objects in master, everything is in the correct user defined database.
The application logins have no access to the master database they only have db_owner on their specific database.
Any ideas what is going on here?
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

as far as i know, every login has access to master and tempdb; but they can hardly see anything that has not been granted permissions to them in those databases;

select * from master.sys.tables might return spt_values, and select * from master.sys.databases returns all databases, as that is open to the public role.

that's because they inherit the public role, which gives them some limited access.

from there, an application might change it's connection context to another database, or even make three part naming convention calls...ie AppDatabase.dbo.Invoices;

could that be what you are seeing?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 2

Do a search for usp_who5. I think it will help out.

Cheers

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 3

I tried usp_who5 It does gives me more information but not what I am looking for.

The applications login default database is correct and using SQL Profiler I can confirm that none of the sql statements are db.schema.table fully qualified. They are all simply table_name or view_name.

I should also mention that there are other user defined databases on this server and they are all working correctly.

But this one database was 93% of total execution time and all of it is on the master database???

By far the highest wait state was CXPACKET waits so we turned off Parallel query. The server is performing better but 60% of total execution time is still in the master database.

Regards

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 4

Does this app utilize stored procedures heavily and if so, do the names begin with 'SP'? That is kind of a long shot but thought I'd ask.

Cheers

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 5

It does use lots of procedures but they all start with proc_

Most of the application is .Net code and compiled dll's that I don't see. Some of the profiler text is 'SQL text can not be displayed'. But that is maybe 1 in 20 or less.

Regards