Month: July 2019

Amazon RDS SQL Server: Get Instance Size Using TSQL

by

You can obviously retrieve an Amazon RDS SQL Server’s instance type (size) from the AWS portal, but I wanted to get it using TSQL:

IF OBJECT_ID('tempdb..#AmazonErrorLog') IS NOT NULL
    DROP TABLE #AmazonErrorLog;
 
CREATE TABLE #AmazonErrorLog
(
    LogDate DATETIME,
    ProcessInfo NVARCHAR(20),
    [Text] NVARCHAR(1000)
);
 
DECLARE @pattern nvarchar(30) = N'System Model:';
 
INSERT INTO #AmazonErrorLog
EXEC rdsadmin.dbo.rds_read_error_log;
 
IF @@ROWCOUNT > 0
BEGIN
    SELECT InstanceSize = CAST(REPLACE(SUBSTRING(Text, LEN(@pattern) + 1 + PATINDEX (N'%' + @pattern + N'%', Text), 100), '''', '') AS varchar(100))
    FROM #AmazonErrorLog
    WHERE PATINDEX (N'%' + @pattern + N'%', Text) > 0 
END
 
DROP TABLE #AmazonErrorLog;

SQL Server: Plan Cache and Adhoc Workloads

by

Applications generating many dynamic queries (such as ORM frameworks) can lead to a query plan cache bloated by single use plans. Caching something that is only used once is obviously a waste of memory that could otherwise be used to store data pages.

If you have a predominately adhoc workload, turning on ‘optimize for adhoc workloads’ can help reduce the memory footprint of single use plans (it won’t solve the problem entirely though). A system I’ve recently worked on was able to regain 9GB of memory for data pages by turning this setting on.

sp_configure 'show advanced options', 1
GO
reconfigure
GO

sp_configure 'optimize for ad hoc workloads', 1
GO
reconfigure
GO

I’ve recently been using a slightly modified version of Kimberly Tripp’s query from her post, Plan cache and optimizing for adhoc workloads to categorise a workload:

SELECT 
	CacheType = objtype,
	TotalPlans = COUNT_BIG(*),
	TotalMBs = CAST(SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
	AverageUseCount = AVG(usecounts),
	TotalMBs1USE = CAST(SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
	TotalPlans1USE = SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END),
	[%TotalPlans] = CAST(100. * SUM(1) / (SELECT COUNT_BIG(*) FROM sys.dm_exec_cached_plans) AS decimal(9,2)),
	[%TotalMB] = CAST(100. * SUM(CAST(size_in_bytes AS DECIMAL(18,2))) / (SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2))) FROM sys.dm_exec_cached_plans) AS decimal(9,2))
FROM 
	sys.dm_exec_cached_plans
GROUP BY 
	objtype

SQL Server Security: Find Users with Weak Passwords

by

Data Breaches are common, and their cause is often as simple as the use of weak passwords.

SQL Server has an internal system function, PWDCOMPARE(), that can be used to find SQL logins with a weak password. A list of very common weak passwords can be found here as well as many other places.

IF OBJECT_ID('tempdb..#CommonPasswords') IS NOT NULL
    DROP TABLE #CommonPasswords;

CREATE TABLE #CommonPasswords(Password varchar(30) COLLATE Latin1_General_CS_AS not null primary key);

INSERT INTO #CommonPasswords(Password) VALUES
(''),
('123'),
('1234'),
('12345'),
('123456'),
('1234567'),
('12345678'),
('123456789'),
('1234567890'),
('987654321'),
('123qwe'),
('mynoob'),
('18atcskd2w'),
('55555'),
('555555'),
('3rjs1la7qe'),
('google'),
('zxcvbnm'),
('000000'),
('1q2w3e'),
('1q2w3e4r5t'),
('1q2w3e4r'),
('qwerty'),
('qwerty123'),
('password'),
('p@ssword'),
('p@ssw0rd'),
('password1'),
('p@ssword1'),
('password123'),
('passw0rd'),
('111111'),
('1111111'),
('abc123'),
('666666'),
('7777777'),
('654321'),
('123123'),
('123321'),
('iloveyou'),
('admin'),
('nimda'),
('welcome'),
('welcome!'),
('!@#$%^&*'),
('aa123456'),
('lovely'),
('sunshine'),
('shadow'),
('princess'	),
('solo'),
('football'),
('monkey'),
('Monkey'),
('charlie'),
('donald'),
('Donald'),
('dragon'),
('Dragon'),
('trustno1'),
('letmein'),
('whatever'),
('hello'),
('freedom'),
('master'),
('starwars'),
('qwertyuiop'),
('Qwertyuiop'),
('qazwsx'),
('corona'),
('woke'),
('batman'),
('superman'),
('login');

SELECT 
    name,
    create_date,
    is_disabled
FROM 
    sys.sql_logins sl (nolock)
    cross apply #CommonPasswords cp
WHERE 
    PWDCOMPARE(cp.Password, sl.password_hash) = 1

UNION ALL

SELECT 
    name,
    create_date,
    is_disabled
FROM 
    sys.sql_logins sl (nolock)
WHERE 
    PWDCOMPARE(sl.name, sl.password_hash) = 1; -- password same as username

Troy Hunt has collected the passwords from several major data breaches, and he has made the passwords searchable.