1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:    
select id from t where num is null    
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:    
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:    
select id from t where num=10 or num=20    
可以这样查询:    
select id from t where num=10    
union all    
select id from t where num=20
5.in 和 not in 也要慎用,否则会导致全表扫描,如:    
select id from t where num in(1,2,3)    
对于连续的数值,能用 between 就不要用 in 了:    
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描:    
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
select id from t where num/2=100    
应改为:    
select id from t where num=100*2    

8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
select id from t where substring(name,1,3)='abc'--name以abc开头的id    
应改为:    
select id from t where name like 'abc%'    

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。    

10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,    
否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。    

11.不要写一些没有意义的查询,如需要生成一个空表结构:    
select col1,col2 into #t from t where 1=0    
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:    
create table #t(...)    

12.很多时候用 exists 代替 in 是一个好的选择:    
select num from a where num in(select num from b)    
用下面的语句替换:    
select num from a where exists(select 1 from b where num=a.num)    

13.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,    
如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。    

14.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,    
因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。    
一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。    

15.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。    
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。    

16.尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,    
其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。    

17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。    

18.避免频繁创建和删除临时表,以减少系统表资源的消耗。

19.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。    

20.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,    
以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

21.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。    

22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。    

23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

24.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
25.尽量避免大事务操作,提高系统并发能力。
26.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。 
 1 /*学习事物基本语法*/
 2 
 3 /*增加课室名的唯一索引*/
 4 ALTER table class add constraint uni_ClassName unique(name)
 5 
 6 /*创建存储过程,其中增加教师,并增加课室*/
 7 CREATE proc pro_AddClass
 8 @className varchar(50),
 9 @teacherName varchar(50)
10 as
11 begin
12 declare @teacherid int
13 select @teacherid=id from teacher a where a.name=@teachername
14 
15 begin tran
16 IF(@teacherid=0 or @teacherid is null)
17 begin
18 insert into teacher(name) VALUES(@teachername)
19 select @teacherid=IDENT_CURRENT('teacher')
20 end
21 
22 insert into class(teacherid,name) VALUES(@teacherid,@classname)
23 
24 IF @@error<>0
25 begin
26 rollback tran
27 end
28 else
29 commit tran
30 end
31 
32 
33 /*执行存储过程,查看结果*/
34 exec pro_AddClass 'Soft','Teacher Li'

SQL Server日常维护常用的一些脚本整理。

 

 

1.sql server开启clr权限:

图片 1

exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;

 

2.查询数据库大小

1 exec pro_AddClass 'Math','Teacher Ann'
Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles

 

3.数据库日志压缩

图片 2

--选择需要使用的数据库
USE PIMS
--将数据库模式设置为SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- 将日志文件收缩到1M 
DBCC SHRINKFILE ('PIMS_log', 1)
-- 还原数据库
ALTER DATABASE PIMS SET RECOVERY FULL

 

4.查看数据库连接用户

 

Select * From sys.dm_exec_connections

主要还是记录自己学习的过程,这些基础的知识相信园内已经有很多分享,参考了各位的资料,再作为自学的记录而已,如有差错,请指正

5.查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

6.查看缓存中重用次数少,占用内存大的查询语句(当前缓存中未释放的)–全局

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc

7.看BUFFER POOL中,都缓存了哪些表(当前数据库)的数据

select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb                              
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
       and b.container_id=c.hobt_id             
       and database_id=DB_ID()                              
group by OBJECT_NAME(object_id)                           
order by 2 desc  

8.查询SQLSERVER内存使用情况

select * from sys.dm_os_process_memory

9.查询SqlServer总体的内存使用情况

select      type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的内存  
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的内存  
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存  
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留内存  
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交内存  
from    sys.dm_os_memory_clerks
group by type
order by type

10.查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量

-- 查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量
-- 从这些信息可以看出,系统经常要访问的都是哪些表,有多大?
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc 

11.查询缓存的各类执行计划,及分别占了多少内存

-- 查询缓存的各类执行计划,及分别占了多少内存
-- 可以对比动态查询与参数化SQL(预定义语句)的缓存量
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

12.查询缓存中具体的执行计划,及对应的SQL

-- 查询缓存中具体的执行计划,及对应的SQL
-- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑
-- 查询结果会很大,注意将结果集输出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO

13.查询sql server内存整体使用情况

--查询sql server内存整体使用情况
  SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  FROM sys.dm_os_performance_counters t
  WHERE counter_name = 'Total Server Memory (KB)';

发表评论

电子邮件地址不会被公开。 必填项已用*标注