SQL Azure

When Did My Azure SQL Database Server Restart?

by

Getting the server restart time for an on-premise SQL Server is simple, and in fact there are several ways using sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases

In an Azure SQL Database, you don’t get access to those system objects.

Brent Ozar posted a way to get the approximate Azure SQL Database restart date/time but I found that some of the wait types can produce large outliers and skew the result:

Instead, I’ve modified to use a standard statistics technique to reject outlier values that are outside 1.5 times the interquartile range:

;with cte as
(
    SELECT wait_time_ms 
    FROM sys.dm_os_wait_stats w with(nolock)
    WHERE wait_type IN 
    (
        'BROKER_TASK_STOP',
        'DIRTY_PAGE_POLL',
        'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        'LAZYWRITER_SLEEP',
        'LOGMGR_QUEUE',
        'REQUEST_FOR_DEADLOCK_SEARCH',
        'XE_DISPATCHER_WAIT',
        'XE_TIMER_EVENT'
    )
)
select 
    approx_ms_since_restart = AVG(wait_time_ms), 
    approximate_restart_date = DATEADD(s, AVG(-wait_time_ms)/1000, GETDATE())
from 
cte
cross join
     (select 
         q1 = min(wait_time_ms), 
         q3 = max(wait_time_ms), 
         iqr = max(wait_time_ms) - min(wait_time_ms)
      from (select 
               wait_time_ms,
               row_number() over (order by wait_time_ms) as seqnum,
               count(*) over (partition by null) as total
            from cte
           ) t
      where seqnum = cast(total * 0.25 as int) or seqnum = cast(total * 0.75 as int)
     ) qts
 where (wait_time_ms >= q1 - 1.5 * iqr) AND (wait_time_ms <= q3 + 1.5 * iqr)

This tells me my Azure server restarted on the 21st Sept 2018 around 23:46.

It’s not as complicated as it first looks:

The first part is obviously the same as Brent Ozar’s query, it gets a list of waits and their respective cumulative wait times. We generate a row number ordered by wait time and the total number of rows, and then pick 2 values using the row number at positions a quarter and three quarters along that list (the first and third quartile values). We then reject any value that is smaller than the 1st quartile value minus 1.5 times the interquartile range and any value that is larger than the 3rd quartile value plus 1.5 times the interquartile range.

Note: DATEADD() only accepts an integer for its second parameter, and using milliseconds(ms) overflows for even short periods of server up-time. In fact, since it’s approximate it might be better to use:

approximate_restart_date = DATEADD(minute, AVG(-wait_time_ms)/60000, GETDATE())

SQL Server Unindexed Foreign Keys

by

I saw this, DMV To List Foreign Keys With No Index, via Brent Ozar’s weekly links email.

Unindexed foreign key columns might not be captured by the sys.dm_db_missing_index_details DMV because of their relatively small size. Lack of indexes on foreign keys might only have a small performance impact during reads but can lead to lock escalations during heavy write loads causing excessive blocking and possibly dead locks.

I’ve updated the original posted query to generate TSQL to create the missing indexes (which you should compare to the existing index landscape to see if any indexes can be consolidated before running in).

[Note: if you are unfortunate enough to have spaces in your table/column names, then you’ll need to replace them with an underscore ‘_’  (or other character) in the index name.]

;with cte_fk as 
( 
    select   
        fk_table_schema = OBJECT_SCHEMA_NAME(fk.parent_object_id),
        fk_table = OBJECT_NAME(fk.parent_object_id),
        fk_column = c.name,
        fk_name   = fk.name,
        fk_has_index = CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END,
        is_fk_a_pk_also = i.is_primary_key,
        is_index_on_fk_unique = i.is_unique,
        index_def = 'create index NC_' + OBJECT_NAME(fk.parent_object_id) + '_' + c.name + 
           ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + '(' + QUOTENAME(c.name) + ')',
        pk_table_schema = OBJECT_SCHEMA_NAME(fk.referenced_object_id),
        pk_table = OBJECT_NAME(fk.referenced_object_id),
        pk_column = c2.name,
        pk_index_name = kc.name,
        fk.*
    from     
        sys.foreign_keys fk
        join sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        join sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
        left join sys.columns c2 ON c2.object_id = fk.referenced_object_id AND c2.column_id = fkc.referenced_column_id
        left join sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK'
        left join sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        left join sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
)
select  
    * 
