Occasionally, we must resize or realign our Tempdb log file (.ldf) or data files (.mdf or .ndf) due to a growth event that forces the file size out of whack. To resize we have three options, restart the SQL Server Service, add additional files, or shrink the current file. We most likely have all been faced with run away log files and in an emergency situation restarting the SQL Services may not be an option but we still need to get our log file size smaller before we run out of disk space for example. The process of shrinking down that file can get tricky so I created this flow chart to help you out if you ever get into this situation.

Now its very important to note that many of these commands will clear your cache and will greatly impact your server performance as it warms cache backup. In addition, you should not shrink your database data or log file unless absolutely necessary. But doing so,  it can result in a corrupt tempdb.

Let’s walk through it and explain somethings as we go along.

First thing you must do is issue a Checkpoint. A checkpoint marks the log as a “good up to here” point of reference. It lets the SQL Server Database Engine know it can start applying changes contained in the log during recovery after this point if an unexpected shutdown or crash occurs. Anything prior to the check point is what I like to call “Hardened”. This means all the dirty pages in memory have been written to disk, specifically to the .mdf and .ndf files. So, it is important to make that mark in the log before you proceed. Now, we know tempdb is not recovered during a restart it is recreated, however this is still a requirement.

USE TEMPDB;  
GO  
CHECKPOINT;

Next, we try to shrink the log by issuing a DBCC SHRINKFILE command. This is the step that frees the unallocated space from the database file if there is any unallocated space available. You will note the Shrink? decision block in the diagram after this step. It is possible that there is no unallocated space and you will need to move further along the path to free some up and try again.

USE TEMPDB;  
GO 
DBCC SHRINKFILE (templog, 1000);   --Shrinks it to 1GB

If the database shrinks, great congratulations, however for some of us we still might have work to do. Next up is to try and free up some of that allocated space by running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.

DBCC DROPCLEANBUFFERS – Clears the clean buffers from the buffer pool and columnstore object pool. This will flush cached indexes and data pages.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE – Clears the procedure cache, you are probably familiar with as a performance tuning tool in development. It will clean out all your execution plans from cache which may free up some space in tempdb. As we know though, this will create a performance  as your execution plans now have to make it back into cache on their next execution and will not get the benefit of plan reuse.  Now it’s not really clear why this works, so I asked tempdb expert Pam Lahoud (B|T) for clarification as to why this has anything to do with tempdb. Both of us are diving into this to understand exactly why this works. I believe it to be related to Tempdb using cached objects and memory objects associated with stored procedures which can have latches and locks on them that need to be release by running this. Check back for further clarification, I’ll be updating this as I find out more.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Once these two commands have been run and you have attempted to free up some space you can now try the DBCC SHRINKFILE command again. For most this should make the shrink possible and you will be good to go.  Unfortunately, a few more of us may have to take a couple more steps through to get to that point.

The last two things I do when I have no other choice to get my log file smaller is to run those last two commands in the process. These should do the trick and get the log to shrink.

DBCC FREESESSIONCACHE– This command will flush any distributed query connection cache, meaning queries that are between two or more servers.

DBCC FREESESSIONCACHE WITH NO_INFOMSGS;

DBCC FREESYSTEMCACHE – This command will release all unused remaining cache entries from all cache stores including temp table cache. This covers any temp table or table variables remaining in cache that need to be released.

DBCC FREESYSTEMCACHE ('ALL');

In my early days as a database administrator I would have loved to have this diagram. Having some quick steps during stressful situations such as tempdb’s log file filling up on me would have been a huge help. So hopefully someone will find this handy and will be able to use it to take away a little of their stress.

SQL Server 2017 (compatibility 140) brought us many Intelligent Query Processing (IQP), formally known as Adaptive Query Processing, features that improve performance on workloads straight out of the box with no code changes to implement. One of those features introduced was Adaptive Joins. In this feature the join operator is dynamically determined at runtime by the optimizer, which defines a threshold number of rows and then chooses between a Nested Loop or Hash Match join operator. This operator switch can produce a better join and improve performance of your queries without you having to lift a finger.

Not all queries will qualify for this new feature. The feature only applies to SELECT statements that would have normally returned a Nested Loop or Hash Match, no other joins are apply. In addition, the query must be run in Batch mode (using a Columnstore Index in the query) or using the SQL Server 2019 Batch Mode on Rowstore feature. To find out more about the latter, I recently blogged about Batch Mode on Rowstore here.

Now let us understand the difference between the two different join operators the optimizer will choose from in the feature.

Hash Match– Creates a hash table (in memory) for required columns for each row then creates a hash for second table and finds matches on each row. It is very expensive and requires a lot of memory resources.

Nested Loop– It performs a search on the inner (smaller) table for each row of the outer (larger) table. Less expensive than a Hash Match and ideal for small row inputs, it is the fastest join operator that requires the least I/O with the fewest rows having to be compared.

For this feature a new operator was introduced to show us that an Adaptive Join was used, and the properties give us details on how it determined which join to use.

At runtime if the row count is smaller than the Adaptive Threshold of rows a Nested Loop will be chosen. If it is larger than the threshold it will choose a Hash Match, it is that simple. This can be great for workloads the fluctuate between small and large row inputs for the same query. Note the screen shot below. Using estimations, the plan would have returned a Hash Match but during actual executions it dynamically changed to Nested loop.

