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

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 ,这个不能弄混了。

因为业务的需求，需要用Linked Server从当前数据库获取远端数据库的 数据库大小 以及 远端数据库的 表大小

　　关于Linked Server 连接，请看我的另一篇文档：

　　http://blog.csdn.net/zwxrain/archive/2007/01/18/1486304.aspx

　　在操作中遇到以下几个问题，并都以解决

　　问题一： 不可以直接对远需数据库操作 sp_spaceused　 查看大小（数据库大小和表大小）

　　sp_spaceused 的操用方法：

　　查看本地表大小

　　sp_spaceused ‘TableName’

　　查看远端数据库大小

　　Srv_MES.InsiteProdDG.dbo.sp_spaceused ‘TableName’

　　说明：

　　Srv_MES 为Linked Server , InsiteProdDG 为Linked Server 远端的数据库名。

　　查看远端数据大小的错误描述:

　　Msg 7411, Level 16, State 1, Line 14

　　Server 'Srv_MES' is not configured for RPC.

　　解决方法：将Linked Server 的Rpc_Out 设成 True

　　问题2： 解决第一个问题，我们可以正常查询远端数据库的表大小，但当然们将查询结果保存到表时却出现如下错误。

　　insert into @tb_size exec Srv_MES.InsiteProdDG.dbo.sp_spaceused ‘tb_name’

　　错误描述:

编缉推荐阅读以下文章

• Sql Server 性能分析4 –数据库大小，数据库表大小综合性分析报表输出
• Sql Server 性能分析3 –数据库大小，数据库表大小综合性分析
• Sql Server 性能分析1 –查看数据库的相关信息

　　OLE DB provider "SQLNCLI" for linked server "Srv_MES" returned message "The transaction manager has disabled its support for remote/network transactions.".

　　Msg 7391, Level 16, State 2, Line 13

　　The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "Srv_MES" was unable to begin a distributed transaction.

　　原因：

　　由于开启了事务，需要分布式事务协调器的支持。打开本机的控制面板 - 管理工具 - 服务，将两台服务器上的 Distributed Transaction Coordinator (MSDTC) 服务开启，注意 MSDTC 服务的登陆名必须选择 NETWORK SERVICE 用户。然后设置高级选项，运行 dcomcnfg.exe ，进入组件服务，选择我的电脑右键属性，MSDTC - 安全配置，勾选：网络DTC访问，允许入站，允许出站，并且不要求进行验证。其他的可以根据需要选择，不是必须选项。

　　解决：

　　SQL 代码部分：

　　---Linked Server 获取远端数据库的大小----

Create procedure [dbo].[Proc_DBA_Database_Size]
as
Declare @l_datetime varchar(20)
set @l_datetime = convert(varchar(20),getdate(),120)

insert into TB_DBA_Database_Size
select --　 fileid,
　 @l_datetime as Date_Time,
　 'InsiteProdDG' [Database Name] ,
　 cast(sum(size)*8/1024.　　 as　　 decimal(10,2))　　 [DB Size],　　
　 'MB' Size_Type
　-- name　　 logic filename,　　
　-- filename　　 physics filename　
　 from　　 Srv_MES.InsiteProdDG.dbo.sysfiles

编缉推荐阅读以下文章

• Sql Server 性能分析4 –数据库大小，数据库表大小综合性分析报表输出
• Sql Server 性能分析3 –数据库大小，数据库表大小综合性分析
• Sql Server 性能分析1 –查看数据库的相关信息

　　---Linked Server 获取远端数据库表的大小----

Create procedure [dbo].[Proc_DBA_MES_Table_Size]
as
Declare @tb_name varchar(50)
Declare @tb_list table
(
　　　 tb_name varchar(50)
)

declare @tb_size table
(
　　　 name　　 　　 varchar(50),
　　　 rows　　 　　 varchar(50),
　　　 Reserved　　 varchar(50),
　　　 Data　　 　　 varchar(50),
　　　 Index_Size varchar(50),
　　　 Unused　 　　 varchar(50)
)

declare cur_tb_list cursor for
　　　 select name
from dbo.sysobjects
where objectproperty(id, N'istable') = 1
and name not like N'#%%'
order by name


