Improve database performance
- Monica Holliday
- Max Avagliano
- Julie Hanlon
Problem
When running a report or opening a form/window, data is slow to appear.
Solution
Following are some general suggestions for addressing general database performance tuning.
Advance SQL Programming skills required.
Advanced SQL programming and DBA skills are required to complete this article.
Before performing any of these procedures it is advisable to do database backup.
Jiwa also come shipped with a standard stored procedure usp_JIWA_SpaceUsed. Execute this stored procedure first. This will give you an idea of where all data is currently being used.
Table Statistics
SQL server keeps statistics on the best strategy to use for queries - which indexes to use and when to use a table scan. This is constantly updated and sometimes will need to be rebuilt if the environment changes significantly or the data changes significantly in a short time frame (such as when large volumes of data are imported).
Jiwa comes with a stored procedure to rebuild statistics for all tables - run the following query to run this stored procedure. This may take some time to run. Degraded performance may be experienced in the short term after running this - but will improve quickly as the application submits queries and the SQL Server has time to build new statistics.
Rebuild table statistics.
update_all_stats
Database Indexes
Databases use indexes on tables to locate data quickly. If there are large changes in data or volumes of data over time - or if the database is moved to another SQL Server, maintenance may be required on the indexes.
Rebuild Indexes
DECLARE @TableName VARCHAR(max)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+ name +']' AS TableName
FROM sys.tables
ORDER BY name
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TableName <> 'dbo.[SY_LicenceUsages]'
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
RAISERROR(@sql, 0, 1) WITH NOWAIT
EXEC (@sql)
END
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Reorganize Indexes
After indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. Even so, these automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index over time. The result – indexes now have pages where logical ordering differs from the physical ordering inside the data file. This means that there is a high percentage of free space on the index pages and that SQL Server has to read a higher number of pages when scanning each index.
Reorganize Indexes
DECLARE @TableName VARCHAR(max)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+ name +']' AS TableName
FROM sys.tables
ORDER BY name
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TableName <> 'dbo.[SY_LicenceUsages]'
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
RAISERROR(@sql, 0, 1) WITH NOWAIT
EXEC (@sql)
END
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
If these suggestions don’t provide an improvement in performance, then see our article on Query Store