Automated batch update or delete operation in SQL Server

If we perform update or delete operation on huge number of rows in a large table at once, there are chances that the SQL Server would freeze or would cause outage. And if we try to kill the transaction in the middle, we might end up in a more dangerous “rollback” state. That’s why I spent some time to build this script that helps in automated batch operation in small chunks with constant check for query performance. The script would increase the chunk size based on the time taken by the each execution so that it would end at maximum achievable chunk size that has adequate performance.

List out and script all indexes in a database

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. ūüė•

Continue reading “List out and script all indexes in a database”

Monitor Page splits using Extended Events session in SQL Server 2012

There are several ways to configure and start monitoring page splits on SQL Server. But now in 2012, we could make use of extended events to easily track down page splits.

If you do not want to go through the session wizard, I am sharing the script with which you could create the session in one go.

Continue reading “Monitor Page splits using Extended Events session in SQL Server 2012”

Interview Questions – Part II

  1. What are differences between a latch and a lock?
  2. What are the different states of an spid in SQL Server? Explain each state.
  3. What are workers in a SQL Server?
  4. How to reduce network usage by a query?
  5. Explain SOS_Scheduler_Yield wait type.
  6. What are CXPACKET waits? how to avoid that?
  7. Difference between a VAS and buffer pool?
  8. What are the different components of a SQL Engine? Explain how a SQL query is processed by SQL Server (Query lifecycle)?
  9. What are the different components of an RDBMS Engine?
  10. What are primitive and non-primitive way of DBMS functioning? In which type does the SQL server belong to?
  11. What are Memory Clerks?
  12. Explain SQLOS.

Auto Index Maintenance for All Databases in a SQL Server Instance


A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at But that script would perform the maintenance only on a particular database at a time.


The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.

1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.

2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag % and @maxfrag % will be reorganized. All remaining indexes will be rebuilt.

Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.

Continue reading “Auto Index Maintenance for All Databases in a SQL Server Instance”

FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions

One fine morning,¬†users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged in and checked if there are any blocks.


FROM   sysprocesses

WHERE  blocked <> 0 

There were several blocks caused by a naughty spid 80.

When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.

Continue reading “FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions”

Query Sharepoint Content DB for Doc Library Sizes and Number of versions

SELECT v.[id],


       Count(v.[version]) MaxNumberOfVersions,

       Sum(v.[size])      TotalDocLibSize,

       Max(v.[size])      LargestDocSize

FROM   [dbo].[alldocversions] v

       JOIN [dbo].alldocs d

         ON =




Resumable Auto-Index Rebuild and Reorganize Script

As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.

To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.

Continue reading “Resumable Auto-Index Rebuild and Reorganize Script”

Kill waiting for preemptive _os_pipeops


Detaching a database with drop all connections enabled was running continuously without completing. When checked what was causing the block or lock, I found the Kill/Rollback was waiting with waittype “preemptive _os_pipeops”.


With some googling, I came to know that it is something to do with a process running at the operating system level. So the solution was to end any process like wscript or cscript or cmd running with sql server service account or whatever you think might be the process that used the SQL Database.

In my case, it was a vbscript running as wscript process running with SQL server service account. I found that in Windows task manager.