from    
    cte_fk c
    left join sys.dm_db_partition_stats ps on ps.object_id = c.parent_object_id and ps.index_id <= 1
where   
    fk_has_index = 0 
    -- and fk_table = 'mytablename'
order by 
    used_page_count desc

Do you Encrypt your Remote Connections to SQL Azure Databases?

by

If you’re not encrypting connections to SQL Azure (or any remote SQL Server instance), then you probably should.

Encrypted connections to SQL Server use SSL,  and that is about as secure as you can get (currently).

[Remember: SSL protects only the connection, i.e. the data as it is transmitted ‘on the wire’ between the client and SQL Server. It says nothing about how the data is actually stored on the server].

Update: Don’t forget to also set TrustServerCertificate=false

SSMS

When you open SSMS’s ‘Connect to Server’ dialog, click the bottom right ‘Options’ button, and make sure you tick the checkbox ‘Encrypt Connection’:

image

SQLCMD

Ensure you add the -N command line option. The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option ENCRYPT = true.

e.g.

sqlcmd –N –U username –P password  –S servername –d databasename –Q “SELECT * FROM myTable”

Linked Servers

When creating a linked server to SQL Azure,  the @provstr parameter must be set to ‘Encrypt=yes;’:

-- Create the linked server:
EXEC sp_addlinkedserver
@server     = 'LocalLinkedServername',
@srvproduct = N'Any',
@provider   = 'SQLNCLI',
@datasrc    = '???.database.windows.net', -- Azure server name
@location   = '', 
@provstr    = N'Encrypt=yes;',       -- <<--  Important!
@catalog    = 'RemoteDatabaseName';  -- remote(Azure) database name
go

 

ADO.NET Connection strings

Add “ENCRYPT = true” to your connection string, or set the SqlConnectionStringBuilder property to True.

[Remember: don’t distribute passwords by sending as plaintext over the Internet, i.e. don’t email passwords! ]

SQL Server and SQL Azure: Clear Plan Cache

by

For on-premise SQL Servers you can run

dbcc freeproccache

which clears the entire server cache (provided you have the ALTER SERVER STATE permission). Many online resources and SQL Server Books Online give scary warnings about running this, but running DBCC FREEPROCCACHE will cause very few problems, even on a busy OLTP system. It will cause a small CPU spike for a few seconds as query plans get recompiled. It can be a useful tool when base-lining expensive queries or stored procedures.

If that’s not selective enough, you can free the cached plans for a single database using an undocumented DBCC command, FLUSHPROCINDB:

-- Flush all plans from the plan cache for a single database  

declare @dbid int;

select @dbid = dbid from master.dbo.sysdatabases where name = 'MyDatabaseName';



dbcc flushprocindb(@dbid);

If you want to remove a single plan from the cache:

-- Get the plan handle (varbinary(64)) for a cached query plan

select 

    cp.plan_handle, 

    st.text

from 

    sys.dm_exec_cached_plans cp 

    cross apply sys.dm_exec_sql_text(plan_handle) st

where 

    text LIKE N'%GetJournal%';



-- Remove a specific plan from the cache using its plan handle

dbcc freeproccache (0x060050000C267C1030CE4EC70300000001000000000000000000000000000000000000000000000000000000);

DBCC FREEPROCCACHE is not supported in SQL Azure as that wouldn’t be practical in a multi-tenanted database environment. SQL Azure (and SQL Server 2016) has introduced a new mechanism for clearing the query plans for a single database:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

ALTER DATABASE SCOPED CONFIGURATION

This statement enables the configuration of a number of database configuration settings at the individual database level, independent of these settings for any other database. This statement is available in both SQL Database V12 [SQL Azure] and in SQL Server 2016. These options are:

  • Clear procedure cache.

  • Set the MAXDOP parameter to an arbitrary value (1,2, …) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).

  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.

  • Enable or disable parameter sniffing at the database level.

  • Enable or disable query optimization hotfixes at the database level.

Database Scoped Configuration