NetInverse Developers Blog

March 7, 2009
SQL DMV and CROSS APPLY
Category: SQL — Tags: , — admin @ 11:38 pm

Use DMV and CROSS APPLY to Get Details of SQL’s Cached Query Plans

SQL 2005’s dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. For example, following query fetches SQL server’s cached query plans. You can use the refCounts and useCounts for performance analysis.

       SELECT  cached.*,
               sqltext.*
         FROM  sys.dm_exec_cached_plans cached
  CROSS APPLY  sys.dm_exec_sql_text (cached.plan_handle) AS sqltext

You can click here to see the screen snapshot of the query output.

Note: CROSS APPLY, a new feature of SQL Server 2005, is quite simple: an INNER (or outer) JOIN between a table and a table-valued function.

sys.dm_exec_sql_text: Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.

sys.dm_exec_cached_plans: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

March 5, 2009
Dynamic SQL Query Performance
Category: SQL — Tags: , , — admin @ 9:41 pm

Dynamic(Ad hoc) SQL query can be as performant as stored procedures if you use it correctly. SQL server compiles it, caches the compiled query plan and re-uses it next time. If not, SQL server may have to re-compile the query every time, and which can be sometimes 100 times slower!

For example:

     String query = @"SELECT *
                        FROM employees e
                  INNER JOIN EmployeeDetails et
                          ON e.employeeid = et.employeeid
                       WHERE e.employeeid = @id";

     sqlCmd.CommandType = System.Data.CommandType.Text;
     SqlParameter idParam = new SqlParameter(
         "@id", System.Data.SqlDbType.Int, 0);
     sqlCmd.Parameters.Add(idParam);
     sqlCmd.Parameters[0].Value = 100;
     sqlReader = sqCmd.ExecuteReader();

If you use SQL Tracer, you will see the SQL server executes following statement:

 EXEC sp_executesql N'SELECT * FROM employees e
 INNER JOIN EmployeeDetails et ON e.employeeid = ee.employeeid
 WHERE e.employeeid = @id', N'@id int',@id=100

In this case, SQL server can cache the query plan and re-used it effectively. Because the statement is parameterized, even the value of @id changes, the compiled statement remains same and can be always re-used.

A common mistake of using dynamic SQL is like below:

      String query = @"SELECT *
                         FROM employees e
                   INNER JOIN EmployeeDetails et
                           ON e.employeeid = ee.employeeid
                        WHERE e.employeeid = " + id.ToString();

     sqlCmd.CommandType = System.Data.CommandType.Text;
     sqlReader = sqCmd.ExecuteReader();

If you trace again, this time you will see SQL server executes the following instead:

 SELECT * FROM employees e INNER JOIN EmployeeDetails et
 ON e.employeeid = ee.employeeid WHERE e.employeeid = 100

SQL server can cache and re-use above statement as well. However, since the statement is not parameterized, if the
employeeid changes, SQL server will have to re-compile the statement. The consequence is low cache hit and SQL server will have a lot of recompiling, the overall performance will be poor.

So the take away here is, you can use dynamic SQL query, but do use SqlParameter to make your dynamic SQL statement parameterized.

©2009 NetInverse. All rights reserved. Powered by WordPress