Troubleshooting Non-Buffer Latch Contention: LATCH_XX

Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures Latch waits are one of the possible causes of slowness in SQL Server which can limit the throughput (tps) of OLTP application. Whenever we experience slowness in performance of… Continue reading Troubleshooting Non-Buffer Latch Contention: LATCH_XX

SQL Server Predeployment IO Stress Testing using sqlio

While there are some really good in-depth whitepapers and blogs (specifically by CSS Team and Brent Ozar & his team) to address this topic. However the information in these blogs,whitepapers is kind-off scattered and for a novice the information in some these whitepapers can be overwhelming.In this blog post I would like to explain you… Continue reading SQL Server Predeployment IO Stress Testing using sqlio

What Perfmon counters should I monitor and what each of them mean?

Off Late, I am not able to update this blog as frequently as I would have liked to as I was some of personal work and lot of professional work. However most customers ask me this question on what performance counters do we need to monitor in order to troubleshoot performance issues with SQL Server… Continue reading What Perfmon counters should I monitor and what each of them mean?

I killed Reindexing Job and now it is in KILLED/ROLLBACK State for more than 9 hours

  One of our Customers ran into this issue where in he killed a Reindexing Job and following that it went into KILLED/ROLLBACK State and was in that State for more than 9 hours. First of all we would not recommend you to kill Job like Reindexing or DBCC CHECKDB which performs internal operations. However… Continue reading I killed Reindexing Job and now it is in KILLED/ROLLBACK State for more than 9 hours

Will NoLock Hint allow you to query a table which undergoing offline reindexing?

  Recently I was delivering a workshop and this question came from one of the participants Will NoLock Hint allow me to query a table which is undergoing offline reindexing? My instant answer to that was NO, we cannot query a table with or without NOLOCK hint while we are performing Offline reindexing but I… Continue reading Will NoLock Hint allow you to query a table which undergoing offline reindexing?

Fragmentation should not be the only factor which decides the Rebuild Index

Until now I was under the impression that fragmentation in the Index is the only factor which decides whether we should rebuild the index. As a rule of thumb we always recommend that when the avg_fragmentation_in_percent in the view  sys.dm_db_index_physical_stats  is between 5 and 30 We should reorganize the index and when the fragmentation is… Continue reading Fragmentation should not be the only factor which decides the Rebuild Index

HIGH CPU on Sql Server running Biztalk instances

  Recently we encountered a case where in a sql server which was running and hosting Biztalk instances was showing 100% CPU.   We started troubleshooting from the SQL end to identify who is major consumer of CPU and following were our observations and findings   ·         We used the perfmon counter Process  %Processor time… Continue reading HIGH CPU on Sql Server running Biztalk instances

Sql server performing slow after changing the Cost Threshold for parallelism

We have observed a few cases now where sql server has started performing slow after changing the Cost Threshold Of Parallelism server option of the sql server. Ironically in such cases we observe that sql server is performing slow even when there is no high CPU utilization and no high I/O operations on the server.… Continue reading Sql server performing slow after changing the Cost Threshold for parallelism