Do I need to upgrade my DBA skills for the cloud?

  • Article

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