کسب اطلاعات (SERVERPROPERTY)SQL Server با TSQL و DMVs راهنمای کامل

کسب اطلاعات جامع سرور (SERVERPROPERTY) SQL Server: راهنمای تخصصی برای عملکرد بهینه

مدیران پایگاه داده (DBAها) و توسعه‌دهندگان همواره به دنبال روش‌هایی برای جمع‌آوری اطلاعات دقیق در مورد سرورهای SQL Server خود هستند. این اطلاعات برای تصمیم‌گیری آگاهانه در مورد عملکرد، پیکربندی و برنامه‌ریزی ظرفیت بسیار حیاتی است. درک جامع از مشخصات سخت‌افزاری و نرم‌افزاری سرور SQL Server به شما کمک می‌کند تا مشکلات را عیب‌یابی کرده، بهینه‌سازی‌های لازم را اعمال کنید و از پایداری محیط خود اطمینان حاصل نمایید. در این مقاله، به بررسی روش‌های مختلف برای دسترسی به این اطلاعات حیاتی از طریق T-SQL می‌پردازیم.

همانطور که می‌دانید، کسب اطلاعات سرور SQL Server از طریق T-SQL یک مهارت اساسی است. SQL Server ابزارهای قدرتمندی را برای این منظور فراهم می‌کند. در ادامه، توابع و نماهای مدیریتی پویا (DMVs) کلیدی را معرفی می‌کنیم که به شما امکان می‌دهند به طیف وسیعی از جزئیات سرور و سیستم عامل دسترسی پیدا کنید، از جمله نسخه SQL Server، ویرایش، منابع سیستم و تنظیمات مهم.

تابع SERVERPROPERTY

یکی از پرکاربردترین توابع برای کسب اطلاعات کلی در مورد SQL Server، تابع SERVERPROPERTY است. این تابع به شما اجازه می‌دهد تا ویژگی‌های مختلفی از instance سرور را بازیابی کنید. برای مثال، برای دریافت نسخه (Edition) سرور، می‌توانید از دستور زیر استفاده کنید:

SELECT SERVERPROPERTY('Edition') AS ServerEdition;

این دستور اطلاعات مربوط به ویرایش SQL Server را به شما بازمی‌گرداند. تابع SERVERPROPERTY پارامترهای متنوعی دارد که هر کدام اطلاعات خاصی را ارائه می‌دهند. در زیر یک مثال جامع برای جمع‌آوری اطلاعات کلیدی با استفاده از این تابع آورده شده است:

SELECT
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName,
    SERVERPROPERTY('InstanceName') AS InstanceName,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Collation') AS ServerCollation,
    SERVERPROPERTY('IsClustered') AS IsClustered,
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ProcessID') AS ProcessID,
    SERVERPROPERTY('BuildClrVersion') AS CLRVersion,
    SERVERPROPERTY('EngineEdition') AS EngineEdition,
    SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
    SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath,
    SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
    SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
    SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled,
    SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported,
    SERVERPROPERTY('LicenseType') AS LicenseType,
    SERVERPROPERTY('NumLicenses') AS NumLicenses,
    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
    SERVERPROPERTY('ServerName') AS ServerNameProperty,
    SERVERPROPERTY('SqlCharSetName') AS SqlCharSetName,
    SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName
;

این کوئری تمام اطلاعات ضروری را برای مدیریت و بهینه‌سازی SQL Server در یک نگاه در اختیار شما قرار می‌دهد.

نمای مدیریتی پویا sys.dm_os_sys_info

برای کسب اطلاعات دقیق‌تر در مورد منابع سیستم عامل (OS) که SQL Server روی آن اجرا می‌شود، نمای مدیریتی پویا sys.dm_os_sys_info بسیار مفید است. این DMV اطلاعاتی مانند حافظه فیزیکی، حافظه مجازی و تعداد پردازنده‌ها را ارائه می‌دهد.

SELECT
    cpu_count AS NumberOfCPUs,
    hyperthread_ratio AS HyperthreadRatio,
    physical_memory_kb / 1024 AS PhysicalMemoryMB,
    virtual_memory_kb / 1024 AS VirtualMemoryMB,
     committed_kb / 1024 AS CommittedMemoryMB,
    committed_target_kb / 1024 AS CommittedTargetMemoryMB,
    max_workers_count AS MaxWorkersCount,
    scheduler_count AS SchedulerCount,
    os_priority_class AS OSPriorityClass,
    sqlserver_start_time AS SQLServerStartTime,
    system_memory_state_desc AS SystemMemoryState
FROM sys.dm_os_sys_info;

این اطلاعات برای مانیتورینگ عملکرد و برنامه‌ریزی ظرفیت SQL Server حیاتی هستند.

نمای کاتالوگ sys.configurations