The adaptive threshold rows is determined is based on operator cost. The optimizer will evaluate each operator cost using an algorithm for the join operation. Where that cost intersects (the row count tipping point) is what it uses to determine the threshold. Microsoft Docs gives us a good image of this.

Like with any SQL Server feature you have the ability to turn it off by disabling it if you find it is not providing any performance gains or is causing query regressions within your environments.

— SQL Server 2017

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

— Azure SQL Database, SQL Server 2019 and higher

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Intelligent Query Processing gives us many new “auto” fixes for our queries Adaptive Joins is one that has piqued my interest as a database administrator that loves performance tuning. Improper JOIN choices made by the optimizer can really hinder performance, which would likely require me to implement query hints or plan guides. I really like that SQL Server is now making automatic intelligent decision and fixing them for me on the fly without my intervention.

Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal. By right clicking on your database properties and choosing the Configure SQL page you can change your Subscription and Azure Location. Even more and what is really cool is I can also scale up and down my Edition (which is generally referred to as Service Tier outside of SSMS), Size and Storage Tiers as well.

You will have to authenticate to Azure the first time you use this.

Once authenticated you will see the below options available.

Depending on your Service Tier such as Basic or Premium, or the more current General Purpose or Business Critical, you have selected your drop will dynamically change for each option. As you can see in the example above it is currently set to Premium, thus I would see only the “P” level objectives (compute and memory levels). However, if I changed my Editions to anything else my Service Level Objective would change accordingly. Azure changes frequently as do the offerings. SSMS is making a call to the SQL resource provider in Azure to get the offerings, so it should always be current, though it may look different than this screenshot. If you notice that the option, you want it not in the dropdown they have given you the ability to simply type the value.

The Max Size will allow you to see your current dataset maximum storage size or scale it up and down when needed. Leaving it blank will set it to the default size for the edition and service level objective.

By clicking ok these changes will be implement and make take a slight downtime (should be minimal) event so be careful. Another thing worth noting is permissions to alter a database per ms docs a login must be either:

• the server-level principal login
• a member of the dbmanager database role in master
• a member of the db_owner database role in the current database, or dbo of the database

If you are GUI averse, you can also script these changes out to T-SQL and run those. For example, this script changes the Service Level Objective (SLO) to a Premium P2.

ALTER DATABASE [YOUR DATABASE] MODIFY (SERVICE_OBJECTIVE = 'P2');

GO

Once again Microsoft has catered to lazy DBAs like me that want everything in one place. This is another one of those things I am grateful to have the ability to do in SSMS.

Index Maintenance is an important aspect of database health. Above and beyond regular index rebuilds and reorganizations it is important to understand the usage of the indexes on your database. Cleaning up unused indexes can save a lot of overhead on Insert/Update/Delete operations. To achieve that goal, I typically run a script like the one shown below and check out whether or not an index has had any seeks or scans against it as a starting point in my cleanup regiment.

SELECT d.name,

       OBJECT_NAME(i.[object_id]) AS [ObjectName],

       i.[name] AS [IndexName],

       s.user_seeks,

       s.user_scans

FROM sys.indexes AS i

    LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s

        ON i.[object_id] = s.[object_id]

           AND i.index_id = s.index_id

           AND s.database_id = DB_ID()

    JOIN sys.databases d

        ON d.database_id = s.database_id

WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1

ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC

OPTION (RECOMPILE);

Azure SQL Database

If the index has not shown any utilization I investigate to determine if it is one that can be removed. However, this week something caught my attention. I was looking at a client’s indexes and noted the values for these were not as high as I would have expected. I know that these index statistics are reset upon every SQL Server Service restart, but in this case, I was working on an Azure SQL Database. which got me wondering exactly how that worked. With an Azure Virtual Machine or an on Prem SQL Server instance this is easy to figure out. But with an Azure SQL Database we do not have control over when restarts are done, and what about the Serverless offering (which pauses unutilized databases to reduce costs), how do those behave?  I really want to make sure before I remove any indexes from a database that I am examining the best data possible to make that decision. So, I did some digging.

What I found is that in Azure SQL Database, the Index statistics are reinitialized when the instance hosting your database or elastic pool is restarted. Since this occurs out of the database administrator’s hands it’s important that you run a query like the below to see the instance start time before making any determinations on whether to drop indexes or not.

SELECT sqlserver_start_time

FROM sys.dm_os_sys_info;

Serverless

As we know, a paused serverless database does not have a running instance of the database engine (it is just database files in storage), so index stats are also reinitialized every time a serverless database is resumed. This presents a big problem because I have a server that regularly pauses. There is no way I can use these index statistics to determine much of anything in regard to index cleanup and maintenance. Thus, I am writing this blog to make sure others are aware of this as well.

Using the Serverless option for your Azure SQL Database is a great way to save on resources, but it is very important that you know any drawbacks that it may have such as this. In the meantime, I would suggest trying to persist this data using DMV’s and writing it out to a table. I have not done this yet and not sure how well it will work, but it is worth a try.