Clota Blog

Category: Monitoring

  • Important SQL Server Scripts…

    Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1     @DBName AS [DBName]    ,[LogDate]    ,CASE       WHEN…

  • Database Maintenance Plan

    Hii guys, I hope you are doing great, today here I am going to explain you about the Database Maintenance Plan.  Database Maintenance Plan- A database maintenance plan is a set of specific, proactive tasks that need to be performed regularly on databases to ensure their adequate performance and availability. Uses of Maintenance plan– The…

  • New features of SQL Server 2022

    Hello guys, hope you are doing well. In this blog I’m going to discuss about the “New features of SQL Server 2022”  The release of the SQL Server 2022 is much anticipated by the audience since the announcement of it being in the works on November 2nd, 2021. Although the complete details of this brand-new version will…

  • Buffer Cache Hit Ratio

    Hey guys, In this blog I am going to explain you about Buffer Cache Hit Ratio Buffer Cache Hit Ratio- It is a pool of memory pages into which data pages are read. In other words, the data pages which are read from Disk are stored in Memory (Cache) to provide data faster. When a Data Read…

  • Page Life Expectancy (PLE) in SQL Server

    Hey guys, In this blog I am going to explain you about Page Life Expectancy and How to monitor it. Page Life Expectancy (PLE)- Page Life Expectancy (PLE) is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory.…

  • Memory Grants Pending

    Hello there, everyone! I hope everything is going well for you. Today In this blog I am going to explain you about Memory Grants Pending and How to monitor it. Memory Grants Pending- The number of processes that are pending a workspace memory grant is known as Pending. Because they are unable to acquire adequate RAM while…

  • SQL Server’s in-memory OLTP technology

    Hey guys, In this blog I am going to explain you about SQL Server’s in-memory OLTP technology. Introduction- A native stored procedure compiler and specialized, memory-optimized relational data management engine are both built into SQL Server as part of In-Memory OLTP. For the most demanding OLTP workloads, Microsoft created In-Memory OLTP. In many instances, the…

  • Performance Monitoring Related

     Performance Monitoring Related- Session Related –Returns information about all the waits encountered by threads that executed for each session. select * from sys.dm_exec_session_wait_stats where session_id in (select session_id from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –and session_id <> *** ) order by 3 desc –Returns information about running session. select * from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –Find all queries waiting in the memory queue.  SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL; — Retrieve every query plan from the plan cache  SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  –sp_whoisactive –Active requests with memory grants  SELECT…