Daily Monitoring Queries

----------------------------------------------------------------------------------------------------------------------------
-- data file and log file size as per today

SELECT DB_NAME(database_id) AS [Database Name],
       [file_id], name AS [Logical Name], physical_name, type_desc, state_desc,
   --is_percent_growth, growth,
       CONVERT(bigint, size/128.0) AS [Total Size in MB],
   CONVERT(DECIMAL(18,2), size/131072.0) AS [Total Size in GB],cast (sysdatetime() as date) taken_date
FROM sys.master_files WITH (NOLOCK)
WHERE database_id > 4
AND database_id <> 32767
OR database_id = 2
ORDER BY DB_NAME(database_id) OPTION (RECOMPILE);

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

-- current CPU utilization
-----------------------------------------------------------------------------------------------------------------------------

WITH DB_CPU_Stats
AS (
SELECT DatabaseID
,DB_Name(DatabaseID) AS [DatabaseName]
,SUM(total_worker_time) AS [CPU_Time_Ms]
,CONVERT([smalldatetime], GETDATE()) AS [CPU_Stats_DateTime]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (
SELECT CONVERT(INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid'
) AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER (
ORDER BY [CPU_Time_Ms] DESC
) AS [row_num]
,DatabaseName
,[CPU_Time_Ms]
,CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
,[CPU_Stats_DateTime]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num
OPTION (RECOMPILE);

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

----- ****** I/O Usage by database ****

SELECT Name AS'Database Name'
,SUM(num_of_reads)AS'Number of Reads'
,SUM(num_of_writes)AS'Number of Writes'
,cast (sysdatetime() as date) taken_date
FROM sys.dm_io_virtual_file_stats(NULL,NULL) I
INNER JOIN sys.databases D ON I.database_id = D.database_id
where name not in ('msdb','master','model')
GROUP BY Name
ORDER BY 'Number of Reads'
DESC;


--- Memory utilization history -----------


declare @page_size int, @Instancename varchar(50);
declare @Total_physical_memory_GB as varchar(50);
declare @Available_physical_memory_MB as varchar(50);
declare @Buffer_Pool_Usage as varchar(50);
declare @Max_Server_Mermory as varchar(50);
declare @ple as int;
declare @SQLcmd varchar(8000); set @SQLcmd = '<Total Physical Memory Query>';
declare @Mem table ([Total_physical_memory_GB] varchar(50));

-- Get the SQL server version
declare @version varchar(4);
select @version = SUBSTRING(@@VERSION, CHARINDEX('2',@@VERSION),4);

begin
SET NOCOUNT ON;
SELECT @Total_physical_memory_GB = cast((([total_physical_memory_kb] / 1024)) as varchar(50))FROM [master].[sys].[dm_os_sys_memory];
SELECT @Available_physical_memory_MB = cast((([available_physical_memory_kb] / 1024)) as varchar(50))FROM [master].[sys].[dm_os_sys_memory];
SELECT @Buffer_Pool_Usage =cast(CEILING(cntr_value/1024) as varchar(50)) FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)';
SELECT @Max_Server_Mermory = cast([value_in_use]as varchar(50)) FROM [master].[sys].[configurations]WHERE NAME IN ('Max server memory (MB)');
select @ple = cntr_value from sys.dm_os_performance_counters where object_name like '%Manager%' and counter_name = 'Page life expectancy';
end

select  @Total_physical_memory_GB 'Total RAM MB ' ,
        @Available_physical_memory_MB  '  Available RAM MB ',
        @Max_Server_Mermory ' Max Server Memory MB ',
@Buffer_Pool_Usage ' Current SQL Usage MB ',
cast(@ple as varchar(10)) ' Page Life Expectancy Sec '
,cast (sysdatetime() as date) taken_date

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

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database FULL'
WHEN 'L' THEN 'Transaction Log'
WHEN 'I' THEN 'Incremental'
END AS backup_type,
--msdb.dbo.backupset.backup_size,
cast (round (msdb.dbo.backupset.backup_size/1024/1024,1) as int) 'backup_size(MB)',
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
and
database_name not in ('master','model','msdb')
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date;


---------------------------------------------------------------------------------------------------------------------------------
--- Are there any errors in your SQL Server Error Log?
declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();
-- Create the temporary table
CREATE TABLE #ErrorLog (logdate datetime
                      , processinfo varchar(255)
                      , Message varchar(500))
-- Populate the temporary table
INSERT #ErrorLog (logdate, processinfo, Message)
   EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';
-- Filter the temporary table
SELECT LogDate, Message FROM #ErrorLog
WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'
ORDER BY logdate DESC
-- Drop the temporary table
DROP TABLE #ErrorLog;

----------------------------------------------------------------------------------------------------------------------------
 -- Are there any SQL Server statements in the cache that could use tuning?
 -- The following query will identify any poor performing SQL statements. You can alter the "order by" clause depending on what you are most concerned with (IO vs. CPU vs. Elapsed Time).

SELECT top 10 text as "SQL Statement",
   last_execution_time as "Last Execution Time",
   (total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO],
   (total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)],
   (total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)],
   execution_count as "Execution Count",
   qp.query_plan as "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
order by total_elapsed_time/execution_count desc;

------------------------------------------------------------------------------------------------------------------------------
-- statistics older than 2 days
SELECT
  --st.object_id                          AS [Table ID]
 OBJECT_NAME(st.object_id)             AS [Table Name]
--, st.name                               AS [Index Name]
, cast (STATS_DATE(st.object_id, st.stats_id) as date) AS [LastUpdated]
, sum(modification_counter    )              AS [Rows Modified]
FROM
sys.stats st
CROSS APPLY
sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE
(
STATS_DATE(st.object_id, st.stats_id)<=DATEADD(DAY,-2,GETDATE())
) and (STATS_DATE(st.object_id, st.stats_id)>DATEADD(DAY,-3,GETDATE())  )
AND modification_counter > 0
AND OBJECTPROPERTY(st.object_id,'IsUserTable')=1
group by  OBJECT_NAME(st.object_id) , cast (STATS_DATE(st.object_id, st.stats_id) as date)
order by  cast (STATS_DATE(st.object_id, st.stats_id) as date) desc;
------------------------------------------------------------------------------------------------------------------------------