2009年1月16日

公司数据库服务器的空间越来越紧张、最大的数据库达到400个G,100G 以上的库就有四五个。当然我们应该感到欣慰，数据高速增长说明我们的业务发展较好，但不可否认，我们的应用设计也存在着某些问题。诸如：滥建索引、过度冗余或者是系统在设计时没有考虑对超过价值期的历史数据进行清理。

下面这个脚本用来获取数据库每张表/索引的空间使用情况。

Code


with  pa as 
(
SELECT p.object_id,p.index_id,a.type_desc as pagetype_desc,a.total_pages,a.used_pages,a.data_pages
FROM sys.partitions p JOIN sys.allocation_units a
   ON p.partition_id = a.container_id
),
indexes as
(
    select object_id,index_id,object_name(object_id) as tbname , name as indexname,type_desc as tbtype_desc
    from sys.indexes
    where object_id > =100
),
result as
(
select i.*,p.pagetype_desc,p.total_pages,p.used_pages,p.data_pages
from pa p inner join indexes i 
on p.object_id=i.object_id and p.index_id=i.index_id
)

select  * from result  order by total_pages desc

下面这个脚本用以统计索引的使用率

Code
declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

  2008年11月18日

同事在准备新老系统的切换，清空一个表的时候往往发现这个表的主键被另一个表用做外键，而系统里有太多层次的引用.所以清起来相当麻烦

用下面这个脚本可以做到找出一个特定表的引用树，比如 table2 有个外键引用到了table1  table3有个外键饮用到了table2  .......

Code
declare @tbname nvarchar(256);
set @tbname=N'dbo.aspnet_Applications';

with  fkids as 
(
    select  
    object_id(CONSTRAINT_NAME) as FkId,
    object_id(UNIQUE_CONSTRAINT_NAME) AS PkId
    from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
)
,realations as
(
    select p.parent_object_id as pktableId
    ,f.parent_object_id as fktableid
    ,i.pkid,i.fkid
    from 
    fkids i inner join sys.objects p on i.pkid=p.[object_id]
    inner join sys.objects f on i.fkid=f.[object_id]
 
)
,cte as
(
    select  * from realations where pktableid=object_id(@tbname) 
    union all 
    select r.* from cte c join realations r  on r.pktableid=c.fktableid
)

select 
object_name(pktableid) as pktable
,object_name(fktableid) as fktable
,object_name(pkid) as pk 
,object_name(fkid) as fk from cte  

  2008年11月17日

在sql server 中我们会通过邮件菜单来查看一个对象的依赖关系，但有些意外情况会造成查看结果并不是非常的准确！

可以尝试以下例子 ：

第一步：颠倒顺序，创建一个缺失依赖关系的存储过程

Code
CREATE PROC dbo.TestRefresh1
as 
EXEC dbo.TestRefresh2
EXEC dbo.TestRefresh3
EXEC dbo.TestRefresh4
EXEC dbo.TestRefresh5
EXEC dbo.TestRefresh6

GO  

CREATE PROC dbo.TestRefresh2
AS SELECT 1 
GO

CREATE PROC dbo.TestRefresh3
AS SELECT 1 
GO 

CREATE PROC dbo.TestRefresh4
AS SELECT 1 

第二步: 查看一下这个存储过程的依赖关系

Code
EXEC sp_depends @objname = N'dbo.TestRefresh1'

在结果中并没有发现dbo.TestRefresh1同其他对象有任何的依赖关系 ，细心的朋友早就发现这个问题是因为创建存储过程时的顺序问题所致。假设我们的数据库中并不缺失相关对象，可以尝试用如下代码来更新他们的依赖关系：

Code
declare @cmd as nvarchar(max);
declare c cursor fast_forward for 
select 'EXEC sys.sp_refreshsqlmodule  ' + ''''+schema_name([schema_id])+'.'+ [Name]+''''  as cms from  sys.objects where type='p'
open c
fetch next from c into @cmd ;
while @@fetch_status = 0
begin
  exec(@cmd);
  fetch next from c into @cmd;
end
close c;
deallocate c;

完成之后再次执行

Code
EXEC sp_depends @objname = N'dbo.TestRefresh1'

