What is the size of the SQL Server log file's write operation to the disk?

Source: Internet
Author: User

Original: What is the size of the SQL Server log file's write operation to the disk?

The SQL Server database has three file types, data files, secondary data files, and log files, where the log files contain all the log information used to recover the database, SQL Server always writes the log file LDF first, and data changes are written to MDF, which can lag So the speed of log writes determines the amount of write transactions that SQL Server can host in a certain program, so what is the LDF write size?

To know the size of SQL Server write log, use the tool process Monitor

Here a filter is set to satisfy only the collection of SQL Server write logs, as shown in:

The results in an OLTP production environment are analyzed as follows:
From this you can see that the log write of this userdb.ldf is sequential, and the write size is mainly 1024byte :

Through the above results, for the log file LDF write requirements, we select the disk and RAID, how to do? It is recommended to use the tool Sqlio to do a test for single-threaded sequential write 1KB hardware performance, to simulate log writes, which will be tested in a later article.

So, does SQL log write to LDF must be up to 1024byte? This is not necessarily the case, the following are the results obtained under Additional OLTP:

Here is the tempdb:

So, how much are you? Can be measured with the process monitor.
Process Monitor

The collected data is stored as logfile.csv.

The following statements are then used for analysis:

?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 USE tempdb GO CREATE TABLE [dbo].[Logfile]     (       [Time of Day] [nvarchar](MAX) NULL ,       [Process Name] [nvarchar](MAX) NULL ,       [PID] [nvarchar](MAX) NULL ,       [Operation] [nvarchar](MAX) NULL ,       [Path] [nvarchar](MAX) NULL ,       [Result] [nvarchar](MAX) NULL ,       [Detail] [nvarchar](MAX) NULL     ) ON[PRIMARY]GO BULK INSERT [Logfile] FROM ‘D:\Tmp\Logfile.CSV‘ WITH (          FIELDTERMINATOR =‘,‘,  FIRSTROW=2 )SELECT[Time of Day] ,         [Process Name] ,         PID ,         Operation ,         REPLACE(PATH, ‘DBname‘, ‘myDB‘) AS Path ,         Result ,         Detail FROM[Logfile] --WHERE PATH LIKE ‘%DBname%‘SELECTSUBSTRING(detail, CHARINDEX(‘Length:‘, detail) + 7,                   CHARINDEX(‘Flags‘, detail) - CHARINDEX(‘Length:‘, detail)                   - 13) AS size INTO# FROM [Logfile] WHEREPATH LIKE ‘%HighEndSeekerDB%‘SELECTsize AS [SIZE(Bypte)] ,         COUNT(*) AS CNT ,         LTRIM(CAST(COUNT(*) * 1.0 / ( SELECTCOUNT(*)                                       FROM#                                     ) * 100 AS NUMERIC(18, 4))) + ‘%‘ AS ratio FROM# GROUP BY size --ORDER BY count(*) desc --ORDER BY CAST(REPLACE(size, ‘,‘, ‘‘) AS BIGINT)  ORDER BY ratio DESC

  


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.