logo
  • Home
  • My Works
  • Downloads
  • Personal
  • Guest Book
  • About Me

Query optimization in SQL Server

Published by Rafeeque B Muhammad on Wednesday, January 20, 2010 12:00 AM
Updated on Wednesday, January 20, 2010 12:00 AM


First we need to clear buffer cache and procedure cache before executing the query. Also we need to set on the statistics IO and TIME on befor running the query each and everytime.

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

 

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

select * from dbo.PER_Person P

inner join dbo.PCR_PersonContactRelation PC

on P.PER_ID = PC.PCR_PER_ID

 

Result:

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 64 ms.

 

(25609 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PER_Person'. Scan count 1, logical reads 1139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PCR_PersonContactRelation'. Scan count 1, logical reads 290, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row(s) affected)

 

 SQL Server Execution Times:

   CPU time = 562 ms,  elapsed time = 1642 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Another query which has specified only 1 column:

select P.PER_Country from dbo.PER_Person P

inner join dbo.PCR_PersonContactRelation PC

on P.PER_ID = PC.PCR_PER_ID

Result:

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 4 ms, elapsed time = 4 ms.

 

(25609 row(s) affected)

Table 'PCR_PersonContactRelation'. Scan count 1, logical reads 111, physical reads 5, read-ahead reads 109, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PER_Person'. Scan count 1, logical reads 1139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row(s) affected)

 

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 140 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Also we can look one more thing in Execution plan. For first query you can see the Estimated subtree cost 2.93753.

Here you can see the second query has Estimated subtree cost is 1.10072.

Technical Articles

  • All
  • .NET Framework
  • ASP.NET
  • C#
  • Win Forms
  • WCF
  • SharePoint(WSS 3.0)
  • SQL Server
  • TSQL
  • Oracle
  • PL/SQL
  • Performance and Tuning
  • Application and Tools
  • Outlook programming
  • HTML/HTML5/XHTML/DHTML/JAVAScript/CSS
  • Windows Services
  • Web Services
  • Security
  • Web
  • General
  • Architecture

Technical Books

  • All
  • C# Threading
  • WCF

Other Links

  • Important Links

Techies Craft © 2014 | Privacy Policy