看看结果吧！

其实，针对这些特性，我们可以用上面这段脚本来检查生产数据库数据库存储过程的健康状况，发现缺失的依赖关系！虽然用处不大，毕竟是一个知识点。与大家分享

  2008年9月28日

在web开发过程中、我们往往会碰上这样的麻烦事：

我们很容易利用VSTF等工具来对代码进行版本控制、但是对数据库呢？我知道有些朋友会有很好的习惯：
1、每一次数据库更改的脚本，都会在第一时间保存在VSTF内，使用这种方法其实其实也足够方便，但习惯培养阶段确实让人不爽、而且不免遗漏。
2、甚至会在解决方案中创建一个数据库项目、每一次对都在VSTS中修改数据库。 参考：http://msdn.microsoft.com/zh-cn/library/aa833253.aspx。 这种方式应该是微软所推崇的。可用起来实在不怎么方便、最受不了的是在VSTS中写存储过程！

我们总监的口号是：把工具进行到底！他使用CruiseControl.NET进行每日构建、不管使用是上面何种方法、开发库同测试库之间的同步始终没有办法自动化同步，或许是没找到正确的方法吧!如果园子里的朋友有什么高招、不防在下面回复一下！

最后我们哪种方法都没有用，而是想到了DDL触发器、在一个叫DataBaseVersion 的库中自动记录每一次的DDL操作、然后写控制台程序读取DDL操作日

志在相应的测试库中回放。再把这个控制台配置到CruiseControl.NET。 这个时候数据库的每日构建便可以进行了。

下面附上用到的DDL触发器:

Code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_log]
ON DATABASE
FOR DDL_TABLE_EVENTS
    ,DDL_VIEW_EVENTS
    ,DDL_INDEX_EVENTS
    ,DDL_FUNCTION_EVENTS
    ,DDL_PROCEDURE_EVENTS
    ,DDL_ASSEMBLY_EVENTS
    ,DDL_SCHEMA_EVENTS
AS

DECLARE @EventData XML; 
DECLARE @EventType NVARCHAR(50) ;
DECLARE @PostTime  DATETIME ;
DECLARE @LoginName NVARCHAR(50) ;
DECLARE @UserName NVARCHAR(50) ;
DECLARE @DatabaseName NVARCHAR(50); 
DECLARE @SchemaName NVARCHAR(50);
DECLARE @ObjectName NVARCHAR(500); 
DECLARE @SetOptions NVARCHAR(max); 
DECLARE @CommandText NVARCHAR(max); 
DECLARE @IsDebug BIT ;
DECLARE @Synchronized BIT ;
DECLARE @NeedSynchronize BIT ;
DECLARE @ObjectDefinition NVARCHAR(MAX);

SET @EventData =EVENTDATA();
SET @EventType =@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)');
SET @PostTime=@EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
SET @LoginName=@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(50)');
SET @UserName=@EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(50)');
SET @DatabaseName=@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(50)');
SET @SchemaName=@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(50)');
SET @ObjectName=@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(500)'); 
SET @CommandText =@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(max)'); 

SET @IsDebug =0;
SET @Synchronized=0; 
SET @NeedSynchronize=1; 
SET @ObjectDefinition = OBJECT_DEFINITION (Object_ID(@DatabaseName+'.'+@SchemaName+'.'+@ObjectName)) ;


INSERT INTO [DataBaseVersion].[dbo].[DDLEventLog]
           ([EventData]
           ,[EventType]
           ,[PostTime]
           ,[LoginName]
           ,[UserName]
           ,[DatabaseName]
           ,[SchemaName]
           ,[ObjectName]
           ,[CommandText]
           ,[IsDebug]
           ,[Synchronized]
           ,[NeedSynchronize]
           ,[ObjectDefinition])
     VALUES
           (@EventData
           ,@EventType
           ,@PostTime
           ,@LoginName
           ,@UserName
           ,@DatabaseName
           ,@SchemaName
           ,@ObjectName
           ,@CommandText
           ,@IsDebug
           ,@Synchronized
           ,@NeedSynchronize
           ,@ObjectDefinition)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [tr_ddl_log] ON DATABASE