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)

  • A Statistic is a database object that represents data distribution within your table in a histogram.
  • An Index is a database object that orders data rows in a table. Indexes can be clustered or non-clustered
  • An Execution Plan is representation of steps done by the database engine in order to execute the query or stored procedure that you are submitting from the application.
  • Dynamic Management Views (DMVs) are system populated views that contain useful information for tuning and troubleshooting queries.

The common DBA type activities that we suggest you look into for WASD are as follows

  • Update Statistics

    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

  • Rebuilding Indexes

    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

    • REBUILDing any indexes that are more than 15 % fragmented
    • UPDATING STATISTICS for all stats objects for the table

      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

      FROM @myIdxTable;

       

      Open myTblCursor;

      Fetch Next From myTblCursor Into @SchemaName,@ObjectName

      While (@@FETCH_STATUS = 0 )

      Begin

              Set @command = ''

              Set @command = 'Update Statistics ' + @SchemaName + N'.' + @ObjectName+N' WITH FULLSCAN,ALL'

              print(@command)

      --exec sp_ExecuteSQL @command;

              Fetch Next From myTblCursor Into @SchemaName,@ObjectName

      End

      Close myTblCursor;

      DeAllocate myTblCursor;

      GO

     

  • Add Missing Indexes

    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 ''

    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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • Add Missing Indexes

    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

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

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

 

 

 

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

Reviewers:- Keith Elmore, José Batista-Neto

Escalation Services., Microsoft