Currently Browsing: SQL Monitoring
Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks
on Jan 21st, 2022 | 1 comment
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...
Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks
on Apr 10th, 2021 | 0 comments
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...
Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks
on Oct 12th, 2018 | 1 comment
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...
Posted by Barac in SQL Monitoring, SQL Server
on Jun 24th, 2018 | 0 comments
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...
Posted by Barac in SQL Monitoring, SQL Server, T-SQL
on Dec 6th, 2017 | 2 comments
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 ,...
Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks
on Nov 20th, 2017 | 0 comments
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...
Posted by Barac in SQL Monitoring, SQL Server
on Nov 18th, 2017 | 0 comments
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,...
Posted by Barac in SQL Monitoring, SQL Server
on Oct 9th, 2017 | 0 comments
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...
Posted by Barac in SQL Monitoring, SQL Server
on Oct 8th, 2017 | 0 comments
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...
Posted by Barac in Index Usage Report Project, SQL Monitoring, SQL Server
on Oct 8th, 2017 | 0 comments
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...