open cur_tb_list
　　　 　fetch Next from cur_tb_list
　　　　　　　　　　 into @tb_name
　　　
　　　 　while @@fetch_status=0
　　　 　　　　 begin
　　　　　　 if exists (select * from dbo.sysobjects
　　　　　　　　　　　　　　　　　 where id = object_id(@tb_name)
　　　　　　　　　　　　　　　　　 and objectproperty(id, N'isusertable')=1)

　　　　　　　　　　 insert into @tb_size exec Srv_MES.InsiteProdDG.dbo.sp_spaceused @tb_name
　　　
　　　　　　 fetch Next from cur_tb_list
　　　　　　　　　　　　　 into @tb_name
　　　　　　 　end


insert into TB_DBA_Database_Table_Size
select convert(varchar(20),getdate(),120) as date_time,*
from @tb_size

close cur_tb_list
deallocate cur_tb_list

 

　其实这最近写的几篇文档记跟标题的性能都有点不太占边，具体的性能分析，用Sql Server 的Profiler 将trace 信息保存到表，做具体的分析，应该会很详细的。由于数据库的增长的很快，这几天再做数据库的监控工作，主要是数据库和表的大小。

　　记录当天的数据库、表大小信息，并与 昨天的数据库 和 表做比较。具体Sql code 与下：

　　------获取数据库大小-----

/* 注意 做database 单位换算的时候1024. 的小数点不能掉，如果没有小数点，换算后以结果小点后面的值可能会被舍弃*/

Create procedure [dbo].[Proc_DBA_Database_Size]
insert into TB_DBA_Database_Size
select --　 fileid,
　 @l_datetime as Date_Time,
　 'InsiteProdDG' [Database Name] ,
　 cast(sum(size)*8/1024.　　 as　　 decimal(10,2))　　 [DB Size],　　
　 'MB' Size_Type
　-- name　　 logic filename,　　
　-- filename　　 physics filename　
　 from　　 Srv_MES.InsiteProdDG.dbo.sysfiles

　　------获取数据库表记录条数-----

/* Author: wei_zhu at 2009-08-27
　　 Description: Proc_DBA_MES_Table_Count
　　 Log: ----
*/
ALTER procedure [dbo].[Proc_DBA_MES_Table_Count]
as
declare @l_date varchar(20)
set @l_date=convert(varchar(20),getdate(),120)

begin
　　　 insert into tb_dba_mes_table_count
select @l_date as Date_Time,
　　　 　　 b.name as Table_Name,
　　　 　　 max(a.rows) as Row_Count
from Srv_MES.InsiteProdDG.dbo.sysindexes a,
　　　　 Srv_MES.InsiteProdDG.dbo.sysobjects b
where a.id = b.id
group by b.name
order by 3 desc

end

编缉推荐阅读以下文章

• Sql Server 性能分析4 –数据库大小，数据库表大小综合性分析报表输出
• Sql Server 性能分析2 –查看数据库的大小以及数据库表的大小(Linked Server)
• Sql Server 性能分析1 –查看数据库的相关信息

　　------获取数据库表大小----

/* 这里的数据库表是接合上面的Store Procedure 获取的表来查询的 */

Create procedure [dbo].[Proc_DBA_MES_Table_Size]
as
Declare @tb_name varchar(50)
Declare @tb_list table
(
　　　 tb_name varchar(50)
)

declare @tb_size table
(
　　　 name　　 　　 varchar(50),
　　　 rows　　 　　 varchar(50),
　　　 Reserved　　 varchar(50),
　　　 Data　　 　　 varchar(50),
　　　 Index_Size varchar(50),
　　　 Unused　 　　 varchar(50)
)

declare cur_tb_list cursor for
　　　 select Table_Name
　　　 from TB_DBA_MES_Table_Count
　　　 where convert(varchar(10),date_time,120) =convert(varchar(10),DATEADD(day,-1,getdate()),120)

open cur_tb_list
　　　 　fetch Next from cur_tb_list
　　　　　　　　　　 into @tb_name
　　　
　　　 　while @@fetch_status=0
　　　 　　　　 begin
　　　　　　　　　　 insert into @tb_size exec Srv_MES.InsiteProdDG.dbo.sp_spaceused @tb_name
　　　
　　　　　　 fetch Next from cur_tb_list
　　　　　　　　　　　　　 into @tb_name
　　　　　　 　end


insert into TB_DBA_Database_Table_Size
select convert(varchar(20),getdate(),120) as date_time,*
from @tb_size

