Sql application is slow at different intervals

Transact-SQL
1

Hi all,

I have an windows 2012 with SQL 2012 installed, i have an ERP application that has an mdf file of 40 GB , and the actual used space is 26 GB , and the ldf file has 19 GB , but when i query the disk usage it only shows 5 % used. A few months ago the application started to work slow at different intervals ( at about 9 am, 12 PM, 15 PM ) .The server has a 48 GB RAM and 8 cores and the space on HDD is about 180 GB free, i have monitored the servers resources and they all look good, no load that could issue the problem.

I have checked the data base activity monitor and no issues there they all look ok, no load.
I only do a Simple backup, not a full one, so i;m thinking could the problem be from the big transaction log file ?

Any other checks i can do ?

Thank you
Bogdan

2

Having 19 GB allocated to log file, but only 5% of it being used is helping, rather than hurting performance. When SQL Server needs more log space, it can use the 95% that is free in the log file - which means it won't have to expand the log file, and it won't have to initialize it. So the root of your problem is likely somewhere else.

Given that it happens at specific times, it could be that the database vendor is doing some type of operations at that time. You can use sp_who2 to inspect if there are specific processes that are blocking. Also, sp_whoisactive is a good tool to find out what is going on on the server at that time. Also, you can run Perfmon and select some of the SQL Server related performance counters to see if you notice anything abnormal during those times.

3

Thank you for the reply, i will try to monitor the activity and see if there are any processes in sql . I will get back to you .

I was thinking maybe if do a backup with recovery type full ( i have Simple now ) maybe it will help me with the cleaning up the transaction log.

4

It really depends on what your objective is.

If you are trying to reduce the disk space that the log file is taking up (19GB), then you can shrink it. If that is the objective, keeping the recovery mode SIMPLE is better. If you change to FULL or BULK LOGGED mode, the log file will not be cleared unless you do log backups.

If your objective is to improve the query performance and diagnose the slow-downs you are experiencing, keep the log file as it is and monitor how much is used at times when you are experiencing slow down. You can use DBCC SQLPERF(LOGSPACE) to see how much space is allocated and how much is actually used. Unless the use percent gets to be very high, log file size/log file usage is not the cause of the slow down.