برای بررسی تنظیمات پیکربندی (Configuration) سرور SQL Server، از نمای کاتالوگ sys.configurations استفاده کنید. این نما شامل تمام گزینه‌های پیکربندی SQL Server، از جمله مقادیر فعلی و پیش‌فرض آن‌ها است. این برای اطمینان از مطابقت تنظیمات با بهترین شیوه‌ها (best practices) یا نیازمندی‌های خاص محیط شما ضروری است.

SELECT
    name AS ConfigurationName,
    value AS CurrentValue,
    minimum AS MinimumValue,
    maximum AS MaximumValue,
    value_in_use AS ValueInUse,
    description AS Description,
    is_dynamic AS IsDynamic
FROM sys.configurations
ORDER BY name;

با بررسی این نما، می‌توانید تنظیمات حیاتی مانند حداکثر حافظه سرور (max server memory) یا آستانه هزینه موازی‌سازی (cost threshold for parallelism) را مشاهده و مدیریت کنید.

نماهای مدیریتی پویا sys.dm_os_host_info و sys.dm_os_windows_info

برای SQL Server 2017 و نسخه‌های جدیدتر، نمای sys.dm_os_host_info اطلاعات مفصلی درباره سیستم عامل میزبان ارائه می‌دهد. اگر از Azure SQL Database استفاده می‌کنید، sys.dm_os_windows_info معادل آن است. این نماها برای تشخیص نسخه‌های سیستم عامل و سایر جزئیات سطح میزبان که برای عیب‌یابی و سازگاری حیاتی هستند، بسیار مفیدند.

SELECT
    host_platform AS HostPlatform,
    host_distribution AS HostDistribution,
    host_release AS HostRelease,
    host_service_pack_level AS HostServicePackLevel,
    host_sku AS HostSKU,
    os_language_version AS OSLanguageVersion
FROM sys.dm_os_host_info; -- Use sys.dm_os_windows_info for Azure SQL Database

این کوئری اطلاعات مهمی در مورد محیط سیستم عامل که SQL Server در آن اجرا می‌شود، ارائه می‌دهد.

نمای مدیریتی پویا sys.dm_exec_connections

برای کسب اطلاعات در مورد اتصالات فعال به SQL Server، از نمای sys.dm_exec_connections استفاده کنید. این DMV جزئیاتی مانند زمان اتصال، آدرس IP کلاینت و پروتکل شبکه مورد استفاده را نمایش می‌دهد. این اطلاعات برای نظارت بر امنیت و تشخیص مشکلات اتصال به سرور SQL Server مفید است.

SELECT
    c.session_id,
    c.connect_time,
    s.login_name,
    c.client_net_address,
    c.protocol_type,
    c.client_interface_name,
    s.program_name
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE s.is_user_process = 1;

این کوئری به شما کمک می‌کند تا اتصالات جاری را رصد کرده و از فعالیت‌های مشکوک یا غیرعادی مطلع شوید.

متغیر جهانی @@VERSION

برای یک نمای کلی سریع از نسخه SQL Server، از متغیر سراسری @@VERSION استفاده کنید. این متغیر اطلاعاتی در مورد نسخه، ویرایش، سطح سرویس پک و سیستم عامل SQL Server ارائه می‌دهد.

SELECT @@VERSION AS SQLServerVersion;

این متغیر خروجی رشته‌ای را بازمی‌گرداند که شامل تمام جزئیات مربوط به نسخه SQL Server شما است.

رویه‌ی ذخیره‌شده توسعه‌یافته xp_msver

رویه‌ی ذخیره‌شده توسعه‌یافته xp_msver نیز اطلاعات جامعی در مورد نسخه SQL Server و سیستم عامل میزبان آن ارائه می‌دهد. این رویه مجموعه‌ای از سطرها را بازمی‌گرداند که جزئیات مختلفی از جمله پلتفرم، نسخه محصول و اطلاعات CPU را شامل می‌شود. این رویکرد به ویژه در نسخه‌های قدیمی‌تر SQL Server محبوب بوده است.

EXEC xp_msver;

خروجی xp_msver شامل جزئیاتی است که می‌تواند در مستندسازی و عیب‌یابی سرور SQL Server مفید باشد.

نتیجه‌گیری

با استفاده از توابع و نماهای مدیریتی پویای (DMVs) معرفی‌شده در این مقاله، می‌توانید به طور جامع به اطلاعات سرور SQL Server خود دسترسی پیدا کنید. این ابزارها برای هر متخصص SQL Server ضروری هستند و به شما کمک می‌کنند تا سرورهای خود را به طور مؤثرتری مدیریت، بهینه‌سازی و عیب‌یابی کنید. با تسلط بر این روش‌ها، می‌توانید از عملکرد بهینه و پایداری محیط SQL Server خود اطمینان حاصل نمایید. کسب این اطلاعات دقیق، کلید مدیریت موفقیت‌آمیز و برنامه‌ریزی استراتژیک برای زیرساخت پایگاه داده شماست.