SQL Server Procedure Cache and Execution Plans

 Procedure cache and data  cache information  in sql server can play a vital role in understanding the performance of the sql server.These two contains  two core aspects of  sql server performance  namely the query  executions plans and the  data   pages  both   of which are stored in buffer cache

Procedure Cache:
Sql server engine  generates  query execution plans  by taking different factors into consideration such as  existing indexes , tables, joins  etc  and at the end of this process  it produces an execution plan, which it considers as  best for executing the query .This entire process is done by a cost based  allocation approach  in which  it  estimates   percentage  of   cost  of resources  to  process each operation in the query and generates an best possible execution plan to execute the query .

Each time  Sql Server  generates an execution plan  it  consumes certain amount of system resources    and to avoid  generating a  new  execution plan  for  same query which has been executed previously   sql server  has a mechanism called  procedure cache ,which  stores these execution plans  in memory pool , so that when  a query is executed  and an existing plan is available in procedure cache, it will use that plan  and helps to save the system resources .The amount of time this query is stored in the procedure cache  is again another process, in which sql server  uses a age based approach ,which essentially means if a plan is re used again , than  it will  keep the execution plan  in the procedure cache   and the plan will live  for longer time in the procedure cache, and if a plan is not used ,than the plan  will be eventually  removed from the procedure cache  as other executions plans get added  and by doing this,  it uses buffer cache efficiently .

Understanding Query execution plan is very important to query design and indexing ,sql server management studio enables to view estimated execution plan and actual execution plan as shown below

The execution plan in the above diagram  contains number of operators, to show what actually is happening  when sql server is trying to build the execution plan for the query  .Each operator has a associated cost with it , like in this case the cost of clustered index scan  is 100% as  shown  in the above diagram ,I will change the query slightly  and we will observer how the new execution plan looks like

In this  second query as shown  in the below diagram  i have added a  filter  on geography key and the execution plan  changed  considerable   as shown as below

As you can see when i added a filter , the execution plan changed  and now there is a index seek operation due to the fact that there is a non clustered index on the geography id filter column   and naturally the execution plan determined that the  index  seek is is more cost efficient operation due to the fact fact that it fetches few records when compared to a index scan and also you can see ,there is a  Key Look Up  operator in the execution plan which is essentially used to look up the records from the non clustered index to the clustered index and has an associated cost of 50%  and also  there is a Nested loop join which is the result of  key loop operator.Explaining  each operator in execution plan is beyond the scope of this post  and the above examples were used to show, what is an execution plan, and how it is relevant  in query building



Usefull   DMV   to get  Procedure  Cache   information in Sql Server

select s.usecounts as execcounts , s.size_in_bytes,s.cacheobjtype,  t.text ,db_name(st.database_id) as DatabaseName,st.total_elapsed_time   from sys.dm_exec_cached_plans  s
inner join  sys.dm_exec_procedure_stats  st on  s.plan_handle=st.plan_handle
cross apply sys.dm_exec_sql_text(s.plan_handle) t


UseFull  DBCC command to clear Procedure cache in sql server

DBCC FREEPROCCACHE

Sql Server Query for Blocked Session and login names

The below query can be used to find out the login names and session id of the blocked users,the detailed explanation of blocking is beyond the scope of the post  and this post covers only to extract blocking information
...................................................................................................................................................

To replicate the blocking, i have opened a transaction record  for  an  update  by a user    and   another user is querying the same record  ,both users are using  the sql server default  isolation of read committed  and this causes the blocking,  as the second  user is waiting to acquire a shared lock on the same record

I have made the screen shots at the end of query  and highlighted   activity  monitor  screen shot  to show     the query and blocked session
-----------------------------------------------------------------------------------------------------

SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
  WHERE r.status = N'suspended';
GO

additionally to get the blocked query  text you can use the below query



SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource,qry.Text as BlockedQuery
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)  Qry
  WHERE r.status = N'suspended';
GO

The result can be seen below

The Same can be observed on activity monitor as below

As you  can observe we can use combination of many tools in sql server diagnosis 

Average Cpu Time by Top 10 Queries

SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Query for Sql Server IO Utilization by each database

....................................................................... Io Utilization by each databse in MB ......................................................................... Note:Use of these queries are up to the  user  responsibilities,so i do not recommend to use these views without  testing on a test server,  as the impact of these views on system resources  can be intense at times, and i do not own these views  , the total credit goes to the individual contributors  and companies , the purpose of these posting  is to systematically bring different views at one place for troubleshooting sql server ......................................................................... SELECT

DB_NAME(database_id) AS [Database Name],

CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 ASDECIMAL(12, 2)) AS io_in_mb

FROM

sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]

GROUP BY database_id;

The above query show  the io utilization by each database as shown below

Usefull Queries for Sql Server data base Monitoring

I have collected a list of useful queries  for database  Monitoring ,  for the purpose of these posts,   i'm   running  few queries on adventure works   databases  , i will be using these views to demonstrate the impact of those queries   .

Note:Use of these queries are up to the  user  responsibilities,so i do not recommend to use these views without  testing on a test server,  as the impact of these views on system resources  can be intense at  times, and i do not own some of  these views and for those views  total credit goes to the individual contributors  and companies , the purpose of these posting  is to systematically bring different views at one place for troubleshooting  in sql server
.--------------------------------------------------------------------------------
Index Fragmentation  in Sql Server

---------------------------------------------------------------------------------

IF EXISTS ( SELECT *

    FROM [tempdb].[dbo].[sysobjects]

    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))

    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]

GO



CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](

    [DatabaseName]                  [nvarchar] (100) NULL,

    [ObjectName]                    [nvarchar] (100) NULL,

    [Index_id]                      INT,

    [indexName]                     [nvarchar] (100) NULL,

    [avg_fragmentation_percent]     float NULL,

    [IndexType]                     [nvarchar] (100) NULL,

    [Action_Required]               [nvarchar] (100) default 'NA'

) ON [PRIMARY]



DECLARE @dbname varchar(1000)

DECLARE @sqlQuery nvarchar(4000)



DECLARE dbcursor CURSOR for

SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb','model', 'msdb')



OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname



WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sqlQuery = '

    USE [' + @dbname + '];



    IF EXISTS

    (

        SELECT compatibility_level

       FROM sys.databases

        WHERE

            name  = N'''+ @dbname +'''

            AND compatibility_level >= 90

    )

    BEGIN

        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]

        (

            DatabaseName

            , ObjectName

            , Index_id

            , indexName

            , avg_fragmentation_percent

            , IndexType

        )

        SELECT

            db_name() as DatabaseName

            , OBJECT_NAME (a.object_id) as ObjectName

            , a.index_id, b.name as IndexName

            , avg_fragmentation_in_percent

            , index_type_desc

        FROM

            sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

            JOIN sys.indexes AS b

        ON

            a.object_id = b.object_id

            AND a.index_id = b.index_id

        WHERE

            b.index_id <> 0

            AND avg_fragmentation_in_percent <> 0

    END;'

   

    EXEC sp_executesql @sqlQuery

   

FETCH NEXT FROM dbcursor

INTO @dbname

END



CLOSE dbcursor

Deallocate dbcursor




UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Rebuild'

WHERE avg_fragmentation_percent >30

GO



UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Reorganize'

WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5

GO



SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]

ORDER BY databasename

This  view is great  in the sense that it gives a clear view of what to do  and on which object the impact will be ,as you can see fragmentation  anything over 30%  an   index rebuild is specified  and fragmentation between  30% to to %5 a index reorganization  is  specified