Saturday, December 2, 2017

Things to know about DBCC DROPCLEANBUFFERS

You may have observed that sometimes DROPCLEANBUFFERS is not working as you expected. And there are still some pages in buffer area.

This is because "DBCC DROPCLEANBUFFERS" drops only CLEAN pages from the buffer pool.

A clean page is one that has not been changed since it was read into memory or last written to disk. A dirty page is one that has not been written to disk since it was last changed. Dirty pages are not dropped by DBCC DROPCLEANBUFFERS, they are only made clean by writing them to disk (either through one of the various kinds of checkpoints or by the lazy writer – or one of the per-NUMA node lazy writers if you have NUMA configured).

If you want that all pages from a database are flushed from memory, you need to first perform a manual CHECKPOINT of that database and then run DBCC DROPCLEANBUFFERS.

Thank You!

Script to find Dirty/Clean Pages in SQL Server

I use below script to find out the dirty and clean pages in memory. Along with this,the script
will also give you the size for both Dirty and Clean pages: 

T-SQL Script:

    [DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],
    [CleanPageCount] * 8 / 1024 AS [CleanPageMB]
        (CASE WHEN ([database_id] = 32767)
            THEN N'Resource Database'
            ELSE ([database_id]) END) AS [DatabaseName],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 1 ELSE 0 END) AS [DirtyPageCount],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 0 ELSE 1 END) AS [CleanPageCount]
    FROM sys.dm_os_buffer_descriptors
    GROUP BY [database_id]) AS [buffers]
ORDER BY [DatabaseName]

Thank You!

Reference: Paul Randal Blog

Thursday, November 30, 2017

SQL Server Query Performance Tuning Apress Book Review.

SQL Server Query Performance Tuning A-Press Book Review:

Last year I have finished reading this book and I found this very useful. I've found here some very interesting details I haven't seen anywhere else. The book explains you each and everything in so detail which you will love to read. There are so many things which will make you perfect in are of Query optimization and Performance tuning. The book is written Grant Fritchey.

The book is having 26 chapters:

Chapter 1: SQL Query Performance Tuning
Chapter 2: Memory Performance Analysis
Chapter 3: Disk Performance Analysis
Chapter 4: CPU Performance Analysis
Chapter 5: Creating a Baseline
Chapter 6: Query Performance Metrics
Chapter 7: Analyzing Query Performance
Chapter 8: Index Architecture and Behavior
Chapter 9: Index Analysis
Chapter 10: Database Engine Tuning Advisor
Chapter 11: Key Lookups and Solutions
Chapter 12: Statistics, Data Distribution, and Cardinality
Chapter 13: Index Fragmentation
Chapter 14: Execution Plan Generation
Chapter 15 Execution Plan Cache Behavior
Chapter 16: Parameter Sniffing
Chapter 17: Query Recompilation
Chapter 18: Query Design Analysis 
Chapter 19: Reduce Query Resource Use 
Chapter 20: Blocking and Blocked Processes 
Chapter 21: Causes and Solutions for Deadlocks
Chapter 22: Row-by-Row Processing
Chapter 23: Memory-Optimized OLTP Tables and Procedures
Chapter 24: Database Performance Testing 
Chapter 25: Database Workload Optimization
Chapter 26: SQL Server Optimization Checklist 

I will recommend this book to someone who really want to enhance his skills in area of Query Optimization and performance tuning.

Below are the option to purchase this book:

Buy it from Apress official webpage:

You can also buy it from amazon:

Thank You!

Script to find Read Write Latency in SQL Server

Script to find Read Write Latency in SQL Server

In order to view the disk latencies in SQL Server we can quickly and easily query the DMV sys.dm_io_virtual_file_stats. This DMV accepts two parameters: database_id and file_id.
You can also pass NULL as both values and return the latencies for all files for all databases. The output columns include:

What values are good or bad?
If you check Paul Randal blog on SQLskills, You will find below values to make your decision about good or bad latencies:

Excellent: < 1ms
Very good: < 5ms
Good: 5 – 10ms
Poor: 10 – 20ms
Bad: 20 – 100ms
Really bad: 100 – 500ms
OMG!: > 500ms

Below is the SQL script which I generally used to check the disk latencies. This script is created by Paul Randal. It allows you to filter on read or write latencies and it will also provide you the database names and file paths.

SQL Script:

    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;

You can check the detailed info here:

Thank You!