SQL Server

SQL Server: Don’t Make the Query Optimiser’s Job More Difficult

by

Part of my job is tuning complex queries: I’ve seen some recently with eye watering complexity. A post from Erik Darling explains how abstraction can be the cause of poor performance in SQL Server:

Sometimes it’s views, CTEs, or derived tables. Sometimes it’s functions. obviously functions can have a weirder set of effects, but the general idea is the same.

If you start chaining things, or nesting them together, you’re making the optimizer’s job harder and likely introducing a lot of overhead.

There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.

Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.

Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.

In addition, as your query becomes complex, the query optimiser eventually gives up and produces a less than efficient query plan because there are too many potential query plans to choose from.

Erik references Grant Fritchey’s post from 2012, The Seven Sins against TSQL Performance, which is still as relevant today.

Hardening SQL Server Security

by

Three part article on hardening SQL Server Security:

Below are some Microsoft recommended best practices for network settings:

  • Enable Windows Firewall and limit the network protocols supported.
  • Do not enable network protocols unless they are needed.
  • Disable NETBIOS and SMB protocol unless specifically needed.
  • Do not expose a server that is running SQL Server to the public Internet.
  • Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.
  • Use extended protection in SQL Server 2012 if the client and operating system support it.
  • Grant CONNECT permission only on endpoints to logins that need to use them. Explicitly deny CONNECT permission to endpoints that are not needed by users or groups.

SQL Server: Poison Waits

by

SQL Server performance tuning often starts by examining your top wait statistics. There are certain wait types where even a small number of occurrences can indicate performance problems. These are termed Poison Waits.

RESOURCE_SEMAPHORE_QUERY_COMPILE
A query was sent to SQL Server, and there wasn’t an execution plan for it in the query plan cache. In order to create an execution plan, SQL Server requests a small amount of memory, but due to memory pressure the requested memory wasn’t available. So SQL Server had to wait for memory to become available before it could even build an execution plan, let alone execute the query. In this situation, cached query plans and small un-cached plans may be able to run depending on how much pressure the server is under, but complex queries will experience memory request waits and feel sluggish.

RESOURCE_SEMAPHORE
SQL Server compiled an execution plan (or retrieved the query plan from cache), but now it needs memory to actually execute the query (a memory grant request). If other queries are already using a lot of memory, then our query won’t be able to start executing because there is insufficient memory available. Similar to the RESOURCE_SEMAPHORE_QUERY_COMPILE wait, smaller queries may be able to execute, but complex ones will be blocked from executing and wait for memory to become available.

THREADPOOL
At startup, SQL Server creates a predefined number of worker threads based on how many logical processors the server has (each worker thread uses 2MB of memory). As queries arrive, they get assigned to worker threads. If enough queries queue up, such as when queries get blocked, you can run out of available worker threads (worker thread starvation). You might be tempted to increase max worker threads (and Microsoft support sometimes makes this suggestion), but then you might simply escalate the problem to a RESOURCE_SEMAPHORE or RESOURCE_SEMAPHORE_QUERY_COMPILE issue. Blocking is the most common culprit of THREADPOOL waits, but it can also be due to a large amount of connections trying to run queries at the same time. If you are unable to connect to SQL Server to troubleshoot because of worker thread starvation, try connecting using the Dedicated Admin Connection.

Whenever any of these poison waits occur, you have to get to the root cause of the problem. For a list and explanation of the various waits: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

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.