Currently Browsing: SQL Monitoring

SQL Server / Configure an Azure Load Balancer for a SQL Server Always On AG in Azure Virtual Machines / Possible Floating IP and Health Probes Connectivity and Networking issues

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Jan 21st, 2022 | 1 comment
SQL Server / Configure an Azure Load Balancer for a SQL Server Always On AG in Azure Virtual Machines / Possible Floating IP and Health Probes Connectivity and Networking issues
I will take you through the steps on how to properly configure Azure Load Balancer with Azure SQL Server VMs (IaaS), Windows Failover Cluster (WFC),  Availability Group AG, and listener endpoint. The following link shows us what is the best Microsoft Practice, and how Microsoft suggests we should configure an Internal Load Balancer (ILB) for the Availability Group Listener. Create & configure the load balancer   How LB really works with Always On Availability Group on SQL Server on Azure VMs? There are two types of Load Balancers  – Internal load balancers which balance traffic within a VNET...
read more

SQL Server / Availability Group / Readable Secondary Replica Delay / Parallel Redo Operations

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Apr 10th, 2021 | 0 comments
SQL Server / Availability Group / Readable Secondary Replica Delay / Parallel Redo Operations
One of the most common issues with Availability Group and using readable secondary replica is with Parallel REDO operations. On your readable secondary replica, you will notice a few different types of waits commonly related to this, but in this article, I will cover just one of them PARALLEL_REDO_TRAN_TURN PARALLEL_REDO_TRAN_TURN This type of waits happens in readable secondary replica and it is caused by page splits or forwarded records on heap tables triggered by new inserts or updates on primary replica. There are few ways to fix this issue– Reduce the number of pages splits on the primary...
read more

SQL Server / ALTER INDEX / CREATE INDEX Progress

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Oct 12th, 2018 | 1 comment
SQL Server / ALTER INDEX / CREATE INDEX Progress
Creating a new and altering existing indexes on the big tables can be time-consuming. There is a simple way to see the progress of the CREATE / ALTER INDEX command. Since SQL Server 2014 we can use sys.dm_exec_query_profiles DMV to monitor real-time query progress. sys.dm_exec_query_profiles (SQL SERVER 2014 and above) “Monitors real-time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the description field”   The first thing we need to...
read more

SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)

Posted by in SQL Monitoring, SQL Server on Jun 24th, 2018 | 0 comments
SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)
Recently I had a customer with a really slow query execution and a case of large page splits number on the temporary database. In this example, I tried to replicate the similar issue on my local SQL instance using StackOverflow database. You can download this StackOverflow DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent   Firstly I created an Extended Event session to follow up if the page splits occur. DBCC DROPCLEANBUFFERS CREATE EVENT SESSION [PageSplits] ON SERVER ADD EVENT sqlserver.page_split ADD TARGET...
read more

SQL Server / Monitor Backup and Restore progress

Posted by in SQL Monitoring, SQL Server, T-SQL on Dec 6th, 2017 | 2 comments
SQL Server / Monitor Backup and Restore progress
Recently I had a situation on QA environment where Backup and Restore took a long time to finish. It was HA environment with two nodes in Always On High Availability Group, so backup and restored occurred over network shared location. Drives were slow, database was huge and network latency not the best  so it took a long time to add database to the availability group. To follow up backup or restore progress with start time, total elapsed time and estimated completion time in similar situation you can use following script SELECT Session_id as SPID , Command , query.text AS "Query Text" , Start_Time ,...
read more

SQL Server / Extended Events / Finding Deadlocks

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Nov 20th, 2017 | 0 comments
SQL Server / Extended Events / Finding Deadlocks
Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it. First of all you need location where you gonna keep your XEL file, then we can create Extended Event session Using SSMS Creating new Extended Event session   Selecting Events you want to capture, in this case xml_deadlock_report   Save the event data to an XEL file   Using T-SQL CREATE EVENT SESSION [Deadlock_Report] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET...
read more

SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB

Posted by in SQL Monitoring, SQL Server on Nov 18th, 2017 | 0 comments
SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB
On one of our older instances I noticed unusually high number of page splits per second, nothing spectacular, average around 20+ (normally below 5) just something unusual for that period of time.   Same period of time day before   I was curious and wanted to investigate what is causing this additional page splits. I did not noticed any performance issues. As well I checked the indexes of all user databases and there where no additional page splits. SQL Server provides a DMV to track page splits sys.dm_db_index_operationl_stats. You can use per table or for the whole DB. SELECT IOS.INDEX_ID,...
read more

SQL Server / Different SQL Versions different query hashes

Posted by in SQL Monitoring, SQL Server on Oct 9th, 2017 | 0 comments
SQL Server / Different SQL Versions different query hashes
Recently I did upgrade from SQL Server 2012 towards version 2016, and I had some PRTG maps in place for two really important queries. After upgrade monitoring failed. Query hash has been changed for the exact same query. Honestly, I did not pay much attention to that previously since the majority of those PRTG maps checking same versions of SQL Server. Also, I was under impression that query optimizer computes the query hash during query compilation on base on query logic.  In that case, if query hash is changed that also means query has been changed.   I am calculating query speed on the base of...
read more

SQL Server / Backup File Size Issue

Posted by in SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Backup File Size Issue
Yesterday, one of my SQL alerts just sent me an email about my daily backups size. I noticed that my backup has doubled in size. What happened is that I changed my Task Scheduler which is running my PowerShell backup script and accidentally ran backup twice for the same backup set.  Nevertheless, SQL Server is smart enough and instead of overwriting your existing backup file its just append the new one. You can see from the picture below that default for SQL server (2012 in this case) is “append to the existing backup set”. Can be confusing but it is really the safest option for the user. In this...
read more

SQL Server / Index Usage Report Project / Most Expensive Query Plans

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Index Usage Report Project / Most Expensive Query Plans
I created this ETL to fetch and save expensive execution plans daily. When ETL process runs again firstly it creates directory and copy all plans from previous day to that directory, and only after that fetch new execution plans. This way I can look old plan later if I like. SSIS Package Description. Control Flow Screen   I am using File System Task to create new directory using the name of  day before, to store my yesterday’s execution plans.   As you can see I am using variable FolderPath to create folder name with day before date. This is Expression I used   From the valuated value...
read more

« Previous Entries