The effect of RAID cache backup battery failure

RAID write times are improved by writing to a cache.   This is a quick operation, the calling program can get on with its business, and the RAID controller can solidify the cached changes to disk(s) in its own time.  The cache power supply has a backup battery.

When the cache backup battery condition measures below a safe limit it is marked as FAILED! (which raises alerts) and the RAID controller stops using the cache and writes only direct to disk.

Performance Effect?

How much does that affect performance?   Those in the know (some network engineers) suggested it might add 20ms to write times.  They weren’t far wrong.  The Disk avg write time metric on the Redgate monitor showed just under 30ms increase

It had no effect on average disk read time, as would be exected:

 

 

Backup Compression

Sample backup compression figures – SQL Server 2012.  The relative sizes and timings are interesting. This was a database with data that compressed well.

  • Uncompressed backup file:  73.4GB.  18 minutes to create
  • Compressed backup:  17.5GB.  About 20 minutes to create
  • Compressing the 73.4GB backup file using 7-zip:  9.7GB.  About 2 hours to compress.

 

Powershell Commands for SQL Server

SQLPS

The SQLPS utility is being replaced by the sqlps module:

Import-Module "sqlps" -DisableNameChecking

To run this interactively against a remote server, enter a remoting session:

Enter-PSSession -ComputerName HOST-NAME -Credential DOMAIN\user

Add a User to a SSAS Role

This solves the problem of adding a domain user to a SSAS database role from a different domain which cannot be done from the SSAS project or via SSMS (XMLA) from outside the domain since these methods attempt to resolve credentials locally.

Import-Module "sqlps" -DisableNameChecking
cd sqlas
cd "database name"
Add-RoleMember -Database "Telecoms Sales" -RoleName "ReportingServices" -M
emberName "DOMAIN\UserName"

(It looks like the SSRS default execution account can’t be used implicitly as with sql datasources)

Data-Tier Application (dac) and SqlPackage.exe

Can you install tools to export/deploy DacPac files without requiring a Visual Studio installation?

Favourite SqlPackage commands

Command Line reference  https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

Extract DACPAC I didn’t find that the /p:IgnoreUserLoginMappings={True | False} switch made any difference. Users were still created on default deployment.

The definition in the reference is not easy to interpret: “Specifies whether relationships between users and logins will be ignored.”  Does that mean it will still attempt to create users, or not?

Deploy DACPAC   “C:\Program Files\Microsoft SQL Server\120\DAC\bin\SQlPackage.exe” /a:publish /tcs:”Data Source=localhost;Initial
Catalog=mydb;Integrated Security=true” /sf:”C:\Temp\db_backup\ADatabase.dacpac”
/p:ExcludeObjectTypes=RoleMembership;users;Logins;

This excludes users and logins, necessary if deploying to environment that doesn’t support users that were in the source.  If there are objects owned by a custom user the deployment will still fail.

Move Data Files

Taking a database offline is probably less invasive than a detach-move files-reattach sequence.

-- Get file name and current location of files.
SELECT 
 name AS LogicalFileName, 
 physical_name AS FileLocation,
 state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('RedgateMonitor');

USE MASTER
-- Alter database file locations.
-- This has no effect until database status is cycled.
ALTER database RedGateMonitor
modify file 
(name = RedGateMonitor,
filename = 'D:\MSSQL\Data\RedGateMonitor.mdf')

ALTER database RedGateMonitor
modify file
(name = RedGateMonitor_log,
filename = 'D:\MSSQL\Logs\RedGateMonitor_log.ldf')

ALTER DATABASE RedGateMonitor SET OFFLINE WITH ROLLBACK IMMEDIATE

-- NOW move the files

ALTER DATABASE RedGateMonitor SET ONLINE

 File Permissions

Ensure that the files are copied preserving their security settings.

SQL 2012 virtual accounts:  the “MSSQLSERVER” user should have full control:

MSSQLSERVER user

 

This user cannot be typed in nor selected using Advanced Find.  Paste this exact spelling:

NT SERVICE\MSSQLSERVER

Validation errors may be reported; persevere.  See this article https://msdn.microsoft.com/en-us/library/jj219062.aspx

 

SQL Server Collation

The answer is:  Latin1_General_CI_AS.  (the windows collation)

This is Microsoft’s recommendation.  It’s in article 322112 Comparing SQL Collations to Windows collations.

References

 

What is the most efficient, least intrusive, way of getting table row counts (say, for keeping periodic statistics)?    Select(*) is accurate.  Using index statistics is instant but the statistics could be out of date.

dm_db_index_physical_stats

Dynamic management view.  http://technet.microsoft.com/en-us/library/ms188917.aspx

-- record_count for all tables in current database.
SELECT object_name(object_id) AS TableName
	,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE index_id IN (
		0
		,1
		)
	AND alloc_unit_type_desc = 'IN_ROW_DATA'

record_count may not match (count(*)) for heaps (see TN article), and indeed I’ve found that to be the case.    Adding a PK to the table and running sp_updatestats changed record_count, but it still didn’t match count(*).

dm_db_partition_stats

Sum the row_count of each partition.  This gave accurate results in my tests where db_index_physical_stats didn’t. However one shouldn’t rely on it because TN describes row_count as “The approximate number of rows in the partition.”

SELECT 
    t.name, 
    object_schema_name(t.object_id) as schemaName,
    SUM(stat.row_count)
FROM sys.dm_db_partition_stats stat
INNER JOIN sys.tables t 
    on stat.object_id=t.object_id
WHERE 
    (index_id =1 or index_id=0)
   AND t.type='U'
GROUP BY
    t.name,
    object_schema_name(t.object_id) 
ORDER BY t.name

When were Statistics last updated?

List indexes and the date their statistics were last updated – using STATS_DATE(objetc_id, index_id).

SELECT
    t.name AS Table_Name
    ,i.name AS Index_Name
    ,i.type_desc AS Index_Type
    ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
 FROM
    sys.indexes i JOIN
    sys.tables t ON t.object_id = i.object_id
 WHERE
    i.type > 0
 ORDER BY
    t.name ASC
    ,i.type_desc ASC
    ,i.name ASC

Full statistics details – including usage histogram:

dbcc show_statistics ([Transactions], IX_Transactions_compound)

Sample output of DBCC SHOW_STATISTICS

Sample output of DBCC SHOW_STATISTICS

Updating Statistics

Updating statistics can be resource intensive in itself; it can also cause queries to recompile.

  • exec sp_updatestats

Updates stats for all tables in the current database if necessary.  For example, if at least 1 row has been modified since last statistics update.

  • DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
  • UPDATE STATISTICS [table_name]

Query to find rowmodctr (rows modified).  Source:  http://www.littlekendra.com/2009/04/21/how-stale-are-my-statistics/

SELECT DISTINCT tablename = object_name(i.object_id)
	,o.type_desc
	,index_name = i.[name]
	,statistics_update_date = STATS_DATE(i.object_id, i.index_id)
	,si.rowmodctr
FROM sys.indexes i(NOLOCK)
INNER JOIN sys.objects o(NOLOCK) ON i.object_id = o.object_id
INNER JOIN sys.sysindexes si(NOLOCK) ON i.object_id = si.id
	AND i.index_id = si.indid
WHERE o.type = 'U' --ignore system objects 
	AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL
ORDER BY si.rowmodctr DESC