SQL Stress Test using OStress

Although not bundled with SQL Server, Microsoft provides a sql query stress tool, named OStress, comes with RML utilities package which is absolutely free of cost. OStress is a command line tool similar to SQLCMD utility, so database developers and administrators should find it's easy to pick up. You can download OStress in the RML Utilities package available here: Description of the Replay Markup Language (RML) Utilities for SQL Server. There are 32bit and 64bit versions, just download the right one for your server. Below are the steps to demonstrate how to use OStress to stress test your sql server:

1. Download the RML Utilities package as mentioned above.
2. Install the package, just tick term and condition check box and press next to install.
3. Open the RML Utilities Command Prompt, you can find it at:
    Start > All Programs > RML Utilities for SQL Server > RML Cmd Prompt.
4. Type "ostress" then press enter, which shows you all the usage options.

5. For example, you wanna stress test a stored procedure named uspTest1, with 25 user connections having concurrent execution of 50 iterations, and output the result log files into a directory. The command should be like this:
ostress -S.\DEV2014 -dStackOverflow2010 -E -n25 -r50 -q -Q"EXEC uspTest1" -oC:\Temp\OStressOutput
6. Then you can check the query.out log files inside the output directory.

Free SQL Code Formatter - ApexSQL Refactor

I found a free tool, ApexSQL Refactor, which can be integrated into your SSMS and being used for better formatting your sql code. Let me demonstrate how it can be done.
For example, you want to create a stored procedure that you found very useful from the internet, but it wasn't formatted very well:

Then you can format the code inside your active query window, by choosing ApexSQL > ApexSQL Refactor > Format SQL by profile, then click the formatting profile you like:

Then your code will be formatted as below:

Also, you can create your own formatting profile too, by going to ApexSQL > ApexSQL Refactor > Options... > New. There are many options that you can specify in order to create your own favorite sql code formatting profile.

Reclaim Data Space in VarBinary(Max) Column

SQL Server allows applications to store binary data, such as word files and images, in varbinary(max) column. Most likely the applications don't need to store those binary data permanently, so that we can define a retention period for the binary data, and data purging job should be created in order to free up disk space. Some applications only allow purging binary data such as photos, but other data fields related, such as id-number and name must be kept permanently. By the way, UPDATE varbinary(max) column to NULL cannot free up the unused space, only DELETE the row can make it. Below example is a proof:
CREATE TABLE [dbo].[TestBlob](
    [pk] [int] NOT NULL PRIMARY KEY,
    [blob] [varbinary](max) NULL
)
GO

TRUNCATE TABLE TestBlob;

SELECT 'EMPTY'
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

