The AlwaysOn_health XEvent session is installed by SQL Server by default, and started by the Create Availability Group wizard when a new availability group is created. AlwaysOn_health Extended Event Session The xEvent session collects events triggered by events directly affecting AlwaysOn availability group like; AlwaysOn errors AlwaysOn state transitions AlwaysOn DDL executed to create, drop, […]
AlwaysOn Dashboard uses System Policies
Read more
The AlwaysOn Dashboard is an excellent tool in SQL Server Management Studio that allows you to monitor Replica status, Availability Group status, and Database status. But, if you are wondering what the Dashboard uses internally to monitor the AlwaysOn health, so here is the answer – It utilizes system policies for monitoring AlwaysOn health. Policy […]
Why Availability Databases Are Not Synchronizing?
Read more
If you are working on SQL Server Availability Group Databases, you may have seen that Availability Group is online but at-least one availability database at the primary or the secondary is not synchronizing. In the blog, we are going to explore how to find what has interrupted synchronization between availability databases on the primary and a […]
Schedule an Extended Event to run at a Specific Time
Read more
When you are using the extended event to capture some data for troubleshooting purpose, you may want to start or stop it at a specific time. Unfortunately, the Extended Event feature doesn’t provide you the option to schedule it. But, it can easily be accomplished by using “ALTER EVENT SESSION” T-SQL code and SQL Server […]
Another MVP Award – 2018-2019
Read more
Gladly sharing that I got the news of my MVP award on 1st July. I am happy, honored, and humbled to receive the Microsoft MVP award 2nd time in a row. Thank you !! My gratitude to my family who has been always there to support, guide and making me and letting me make it […]
Identify Oldest Active Transaction with DBCC OPENTRAN
Read more
The DBCC OPENTRAN command gives you the information you need by displaying information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions within the specified database. You can run this against any database and it gives very useful information very quickly. /*Select the database where you want to check open transactions*/ use History […]
Enable SQLCMD Mode in SQL Server Management Studio
Read more
SQLCMD mode in SSMS allows you to use the command line tool within SSMS. Using this feature of the SSMS, you don’t have to connect SQLCMD using the command prompt. It is really very helpful and less distracting when you want to connect with multiple SQL Server instances. You can follow the below steps to […]
SQL Server Stress Testing Using OStress – RML Utilities
Read more
As a DBA, implementing a new database consists of a variety of aspects like correct table structure, tuning the stored procedures, creating right indexes, and many more. But all of these items, the stress testing and performance benchmarking play one of the most crucial roles. There are many tools available for stress testing, but they can […]
Find Query with Implicit Conversion in the Plan Cache
Read more
In my previous blog, I discussed about “Which are the queries using a particular index or table?“. Today’s post, I am going to show how you can find all queries with Implicit Conversion in SQL Server. What is an implicit conversion? Implicit conversions occur whenever data with two different data types are being compared. Implicit conversions will often harm […]
Which are the queries using a particular index or table?
Read more
The DMV sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats give you excellent detailed information about how the indexes are being used and what operations are occurring (select, insert, update, delete) in the database. But they don’t tell you a particular index or table is being used by which all the queries. So, when you want to tune the existing […]