Page Life Expectancy (PLE) value for each NUMA node

 Page Life Expectancy (PLE) value for each NUMA node

Ever wonder how healthy your SQL Server's memory is? One crucial metric to keep an eye on is **Page Life Expectancy (PLE)**. This handy value tells you, on average, how long data pages are staying in the buffer cache without being flushed to disk. A higher PLE generally indicates a healthier system, as SQL Server isn't constantly re-reading data from slower storage.

You can quickly check your server's PLE using this simple SQL query:

SELECT @@SERVERNAME AS [Server Name]
 ,RTRIM([object_name]) AS [Object Name]
 ,instance_name
 ,cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
 AND counter_name = N'Page life expectancy'
OPTION (RECOMPILE);

This query leverages the `sys.dm_os_performance_counters` Dynamic Management View (DMV), which provides real-time performance counter information for your SQL Server instance.

Monitoring PLE is a great way to proactively identify potential memory pressure or inefficiencies in your SQL Server environment. What's your typical PLE on your production servers?

List all assemblies in a database

Understanding Assembly Modules in SQL Server

The assembly_modules catalog view in SQL Server returns a row for each function, procedure, or trigger defined by a Common Language Runtime (CLR) assembly. This view helps to map CLR stored procedures, CLR triggers, and CLR functions to their underlying implementations, providing insight into how CLR objects are defined and utilized in the database.

Here is a SQL query that can be used to retrieve detailed information about assembly modules:

SELECT   so.[Name], so.[type], SCHEMA_NAME(so.SCHEMA_ID) AS [Schema],
         asmbly.[Name], asmbly.permission_set_desc, am.assembly_class, 
         am.assembly_method
FROM [sys].assembly_modules am
   INNER JOIN  [sys].assemblies asmbly
   ON  asmbly.assembly_id = am.assembly_id AND asmbly.[Name] NOT LIKE 'Microsoft%'
   INNER JOIN  [sys].objects so
   ON  so.OBJECT_ID = am.OBJECT_ID
UNION
SELECT   at.[Name], 'TYPE' AS [type], SCHEMA_NAME(AT.SCHEMA_ID) AS [Schema], 
         asmbly.[Name], asmbly.permission_set_desc, AT.assembly_class,
         NULL AS [assembly_method]
FROM [sys].assembly_types at
   INNER JOIN  [sys].assemblies asmbly
   ON  asmbly.assembly_id = at.assembly_id
   AND asmbly.[Name] NOT LIKE 'Microsoft%'
ORDER BY 4, 2, 1;

This query joins several system views (assembly_modules, assemblies, objects, and assembly_types) to pull a comprehensive set of information, including the assembly name, schema, class, and method. It excludes assemblies from Microsoft for better relevance in custom scenarios.

For more detailed information about CLR assemblies in SQL Server, refer to the official documentation from Microsoft: Assemblies (Database Engine) - SQL Server | Microsoft Docs.

Tables with at least one TEXT, NTEXT, IMAGE column

SQL Query to List Tables Containing Text, NText, or Image Columns

In some cases, you may want to retrieve a list of database tables that contain specific types of columns such as text, ntext, or image. To achieve this, you can use the following SQL query.

SQL Query:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM [sys].columns c
    WHERE c.[object_id] = t.[object_id]
    AND c.system_type_id IN
    (
      34, -- image
      35, -- text
      99  -- ntext
    )
);

This SQL query retrieves a list of tables from the database that have columns of types image, text, or ntext. It works by joining the sys.tables and sys.schemas system views, then checks if any columns in the table match one of the specified system types for text, ntext, or image data.

T-SQL Script to Extract SQL Server Database Mail Settings

To generate a script that replicates SQL Server Database Mail configurations, we can query specific system tables located in the MSDB database. These tables store all necessary metadata for mail profiles, accounts, and their associated settings.

The key MSDB tables used in this script include:

• msdb.dbo.sysmail_profile
• msdb.dbo.sysmail_profileaccount
• msdb.dbo.sysmail_account
• msdb.dbo.sysmail_server

Below is a T-SQL script that dynamically generates a complete setup script for Database Mail, including profiles, accounts, servers, and associations. It ensures that configurations can be easily re-created or migrated to another SQL Server instance.

-- Script to extract and generate SQL Server Database Mail configuration
-- Queries sysmail_* tables and generates CREATE statements

USE msdb;
GO

DECLARE @TheResults VARCHAR(MAX),
        @vbCrLf CHAR(2);

SET @vbCrLf = CHAR(13) + CHAR(10);
SET @TheResults = '
USE master
GO
EXEC sp_configure ''show advanced options'', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ''Database Mail XPs'', 1;
GO
RECONFIGURE;
GO
';

-- [Insert the long SELECT @TheResults = @TheResults + ... statement from your script here]

-- Use Tally table to split script lines for output (ensures proper formatting)
-- [Insert the Tally table and ItemSplit CTE as in your original script]

SELECT 
    ROW_NUMBER() OVER (ORDER BY ItemOrder) AS ItemID,
    Item
FROM ItemSplit;