What is Accelerated Database Recovery SQL Server 2019 introduced Accelerated Database Recovery (ADR) as a new feature. ADR reduces the time it takes to recover a database from a crash or other failure. It accomplishes this by introducing a new recovery mechanism that greatly reduces the amount of time it takes to roll back transactions … Read more
Recently I got a interesting issue where I was getting error ‘SHOWPLAN permission denied in database ‘SSISDB” even though I was having SYSADMIN permissions on the SQL instance. This is in continuation of previous article https://wordsontech.com/failed-to-execute-is-server-package-because-of-error-0x80131904/ I was trying to get estimated exeution plan for the stored procedure “internal.cleanup_server_retention_window” using the show estimated execution plan … Read more
Troubleshooting SQL Server query slowness issue. Query optimization through parameterization. Fixing duplicate plan issue.
We were working for a customer who was facing below issues on MS SQL Database server: Initial Approach: Issue Identification: Above things helped very little so we started further investigation of deadlocks and execution plans of high CPU queries. We identified below issues: 5. Winner query acquired key lock on entire non clustered index due … Read more
High CPU on a SQL Server could have various causes and we need to provide the resolution accordingly. One of our customer reported CPU being 100% for few hours and it was not going down even for few seconds. Majority of CPU workload was due to SQL processes only. This customer had never complained about … Read more
This code will execute IndexOptimize stored procedure and will reorganize or rebuild indexes based on default fragmentation values ( i.e., 5 and 30% – rebuild). However, this will not update statistics as default set to null.
On one of SQL 2016 server HIGH CPU usage alert was received. It was constantly above 95% and SQL server was highest CPU consumer. As checked with sp_who2, Resource Monitor command running under system SPID was biggest CPU consumer and its CPU usage was not coming down.
In our last post we discussed an approach on what we should do to find whether there is a memory issue or a way to identify if there is a memory crunch which is causing bad SQL performance. However, not always the thing we see on the surface is the root cause. Though there may … Read more
We in the first post saw how capturing and analyzing wait statistics can help a SQL DBA to look for potential performance issue. In the second post we looked at some of the example and potential scenario of wait stats which were similar to a production environment. In this post, we will see how we … Read more
Recently I worked on an interesting scenario where an incident was reported for very low PLE value. Very low PLE value indicates memory pressure on SQL server and should be fixed to ensure good SQL server performance. First lets check the definitions of PLE and BCHR as per Microsoft BOL: Page Life Expectancy: Indicates the … Read more