Sometimes there is a need in our databases or even in external databases to perform some optimization in terms of the data structure.
In my experience, there are three approaches that we can take: one more basic and quick, another more depth that involves a restructure and re-planing of the database, and finally the last one that is a mix of the previous two approaches.
In this article, I want to share the basic and quick approach for this Microsoft Database Engine. If you want to see similar content but for MySQL/MariaDB check my other article: How to Improve MYSQL / MariaDB Database Performance.
I have various projects where I need to integrate existing client MSSQL databases in a custom web application, for various reasons like getting clients, suppliers, products, data of all sorts. Sometimes I have issues in performance and the bottleneck is the external database, using this method in most cases grants me visible results, but always depends on the state of the target database.
The indexes in a database play the main role in this method, in a large database rebuilding the indexes may take your database to another level in performance, sadly the bad implementation of the indexes also could make your database slower (this topic is more advanced and is not covered in this article).
So let’s speed up our databases!
Note: it’s safe, your database data is not at risk!
You can manually rebuild by table using this SQL command (just change table_name for your database table):
ALTER INDEX ALL ON table_name REBUILD ;
Or you could do this more advanced script for the entire database, this will fetch all the database tables and basically use the above command for each table, just replace database_name in the 7th line with your target database name :
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name IN ('database_name') -- databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1OPEN DatabaseCursorFETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGINSET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''-- create table cursor
OPEN TableCursorFETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd -- uncomment if you want to see commands
END CATCHFETCH NEXT FROM TableCursor INTO @Table
DEALLOCATE TableCursorFETCH NEXT FROM DatabaseCursor INTO @Database
That’s it, execute and wait a bit (depends on your database size)… and test the performance of your queries.
I hope this information will help others.