Skip to content

SSMS

Visualizing Log File VLF Sizing

One of my favorite queries this week is the following query that creates a text based bar chart to quickly help visualize the VLF files on any database log file. In the last month I have given the “TempDB Do This and Don’t Do That” presentation twice, once at the Bellingham SQL Server users group, and another time at SQL Saturday Redmond. One of the questions that came up after the users group meeting was around an easy way to visualize the Virtual Log Files. Its one thing to just get a count, but to see the sizing of each VLF is helpful to understand how VLFs work.

If you are not familiar with VLFs, these are Virtual Log Files, or the chunks that make up your entire log file. When your log file grows, VLFs are added to help manage the file space. For the log growth, between 4 and 16 VLFs are added for each growth of the log file (except on SQL Server 2014 and newer, which modifies the sizing algorithm a bit).

Read More »Visualizing Log File VLF Sizing

Status of DBCC CheckDB

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want to check and see what table is currently being checked.

Here is a quick SQL Script that I use to find the current table that is being checked by DBCC CheckDB

use [your database];
go

-- use this to find the session id of DBCC CheckDB
SELECT session_id, start_time, command, percent_complete, total_elapsed_time,
       estimated_completion_time, database_id, user_id, last_wait_type
  FROM sys.dm_exec_requests
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

DECLARE @sessionID as integer = 53; -- enter the session ID of DBCC CheckDB Here

SELECT name, schema_id, type_desc
  FROM sys.objects
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
                      FROM sys.dm_tran_locks
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);



status of dbcc checkdb


Read More »Status of DBCC CheckDB

Using Statistics Time for Performance Tuning in SSMS

Statistics IO and Statistics Time are another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

Have you ever wanted to know exactly how long it took for a query to run? Have you ever wondered how many I/O reads or writes were caused by your query? With Statistics IO and Statistics TIME you can understand both of these.

Statistics Time for Performance Analysis

This post is on using statistics TIME to analyze query performance. There is another post on using Statistics IO for performance tuning.

Another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

This is a simple step to get performance statistics on about any query you are writing in SSMS.

Let’s take the following query as an example:

Read More »Using Statistics Time for Performance Tuning in SSMS