当前位置：首页 > news >正文

Sql Server 性能分析1 –查看数据库的相关信息

news 来源：原创 2024/7/1 14:09:29

1. 在一台sql server 上操作，我们要尽过能的知道数据的相关信息，这是性能分析的根本。

查看能Sql Server 的相关信息，我们可以用Serverproperty来得到数据库的相关信息，以下是ServerProperty 的相关使用说明：

SERVERPROPERTY

Returns property information about the server instance.

语法：

Syntax

SERVERPROPERTY ( propertyname )

Arguments

propertyname

Is an expression containing the property information to be returned for the server. propertyname can be one of these values.

数据库属性的相关参数

Property name	Values returned
Collation	The name of the default collation for the server. Returns NULL if invalid input or error. Base data type: nvarchar
Edition	The edition of the Microsoft® SQL Server™ instance installed on the server. Returns: 'Desktop Engine' 'Developer Edition' 'Enterprise Edition' 'Enterprise Evaluation Edition' 'Personal Edition' 'Standard Edition' Base data type: nvarchar(128)
Engine Edition	The engine edition of the SQL Server instance installed on the server. 1 = Personal or Desktop Engine 2 = Standard 3 = Enterprise (returned for Enterprise, Enterprise Evaluation, and Developer) Base data type: int
InstanceName	The name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, or invalid input or error. Base data type: nvarchar
IsClustered	The server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Invalid input, or error. Base data type: int
IsFullTextInstalled	The full-text component is installed with the current instance of SQL Server. 1 = Full-text is installed. 0 = Full-text is not installed. NULL = Invalid input, or error. Base data type: int
IsIntegratedSecurityOnly	The server is in integrated security mode. 1 = Integrated Security. 0 = Not Integrated Security. NULL = Invalid input, or error. Base data type: int
IsSingleUser	The server is in single user mode. 1 = Single User. 0 = Not Single User NULL = Invalid input, or error. Base data type: int
IsSyncWithBackup	The database is either a published database or a distribution database, and can be restored without disrupting transactional replication. 1 = True. 0 = False. Base data type: int
LicenseType	Mode of this instance of SQL Server. PER_SEAT = Per-seat mode PER_PROCESSOR = Per-processor mode DISABLED = Licensing is disabled. Base data type: nvarchar(128)
MachineName	Windows NT computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Server, it returns the name of the virtual server. Returns NULL if invalid input or error. Base data type: nvarchar
NumLicenses	Number of client licenses registered for this instance of SQL Server, if in per-seat mode. Number of processors licensed for this instance of SQL Server, if in per-processor mode. Returns NULL if the server is none of the above. Base data type: int
ProcessID	Process ID of the SQL Server service. (ProcessID is useful in identifying which sqlservr.exe belongs to this instance.) Returns NULL if invalid input or error. Base data type: int
ProductVersion	The version of the instance of SQL Server, in the form of 'major.minor.build'. Base data type: varchar(128)
ProductLevel	The level of the version of the SQL Server instance. Returns: 'RTM' = shipping version. 'SPn' = service pack version 'Bn', = beta version. Base data type: nvarchar(128).
ServerName	Both the Windows NT server and instance information associated with a specified instance of SQL Server. Returns NULL if invalid input or error. Base data type: nvarchar

例子：如要查询电脑名，Sql server 数据库实例名，数据库版本，数据类型，数据库级别（如升包等）

select

SERVERPROPERTY('MachineName') as N’Machine Name’,

serverproperty('Servername') as N’Server Name’,

SERVERPROPERTY('ProductVersion') N'Database Version',

SERVERPROPERTY ('Edition') N'Database Type',

SERVERPROPERTY('ProductLevel') N'Database Packs'

注意：@@Version 中的信息与 SERVERPROPERTY 中反映的信息是不一定相同的，查看Sql Server数据库的信息我们应该以SERVERPROPERTY 为准。

Select

cast(SERVERPROPERTY('MachineName')as varchar(10)) as N'Machine Name',

cast(SERVERPROPERTY('Servername') as varchar(20)) as N'Server Name',

cast(SERVERPROPERTY('ProductVersion') as varchar(10)) N'Database Version',

cast(SERVERPROPERTY ('Edition') as varchar(10)) N'Database Type',

cast(SERVERPROPERTY('ProductLevel')as varchar(10)) N'Database Packs'

Machine Name Server Name Database Version Database Type Database Packs

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

VS-HKMESDB VS-HKMESDB\HKDB 8.00.760 Enterprise SP3

select @@version

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

Microsoft SQL Server 2000 - 8.00.760 (Intel IA-64)

Feb 6 2003 16:07:24

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

上面两个Sql 都返回了一些sql server 数据库的信息，我们看到却有两个Service Pack,这里正确的Sql server server packe 是 SP3 , @@version 中的Service Pack 2 是sql server 所在电脑的windows 的 service pack ,这个不能弄混了。