My Review of Immersion Event on Performance Tuning and Optimization 1

I was fortunate enough to attend IEPTO1 (Immersion Event on Performance Tuning and Optimization part 1) by Kimberly Tripp (twitter) and Paul Randal (twitter) of SQLSkills this past Spring. It was absolutely amazing! If you are a DBA, developer, or just an IT tech geek who loves to learn then you must attend this event. Find registration information here.

Getting situated…

I flew in on Saturday and stayed with a friend. Some people stayed at the hotel where the event was taking place. I was staying only a couple miles away from the event so it was bad at all.

On the very first day, before class started, Kimberly reassured us that we will not go hungry. There was so much food! Breakfast and lunch was served every day. Snacks, soda, coffee/tea, water were all available throughout the day. The last thing anyone worried about was an empty stomach.

The conference room was a bit chilly but I got used to it. If you easily get cold then I advise wearing a fleece or light sweater.


The agenda is no joke. Before I even thought about signing up for this event, I watched SQLSkills MCM videos at least 2-3 times each and still was in for a surprise.

The IEPTO1 event is broken into 11 modules spread over 5 days:

Module 1: Database Structures
Module 2: Data Files
Module 3: Locking & Blocking
Module 4: Versioning
Module 5: Logging & Recovery
Module 6: Index Internals
Module 7: Index Fragmentation
Module 8: Internals & Data Access
Module 9: Statistics
Module 10: Cardinality
Module 11: Indexing Strategies

It was an immense amount of information. I definitely plan on attending IEPTO2.

Kim and Paul encouraged everyone to go back home and practice, practice, practice! They sent us back with demo scripts, notes, homework, printouts, and a few goodies. (see pic below)

SQLSkills Event

On the last day we all got a certificate of completion (see below) and posed for a group picture! Paul asked us to “act silly”…and yes, that is Paul in the back doing what seems like choking (or trying to kiss) Tim Radney. :)


Immersion Event Performance Tuning and Optimization

How to Stop & Delete a SQL Server Trace

There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.

sp_trace_setstatus takes 2 parameters, trace id and status.

The trace id is the id you find by doing a select * from sys.traces

The status is either a 0, 1 or 2:

0 stops the trace, 1 starts the trace, 2 closes the specified trace and deletes its definition from the server. (see MSDN image screenshot below)

Status for sp_trace_setstatus(According to Microsoft, “A trace must be stopped first before it can be closed.”)

Let’s walk through the process.

First, do a select * from sys.traces to get the trace ID. Second, stop the trace by executing sp_trace_setstatus trace_id, 0 then, close/delete the trace by executing sp_trace_setstatus trace_id, 2. (see below screenshot for example)

sp_trace_setstatus screenshot

Hope that helps!

How to Refresh IntelliSense Cache in SSMS

I recently created a database called “testDB” and when I typed “USE testDB” in the SSMS Query window it gave me a little red squiggly underline (see below snapshot).use database red errorWhy did this happen when I just created a new database? That’s because the local IntelliSense cache hasn’t updated yet.

How to Refresh IntelliSense Cache in SSMS

If you want to force the IntelliSense Cache to update, go to Edit, then IntelliSense, then click “Refresh Local Cache.” (See snapshot below)

refresh ssms intellisense cache

That should refresh your IntelliSense Cache and remove all those annoying red squiggly underlines!

How to Find the Space Used in Your Data Files

I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.

SELECT sdb.file_id as [File ID],
sdb.physical_name [File Location], [File Name],
CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB],
CONVERT(numeric(10,2),ROUND(FILEPROPERTY(, 'SpaceUsed')/128.,2)) AS [Space Used in MB], 
CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(, 'SpaceUsed'))/128.,2)) AS [Space Left in MB]
FROM sys.database_files sdb

Here is a sample result of the output:



Gain More Real Estate in SQL Server Management Studio

The default layout in SSMS is too restrictive. I’m always having to scroll up and down and it gets pretty annoying. So I made a simple settings change that gives me more “real estate.”

The configuration for this is quite simple:  Go to Tools, then click on Option, expand Query Results, expand SQL Server, click on Results to Grid and make sure the “Display results in a separate tab” is checked. (see image below)

You might have to restart SSMS or open another session for the new layout to take effect. Enjoy!

gain more real estate in sql server management studio

Msg 5041, Level 16, State 1, Line 1 – Resize Datafile

Recently, I wanted to resize the tempdb file size to 2 MB so I ran the below script:

ALTER DATABASE tempdb MODIFY FILE (name = tempdb, size = 2MB);

and got the below error:

Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File ‘tempdb’ does not exist. (screenshot below)

Msg 5041, Level 16, State 2, Line 1

After some research I found out that the file name that I was using was incorrect. To find the correct logical name, you have to right-click the database, go to Properties —> Files and you’ll see it under Logical Name (see green arrow in screenshot image below). In my case it was “tempdev” NOT “tempdb” (which is what I used and got the error).

Msg 5041, Level 16, State 2, Line 1

Once I put that name in the script it ran perfectly fine. Make sure to restart SQL Service in order to see the change.

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, size = 2MB);

How To Use sp_cycle_errorlog

Before I do any testing I like to execute the sp_cycle_errorlog stored procedure. That way I can quickly see the specific SQL Server log entry without having to scroll / filter through tons of log.

It’s pretty simple to use. Just run this in the query window: sp_cycle_errorlog

Whenever you execute sp_cycle_errorlog the SQL Server log closes the “Current” log and cycles it like a server restart does. So your “current” log becomes #1, #2 becomes #3, and so on. The new “Current” log will only have a couple rows that include Microsoft copyright information, authentication mode, etc. (see screenshot below).


That should make testing and searching through the log MUCH easier!

How to Check SQL Server Database Encryption Algorithm

I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers.

I already knew it was AES-256 but IA needed a screenshot as proof. So I wrote this little query to bring back the database name, id, encryption key length and algorithm. I ran it, took a screenshot and sent it to them.

--Bring back database name, id
--key algorithm and key length
from sys.dm_database_encryption_keys e
join sys.databases db on db.database_id = e.database_id

Dirty Reads vs Phantom Reads in SQL Server

During a recent “interview” I was asked, “What two isolation levels in SQL Server will prevent phantom reads?”

I had never heard of “phantom reads” before but thought the person meant, “dirty reads.” So I replied, “READ COMMITTED and SNAPSHOT isolation levels.”

I was wrong…sort of. The interviewer said, “It’s actually Serializable and Snapshot.” As soon as the “interview” was over, I had to read up on phantom reads.

This is what I learned:

Phantom Reads: Occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Dirty Reads: Reading uncommitted modifications. When a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Below is a table that shows each isolation level and whether they allow dirty or phantom reads. (Photo taken from MSDN)

Isolation Levels Dirty Reads vs Phantom Reads