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-HKMESDBHKDB 8.00.760 Enterprise SP3
select @@version
----------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel IA-64)
Feb 6 2003 16:07:24
Copyright (c) 1988-2003 Microsoft Corporation
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 ,這個不能弄混了。
posted on 2009-12-30 10:42
老馬驛站 閱讀(231)
評論(0) 編輯 收藏 引用 所屬分類:
DataBase