close cur_tb_list
deallocate cur_tb_list

　　------获取数据库增长大小----

编缉推荐阅读以下文章

• Sql Server 性能分析4 –数据库大小，数据库表大小综合性分析报表输出
• Sql Server 性能分析2 –查看数据库的大小以及数据库表的大小(Linked Server)
• Sql Server 性能分析1 –查看数据库的相关信息

/* 当前的数据库大小与昨天的数据库大小做比较得来*/

Create Procedure [dbo].[Proc_DBA_Database_Size_Grow]
as
select date_time,
　　　　　　 database_name,
　　　　　　 db_size,
　　　　　　 size_type
from TB_DBA_Database_Size
where database_name = 'InsiteProdDG'
and convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
union all
(select date_time,
　　　　　　 database_name,
　　　　　　 db_size*-1,
　　　　　　 size_type
from TB_DBA_Database_Size
where database_name = 'InsiteProdDG'
and convert(varchar(10),date_time,120)=convert(varchar(10),dateadd(day,-1,getdate()),120)
)


　　------获取数据库表增长大小----

/* 当前的数据库表大小与昨天的数据库表大小做比较得来*/

Create　 Procedure [dbo].[Proc_DBA_MES_Table_Grow]
as
select convert(varchar(20),getdate(),120) as date_time,
　　　 　　 name,
　　　 　　 sum(rows) as rows,
　　　 　　 sum(reserved) as reserved,
　　　 　　 sum(data) as data,
　　　 　　 sum(index_size) as index_size,
　　　 　　 sum(unused) as unused,
　　　 　　 'KB' as Size_Type　
from
(select * from
(select top 10　
　　　　　　 name,
　　　 　　 cast(rows as int) as rows,　　　　　　
　　　 　　 cast(left(reserved,len(reserved)-2) as int) as reserved,
　　　 　　 cast(left(data,len(data)-2)as int) as data,
　　　 　　 cast(left(index_size,len(index_size)-2) as int)　 as index_size,
　　　 　　 cast(left(unused,len(unused)-2) as int)　 as unused
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
order by cast(rows as int) desc) t
union
select
　　　　　　 name,
　　　 　　 rows*-1 as rows,
　　　 　　 left(reserved,len(reserved)-2) *-1 as reserved,
　　　 　　 left(data,len(data)-2) *-1 as data,
　　　 　　 left(index_size,len(index_size)-2) *-1 as index_size,
　　　 　　 left(unused,len(unused)-2) *-1 as unused
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),dateadd(day,-1,getdate()),120)
and name in(
select top 10 name
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
order by cast(rows as int) desc)
) x
group by name
order by 3 desc

一：MS SQL Report Server 报表的制作。

　　1.打开Microsoft Visual Studio 2005.

　　2. 创建Report Server Project (也可以选择Report Server Project Wizard 向导来创建),命名为DBA_Report_Project

　　3.添加数据源

　　4.添加报表

　　5　添加数据集，这里可以添加多个数据集，生成数据集的方式有两种，1. Sql 语句 ；2.. Store Procedure, 这里的数据集的Sql 和Store Procedure 用的是上一篇文档的。

　　6. 向报表中放置数据，可以用Table 和 Matrix 来定义输入出的格式

　　7. 预览

　　二：MS SQL Report Server 报表的上传及相关设定。

　　1. 设置Reporting Services Configuration

　　2.设置E-Mail （后面我们用Report Server设置 Job 发送Report 会用到它里的E-mail

　　3. 通过Home -> Contents ->Upload File 将MES_Database_Analysis.rdl 的报表文件上传至SQL Service Serporting Services.

　　4.要让Reporting Server 能够顺利的发出邮件，需要注意下图的Data Sources 中的保存可信认的凭证的用户名和密码的设置。如果没有选择设置成保存信人凭证，会出现可以正常查看报表，但却不能用job 让系统自动发出报表邮件给相关的人。正确的认置如下

　　5. Home -> 选种你的Report-> Subscriptions->New Subscription ，通过订阅来设置Job 发报表邮件

　　6.发送邮件的相关设置

　　7.选择上图的Select Schedule 来设置报表的发送时间

　　三．完成上面的设置后，我们就可以自动的收到每天的数据库的增长以及表的增长情况了。

 

 

http://doc.chinaunix.net/sqlserver/200909/209403.shtml