贝网博客

我的分类
流水记事
源码下载
Asp.net
其它
数据库
Javascript
.Net技术
我的相册
友情链接
博客园
CSDN博客
Start0
最新回复
fasdfasdf
[:..
lz这个东西好厉害,我..
哈哈,好照片
不错,以前一直用黄色..
终于找到支持ff的修正..
终于找到支持ff的修正..
新鲜性
看看,试试,好不好使。
好东西一起学习[:img0..
分类 =》数据库
Sql Server常用维护的sql
发表于:2012-06-07 09:38:03
更新于:2012-10-29 16:35:56

-- 获取表的行数,以及每个表占用的索引空间和数据空间
DECLARE @PageSize INT;
select @PageSize = v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
SELECT a.id,b.name,a.rowcnt AS [行数],
 ISNULL((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
     FROM sys.indexes as i
     JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
     JOIN sys.allocation_units as a ON a.container_id = p.partition_id
     where i.object_id = b.object_id),0.0)
 AS [索引空间(KB)],
 ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
     FROM sys.indexes as i
     JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
     JOIN sys.allocation_units as a ON a.container_id = p.partition_id
     where i.object_id = b.object_id),0.0)
 AS [数据空间(KB)]
FROM sys.tables AS b,sysindexes a
WHERE a.id = b.[object_id] AND a.indid <=1 ORDER BY b.[name]
 

-- 查看当前数据库日志文件大小和使用率,size以8 KB 为单位,所以除128就是MB
SELECT name, data_space_id 文件组id, size/128 [文件大小(兆)],
    FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],
    size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],
    FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],
    max_size/128 [最大值(兆)],
    case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end 增长值,
    physical_name 物理路径
FROM sys.database_files a  ORDER BY a.[name]
注:Sql2005建议使用sys.database_files,sysfiles是为了兼容Sql2000才有的
前者表定义参考:http://msdn.microsoft.com/zh-cn/library/ms174397.aspx
后者兼容表定义参考:http://msdn.microsoft.com/zh-cn/library/ms178009.aspx

--查看数据库信息,如日志模式:
SELECT NAME, database_id,create_date 创建时间,is_auto_shrink_on 自动收缩, state_desc 状态,recovery_model_desc 恢复模式,is_published 是否发布库,log_reuse_wait_desc 日志重用状态 FROM sys.databases

查看日志文件使用率:
DBCC SQLPERF(LOGSPACE)

如果镜像的主体数据库日志过大,可以在主体数据库上定时作日志备份(比如1小时1次)
备份日志可以截断日志,让日志空间可以循环使用,这样可以使日志文件的大小不再增长。
BACKUP LOG newresourcedb TO DISK = 'e:\log20111122.bak'--备份完整日志
BACKUP DATABASE newresourcedb TO DISK = 'e:\db20111122.bak' with INIT--备份完整数据库

--修改数据库日志模式:Simple,Full,BULK_LOGGED
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE
--设置自动收缩
ALTER DATABASE [数据库名] SET AUTO_SHRINK ON WITH NO_WAIT

-- 查看当前数据库总大小
SELECT sum(size)/128 [文件(兆)] FROM sys.database_files

-- 查看所有数据库大小
if exists (select 1 from sys.tables where name='tb1')
 drop table tb1;
SELECT *, GETDATE() dt, '                                           ' tbname into tb1 FROM sys.database_files where 1=2
EXECUTE sp_msforeachdb 'INSERT INTO tb1 SELECT *, GETDATE(),''?'' FROM [?].sys.database_files'
select tbname, sum(size)/128 [大小(M)] from tb1 group by tbname order by [大小(M)] desc
--sp_msforeachdb是循环所有数据库,把数据库名替换掉脚本里的?

查看sql执行计划命中率
SELECT count(1) FROM sys.dm_exec_cached_plans?
SELECT count(1) FROM sys.dm_exec_cached_plans? WHERE usecounts<=2
SELECT TOP 1000 stats.execution_count AS exec_count,? p.size_in_bytes as [size],? [sql].[text] as [plan_text]?
FROM sys.dm_exec_cached_plans p? outer apply sys.dm_exec_sql_text (p.plan_handle) sql?
join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle?
ORDER BY usecounts

--获取前100个最占cpu的执行
select top 100
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle, [sql].[text] as [plan_text]
from sys.dm_exec_query_stats qs
 outer apply sys.dm_exec_sql_text (qs.plan_handle) sql
group by qs.plan_handle,[sql].[text]
order by total_cpu_time desc

-- 清空上面的执行计划缓存列表
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE

--查找表的所有索引
SELECT a.name, b.name
FROM   sys.tables a, sys.indexes b
WHERE  a.[object_id] = b.[object_id]
ORDER BY a.name, b.name

-- 获取Sqlserver的查询计划
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SQL)
SELECT c.value('@StatementId', 'INT') AS [no],
       c.value('(./@StatementText)', 'NVARCHAR(MAX)') AS [Statement],
       qplan.query_plan AS queryPlan
FROM   (
           SELECT DISTINCT plan_handle
           FROM   sys.dm_exec_query_stats
       ) AS qstats
       CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan
CROSS APPLY query_plan.nodes(
    '/SQL:ShowPlanXML/SQL:BatchSequence/SQL:Batch/SQL:Statements/descendant::*[attribute::StatementText]'
) AS t(c)
ORDER BY plan_handle, [no]

 

发表评论
名称(*):
邮箱:
正文:

©2008 Beinet.cn 版权所有