mssql

SQL 2005 – Bad Cached Query Plan

Posted on

After a while working on a migration from SQL 2000 and SQL 2005 (hello it’s 2009 now btw :p), we found some common performance problems with some of the stored procedures / queries related to statistics and the cache query plan.

The databases are used for data warehouse reporting purpose, so we do have several tables with more than 100 million of rows, with this kind of size, it can be amazingly slow when something’s not right.

The 2 most common reasons

  • Statistics not updated
  • Bad Cached query plan

Preliminary steps to resolve before changing anything to the stored procedures

  • Run UPDATE STATISTICS to the respective tables
  • Run EXECUTE [Proc] [Parameter] WITH RECOMPILE (This is very useful in a controlled environment where you can’t ALTER the Proc just to refresh the cache)

If the preliminary steps above are still not working, then continue with the steps below to optimize

  • Use temporary tables instead of joining 2 or more giant tables where you can actually eliminate 90% of the 2nd table based on the parameter.
  • Try using temporary tables instead of sub queries, this might help
  • Use WITH RECOMPILE option in the DDL so the query plan will not be cached (either in the stored procedure level or query level)
  • Use local variable for parameter to avoid parameter sniffing
  • Convert ISNULL(@VARIABLE, COLUMN) to (COLUMN = @VARIABLE or @VARIABLE IS NULL)

One thing worth to mention

It’s not always because of the statistics not updated or the bad cached query plan, I found some cases that the performance is up to speed again after recompiling the stored procedure or update the statistics, but by tweaking the query it self, they are able to work fine consistently without having to update the statistics or to recompile the stored procedure.

Useful SQL Queries

  • Get the execution details of the stored procedure

Select Last_execution_time, creation_time, execution_count, total_worker_time/execution_count
 , (SUBSTRING(TEXT,statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),TEXT)) * 2 ELSE statement_end_offset END - statement_start_offset)/2)) AS Sql_statement
From sys.dm_exec_query_stats qt
 Cross Apply sys.dm_exec_sql_text(qt.plan_handle) sql
Where db_name(sql.dbid) = '[To replace with database name]'
 And object_name(sql.objectid) = '[To replace with the stored procedure name]'
Order By Last_execution_time

  • Get the cached query plan

Select Top 1 pl.query_plan
From sys.dm_exec_query_stats qt
 Cross Apply sys.dm_exec_sql_text(qt.plan_handle) sql
 CROSS APPLY sys.dm_exec_query_plan(qt.plan_handle) pl
Where db_name(sql.dbid) = '[To replace with database name]'
 And object_name(sql.objectid) = '[To replace with the stored procedure name]'

Check transaction log space used for SQL Database

Posted on

DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS

http://msdn.microsoft.com/en-us/library/ms189768.aspx

Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
master         3.99219      14.3469            0
tempdb         1.99219      1.64216            0
model          1.0          12.7953            0
msdb           3.99219      17.0132            0
AdventureWorks 19.554688    17.748701          0