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:

https://www.apress.com/in/book/9781430267430

You can also buy it from amazon:

https://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp/1430267437

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:

database_id

file_id

sample_ms

num_of_reads

num_of_bytes_read

io_stall_read_ms

num_of_writes

num_of_bytes_written

io_stall_write_ms

io_stall

size_on_disk_bytes

file_handle

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:

SELECT

    [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],

    [mf].[physical_name]

FROM

    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;

GO

You can check the detailed info here:

How to examine IO subsystem latencies from within SQL Server

Thank You!

Short Video for Everything you need to know about SQL Server 2017

Click to see Everything you need to know about SQL Server 2017:

Thank You!

Data source CHECK_DEPENDENCIES error while running SQL Performance Dashboard Report

“Data source 'CHECK_DEPENDENCIES“ error while running SQL Performance Dashboard Report

Today morning when I was working on one performance issue of a database server. I ran SQL Performance Dashboard Report and received error which I had never seen before. The error was:

A data source instance has not been supplied for the data source 'CHECK_DEPENDENCIES"

I closed the window and ran the report once again but no luck. Same error was on screen once again.

I tried to google it but did not find anything useful. I ran report one last time and tried to refresh once again and the error was changed this time

Error:

Could not fine stored procedure ‘msdb.MS_PerfDashboard.usp_SessionDetails’

Now the new error was regarding one missing stored procedure in my case. I copied and created that stored procedure from other server where it was working fine. After this my performance dashboard report started working fine.

Thank You!

How to find port on which SQL Server is running?

How to find port on which SQL Server is running?

One of the basic question which is generally asked in interview is “How to find the port number on which SQL Server is listening?”.   

There are multiple ways to find the SQL server port number and they are listed below:

1.    Read SQL Server Error Log file

2.    You can see in SQL Server Configuration Manager

3.    You can see in Windows Application Event Viewer

4.    You can use the DMV to get the Port number

Read SQL Server Error Log file

One on the easiest way to find the port number of SQL server is "ERRORLOG". The SQL Server Error Log records information with respect to the port in which an instance of the SQL Engine is listening. We can execute the below T-SQL command to find this info:

TSQL-Command:

xp_readerrorlog 0, 1, N'Server is listening on'

Using SQL Server Configuration Manager

We can check the Port number in SQL Server Configuration Manager. We need to expand SQL Server Network Configuration  and then select Protocols for "SQL instance name" on the left side. To identify the TCP/IP Port used by the SQL Server Instance,  right click on TCP/IP and select Properties from the drop down as shown below.

Using Windows Application Event Viewer:

We can also check the port number in event viewer log.  Just open event viewer and select Application on the left side panel.  In the right panel you need to filter for events with Event-ID 26022 as shown in the below screen shot.

 To set a filter , right click on the Application and then select Filter Current Log.

Using the DMV to get the Port number:

We can use the DMVs   sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections WHERE  session_id = @@SPID

Thank You!