ClearTrace - Free Tool to summarize SQL Trace Results

Let's say you capture the workload on your production SQL Server, by using SQL Profiler or SQL Trace to gather the events of query executions. In order to determine the most expensive queries within the workload, you need to summarize the trace results, group the events by query text, and order the summarized items by CPU or disk IO or duration. You also want to normalize the query text, such that common statements can be grouped together. ClearTrace is a free tool that can help, it can imports SQL Profiler and SQL Trace results, from SQL Server 2000 to the latest SQL Server version. Below are the steps demonstrating how to use ClearTrace:

1. Download ClearTrace from scalesql.com. You don't need to set it up on the database server, it can be executed in any computer which able to access the trace result files.
2. Extract the downloaded ClearTrace.XX.zip compressed file.
3. Execute the ClearTrace.exe executable file, which shows the ClearTrace GUI.
4. When you first time start ClearTrace in your computer, you will be asked to set the database in the Tools -> Options menu. If the database doesn’t exist you will be given the option to create it. If it does exist ClearTrace will create the necessary objects in the database. All tables and views are prefixed with “CT” so they are easy to identify.
    
   
    
   
5.  Select the first trace result file to import. Trace files in the same directory will also be imported in sequence. ClearTrace only processes RPC:Completed and SQL:BatchCompleted events. You can create the trace by yourself, including the required event columns: EventClass, TextData, HostName, ApplicationName, LoginName, SPID, Duration, EndTime, Reads Writes, and CPU. Or you can download the sql script from ClearTrace website. Anyway, I prefer creating it by myself.
    
   
   
    
   
6. Press Import Files button. ClearTrace will display the status. After the process completed, the summarized result will be shown. Then you can change the grouping and ordering of it.
    
   
7. You can also get the summarized data from the ClearTrace database, by querying the view CTTraceSummaryView.

Please be reminded that ClearTrace is still in BETA. But it still a handy tool for database performance tuning, and it's free.

Reduce PAGELATCH Waits in TempDB on MSSQL 2016

An Microsoft KB article, Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016, really catch my eye. This article mentions that in order to workaround that TempDB PAGELATCH wait symptom in SQL Server 2016, you should remove the DROP TABLE commands for any temporary tables that won't be reused within the same execution of the stored procedure. What? Don't drop temporary tables inside stored procedures? Dropping them before the stored procedure finish should be the coding practice that many SQL developers believed in! But in fact, according to SQL Server book online, a local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished, so actually SQL developers not need to worry about it. Another suggestions mentioned in the KB article also worth to note:

• Avoid using the TRUNCATE TABLE command against temporary tables.
• Avoid altering temporary tables after they have been created.
• If indexes are created against the temporary table, consider moving these to inline index creation statements within the CREATE TABLE command.

Inline Index Creation was introduced in SQL Server 2014, and further improved in SQL Server 2016. Below is an simple example demonstrating the idea of that KB:

CREATE PROCEDURE uspTestTempTable
AS
BEGIN
    CREATE TABLE #tempTable1 (
        pk int PRIMARY KEY,
        col1 int INDEX idxTempTbl1Col1,
        col2 int,
        col3 int,
        INDEX idxTempTblCol2Col3 (col2, col3)
    );
    INSERT #tempTable1 (pk, col1, col2, col3) VALUES (0, 1, 2, 3);
    SELECT * FROM #tempTable1;
    /* DON'T DROP the TEMP TABLE */
END
GO

EXEC uspTestTempTable;
EXEC uspTestTempTable;

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: