If procmon.exe is used to monitor the logwrite size of SQLSERVER

Today, I also introduced this tool. This experiment is based on this article, "How big is each SQL Server logwrite?" This tool

How can I use procmon.exe to monitor SQLSERVER's logwrite size? Today I will also introduce this tool. This experiment is based on this article, "How big is SQL server every log write?" This tool

If procmon.exe is used to monitor the logwrite size of SQLSERVER

In the technical support team of Microsoft Asia Pacific data warehouse, you will find that many articles use the procmon.exe tool.

Today, I will also introduce this tool.

This experiment is based on this article 《What is the size of each log write on SQL server?

Of this tool:Http://files.cnblogs.com/lyhabc/ProcessMonitor.zip

The test content in this article is as follows:

What is the minimum size of SQL server log write?

So I did a test (Windows 7 + SQL server 2012)

1) I format the disk. The minimum allocation unit is 4 kb.
2) I put the log on the disk.
3) I commit a very small transaction.

To be the same as the test environment in the article

I will show the distribution unit of the drive letter of the database.

Test script:

1 USE [Northwind]2 GO3 CREATE TABLE t1 ( c1 INT )4 GO5 BEGIN TRAN6 INSERT  INTO t17 VALUES  ( 1 )8 COMMIT

How can we test it ??

Step 1: first, of course we need to open SSMS and then copy the script. Haha

Step 2: Find the SQL server process number, because my machine has installed SQL2005, SQL2008, SQL2012

So you need to find out what the SQL2005 process number is.

Step 3: Open the service manager. When I install SQL2005 and use the default instance, find the SQLSERVER service of the default instance,

Check which account is used to log on to the service.

SQL2005: system

SQL2008: network service

SQL2012: MSSQL $ SQL2012

Step 4: Open the task manager and check the process corresponding to SQL2005.

Find it. The process number is 1736

Of course, if you only have one SQLSERVER installed on your computer, there is only one default instance.

Step 5: Open procmon.exe

First, let's briefly introduce the functions of some buttons.

In fact, this software is the same as SQLSERVER profiler. Capturing an event is equivalent to "start tracking"

The following display box displays the event information one by one, which is equivalent to the tracking record in profiler and one record for you to see.

Event types: Registry, file system, process and thread, network, and performance

Just like the new tracking in profiler, there are also a lot of events for you to choose from

Step 6: Because I only observe logwrite here, you only need to monitor the file system. The Registry and network buttons do not need to be clicked.

Step 7: filter: you can filter a lot of content. Here I choose to display only the content of the SQL2005 process.

Click OK.

Capture and stop capture, and clear the display button

After clicking confirm, procmon.exe will immediately capture all file system-related operations of SQLSERVER.

Step 8: Stop capturing and clear the display.

Start testing

Test 1:

Run the test script multiple times.

1 BEGIN TRAN2 INSERT  INTO t13 VALUES  ( 1 )4 COMMIT

But since there are too many disk-related records, how do we look at the records related to transaction logs ??

We can use the highlight Function

Add two filter conditions:

OperationYesWritefile

PathYesE: \ database file 2013-10-30 \ northwnd. ldf

Records that meet the preceding two filter conditions are highlighted.

Then you will see all the matching items highlighted.

The minimum write size of SQL server logs is 512 bytes, which is the size of one slice.

This is basically the same as what is mentioned in the article.

Why is it basically the same, because I found a record of 2.5 MB, said Song:

The above 8 m and 4 M Disk writes should be caused by log growth, so I think it cannot be confused with log write?

Test 2:

We first clear and stop capturing events

 1 --CREATE TABLE t2 ( c1 INT, c2 CHAR(7000) ) 2 --go 3 BEGIN TRAN 4 DECLARE @i INT 5 SET @i = 0 6 WHILE ( @i < 100000 ) 7     BEGIN 8         INSERT  INTO t2 9         VALUES  ( @i, 'dadf' )10         SET @i = @i + 111     END12 CHECKPOINT13 COMMIT14 DELETE  FROM t2

Run the above script in two windows at the same time

Click "capture event" again to start capturing events.

We can see that the records are basically larger than 60 kb.

Why ??

Let's take a look at Master Song's article:Roles of logs in SQL Server

Summary

In fact, you can double-click each record with the left button. A detailed attribute dialog box is displayed, which contains a lot of process information and stack information.

Call Stack for readfile operations

Process tree

If anything is wrong, you are welcome to make a brick o