<commit_before>﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using smo = Microsoft.SqlServer.Management.Smo;

namespace KMO
{
    public static class KServer
    {

        #region General informations
        /// <summary>
        /// Get the machine type : this server is a physical server or a Virtual Machine ?
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>Physical, virtual or unknown</returns>
        public static string MachineType(this smo.Server s)
        {
            string serverType = "Unknown";
            smo.Database d = s.Databases["master"];
            DataTable dt = d.ExecuteWithResults("SELECT virtual_machine_type_desc FROM sys.dm_os_sys_info").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                string virtualType = dt.Rows[0]["virtual_machine_type_desc"].ToString();
                switch (virtualType)
                {
                    case "NONE":
                        serverType = "Physical server";
                        break;
                    case "HYPERVISOR":
                        serverType = "Virtual server";
                        break;
                }
            }
            return serverType;
        }

        /// <summary>
        /// When Sql server service restarts, TempDB is recreated. So tempdb create date can be used to know when the server restarted.
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>the datetime when sql service restart</returns>
        public static DateTime LastRestart(this smo.Server s)
        {
            return s.Databases["tempdb"].CreateDate;
        }

        /// <summary>
        /// Get services account with status
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>a datatable with service name + service informations</returns>
        public static DataTable ServiceStatus(this smo.Server s)
        {
            string sql = @"SELECT servicename
    , service_account + ' (' + status_desc + ')' AS serviceInfo
FROM sys.dm_server_services";
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(sql).Tables[0];
        }
        #endregion

        #region SQL Server Versions
        /// <summary>
        /// Get Sql server version name from major and minor version
        /// </summary>
        /// <param name="s">your smo server object</param>
        /// <returns>a string</returns>
        public static string VersionName(this smo.Server s)
        {
            string version;
            switch (s.VersionMajor.ToString() + "." + s.VersionMinor.ToString())
            {
                case "13.0":
                    version = "Sql Server 2016";
                    break;
                case "12.0":
                    version = "Sql Server 2014";
                    break;
                case "11.0":
                    version = "Sql Server 2012";
                    break;
                case "10.50":
                    version = "Sql Server 2008 R2";
                    break;
                case "10.0":
                    version = "Sql Server 2008";
                    break;
                case "9.0":
                    version = "Sql Server 2005";
                    break;
                case "8.0":
                    version = "Sql Server 2000";
                    break;
                case "7.0":
                    version = "Sql Server 7";
                    break;
                default:
                    version = "Unknown edition";
                    break;
            }
            return version;
        }

        /// <summary>
        /// Get Sql server full version name from major and minor version, productLevel, Edition and version
        /// </summary>
        /// <param name="s">your smo server object</param>
        /// <returns>a string</returns>
        public static string VersionFull(this smo.Server s)
        {
            return string.Format("{0} {1} {2} ({3})", s.VersionName(), s.ProductLevel, s.Edition, s.Version);
        }

        #endregion

