Recovery model from Full to simple and Shrink transact log result on poor performance

Question

I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba.

My boss told me to change the recovery model from Full to simple because there was no specific routine to back up log and store the backups and the result was a 1.5 gb transaction log with no other initial backups (or maybe missing, the guy how do the project left) besides they do not care about the possible dataloss on daily basis. So I followed the command.

We schedule a windows and I do the job on SSMS and do the following steps:

• Make a full copy to database with -> Tasks Copy Databases.
• Make a full and transact log copy on Database.

• Go to database options and change Recovery model from full to simple.

• Shrink the transaction log ->Tasks > Shrink > Files

In the Shrink file dialog the currently allocate space field was the 1.5gb but were about 1 gb the Avaliable free space so I left 600MB to the log file

Reboot...

So after this the information was able to query but the performances is bad as hell a stored procedure that take 0.6 sec to execute before the changes now take up to 12 secs to run.

Moreover I repeat this in database copies (from the copy that I do before changes) and realized that instead of shrink the space in disk it's growing exactly the space trying to shrink.

After this I try to see if was fragmentation and indeed there about the 40% of tables about up to 50% in avg_fragmentation_in_percent

SELECT  
    OBJECT_NAME(object_id) AS tblName 
    ,dbo.index_name(object_id, index_id) AS ixName 
    ,avg_fragmentation_in_percent 
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) 
WHERE 
    avg_fragmentation_in_percent > 20 
    AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY 
    avg_fragmentation_in_percent DESC

So I try to reorganize the index on the tables. Some go down to 20% but must still on ~ 45%

Finnaly I realised that the CPU was overheating more and more until reach 100% of CPU usage.

Here I used this query to make the what querys are cosuming more resources

SELECT TOP 50
     [Average CPU used] = total_worker_time / qs.execution_count,
     [Total CPU used] = total_worker_time,
     [Execution count] = qs.execution_count,
     [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
     (CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
      ELSE qs.statement_end_offset END - 
    qs.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY 
    [Average CPU used] DESC;

Can anyone tell me what's happend?

Answer 1

Seen this happen a bunch. Your first mistake was to shrink the transaction log. When you set the recovery model to Simple the log is automatically truncated after a full backup of the Database.

What you have now is a combination of Index Fragmentation and Statistics needing to be rebuilt. I would high recommend Ola Hallengren's free utility that can do both for you.

The following works for me after creating the procedures:

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL'

The second thing that happened is most likely your query cache got purged and it now has to build new statistics and has cause parameter sniffing on your queries. Rebuilding your statistics should help.