How to Secure Azure Bot Service Endpoints with Teams Channel?
August 7, 2025
Getting Started with GitHub Spark: From Thought to App Creation
August 7, 2025
Azure SQL Database
SQL Server Management Studio (SSMS)
Is not recommended to execute in business hours
In update statistics and rebuild indexes there is not status of the process it means you will not see % of the process you will see an update when process is finished. Is important to consider number of lines to execute according to size of the tables because SQL is going to execute everything at the same time and may impact performance of the database by instance you can select 5 lines for small tables but 1 line in huge tables
1-Update statistics with fullscan (mandatory, apply to all tables)
*When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute
— Update ALL Statistics WITH FULLSCAN
— ONLY GENERATES COMMANDS DOES NOT EXECUTE
— This will update all the statistics on all the tables in your database.
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
where TABLE_TYPE = ‘BASE TABLE’
OPEN updatestats
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement = ‘UPDATE STATISTICS ‘ + ‘[‘ + @tableSchema + ‘]’ + ‘.’ + ‘[‘ + @tableName + ‘]’ + ‘ WITH FULLSCAN’
PRINT @Statement — comment this print statement to prevent it from printing whenever you are ready to execute the command below.
— Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server
–EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
END
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO
— end of script
After execution you will see results
Now you can select number of rows and copy in a new query window
2-Rebuild indexes (mandatory, apply to all tables)
*When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute
— Rebuild indexes
— ONLY GENERATES COMMANDS DOES NOT EXECUTE
— This will rebuild all the indexes on all the tables in your database.
SET NOCOUNT ON
GO
DECLARE rebuildindexes CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
where TABLE_TYPE = ‘BASE TABLE’
OPEN rebuildindexes
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement = ‘ALTER INDEX ALL ON ‘ + ‘[‘ + @tableSchema + ‘]’ + ‘.’ + ‘[‘ + @tableName + ‘]’ + ‘ REBUILD’
PRINT @Statement
— Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server
–EXEC sp_executesql @Statement
FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName
END
CLOSE rebuildindexes
DEALLOCATE rebuildindexes
GO
SET NOCOUNT OFF
GO
— end of script
After execution you will see results
Now you can select number of rows and copy in a new query window
3- Missing indexes (optional review with your development team)
SELECT CONVERT (varchar, getdate(), 126) AS runtime, migs.avg_user_impact,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement,
‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ +
CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns,”) +
CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ‘,’
ELSE ”
END + ISNULL (mid.inequality_columns, ”) +
‘)’ +
ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’,
”) AS create_index_statement,
migs.avg_total_user_cost,migs.user_seeks,migs.user_scans,
mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1),
migs.avg_total_user_cost *
migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost *
migs.avg_user_impact *
(migs.user_seeks + migs.user_scans) DESC
Based on improvement or avg_user_impact you may decide indexes to create keeping in mind that needs to be analyzed remember adding index increase work with Insert / update / delete operations
Copy create_index text of desired commands and copy in a new window to execute
Now you are ready to start troubleshooting good luck!
How to maintain Azure SQL Indexes and Statistics
https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787
AUTO_CREATE_STATISTICS Option
https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#AutoUpdateStats
Statistics
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16
Query execution times are slow
Statistics options
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16&redirectedfrom=MSDN#statistics-options
When to update statistics
Index maintenance strategy
Reorganize an index
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#reorganize-an-index
Rebuild an index
SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild