This is the fourth installment in a blog series. The previous entry is located here
Based on the previous blogs in this series, you should have gotten your database hosted in WASD by now & secured access to your server. At this point most customers proceed with writing code and deploying their application – never giving a second thought to the database after this initial stand up. After all the SQL Database is designed take care of lot of typical DBA type activities for you like patching, high availability/disaster recovery (HA/DR) to name a few.
However ignoring some basic database maintenance/administration steps could manifest itself as query performance problems down the road when your application becomes more popular and size of your data grows. If you interested in avoiding that 2 AM phone call where your application is getting query timeouts, continue reading ….
There a few basic database concepts that you need to understand (especially if you are not coming from a DBA background)
The common DBA type activities that we suggest you look into for WASD are as follows
We typically suggest customer put a weekly manual action in place to update statistics for all tables in the database via a the following command from Management Studio against the specific user database
EXEC sp_updatestats
The reason for doing the update statistics is to ensure that the query optimizer gets the correct data distribution in your tables in order to generate the optimal execution plan for it. We recommend you do this in non-Production hours is to reduce impact to users -since updating statistics acquires schema locks on the table and will block concurrent access. More information about the impact of updating statistics is given here.
For some customer who have large databases this might not be feasible due to performance impact. They can instead use the UPDATE STATISTICS commands for heavily used tables
In cases where you are doing lot of SELECT activity against tables that are being frequently inserted/updated, rebuilding indexes could help increase the throughput.
Here is script I wrote for one of the customer that generates a set of commands for each table in your database to
Declare @SchemaName varchar(255);
Declare @ObjectID int;
Declare @ObjectName varchar(255);
Declare @IndexID int;
Declare @IndexName varchar(255);
Declare @FragPercent float;
Declare @PageLock TinyInt;
Declare @command nvarchar(4000);
DECLARE @myIdxTable TABLE
(
dbname VARCHAR(255),
objectid INT,
objectname VARCHAR(255),
indexid INT,
indexname VARCHAR(255),
schemaname VARCHAR(255),
fragmentpercent FLOAT
)
Insert Into @myIdxTable
SELECT Quotename(Db_name()) AS DBName,
ips.object_id AS ObjectID,
Quotename(o.name) AS ObjectName,
ips.index_id AS IndexID,
Quotename(i.name) AS IndexName,
Quotename(sc.name) AS SchemaName,
ips.avg_fragmentation_in_percent AS FragmentPercent
FROM sys.Dm_db_index_physical_stats (Db_id(), NULL, NULL, NULL, 'Limited') AS ips
INNER JOIN sys.objects AS o
ON ips.object_id = o.object_id
INNER JOIN sys.schemas AS sc
ON sc.schema_id = o.schema_id
INNER JOIN sys.indexes AS i
ON i.object_id = o.object_id AND ips.index_id = i.index_id
WHERE ips.index_id >= 1
AND ips.avg_fragmentation_in_percent > 5.0
AND o.name <> 'sysDiagrams'
print 'Fragmentation - Before'
SELECT
objectname,
indexname,
fragmentpercent
FROM @myIdxTable;
print N'Script for Rebuilding/Reorganizing Indexes';
Declare myIdxCursor Cursor For
SELECT mytable.objectid,
mytable.objectname,
mytable.indexid,
mytable.indexname,
mytable.schemaname,
mytable.fragmentpercent
FROM @myIdxTable As myTable;
Open myIdxCursor;
Fetch Next From myIdxCursor
Into @ObjectID, @ObjectName,@IndexID, @IndexName, @SchemaName,@FragPercent;
While (@@FETCH_STATUS = 0 )
Begin
set @command = ''
If @FragPercent >= Convert(Float, 15.0)
Set @command = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @ObjectName + N' REBUILD';
print(@command)
--exec sp_ExecuteSQL @command;
Fetch Next From myIdxCursor Into @ObjectID, @ObjectName,@IndexID, @IndexName, @SchemaName,@FragPercent;
End
Close myIdxCursor;
DeAllocate myIdxCursor;
print N'Script for Updating Statistics for tables';
Declare myTblCursor Cursor For
SELECT DISTINCT
schemaname,objectname
Open myTblCursor;
Fetch Next From myTblCursor Into @SchemaName,@ObjectName
Set @command = ''
Set @command = 'Update Statistics ' + @SchemaName + N'.' + @ObjectName+N' WITH FULLSCAN,ALL'
Close myTblCursor;
DeAllocate myTblCursor;
GO
The Missing indexes DMVs give you a feel for what type of indexes the query optimizer found that may help with your workload. The definitive blog post from Bart here covers all you need to know about this feature. The query below needs to be run against your user database on WASD
DECLARE @runtime datetime
SET @runtime = GETDATE()
PRINT ''
PRINT '==============================================================================================='
PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '
PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT '-- Missing Indexes --'
SELECT CONVERT (varchar, @runtime, 126) AS runtime,
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
'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.*, 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
One of the limitations of WASD is the absence of SQL Agent. So the question becomes how can you schedule these maintenance operations? I have had success with people using their on-premise SQL Server to schedule executions of these scripts via sqlcmd or SQL Agent jobs. Here is an example of using sqlcmd to execute sp_updatestats
@echo off
set SERVERNAME=abc.database.windows.net
set USERNAME=abx
set PASSWORD=P@ssword
set DBNAME=xyz
sqlcmd.exe -S%SERVERNAME% -U%USERNAME%@%SERVERNAME% -P%PASSWORD% -d%DBNAME% -Q"sp_updatestats"
We will continue this conversation in the next installment of this blog series where we look at how to tune queries running against your user database in WASD.
Author: - Rohit Nayak (@sqlrohit)
Reviewers: - Keith Elmore, José Batista-Neto
Escalation Services, Microsoft
Author:- Rohit Nayak
Reviewers:- Keith Elmore, José Batista-Neto
Escalation Services., Microsoft