Recently i have come across an issue with page time out. There was stored procedure that performs updates and inserts to 3 database tables on save. Usually this worked fine but gives a time out expired error when there are any sql jobs running in the background at the time of action. It was quite clear that this is a performance issue and will get solved if the stored procedure was optimized. But the thing is stored procedure was already optimized and there was nothing else to optimize it. I researched for about 1 week but no luck. Then i found out about DBCC DBREINDEX in sql that solved the issue at last. :-)
Those who are not aware of DBCC DBREINDEX, lets have some knowledge about it.
When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, indexfragmentation can occur and the information in the index can get scattered in the database.Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.
Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.
The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database
TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.
Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)
Example
DBCC DBREINDEX ('Vendor')
DBCC DBREINDEX ('Vendor','IX_Vendor',80)
Believe me! this increases the performance of the actions that we perform on these tables.
Those who are not aware of DBCC DBREINDEX, lets have some knowledge about it.
When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, indexfragmentation can occur and the information in the index can get scattered in the database.Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.
Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.
The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database
TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.
Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)
Example
DBCC DBREINDEX ('Vendor')
DBCC DBREINDEX ('Vendor','IX_Vendor',80)
Believe me! this increases the performance of the actions that we perform on these tables.
