Query Tuning example 1 - NOT IN

Here's a real life example showing NOT IN really hurts query performance.
Below is the SQL code snippet extracted from a query to generate a report, which is written by a programmer from external vendor (I don't wanna mention which one, although I'm quite disappointed at it's work):
SELECT ... FROM ... WHERE ... AND demand.time_expense_sheet_id NOT IN (SELECT order_id FROM c_order_line) ...
The c_order_line table contains 1 million of rows in our production environment. That query takes 2 minutes to execute. My boss asked me to troubleshoot it, as users complain again and again that the report cannot be loaded and timeout error raised.
First, I used SQL Profiler to trace what queries are really running, and which query is the slowest one.

I pick the slowest query, execute it on testing environment by using Management Studio, with the "Include Actual Execution Plan" enabled. Then I execute the query, and open the returned actual execution plan by SQL Sentry Plan Explorer (a free tools you must love), and identified the top operation was a Index Scan on that huge table c_order_line.

By checking the SQL query, I found that table was being used in a NOT IN clause. So I send these findings to that favorable vendor (just kidding), they replied me that NOT IN checking may be unnecessary (-_-"). Later they sent me the fix, just removed that NOT IN clause, that report now can be generated within 10 seconds. My users feel happy, my boss feel happy :)

Get SQL Server Database Disk Space Usage

CREATE TABLE #DBInfo
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime,
maxsize int,
growth int
)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate, maxsize,
CAST(sysfiles.growth/128.0 AS int) AS growth
FROM dbo.sysfiles'
INSERT INTO #DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate,
   maxsize,
   growth)
EXEC sp_MSForEachDB @command

SELECT
   ServerName,
   DatabaseName,
   RTRIM(LogicalFileName) AS LogicalFileName,
   FileSizeMB,
   LEFT(PhysicalFileName, 1) AS Drive,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate,
   maxsize,
   growth
FROM #DBInfo
ORDER BY
   ServerName,
   DatabaseName

DROP TABLE #DBInfo

EXEC master..xp_fixeddrives

Get Table or Index name from resource Id returned by blocked process/deadlock report XML

Blocked Process Report and Deadlock Graph XML only report the affected object (table/index) as ID number, either object id, HOBT id, or page id. Below demonstrates how to resolve into object name.


Object ID (OBJECT:db_id:object_id)
SELECT OBJECT_NAME(db_id, object_id);

HOBT ID (KEY:db_id:hobt_id)
SELECT o.name AS TableName, i.name AS IndexName, SCHEMA_NAME(o.schema_id) AS SchemaName FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id WHERE p.hobt_id = hobt_id

Page ID (PAGE:db_id:file_id:page_id) or Row ID (RID:db_id:file_id:page_id:slot)
DBCC TRACEON(3604)
GO
DBCC PAGE(db_id, file_id, page_id)
The object id will be shown in the Metadata: Object_Id = ???
The index id will be shown in the Metadata: IndexId = ???
Then you can check it by OBJECT_NAME function and sysindex dmv.

Ref.: http://support.microsoft.com/kb/224453

My forth article "SQL Coding Practices"

My forth article SQL Coding Practices, completed!

SQL Server specific Performance Monitor (PerfMon) Counters

Processor:
- Process (sqlservr): % Processor Time (desired value: < 80% per core)
- Process (sqlservr): %Privileged Time (desired value: < 30%)

Memory:
- SQL Server:Buffer Manager: Buffer Cache Hit Ratio (desired value: > 90%)
- SQL Server:Buffer Manager: Page Life Expectancy (desired value: > 300 secs)

Disk:
- SQL Server:Latches: Total Latch Wait Time (ms)
- SQL Server:Latches: Latch Waits/sec (desired value: (Total Latch Wait Time) / (Latch Waits/Sec) < 10)

My third article "Create Transactional Replication from Backup"

My third article Create Transactional Replication from Backup, completed!