Showing posts with label Monitoring. Show all posts
Showing posts with label Monitoring. Show all posts

Thursday 21 September 2017

Quickly find a cached execution plan of an Ad-Hoc query

It is clear that sometimes we just need to find a cached execution plan as quickly as possible so that it can be analysed and then use it to optimise the code in question. This work might be harder if the workload of a database server is based on ad-hoc queries because they do not reuse cached plan as good as stored procedures do especially if Ad-Hoc queries work with parameters, so in this context, we might find many cached plans for only one Ad-Hoc query and it will then make more difficult spot what we are looking for. It is of paramount importance to remember that optimisation and tuning is a never-ending task and it is worthy of our attention every so often. Today I am coming with the following script to help quickly find a cached plan execution plan for an ad-hoc query. This query filters out by the specific text you are looking for in the ad-query.

SELECT TOP 10 st.text AS [SQLText], cp.cacheobjtype CacheObjType, cp.objtype ObjType,
COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName], cp.usecounts AS [PlanUsage], 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 '%some text of the object or ad-hoc query%'

It well worth noting that this is why it is highly advisable using stored procedures so that CPU and memory resources can be used more efficiently. That is all for now, let me know any remarks you may have. Stay tuned.

Monday 3 July 2017

Getting important information of SQL Server Backups

Regardless of the method that is used to take SQL Server backups, they need to be carefully monitored 24x7 hours especially in critical database environments whose data is changing rapidly. Keeping the backups up to date is crucial as they can save us a lot of time when it comes to recovering databases in the likely event of unwitting incidents. On the top of that, in my experience it was shameful to see many environments without a backup strategy in place, even more, nobody was in charge of them.  In that context, it is clear that those businesses were surely at high risk of losing a lot of data and jaw-dropping amounts of money. Sometimes it is believed that having backups of the whole virtual machine is good enough, nevertheless, this sort of backup has a different purpose from a database backup. Thus, at no time can we replace SQL Server database backups with virtual machine backups which are time-consuming, inadequate, far-fetched for databases. In this sense, having implemented a suitable database backup strategy (Full + Diff + Log Backups) and carefully monitoring them are fundamental tasks to serve the needs of ensuring the recovery database process.
Broadly, it is of paramount importance to have a script at hand to find out whether or not specific sorts of backups were taken properly as well as how long they last, the size of the backup file, who took the backup, or if the backup was taken using CHECK_SUM option, etc. Today I am sharing that script which returns important information of backups for all databases in a SQL Server instance. In the following script, it filters out info of Full Backups (type='D'). You can use 'L' for Log Backup and 'I' for Differential Backup. So, try it out and make the most out of the info.

SELECT isnull(d.name,'') DatabaseName,
CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, 
cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB ,  
isnull( last_backup.recovery_model,'')RecoveryModel,isnull(last_backup.name,'') as BackupName ,
isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate,  
isnull(case when [backup_size]>1024 then  cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) 
     + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize,
isnull(case when compressed_backup_size>1024 then  cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) +
     'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize ,
 isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged,  
 isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName
FROM master.sys.sysaltfiles  f
    inner join sys.databases d on f.dbid=d.database_id
    inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected,
                has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2))  as [backup_size], 
                cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2))  as compressed_backup_size,
                isnull(cast(  CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' 
                     + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min'    as varchar(40) 
                ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model
              FROM msdb.dbo.backupset bs
              WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
                                                      where    type = 'D'
                                                     GROUP BY database_name) 
                AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases  )
                                ) as last_backup   on d.name = last_backup.database_name
                             where fileid<>2    
                             group by dbid,d.name , d.is_read_only,d.recovery_model_desc, last_backup.database_name, 
last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,last_backup.name, last_backup.duration,
 last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model
ORDER BY DATEDIFF(minute,  last_backup.backup_start_date,  last_backup.backup_finish_date) desc

That is all for now, let me know any remarks you may have.

Wednesday 12 April 2017

Checking Disk Space usage and Database Files usage per partition disk

When it comes to monitoring and managing disk space we may have some graphic tools to help us complete these tasks rapidly, nevertheless, not everybody can afford to pay the tools, and most of them do not give some details we may need on the spur of the moment. For example, we may have the need to check how much disk space is occupied by database files exclusively so that a wise decision can be made to ensure we are using disk space properly of a database server. We may need to see a report something like this.



In the report we can appreciate that all partition disks are displayed, each of them has information related to the space used in total (db_file_size_on_disk_db) by all database files (data & log) and it also includes the equivalent in percentage (db_file_size_on_disk_pct). Thus, we will be able to know whether or not the disk space is being using properly. Furthermore, disk space capacity and disk space available in gigabytes and percentage can also seen (disk_capacity_gb, disk_space_available_gb, disk_space_available_pct).

Now it is high time to show the code to get that report containing that information:

