| Author |
Topic |
|
chulheekim
Starting Member
46 Posts |
 Posted - 2010-09-14 : 19:44:51
|
| A few times a month, web users are experiencing a white screen or time out. It normally lasts 15 minutes and everything is back to normal. Today it happened again. I wanted to catch what's causing it. I ran a trace to see if there's any unusual event. I could see all the events duration was longer than usual. However, no suspicious events I was trying to find. I'm very sure there's something else going on when this happpens. How can I catch this? This is what I have on my trace. Event Class: RPC: Completed, SP: Completed, SQL:BatchCompletedColumns: DatabaseName, HostName, TextData, ObjectId, Duration, SPIDFilters: DatabaseID Equals to the Production database, Duration greater than 500As you can see, I only catch the event that is happening on the production database. is there system database I should include? What about the Event Class? Thank you so much |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-15 : 14:40:24
|
| I did not run sp_who2 during the event. I will run it next time when it happens. However, the list will be sql jobs and web user requests. Is there specific thing I should look for in the sp_who2? It is set to automatic statistics update. I don't think it is because of the any sql jobs. Most of sql jobs and programs are running every 5 min. We don't see the time out or white screen every day. Just only 2 or 3 times a month. It seems the server is busy with system processes. That's why I want to catch the events related to system processes during tracing. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-15 : 18:27:06
|
| Thank you so much |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-17 : 13:27:22
|
| Hi tara,This morning, the white screen happened again. I ran SELECT * FROM master..sysprocesses WHERE blocked <> 0. I should have run sp_who2. Instead, I ran sp_lock2. I was confused with sp_lock2 and sp_who2. Here's the ouptut from the select stmt. I ran it three times with 5 min interval as I wanted to see what is contantly showing up. Here's the spid that was showing constantly. I'm showing columns that seem important to me.spid blocked waittype waittime lastwaittype waitresource dbid cpu physical_io status55 55 0x0422 0 PAGEIOLATCH_SH 5:1:8237153 3 1418787 7002725 sleeping This is what the spid 55 looks like in sp_lock2.spid hostname dbid dbname ObjName Type Resource Mode Status55 SHCISSQL 5 onyx DB S GRANT55 SHCISSQL 3 model DB S GRANT55 SHCISSQL 5 onyx ContactInternal TAB Sch-S GRANTThis one is a sql job that's running remotely from another server every 30 min to import data. I know this one is sucking up hugh resource. However, I don't know why we don't see the white screen every 30 min. It seems to me that there's something else going on with this one. Any thought? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-17 : 17:10:55
|
| The web user call the issue 'White Screen' because their internet browser became white before they gets time out error. When I ran your sysprocesses select stmt, I got two or three rows returned. That's how many I get even when there's no white screen. The blocked processes may be different ones when it happened. Maybe I should collect more information on what processes are blocked when it happens and when it doesn't happen. I got 206 rows from sp_lock2 when the white screen happened. In normal situation, I got 50 to 60 rows. Maybe I should gather more information on difference between the locks when it happens and when it doesn't happen. Am I going right direction? here's one more question. I know research it. I see 1418787 and 7002725 on CPU and physical_io column. Are these seriously big numbers? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-17 : 23:00:32
|
| Thanks again. Let me see whatelse I can do about it. |
|
|
|
|
Any event that I'm missing?