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…
Hi All, It has being quite some time since I added a new post since off late I am not spending much of time on research. But I have started my research again and in this post I will be talking about Fragmentation of Index which occurs due to split of root node of…
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…
Many times we need to reinstall a sql server instance due to number of reason. Whenever you reinstall the sql server instance the success of the installation of the new sql server instance primarily depends on the whether the previous uninstall of the sql server was clean so that all the registry keys and…
While running your application on SQL CE v3.1 databases you may observe slowness in the performance of some of the queries especially those which involve querying the primary key. In Sql server databases when you create a Primary Key on a column of a table, it will automatically create a unique index on the column…
We have observed a number of issue now where in clustered instance of sql server 2005 fails to come online after performing changing on shared disk on a cluster. As mentioned in my previous posts if the sql server fails to come online we need to start troubleshooting by looking at the sql server error…
Recently I encountered a case wherein clustered instance of sql server 2000 fails to come online on other node of a 2 Node Cluster. Usually for such cases we need to start troubleshooting by checking the Error log of the sql server from the server where it fails to come online. The Sql Server…
We have observed a number of cases where the installation of the sql server 2005 instance on the cluster fails with the following unexpected Failure Error Message while performing SCC checks. ERROR MESSAGE: ==================== There is unexpected failure. MSGID 50000 In order to troubleshoot the issue we followed the following steps 1) We…
We have observed a number of case where we use sql server replication on a clustered instance of sql server and when we try to failover from Node 1 to Node 2 the Log Reader agent may fail to start with the following error Error messages: The process could not execute ‘sp_MSpub_adjust_identity’ on…
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….