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…
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…
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…
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…
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…
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…
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…
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….