Issue: Single row delete should not take 2 minutes to complete Cause: Looking at execution plan, a lot of processing on a different table with foreign key link to updating table. Foreign keys are constraints in the database to ensure that data values used in child table entry are present in a parent, reference table.... » read more

How to bring reverting/in recovery secondary db back online after failover

Issue: Secondary Database stuck in “Reverting/In Recovery” after Failover. Resolution: The only way to fix is by restarting the secondary machine, it then will kill the system process which was locking the master db.  Sources:

Database stuck “In Recovery” mode and database is not in a state that allows access checks

Error: Getting the following error when trying to bring the database back online. User does not have permission to alter database 'xxxxxx', the database does not exist, or the database is not in a state that allows access checks. -- Database is "RECOVERING" user_access_desc is_read_only state_desc is_in_standby MULTI_USER 0 RECOVERING 0 Resolution: Wait for database... » read more

With Index

Usually the DB engine should automatically select the index to use based on query execution plans it builds. However, there are some pretty rare cases when you want to force the DB to use a specific index.

Unable to Create Always On Listener Due to Permission Issue

Error: Cluster network name resource ‘XXXX’ failed to create its associated computer object in domain ‘xxxxx’ during: Resource online. The text for the associated error code is: Cannot complete this function. Please work with your domain administrator to ensure that: The cluster identity ‘XXXX$’ has Create Computer Objects permissions. By default all computer objects are... » read more

SQL Server Edition and Always On Setup

Standard Edition only allows Always On setup in basic mode. Switch to Enterprise Edition for full Always On features. Always On Basic Mode (Standard Edition) Limitations Basic availability groups use a subset of features compared to advanced availability groups on SQL Server Enterprise Edition. Basic availability groups include the following limitations: Limit of two replicas... » read more

Install SQL Server First Responder Kit Run Install-All-Scripts.sql in master database. Run sp_BlitzIndex.sql in individual database.

Stored procedure takes longer to run than query.

Issue: Hi I have a stored procedure and when i run it by using declare and set parameter it gives me result with in 30 seconds but if I use (exec sp_procedurename parametervalue1,parametervalue2) statement then it takes 5 minutes to execute. Resolution: If you’re running the same exact SQL as the stored procedure and the... » read more