sp_who2 shows blank loginame

Question

sp_who2 shows blank loginame

htt

SSCertifiable

Points: 5242
More actions
July 12, 2012 at 4:34 pm

#264205

Hi guys, I ran sp_who2 and found SPID's having more than 1 row in the output of sp_who2 and only the first row of an SPID has a loginame while the other rows for that SPID has blank loginame. For example in the screenshot the SPID 68 has the first line suspended with a loginame and there are 4 more rows for SPID 68 with a blank loginame.
Do you know what the rows with blank loginame are for? I never seen this and the SQL server is very busy with CPU constantly at above 80%.
Thx.

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

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

htt SSCertifiable Points: 5242 More actions · Answer 1

I just read some posts here and pretty sure that this is a result of parallelism. The server has 2 dual core AMD Opteron 2.0GHz processors. Never have this kind of problem until today. Our dev guy rolled out new codes over the weekend but they said none from them would be causing the parallelism issue.

Is there anything I can look or do on the server end as the performance is really bad? This is SQL 2005 Std Edition.

Thx.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Yes you will see blank login names for the same SPID in a parallel query, due to a SPID is linked to 1 user and 1 user only, so in that case all 5 rows of SP_WHO2 for SPID 68 relate to the same login.

As for the performance, is there any particular part which is slower than others, can you debug the apps to find out what queries they execute around that slow parts and tune them?

The bottom link in my signature has a e-book to the Accidental DBA Guide, it has a whole chapter on High CPU Usage.

htt SSCertifiable Points: 5242 More actions · Answer 3

There are something interesting. Before we never seen parallel queries. We have an IIS box querying a separate SQL box. Over the weekend our dev guys added new stuffs to IIS with new SQL SP's. Right after that we see CPU usage of SQL server is 80% - 90% average, sometime 100% for a long period. Then we started seeing parallel queries during business hours when the IIS server is being used but the SP's seen in parallel queries are the old SP's that never show sign of performance issue.

Is that something to look from IIS end? The database is reindexed every night so I don't think there is a problem with the database.

Thanks,

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Parellel queries is at the DB end and will be down to the execution plans and the way the SQL optimizer thinks is the best way to get the data the quickest, be it single threaded or multi threaded.

You say its reindexed every night, is that Rebuild or Reorganize?

You could try playing with your MAXDOP settings at query or server level depending if its 1 or 2 procs or all of them. But remember if its at server level then everything is enforced by the same MAXDOP setting unless you override it in the query.

htt SSCertifiable Points: 5242 More actions · Answer 5

anthony.green (7/16/2012)
You say its reindexed every night, is that Rebuild or Reorganize?

Hi Anthony, it is rebuild index. Thx.

htt SSCertifiable Points: 5242 More actions · Answer 6

I see in profiler there are few connections with Transaction Isolation Level set as Serializable. The default for the server is READ COMMITTED. Could that be the reason for seeing parallel queries and most of them are suspended? How can I find out what IIS applications using Serializable Transaction Isolation?

Thx.