CREATE PROC [dbo].[sp_DBA_mon_db_files_size_on_disk] (@volume_mount_point char(1)=NULL)
/*WITH ENCRYPTION*/
AS
begin
if (@volume_mount_point is not null )
begin
    SELECT    vs.volume_mount_point, 
    vs.logical_volume_name,   
    
    DB_NAME(f.database_id) database_name,  f.type_desc,
    vs.total_bytes/1024/1024/1024.0 AS [disk_capacity_gb],
    cast( f.[size] / 128 /1024.0 as decimal(10,2)) AS [db_file_size_on_disk_gb],  
    cast( ( f.[size]*100/ 128 /1024.0 ) /   (vs.total_bytes/1024/1024/1024.0 )  as DECIMAL(10,2) ) AS [db_file_size_on_disk_pct],
    cast(vs.available_bytes/1024/1024/1024.0 as DECIMAL(10,2)) AS [disk_space_available_gb],    
   cast ( ( vs.available_bytes *100.0  / vs.total_bytes) as DECIMAL(10,2) )  AS [disk_space_available_pct],
   f.physical_name,
   vs.file_system_type  
    FROM  sys.master_files AS f
    CROSS APPLY   sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
    WHERE   f.database_id < 32767 and  vs.volume_mount_point=@volume_mount_point + ':\'
    order  by f.[size]  desc
end
 else
  begin
    SELECT vs.volume_mount_point, 
        vs.logical_volume_name,                
        vs.total_bytes/1024/1024/1024.0 AS [disk_capacity_gb],
        cast(SUM( f.[size] / 128 /1024.0) as decimal(10,2)) AS [db_file_size_on_disk_gb],  
       cast( (SUM( f.[size]/ 128 /1024.0) *100) /   (vs.total_bytes/1024/1024/1024.0 )  as DECIMAL(10,2) ) AS [db_file_size_on_disk_pct],
        cast(vs.available_bytes/1024/1024/1024.0  as DECIMAL(10,2)) AS [disk_space_available_gb],    
       cast ( ( vs.available_bytes *100.0  / vs.total_bytes) as DECIMAL(10,2) )  AS [disk_space_available_pct],
       vs.file_system_type  
    FROM  sys.master_files AS f
    CROSS APPLY   sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
    WHERE   f.database_id < 32767
    group by  vs.volume_mount_point,vs.total_bytes ,   vs.logical_volume_name,  vs.total_bytes ,vs.file_system_type  ,  vs.available_bytes 
    
 end
end
    

The report could receive the letter of the partition disk as a parameter, and also it can be omitted. As you can see in the definition of the stored procedure, the default value for @volume_mount_point is NULL which means, according to the logic of it, that general information will be displayed, that is the report in the graphic, otherwise, giving the letter of a partition will allow you to get detail information for every database file located in that disk partition. That's is all for now. I hope it is useful and practical for you.  Let me know any remarks you may have. Thanks for reading. Stay tuned.

Friday 28 October 2016

How to monitor database file space usage in SQL Server

Undoubtedly, when it comes to monitoring database file space usage we need a simple way to do it. Fortunately, SQL Server has considerable quantity of DMV and System Functions to allows us to get closely related information about it. Today's post has the intention of providing a practical way of warning us in case the free database file space is less than the threshold of 20%. With this alert in mind, we will be able to make a preventive decision in order to prevent the performance of in-flight transactions from being impacted.

I am coming up with a simple query to achieve that. Firstly, we need to create a physical temporary table 'FileSpace' which will hold related information for each database. Having successfully creating the table, the following step is to execute the adequate logic, in the context of each database user via 'sp_msforeachdb', to collect file space usage which will be reported in the end. I am getting space usage information of each database file by using FILEPROPERTY system function.

The execution of the entire script (including creation of the table) may be automated via a SQL Job in order to send yourself an alert with format HTML by using database mail. Unluckily, this code does not include the logic to send that email but it is not something difficult to complete, therefore, just do it. That is all for now. Let me know any remarks you may have. Thanks for reading.

Here is the whole script.
USE [master]
GO
CREATE TABLE [dbo].[FileSpace](
    [DatabaseName] [nvarchar](128) NULL,
    [FileName] [sysname] NOT NULL,
    Type varchar(10) not null,
    [FileSizeGB] [decimal](10, 2) NULL,
    [SpaceUsedGB] [decimal](10, 1) NULL,
    [SpaceFreeGB] [decimal](10, 1) NULL,
    [SpaceFree%] [decimal](10, 1) NULL,
    Physical_Name varchar(max)
) ON [PRIMARY]
GO
 
EXEC sp_MSforeachdb '
USE [?]
insert into master.DBO.[FileSpace]([DatabaseName] ,    [FileName] , Type,    [FileSizeGB],    [SpaceUsedGB] ,    [SpaceFreeGB] ,    [SpaceFree%],Physical_Name )
select db_name() DatabaseName, name FileName, Type_Desc ,
cast(size/128.0/1024.0 as decimal(10,2)) SizeGB, 
cast(FILEPROPERTY(name,''SpaceUsed'') /128.0/1024.0  as decimal(10,1)) SpaceUsedGB, 
cast((size - FILEPROPERTY(name,''SpaceUsed'') ) /128.0/1024.0 as decimal(10,1)) SpaceFreeGB, 
cast(((size - FILEPROPERTY(name,''SpaceUsed'') )/(size*1.0)) *100 as decimal(10,1)) [SpaceFree%], physical_name
FROM sys.database_files'

SELECT * FROM FileSpace
WHERE [SpaceFree%]<=20
DROP TABLE FileSpace
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE