Ghost Process attacks again

Question

Ghost Process attacks again

Viewing 6 posts - 16 through 20 (of 20 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Lynn Pettis - Wednesday, January 24, 2018 11:06 AM
Looks like tempdb contention. How is your tempdb database configured?

Suboptimally ... maybe still single file on C drive?? :hehe:

First step is to enable trace flags 1117 and 1118.

Given the extent of the issue that may not fix problem.

If tempdb is on very slow IO it should be moved to faster IO.

Still issue then do 2 or maybe 4 tempdb files. All EXACTLY same size and file growths.

I note this appears to be a different problem from one stated originally.

Now I will advise you to get a tuning professional to give EVERYTHING a review. Your environment (and almost certainly application) need help!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sue_H SSC Guru Points: 90860 More actions · Answer 2

rogelio.vidaurri - Wednesday, January 24, 2018 11:03 AM
Definitely something is wrong with our SQL instance. We switch traffic back, and after a few hours it got really slow.
Even sp_whoisactive was taking more than a minute some times.
I was able to see this (issues with tempdb)?
it is a different issue, not the Ghost Process striking again.
Looks like we need a DBA expert.
login_name waiting for PAGELATCH was our DB user for our main database

What is service broker being used for on this server? Interesting that most/all of these look to be service broker related and earlier you posted issues with BROKER_TASK_STOP

Sue

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 3

TheSQLGuru - Wednesday, January 24, 2018 11:23 AM
Lynn Pettis - Wednesday, January 24, 2018 11:06 AM
Looks like tempdb contention. How is your tempdb database configured?
Suboptimally ... maybe still single file on C drive?? :hehe:
First step is to enable trace flags 1117 and 1118.
Given the extent of the issue that may not fix problem.
If tempdb is on very slow IO it should be moved to faster IO.
Still issue then do 2 or maybe 4 tempdb files. All EXACTLY same size and file growths.
I note this appears to be a different problem from one stated originally.
Now I will advise you to get a tuning professional to give EVERYTHING a review. Your environment (and almost certainly application) need help!!

tempdb
FILEGROUP = PRIMARY
AUTOGROWTH = 10%, unlimited MAXSIZE
HARD DISK: not C: but D: (480 GB KC300 SSD SATA RAID 10)

MDF = 2.67 GB
LDF = 379 MB

thanks for the advise

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 4

Sue_H - Wednesday, January 24, 2018 12:09 PM
rogelio.vidaurri - Wednesday, January 24, 2018 11:03 AM
Definitely something is wrong with our SQL instance. We switch traffic back, and after a few hours it got really slow.
Even sp_whoisactive was taking more than a minute some times.
I was able to see this (issues with tempdb)?
it is a different issue, not the Ghost Process striking again.
Looks like we need a DBA expert.
login_name waiting for PAGELATCH was our DB user for our main database
What is service broker being used for on this server? Interesting that most/all of these look to be service broker related and earlier you posted issues with BROKER_TASK_STOP
Sue

SqlDependency and SqlChangeMonitor

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 5

Sue_H - Wednesday, January 24, 2018 12:09 PM
rogelio.vidaurri - Wednesday, January 24, 2018 11:03 AM
Definitely something is wrong with our SQL instance. We switch traffic back, and after a few hours it got really slow.
Even sp_whoisactive was taking more than a minute some times.
I was able to see this (issues with tempdb)?
it is a different issue, not the Ghost Process striking again.
Looks like we need a DBA expert.
login_name waiting for PAGELATCH was our DB user for our main database
What is service broker being used for on this server? Interesting that most/all of these look to be service broker related and earlier you posted issues with BROKER_TASK_STOP
Sue

SqlDependency and SqlChangeMonitor

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

10% growth is BAD, especially for tlog which MUST be zeroed on growth (as opposed to data file which could be IFI'd).

Whenever you decide you need to bring in a consultant I would like to throw my hat in the ring for consideration.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service