Use of nvarchar may badly affect the performance

  • nvarchar columns should not be used unless there is a special need to store multilingual data (other than English)
  • nvarchar columns consume almost double the space compared to varchar columns, leading to increased I/O and consuming unnecessary space in buffer cache.
  • Data types should be defined consistently while using varchar/nvarchar fields as arguments for stored procedures, functions and filter conditions such that they match with data type of the column in respective base table.
  • A simple nvarchar/varchar type mismatch can have a huge performance impact because index seeks will not happen if there is data type mismatch between search variable and actual column in base table.

Find cache plan for a given SQL text pattern


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+’*’,
‘Resource’) AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = ‘dbid’
AND st.text LIKE ‘%pattern%’

From SQL Server DMVs in Action by Ian W. Stirk