SET NOCOUNT ON;
DECLARE @i int = 1
WHILE @i < 100000
BEGIN
INSERT TestBlob (pk, blob) SELECT @i, CONVERT(varbinary(max), '
asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
')
SET @i +=1
END

SELECT 'FULL'
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

UPDATE TestBlob SET blob = NULL;

SELECT 'UPDATE NULL';
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

DELETE TestBlob;

SELECT 'DELETE';
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';
GO
Here's the result:

As the result shows, UPDATE varbinary(max) to NULL cannot reduce the used space by data, only DELETE can reduce it. In order to make it possible to free up disk space, one method is separating it into another table, e.g.
UserTable
userId int primary key
username varchar(50)
...
PhotoTable
userId int primary key
photo varbinary(max)
Then the binary data can be deleted.

Myth: Index Seek Always Better Than Table/Index Scan

Many DBAs and developers believe index seek always performs better than scan. But in fact, it depends. Let's take a look on an example:

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM [Users] WHERE DisplayName LIKE 'B%';
SELECT TOP 1000 * FROM [Users] WITH(FORCESEEK) WHERE DisplayName LIKE 'B%';

In this example, I use the Stack Overflow public database StackOverflow2010 which is free to download. The Users table there has a primary key clustered index on its id column, and a nonclustered index on its DisplayName column, which has no any included columns. Below shows the table schema and the index creation statement:

CREATE NONCLUSTERED INDEX IX_DisplayName ON Users (DisplayName);

When the 1st select query (without any table hints) being executed, the engine picks Clustered Index Scan operator to run it. And for the 2nd select query, as it has a FORCESEEK table hint, Index Seek on IX_DisplayName and Key Lookup on the primary key will be used. Below shows the actual execution plans:

So many people will jump in and suggest to optimize the query by the FORCESEEK hint. No, it's not so simple. Let's take a look on the STATISTICS IO output:

Table 'Users'. Scan count 1, logical reads 1156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 3081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The "tuned" 2nd query induces more reads, and so more memory usage and more disk IO. That's why the SQL Server engine decides to scan rather than seek by itself. The extra reads on the 2nd query come from the Key Lookup for each rows returned from the Index Seek operator. Also, Clustered Index Scan in the 1st query isn't really scan the whole table, it's because there's a residual predicate on the DisplayName column, and also the TOP operator already placed a limit on the number of rows to be read.

From this demonstration, we can conclude that scan sometimes can perform better than seek.

SSMS New Security Features: Vulnerability Assessment and Data Classification

The latest SSMS version 17.x provides two new features on security, Vulnerability Assessment and Data Classification. Vulnerability Assessment is supported for SQL Server 2012 and later. Data Classification is supported for SQL Server 2008 and later. The Vulnerability Assessment runs a scan on your database, based on Microsoft’s recommended security best practices, highlights any vulnerabilities found, and gives you actionable steps to resolve those security issues. You can run the VA scan on your application database to check any vulnerabilities on it, and also run the VA scan on the master database that checks for server-level security issues. The VA scan can be started by expand Databases > right-click the database to check > point to Tasks > select Vulnerability Assessment > click on Scan for Vulnerabilities. After the scanning completed, the report of the VA results will be shown, containing passed and failed checking items. Each failed checking item has a suggested remediation, mostly an executable SQL script to fix the security issue. For example:

 

SQL Data Discovery and Classification is a new tool to discover and classify sensitive data in your database tables, helps you to meet data privacy standards such as GDPR required by EU. This tool scans your application database, every column in every table, discovers any possibly sensitive data, and classifies those columns by two metadata attributes: Sensitivity Labels - the main classification attributes to define the sensitivity level of the data stored in the column; and Information Types - the additional granularity into the type of data stored in the column. The scan can be started by right click on the database > choose Tasks > Classify Data. Below is the classification result of AdventureWorks2017 database:

SQL Sever 2017 CU3 TempDB Spill Diagnostics

When SQL Server has poorly under-estimated the amount of rows that will be returned from an operator in a query execution plan, less memory will be granted, finally the query execution will spill out to tempdb, and the query runs slow as more disk I/O will be incurred. You can fix those queries by adding missing indexes and update statistics with reasonable sampling size. The latest SQL Server 2017 CU3 added some improvements on tempdb spill diagnostics in DMV and Extended Events to let us find out which queries have tempdb spilling problem. Below query employs the new _spills columns in sys.dm_exec_query_stats DMV:
SELECT
DB_NAME(QP.[dbid]) AS [db_name],
OBJECT_NAME(QP.objectid, QP.[dbid]) AS [object_name],
SUBSTRING(ST.[text], (QS.statement_start_offset/2)+1,  
    ((CASE QS.statement_end_offset
    WHEN -1 THEN DATALENGTH(ST.[text]) 
    ELSE QS.statement_end_offset 
    END - QS.statement_start_offset)/2) + 1) AS stmt_text,
QS.execution_count AS execution_count,
QS.total_spills / 128.0 AS total_spills_mb,
QS.last_spills / 128.0 AS last_spills_mb,
QS.min_spills / 128.0 AS min_spills_mb,
QS.max_spills / 128.0 AS max_spills_mb,
(QS.total_spills / QS.execution_count) / 128.0 AS avg_spills_mb
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.[sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
WHERE QS.total_spills > 0
ORDER BY total_spills_mb DESC;

Troubleshoot SQL Server Disk I/O Slowness

Last week, as the storage device of my production sql server had a hardware component failure, I moved the production database files into a new SAN storage. But after that, some users complain that the system occasionally hang, so I firstly check anything abnormal on the disk I/O performance. There were some errors logged in the sql server error log, saying "SQL Server has encountered XX occurrence(s) of I/O requests taking longer than 15 seconds to complete on the file (...\MyUserDB.mdf) in database [MyUserDB]".

I'm a lucky guy, my company bought me SQL Sentry, which I found it is the best performance monitor for sql server. It shows me that during that time period, the database data file had a high disk read latency (>2,000ms during that period, comparing to normally within 5ms).

Then I tried to find any error on the windows system log, and found an iSCSI error.

Finally, I called my storage hardware vendor to fix it. Meanwhile, I also double checked the antivirus scan excludes SQL Server related files and directory, as listed in this knowledge base. Also, make sure no any disk defragmentation scheduled task on any database disk drives.