        #region Monitoring
        /// <summary>
        /// Get informations about actives session. Locks, performances issues, percent complete, execution plans, etc...
        /// </summary>
        /// <param name="s">Your smo Server</param>
        /// <param name="WithSystemSession">True if you want to select system session. False if you only need user sessions</param>
        /// <param name="WithQueryPlan">True if you want to fill the last column with query plan (xml format). False without execution plan and better performances</param>
        /// <returns>The result of the query in a DataTable</returns>
        public static DataTable GetLiveSession(this smo.Server s, bool WithSystemSession = false, bool WithQueryPlan = false)
        {
            smo.Database d = s.Databases["master"];
            string _sql = @"SELECT CAST(qe.session_id AS VARCHAR) AS [Kill]
    , CASE WHEN CAST(blocking_session_id AS VARCHAR) = '0' THEN '' ELSE CAST(blocking_session_id AS VARCHAR) END AS [Blocking_Session_Info]
	, s.login_name AS [Login]
	, s.host_name AS [Host_Name]
	, s.program_name AS [Program_Name]
	, s.client_interface_name AS [Client_Interface_Name]
	, db_name(qe.Database_id) AS [Database]
	, qe.logical_reads AS [Logical_Read]
	, qe.cpu_time AS [CPU_Time]
	, DATEDIFF(MINUTE, start_time, getdate()) AS [Duration]
	, command AS [Command]
	, qe.status AS [Status]
	, percent_complete AS [Percent_Complete]
	, start_time AS [Start_Time]
    , qe.open_transaction_count AS [Open_Transaction_Count]
    , a.text AS [Query]
    , {0} AS [Execution_Plan]
FROM sys.dm_exec_requests qe (NOLOCK)
	INNER JOIN sys.dm_exec_sessions s (NOLOCK) on qe.session_id = s.session_id 
	LEFT JOIN (select sqe.session_id
					, st.text
				FROM sys.dm_exec_requests sqe (NOLOCK)
					CROSS APPLY sys.dm_exec_sql_text(sqe.sql_handle) st) a ON qe.session_id = a.session_id
{1}
WHERE qe.session_id != @@SPID
	{2}
ORDER BY blocking_session_id DESC
	, Duration DESC
	, [CPU_Time] DESC";
            string _sql0 = "''";
            string _sql1 = string.Empty;
            string _sql2 = string.Empty;
            if (WithQueryPlan)
            {
                _sql0 = "b.query_plan";
                _sql1 = @"	LEFT JOIN (select sqe.session_id
					, ph.query_plan
				FROM sys.dm_exec_requests sqe (NOLOCK)
					CROSS APPLY sys.dm_exec_query_plan(sqe.plan_handle) ph) b ON qe.session_id = b.session_id";
            }
            if (!WithSystemSession)
            {
                _sql2 = "AND s.is_user_process = 1";
            }
            _sql = string.Format(_sql, _sql0, _sql1, _sql2);
            return d.ExecuteWithResults(_sql).Tables[0];
        }

        /// <summary>
        /// Get informations about all sessions on a server
        /// </summary>
        /// <param name="s">Your smo Server</param>
        /// <returns>The result of the query in a DataTable</returns>
        public static DataTable GetWho(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT sp.spid
	, sp.blocked AS Blocked
	, d.name as [Database]
	, RTRIM(sp.hostname) AS [Hostname]
	, RTRIM(sp.program_name) AS [Program Name]
	, RTRIM(sp.loginame) AS [Login Name]
	, RTRIM(sp.nt_domain) AS [NT Domain]
	, RTRIM(sp.nt_username) AS [NT Username]
    , ec.auth_scheme AS [Auth protocol]
	, sp.waittime AS [Wait Time]
	, RTRIM(sp.lastwaittype) AS [Last Wait Type]
	, sp.cpu as [CPU]
	, sp.physical_io AS [Physical IO]
	, sp.memusage AS [Memory]
	, sp.login_time AS [Login Time]
	, sp.last_batch AS [Last Batch]
	, sp.ecid AS [Execution Context ID]
	, sp.open_tran AS [Open Tran]
	, RTRIM(sp.status) AS [Status]
	, sp.hostprocess AS [Host Process]
	, RTRIM(sp.cmd) AS [Command Type]
	, sp.net_address AS [NET Adress]
	, RTRIM(sp.net_library) AS [NET Library]
	, sp2.spid AS [Blocking]
FROM master.dbo.sysprocesses sp
	LEFT JOIN master.dbo.sysdatabases d ON sp.dbid = d.dbid
	LEFT JOIN master.dbo.sysusers u ON sp.uid = u.uid
	LEFT JOIN master.dbo.sysprocesses sp2 ON sp.spid = sp2.blocked
    LEFT JOIN sys.dm_exec_connections ec ON sp.spid = ec.session_id AND ec.parent_connection_id IS NULL
ORDER BY sp.blocked
    , d.name
    , sp.cpu DESC").Tables[0];
        }

        #endregion

        #region Backups
        /// <summary>
        /// For each DB, get the recovery model, the last backup full and the last restore
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>a DataTable with 1 datatable containing the result of the query</returns>
        public static DataTable GetBackupHistory(this smo.Server s)
        {
            smo.Database d = s.Databases["msdb"];
            return d.ExecuteWithResults(@"SELECT sdb.name AS DatabaseName
    , MAX(sdb.recovery_model_desc) as recoveryModel
    , MAX(bus.backup_finish_date) AS LastBackUpTime
	, MAX(rh.restore_date) as LastRestoreTime
FROM sys.databases sdb (NOLOCK)
    LEFT OUTER JOIN msdb.dbo.backupset bus (NOLOCK) ON bus.database_name = sdb.name 
		AND COALESCE(bus.is_snapshot, 0) != 1 
		AND COALESCE(bus.type, 'D') = 'D' 
    LEFT join msdb.dbo.restorehistory rh (NOLOCK) on rh.destination_database_name = sdb.Name
GROUP BY sdb.name
ORDER BY sdb.name").Tables[0];
        }
        #endregion

        #region Logins and Security
        /// <summary>
        /// Get all login with sysadmin rights
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>the result of the query</returns>
        public static DataTable GetWhoIsSa(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT mem.name AS [Login Name]
	, mem.type_desc AS [Login Type]
	, mem.create_date AS [Create Date]
	, mem.modify_date AS [Modify Date]
	, mem.is_disabled AS [Is Disabled]
FROM sys.server_role_members AS srm (NOLOCK)
	INNER JOIN sys.server_principals AS mem (NOLOCK) ON mem.principal_id = srm.member_principal_id
	INNER JOIN sys.server_principals AS rol (NOLOCK) ON rol.principal_id = srm.role_principal_id
WHERE rol.name = 'sysadmin'
ORDER BY mem.name").Tables[0];
        }
        #endregion

        #region Disk
        /// <summary>
        /// Get information (size, freespace, name, last autogrowth) for each databases files.
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <param name="minSize">Use this param to hide files smaller than this size in Mb(0 by default)</param>
        /// <param name="maxSize">Use this param to hide files greater than this size in Mb(2147483647 by default)</param>
        /// <param name="withData">To get data files (true by default)</param>
        /// <param name="withLog">To get log files (true by default)</param>
        /// <returns>a DataTable</returns>
        public static DataTable GetFileTreeMaps(this smo.Server s, int minSize = 0, int maxSize = 2147483647, bool withData = true, bool withLog = true)
        {
            smo.Database d = s.Databases["master"];
            string fileTypeFilter = string.Empty;

            if (!withData)
            {
                fileTypeFilter += " AND b.TYPE != 0 ";
            }
            if (!withLog)
            {
                fileTypeFilter += " AND b.TYPE = 0 ";
            }

            string sql = string.Format(@"CREATE TABLE #TMPLASTFILECHANGE 
(
	databasename nvarchar(128)
	, filename nvarchar(128)
	, endtime datetime
)

DECLARE @path NVARCHAR(1000)
SELECT @path = SUBSTRING(PATH, 1, LEN(PATH) - CHARINDEX('\', REVERSE(PATH))) + '\log.trc'
FROM sys.traces
WHERE id = 1;

WITH CTE (databaseid, filename, EndTime)
AS
(
	SELECT databaseid
		, filename
		, MAX(t.EndTime) AS EndTime
	FROM ::fn_trace_gettable(@path, default ) t
	WHERE EventClass IN (92, 93)
		AND DATEDIFF(hh,StartTime,GETDATE()) < 24
	GROUP BY databaseid
		, filename
)
INSERT INTO #TMPLASTFILECHANGE
(
	databasename
	, filename
	, endtime
)
SELECT DB_NAME(database_id) AS DatabaseName
	, mf.name AS LogicalName
	, cte.EndTime
FROM sys.master_files mf
	LEFT JOIN CTE cte on mf.database_id=cte.databaseid 
		AND mf.name=cte.filename
WHERE cte.EndTime IS NOT NULL

CREATE TABLE #TMPSPACEUSED 
(
	DBNAME    NVARCHAR(128),
	FILENAME   NVARCHAR(128),
	SPACEUSED FLOAT
)

INSERT INTO #TMPSPACEUSED
(
	DBNAME
	, FILENAME
	, SPACEUSED
)
EXEC('sp_MSforeachdb''use [?]; Select ''''?'''' AS DBName
			, Name AS FileNme
			, fileproperty(Name,''''SpaceUsed'''') AS SpaceUsed 
		FROM sys.sysfiles''')

SELECT a.name AS DatabaseName
	, b.name AS FileName
	, CASE b.TYPE WHEN 0 THEN 'DATA' ELSE b.type_desc END AS FileType
	, CAST((b.size * 8 / 1024.0) AS DECIMAL(18,2)) AS FileSize
	, CAST((b.size * 8 / 1024.0) - (d.SPACEUSED / 128.0) AS DECIMAL(15,2)) / CAST((b.size * 8 / 1024.0) AS DECIMAL(18,2)) * 100 AS FreeSpace
	, b.physical_name
	, datediff(DAY, c.endtime, GETDATE()) AS LastGrowth
FROM sys.databases a
	INNER JOIN sys.master_files b ON a.database_id = b.database_id
	INNER JOIN #TMPSPACEUSED d  ON a.NAME = d.DBNAME 
		AND b.name = d.FILENAME
	LEFT JOIN #TMPLASTFILECHANGE c on a.name = c.databasename 
		AND b.name = c.filename
WHERE b.size >= ({0} / 8.0 * 1024.0)
	AND b.size <= ({1} / 8.0 * 1024.0)
    {2}
ORDER BY FILESIZE DESC

DROP TABLE #TMPSPACEUSED
DROP TABLE #TMPLASTFILECHANGE
", minSize, maxSize, fileTypeFilter);
            return d.ExecuteWithResults(sql).Tables[0];
        }

        /// <summary>
        /// Get IO statistics by database files.
        /// Useful to detect disk performance issue or high database IO
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>return the result of the query in a DataTable</returns>
        public static DataTable GetIOStatistics(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT DB_NAME(fs.database_id) AS [Database Name]
	, mf.physical_name AS [Physical Name]
	, io_stall_read_ms AS [IO Stall Read ms]
	, num_of_reads AS [Num of Reads]
	, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Avg Read Stall ms]
	, io_stall_write_ms AS [IO Stall Write ms]
	, num_of_writes AS [Num of Writes]
	, CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [Avg Write Stall ms]
	, io_stall_read_ms + io_stall_write_ms AS [IO Stalls]
	, num_of_reads + num_of_writes AS [Total IO]
	, CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [Avg IO Stall ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs 
	INNER JOIN sys.master_files AS mf (NOLOCK) ON fs.database_id = mf.database_id 
		AND fs.[file_id] = mf.[file_id]
ORDER BY [Avg IO Stall ms] DESC 
OPTION (RECOMPILE)").Tables[0];
        }

        /// <summary><commit_msg>Improve IoStatistics query<commit_after>﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using smo = Microsoft.SqlServer.Management.Smo;

namespace KMO
{
    public static class KServer
    {

        #region General informations
        /// <summary>
        /// Get the machine type : this server is a physical server or a Virtual Machine ?
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>Physical, virtual or unknown</returns>
        public static string MachineType(this smo.Server s)
        {
            string serverType = "Unknown";
            smo.Database d = s.Databases["master"];
            DataTable dt = d.ExecuteWithResults("SELECT virtual_machine_type_desc FROM sys.dm_os_sys_info").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                string virtualType = dt.Rows[0]["virtual_machine_type_desc"].ToString();
                switch (virtualType)
                {
                    case "NONE":
                        serverType = "Physical server";
                        break;
                    case "HYPERVISOR":
                        serverType = "Virtual server";
                        break;
                }
            }
            return serverType;
        }

        /// <summary>
        /// When Sql server service restarts, TempDB is recreated. So tempdb create date can be used to know when the server restarted.
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>the datetime when sql service restart</returns>
        public static DateTime LastRestart(this smo.Server s)
        {
            return s.Databases["tempdb"].CreateDate;
        }

        /// <summary>
        /// Get services account with status
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>a datatable with service name + service informations</returns>
        public static DataTable ServiceStatus(this smo.Server s)
        {
            string sql = @"SELECT servicename
    , service_account + ' (' + status_desc + ')' AS serviceInfo
FROM sys.dm_server_services";
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(sql).Tables[0];
        }
        #endregion

        #region SQL Server Versions
        /// <summary>
        /// Get Sql server version name from major and minor version
        /// </summary>
        /// <param name="s">your smo server object</param>
        /// <returns>a string</returns>
        public static string VersionName(this smo.Server s)
        {
            string version;
            switch (s.VersionMajor.ToString() + "." + s.VersionMinor.ToString())
            {
                case "13.0":
                    version = "Sql Server 2016";
                    break;
                case "12.0":
                    version = "Sql Server 2014";
                    break;
                case "11.0":
                    version = "Sql Server 2012";
                    break;
                case "10.50":
                    version = "Sql Server 2008 R2";
                    break;
                case "10.0":
                    version = "Sql Server 2008";
                    break;
                case "9.0":
                    version = "Sql Server 2005";
                    break;
                case "8.0":
                    version = "Sql Server 2000";
                    break;
                case "7.0":
                    version = "Sql Server 7";
                    break;
                default:
                    version = "Unknown edition";
                    break;
            }
            return version;
        }

        /// <summary>
        /// Get Sql server full version name from major and minor version, productLevel, Edition and version
        /// </summary>
        /// <param name="s">your smo server object</param>
        /// <returns>a string</returns>
        public static string VersionFull(this smo.Server s)
        {
            return string.Format("{0} {1} {2} ({3})", s.VersionName(), s.ProductLevel, s.Edition, s.Version);
        }

        #endregion

        #region Monitoring
        /// <summary>
        /// Get informations about actives session. Locks, performances issues, percent complete, execution plans, etc...
        /// </summary>
        /// <param name="s">Your smo Server</param>
        /// <param name="WithSystemSession">True if you want to select system session. False if you only need user sessions</param>
        /// <param name="WithQueryPlan">True if you want to fill the last column with query plan (xml format). False without execution plan and better performances</param>
        /// <returns>The result of the query in a DataTable</returns>
        public static DataTable GetLiveSession(this smo.Server s, bool WithSystemSession = false, bool WithQueryPlan = false)
        {
            smo.Database d = s.Databases["master"];
            string _sql = @"SELECT CAST(qe.session_id AS VARCHAR) AS [Kill]
    , CASE WHEN CAST(blocking_session_id AS VARCHAR) = '0' THEN '' ELSE CAST(blocking_session_id AS VARCHAR) END AS [Blocking_Session_Info]
	, s.login_name AS [Login]
	, s.host_name AS [Host_Name]
	, s.program_name AS [Program_Name]
	, s.client_interface_name AS [Client_Interface_Name]
	, db_name(qe.Database_id) AS [Database]
	, qe.logical_reads AS [Logical_Read]
	, qe.cpu_time AS [CPU_Time]
	, DATEDIFF(MINUTE, start_time, getdate()) AS [Duration]
	, command AS [Command]
	, qe.status AS [Status]
	, percent_complete AS [Percent_Complete]
	, start_time AS [Start_Time]
    , qe.open_transaction_count AS [Open_Transaction_Count]
    , a.text AS [Query]
    , {0} AS [Execution_Plan]
FROM sys.dm_exec_requests qe (NOLOCK)
	INNER JOIN sys.dm_exec_sessions s (NOLOCK) on qe.session_id = s.session_id 
	LEFT JOIN (select sqe.session_id
					, st.text
				FROM sys.dm_exec_requests sqe (NOLOCK)
					CROSS APPLY sys.dm_exec_sql_text(sqe.sql_handle) st) a ON qe.session_id = a.session_id
{1}
WHERE qe.session_id != @@SPID
	{2}
ORDER BY blocking_session_id DESC
	, Duration DESC
	, [CPU_Time] DESC";
            string _sql0 = "''";
            string _sql1 = string.Empty;
            string _sql2 = string.Empty;
            if (WithQueryPlan)
            {
                _sql0 = "b.query_plan";
                _sql1 = @"	LEFT JOIN (select sqe.session_id
					, ph.query_plan
				FROM sys.dm_exec_requests sqe (NOLOCK)
					CROSS APPLY sys.dm_exec_query_plan(sqe.plan_handle) ph) b ON qe.session_id = b.session_id";
            }
            if (!WithSystemSession)
            {
                _sql2 = "AND s.is_user_process = 1";
            }
            _sql = string.Format(_sql, _sql0, _sql1, _sql2);
            return d.ExecuteWithResults(_sql).Tables[0];
        }

        /// <summary>
        /// Get informations about all sessions on a server
        /// </summary>
        /// <param name="s">Your smo Server</param>
        /// <returns>The result of the query in a DataTable</returns>
        public static DataTable GetWho(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT sp.spid
	, sp.blocked AS Blocked
	, d.name as [Database]
	, RTRIM(sp.hostname) AS [Hostname]
	, RTRIM(sp.program_name) AS [Program Name]
	, RTRIM(sp.loginame) AS [Login Name]
	, RTRIM(sp.nt_domain) AS [NT Domain]
	, RTRIM(sp.nt_username) AS [NT Username]
    , ec.auth_scheme AS [Auth protocol]
	, sp.waittime AS [Wait Time]
	, RTRIM(sp.lastwaittype) AS [Last Wait Type]
	, sp.cpu as [CPU]
	, sp.physical_io AS [Physical IO]
	, sp.memusage AS [Memory]
	, sp.login_time AS [Login Time]
	, sp.last_batch AS [Last Batch]
	, sp.ecid AS [Execution Context ID]
	, sp.open_tran AS [Open Tran]
	, RTRIM(sp.status) AS [Status]
	, sp.hostprocess AS [Host Process]
	, RTRIM(sp.cmd) AS [Command Type]
	, sp.net_address AS [NET Adress]
	, RTRIM(sp.net_library) AS [NET Library]
	, sp2.spid AS [Blocking]
FROM master.dbo.sysprocesses sp
	LEFT JOIN master.dbo.sysdatabases d ON sp.dbid = d.dbid
	LEFT JOIN master.dbo.sysusers u ON sp.uid = u.uid
	LEFT JOIN master.dbo.sysprocesses sp2 ON sp.spid = sp2.blocked
    LEFT JOIN sys.dm_exec_connections ec ON sp.spid = ec.session_id AND ec.parent_connection_id IS NULL
ORDER BY sp.blocked
    , d.name
    , sp.cpu DESC").Tables[0];
        }

        #endregion

        #region Backups
        /// <summary>
        /// For each DB, get the recovery model, the last backup full and the last restore
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>a DataTable with 1 datatable containing the result of the query</returns>
        public static DataTable GetBackupHistory(this smo.Server s)
        {
            smo.Database d = s.Databases["msdb"];
            return d.ExecuteWithResults(@"SELECT sdb.name AS DatabaseName
    , MAX(sdb.recovery_model_desc) as recoveryModel
    , MAX(bus.backup_finish_date) AS LastBackUpTime
	, MAX(rh.restore_date) as LastRestoreTime
FROM sys.databases sdb (NOLOCK)
    LEFT OUTER JOIN msdb.dbo.backupset bus (NOLOCK) ON bus.database_name = sdb.name 
		AND COALESCE(bus.is_snapshot, 0) != 1 
		AND COALESCE(bus.type, 'D') = 'D' 
    LEFT join msdb.dbo.restorehistory rh (NOLOCK) on rh.destination_database_name = sdb.Name
GROUP BY sdb.name
ORDER BY sdb.name").Tables[0];
        }
        #endregion

        #region Logins and Security
        /// <summary>
        /// Get all login with sysadmin rights
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>the result of the query</returns>
        public static DataTable GetWhoIsSa(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT mem.name AS [Login Name]
	, mem.type_desc AS [Login Type]
	, mem.create_date AS [Create Date]
	, mem.modify_date AS [Modify Date]
	, mem.is_disabled AS [Is Disabled]
FROM sys.server_role_members AS srm (NOLOCK)
	INNER JOIN sys.server_principals AS mem (NOLOCK) ON mem.principal_id = srm.member_principal_id
	INNER JOIN sys.server_principals AS rol (NOLOCK) ON rol.principal_id = srm.role_principal_id
WHERE rol.name = 'sysadmin'
ORDER BY mem.name").Tables[0];
        }
        #endregion

        #region Disk
        /// <summary>
        /// Get information (size, freespace, name, last autogrowth) for each databases files.
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <param name="minSize">Use this param to hide files smaller than this size in Mb(0 by default)</param>
        /// <param name="maxSize">Use this param to hide files greater than this size in Mb(2147483647 by default)</param>
        /// <param name="withData">To get data files (true by default)</param>
        /// <param name="withLog">To get log files (true by default)</param>
        /// <returns>a DataTable</returns>
        public static DataTable GetFileTreeMaps(this smo.Server s, int minSize = 0, int maxSize = 2147483647, bool withData = true, bool withLog = true)
        {
            smo.Database d = s.Databases["master"];
            string fileTypeFilter = string.Empty;

            if (!withData)
            {
                fileTypeFilter += " AND b.TYPE != 0 ";
            }
            if (!withLog)
            {
                fileTypeFilter += " AND b.TYPE = 0 ";
            }

            string sql = string.Format(@"CREATE TABLE #TMPLASTFILECHANGE 
(
	databasename nvarchar(128)
	, filename nvarchar(128)
	, endtime datetime
)

DECLARE @path NVARCHAR(1000)
SELECT @path = SUBSTRING(PATH, 1, LEN(PATH) - CHARINDEX('\', REVERSE(PATH))) + '\log.trc'
FROM sys.traces
WHERE id = 1;

WITH CTE (databaseid, filename, EndTime)
AS
(
	SELECT databaseid
		, filename
		, MAX(t.EndTime) AS EndTime
	FROM ::fn_trace_gettable(@path, default ) t
	WHERE EventClass IN (92, 93)
		AND DATEDIFF(hh,StartTime,GETDATE()) < 24
	GROUP BY databaseid
		, filename
)
INSERT INTO #TMPLASTFILECHANGE
(
	databasename
	, filename
	, endtime
)
SELECT DB_NAME(database_id) AS DatabaseName
	, mf.name AS LogicalName
	, cte.EndTime
FROM sys.master_files mf
	LEFT JOIN CTE cte on mf.database_id=cte.databaseid 
		AND mf.name=cte.filename
WHERE cte.EndTime IS NOT NULL

CREATE TABLE #TMPSPACEUSED 
(
	DBNAME    NVARCHAR(128),
	FILENAME   NVARCHAR(128),
	SPACEUSED FLOAT
)

INSERT INTO #TMPSPACEUSED
(
	DBNAME
	, FILENAME
	, SPACEUSED
)
EXEC('sp_MSforeachdb''use [?]; Select ''''?'''' AS DBName
			, Name AS FileNme
			, fileproperty(Name,''''SpaceUsed'''') AS SpaceUsed 
		FROM sys.sysfiles''')

SELECT a.name AS DatabaseName
	, b.name AS FileName
	, CASE b.TYPE WHEN 0 THEN 'DATA' ELSE b.type_desc END AS FileType
	, CAST((b.size * 8 / 1024.0) AS DECIMAL(18,2)) AS FileSize
	, CAST((b.size * 8 / 1024.0) - (d.SPACEUSED / 128.0) AS DECIMAL(15,2)) / CAST((b.size * 8 / 1024.0) AS DECIMAL(18,2)) * 100 AS FreeSpace
	, b.physical_name
	, datediff(DAY, c.endtime, GETDATE()) AS LastGrowth
FROM sys.databases a
	INNER JOIN sys.master_files b ON a.database_id = b.database_id
	INNER JOIN #TMPSPACEUSED d  ON a.NAME = d.DBNAME 
		AND b.name = d.FILENAME
	LEFT JOIN #TMPLASTFILECHANGE c on a.name = c.databasename 
		AND b.name = c.filename
WHERE b.size >= ({0} / 8.0 * 1024.0)
	AND b.size <= ({1} / 8.0 * 1024.0)
    {2}
ORDER BY FILESIZE DESC

DROP TABLE #TMPSPACEUSED
DROP TABLE #TMPLASTFILECHANGE
", minSize, maxSize, fileTypeFilter);
            return d.ExecuteWithResults(sql).Tables[0];
        }

        /// <summary>
        /// Get IO statistics by database files.
        /// Useful to detect disk performance issue or high database IO
        /// </summary>
        /// <param name="s">your smo server</param>
        /// <returns>return the result of the query in a DataTable</returns>
        public static DataTable GetIOStatistics(this smo.Server s)
        {
            smo.Database d = s.Databases["master"];
            return d.ExecuteWithResults(@"SELECT DB_NAME(fs.database_id) AS [Database Name]
	, mf.physical_name AS [Physical Name]
	, io_stall_read_ms AS [IO Stall Read ms]
	, num_of_reads AS [Num of Reads]
	, CASE WHEN num_of_reads = 0 THEN 0
		ELSE CAST(io_stall_read_ms / num_of_reads AS NUMERIC(10,1)) END AS [Avg Read Stall ms]
	, io_stall_write_ms AS [IO Stall Write ms]
	, num_of_writes AS [Num of Writes]
	, CASE WHEN num_of_writes = 0 THEN 0
		ELSE CAST(io_stall_write_ms / num_of_writes AS NUMERIC(10,1)) END AS [Avg Write Stall ms]
	, io_stall_read_ms + io_stall_write_ms AS [IO Stalls]
	, num_of_reads + num_of_writes AS [Total IO]
	, CASE WHEN (num_of_reads + num_of_writes) = 0 THEN 0
		ELSE CAST((io_stall_read_ms + io_stall_write_ms)/(num_of_reads + num_of_writes) AS NUMERIC(10,1)) END AS [Avg IO Stall ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs 
	INNER JOIN sys.master_files AS mf (NOLOCK) ON fs.database_id = mf.database_id 
		AND fs.[file_id] = mf.[file_id]
ORDER BY [Avg IO Stall ms] DESC 
OPTION (RECOMPILE)").Tables[0];
        }

        /// <summary>
{'fraction_non_alphanumeric': 0.11126942575963418, 'fraction_numerical': 0.013320520892011, 'mean_word_length': 2.2588273404599937, 'pattern_counts': {'":': 20, '<': 165, '<?xml version=': 0, '>': 165, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3971179', 'n_tokens_mistral': 9981, 'n_tokens_neox': 9381, 'n_words': 2694}