Skip to content

TSQL

Determining how much of your data file has been modified

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup.


SELECT * 
FROM sys.dm_db_file_space_usage;

What is really interesting about this is that with a little math you can calculate the percentage of your data file that has been modified.  This would be useful when running differential backups to be able to determine if it would make more sense to run a full backup or a differential backup. Basically when your differential backup gets large enough, based on the number of modified_extent_page_count pages, then it may make sense to do a full backup and reset this counter, and get back to smaller differential backups.

Here is an example

SELECT df.name, df.physical_name,
 total_page_count,
 allocated_extent_page_count,
 modified_extent_page_count, 
 100.0 * modified_extent_page_count / allocated_extent_page_count as PercentChanged
FROM sys.dm_db_file_space_usage fsu
INNER JOIN sys.database_files df on df.file_id = fsu.file_id;

 

Now based on the Percent Changed column we can make some assumptions on the size of the differential backup, and decide if we want to do a differential backup or a full backup.

Read More »Determining how much of your data file has been modified

DBCC ShrinkDatabase – I want to shrink my database.

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it.

This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file.

Not shrinking your database is one of the more counter intuitive things out there. You might think that a smaller database is a good thing, however there are some negative side effect if you shrink your database regularly, or have the autoshrink option enabled. Side effects of shrinking your database include:

  • Excessive I/O due to the shrink.
  • Index fragmentation (most likely all of your indexes).
  • Excessive I/O to defragment your indexes.
  • After the shrink is complete, inserting or updating rows that require more space in your database will be slowed due to the time involved with growing your data file.

Read More »DBCC ShrinkDatabase – I want to shrink my database.

DBCC CheckDB All Databases

If you use the SQL Server maintenance plans to run DBCC Check DB, you are not allowed to include TempDB in your DBCC CheckDB sequence. For a while I was using maintenance plans, and ending up adding a separate job step to run TSQL to just check TempDB.

Rather than using two different steps, the regular maintenance plan, and a special step for Temp DB, I have instead dumped the regular maintenance plan step, and just used the following TSQL code to run DBCC CheckDB against all databases on the SQL Server.

Option 1: DBCC CheckDB All Databases using sp_msforeachdb

I have included Option 2 below since there are some pretty serious flaws in the sp_msforeachdb script as shown in option 1, specifically sp_msforeachdb will occasionally skip databases. It also has trouble with databases created with certain characters.  Anyway if you want it, here is, but I would recommend paging down to Option 2 which is much more reliable. I would like to thank Patrick Flynn who showed me the flaws in sp_msforeachdb.

The stored procedure sp_msforeachdb takes a parameter of query that will get run against all databases on your SQL Server Instance.


EXEC sp_msforeachdb 'DBCC CHECKDB(''?'') WITH NO_INFOMSGS;';

You can set it up as a maintenance plan like this:

Read More »DBCC CheckDB All Databases

SQL Server Tables – BTree or Linked List?

Is a clustered table in SQL Server a BTree or Linked List?

When you first learn about the structure behind clustered indexes in SQL Server, you find out that the clustered index is structured as a type of B+Tree where queries that make use of the tree structure to find the rows that you are looking for. However it is not the common belief that that table with the clustered index also happens to be a doubly linked list, and that the B+Tree can be completely skipped when running a query that uses the clustered index via a clustered index scan.

Take the following table and query as an example:

CREATE TABLE [dbo].[Orders](
	[id] [int] IDENTITY(-2147483647,1) NOT NULL,
	[orderDate] [DATETIME] NOT NULL,
	[customerId] [int] NULL,
	[shippingType] varchar(100),
	[orderDetails] varchar(max),
	[totalPrice] DECIMAL(12, 2)
	CONSTRAINT [PK_Revenue] PRIMARY KEY CLUSTERED ([id] ASC, [orderDate])
);

Then insert a bunch of rows, and run a query.

Read More »SQL Server Tables – BTree or Linked List?