How to Improve Microsoft SQL Server Database Performance

Filipe Pires
Jan 19 · 3 min read
Source: Pixabay
Source: Pixabay

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 1
OPEN DatabaseCursorFETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @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
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

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.

Nerd For Tech

Filipe Pires

Written by

Dad, Senior Software Development Engineer for Enterprise Applications, Game Developer/Collector/Player, Electronic development/prototyping

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To know more about us, visit https://www.nerdfortech.org/. Don’t forget to check out Ask-NFT, a mentorship ecosystem we’ve started

Filipe Pires

Written by

Dad, Senior Software Development Engineer for Enterprise Applications, Game Developer/Collector/Player, Electronic development/prototyping

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To know more about us, visit https://www.nerdfortech.org/. Don’t forget to check out Ask-NFT, a mentorship ecosystem we’ve started

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store