Sql Server unresponsive - slower

Question

Sql Server unresponsive - slower

pdsqsql 436

Hello,
I have Sql server 2014 which host 3rd party Sql server management tool and frequently becoming unresponsive - slower.
Even though Sql server I tried to login but can't browse as too slow I can't check much what's going on Sql server and I have to restart the server then it functioning normal.
As per my thinking it's consuming all the CPU.
Sometimes it's happening during day time also and today I noticed from Sql server log, it happened after DBCC check DB command which we run every early morning as a maintenance.
We have 16 GB Memory and 4 CPU Processor.
Any way I can see before or after what actually happening so I can do some troubleshooting?
Is it anything I can setup to capture so I have some idea what will be causing to get server unresponsive - slower?

Thanks for your help!

Shashank Singh 6,251 Reputation points

2021-01-20T08:44:38.377+00:00

Please post output of sp_readerrorlog and wait stats output.
pdsqsql 436 Reputation points

2021-01-21T20:27:17.857+00:00

Thanks Shashank.
I believe You are looking "sp_readerrorlog" for that specific day and time, right?
Shashank Singh 6,251 Reputation points

2021-01-25T07:21:17.4+00:00

Yes and wait stats output. Make sure wait stats output is atleast 3-4 days after the last SQL Server restart

3 answers

Your answer

Shashank Singh 6,251 Reputation points

2021-01-20T08:44:38.377+00:00

Please post output of sp_readerrorlog and wait stats output.
pdsqsql 436 Reputation points

2021-01-21T20:27:17.857+00:00

Thanks Shashank.
I believe You are looking "sp_readerrorlog" for that specific day and time, right?
Shashank Singh 6,251 Reputation points

2021-01-25T07:21:17.4+00:00

Yes and wait stats output. Make sure wait stats output is atleast 3-4 days after the last SQL Server restart

Answer 1

CathyJi-MSFT 22,431 Microsoft External Staff

Hi @pdsqsql ,

If the percentage of CPU usage is more than 90, it will cause SQL server slower. Suggest you find out top usage CPU queries and adjust the queries. Please update statistics to maintain index fragmentation.

> it happened after DBCC check DB command which we run every early morning

If SQL server database is really large, DBCC Check DB command will have some performance issue. You can using PHYSICAL_ONLY option to shorter run-time for DBCC CHECKDB. Reduce the workload for DBCC. Please refer to MS document DBCC CHECKDB (Transact-SQL).

If you want to collect more information about the performance issue. You can using PSSDIAG to collect various logs and data files.

Please refer to MS blog PSSDIAG data collection utility to get more information.

Best regards,
Cathy

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

pdsqsql 436 Reputation points

2021-01-23T00:27:26.393+00:00
Thanks Cathy.
It's not always during DBCC CHECKDB but during the day time also.
We have job running for Index Maintenance and Stats Update.

I am looking query to get the current CPU utilization as query I am running is showing total CPU% but for overtime ((cumulative )
Currently whenever I run the query, it shows similar kind of results for CPU%

DBName CPUPercent RepDB 99.96 ITDB 0.03 DashDB 0
pdsqsql 436 Reputation points

2021-02-02T14:24:26.447+00:00

How to check current CPU load not cumulative CPU%?

Answer 2

pdsqsql 436

Thanks Erland.
I already checked the Sql error log, no memory leak but found following:

Date 1/19/2021 8:11:54 AM
Log SQL Server (Current - 1/19/2021 3:01:00 AM)

Source spid15s

Message
last target outstanding: 6, avgWriteLatency 552

Date 1/19/2021 8:11:54 AM
Log SQL Server (Current - 1/19/2021 3:01:00 AM)

Source spid15s

Message
average writes per second: 2.56 writes/sec
average throughput: 0.04 MB/sec, I/O saturation: 874, context switches 1079

Date 1/19/2021 8:11:54 AM
Log SQL Server (Current - 1/19/2021 3:01:00 AM)

Source spid15s

Message
FlushCache: cleaned up 5520 bufs with 1700 writes in 346379 ms (avoided 115 new dirty bufs) for db 7:0
last target outstanding: 800, avgWriteLatency 8

Shashank Singh 6,251 Reputation points

2021-01-20T08:47:28.53+00:00

so 44MB of data flushed in 345 sec. This is not great. Server is churning out lot of checkpoints. Have you checked your storage do you see any I/O related messages. If nothing related to I/O or memory switching to indirect checkpoint may help. Again post waits and complete logs to help
pdsqsql 436 Reputation points

2021-01-21T20:28:09.137+00:00

Thanks Erland, Please let me know if you need further information.

pdsqsql 436

Thanks Shashank.
I ran the one of Waitstat query after I restarted the server:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
WRITELOG    4661.39 3106.84 1554.55 775102  54.29   0.006   0.004   0.002
LCK_M_S 2050.89 1183.9  867 302298  23.89   0.0068  0.0039  0.0029
SOS_SCHEDULER_YIELD 988.5   1.03    987.47  274588  11.51   0.0036  0   0.0036
PAGELATCH_SH    416.79  316.7   100.08  46561   4.85    0.009   0.0068  0.0021
PAGELATCH_EX    231.07  171.02  60.05   206073  2.69    0.0011  0.0008  0.0003

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-01-21T21:59:21.97+00:00

WRITELOG at the top and together with those messages from the errorlog. Looks like whatever you are storing your transaction log file on is a tad slow. Could be the problem.

I suggest that you keep sampling the wait_stats DMV like once an hour to table through an Agent job. Don't forget to add a timestamp to the table! Then you can see if it continues this way, or some other wait rises to the top.

Answer 3

16 GB - that is not much.

You should check the SQL Server errorlog. There may be some revealing messages there, like out-of-memory conditions.

If you cannot connect on a regular connection, you can try the ADMIN: connection, although by default it is not enabled for remote connection. And a local connection from SSMS when the machine is tanked and with that little memory does not sound a bright idea. But SQLCMD is less expensive - but also rougher.

To connect one the admin connection say ADMIN:Server. Note that you are likely to get an error message saying that connection failed, but that is from Intellisense. There is only one ADMIN connection.

If you use SQLCMD locallly you can say SQLCMD -A.

Share via

Sql Server unresponsive - slower

3 answers

Your answer