DECLARE @ThisWeekStartTime NVARCHAR(100),@ThisWeekEndTime NVARCHAR(100),--本周
            @LastWeekStartTime NVARCHAR(100),@LastWeekEndTime NVARCHAR(100),--上周
            @ThisMonthStartTime NVARCHAR(100),@ThisMonthEndTime  NVARCHAR(100),--本月
            @LastMonthSartTime NVARCHAR(100),@LastMonthEndTime NVARCHAR(100),--上月
            @LastestHalfYearStartTime NVARCHAR(100),@LastestHalfYearEndTime NVARCHAR(100),--近半年
            @LastestOneYearStartTime NVARCHAR(100),@LastestOneYearEndTime NVARCHAR(100)--近一年

    SELECT @ThisWeekStartTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 0),121)--本周开始时间
    SELECT @ThisWeekEndTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 6),121)--本周结束时间

    SELECT @LastWeekStartTime=  CONVERT(nvarchar(10),DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -7, getdate()) ), 0),121)--上周开始时间
    SELECT @LastWeekEndTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -7, getdate()) ), 6),121)--上周结束时间

    SELECT @ThisMonthStartTime=CONVERT(nvarchar(10),dateadd(dd,-day(getdate())+1,getdate()),121)--本月开始时间
    SELECT @ThisMonthEndTime=CONVERT(nvarchar(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),121)--本月结束时间

    SELECT @LastMonthSartTime=CONVERT(nvarchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),121)--上月开始时间
    SELECT @LastMonthEndTime= CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121) --上月结束时间

    SELECT @LastestHalfYearStartTime= CONVERT(nvarchar(10),  dateadd(dd,-day(dateadd(month,-6,getdate()))+1,dateadd(month,-6,getdate())) ,121)--近半年开始时间
    SELECT @LastestHalfYearEndTime=CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--近半年结束时间

    SELECT @LastestOneYearStartTime= CONVERT(nvarchar(10), dateadd(dd,-day(dateadd(month,-12,getdate()))+1,dateadd(month,-12,getdate())) ,121)--近一年开始时间
    SELECT @LastestOneYearEndTime=CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--近一年结束时间

当前时间 select getdate()
当前时间周的起始日期(以周一为例)select DATEADD(week,DATEDIFF(week,0,getdate()),0)
上周起始:select dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上周起始:select dateadd(week,-2,DATEADD(week,DATEDIFF(week,0,getdate()),0))
上上上周起始:select dateadd(week,-3,DATEADD(week,DATEDIFF(week,0,getdate()),0))

先讲下事务执行流程:

目前本人在看《SQL Server性能调优实战》 ,以下内容是本人看书笔记

 

 BEGIN和COMMIT

数据库性能取决于各方面综合因素:
  硬件,操作系统,软件
硬件:内存,CPU,磁盘
  当服务器的物理内存不足时,会产生大量的磁盘I/O,给磁盘带来压力;
  当内存不足时,一些占用CPU资源较多的对象可能就无法被正常缓存在内存中,需要使用大量的CUP资源来处理这些对象的计算,从而给CPU带来更大的压力
  内存:
    1.执行计划缓存
      数据库引擎接收到需要执行的语句时,首先会经过一系列复杂的计算和分析,得到相应的执行计划,然后再根据执行计划进行各种操作,
      由于执行计划的计算和分析需要的CPU资源比较多,所以很容易引起CPU资源的紧张,为了解决这个问题,数据库就会把执行计划缓存起来
    2.数据缓存
      若遇到数据访问操作符,则会首先检查对应的数据是否在数据缓存中,如果存在就从缓存中取数据,否则会从磁盘中读取数据,然后再把数据缓存到缓存中
      如果数据库内存不足,数据库引擎会算法把不常用的缓存清理,把需要的数据从磁盘中读取并缓存到数据缓存中,这会引起大量的磁盘I/O,并且导致执行语句的执行效率低下
      若大批的语句出现这种情况,就会导致服务器CPU占用率飙高。如果服务器CPU长期处于繁忙状态,并且数据库的磁盘I/O偏高,估计是数据库的内存达到了瓶颈
  CPU:
    数据库在进行任务调度,执行计划分析,排序等计算时都需要使用大量的CPU资源
      CPU资源在30%上下时:当前服务器较空闲
      CPU资源在60%上下时:当前服务器较繁忙
      CPU资源达到80%时:服务器非常繁忙,就要查找原因了(通常情况下,一些执行效率较高,并且性能不理想的语句会造成这样的问题;少数情况下是由于数据库服务器达到了瓶颈)
  针对高并发的数据库系统,建议使用如下配置方案:
    将主数据库的数据文件拆分成多个文件
    将数据文件和日志文件存放在不同的物理磁盘,从而提高I/O的并发。
    系统数据库文件,特别是Tempdb的数据文件要放在独立的物理磁盘,并将数据文件拆分成多个,建议与逻辑CPU的个数相同,以提高并发

PRINT @@TRANCOUNT --@@TRANCOUNT统计事务数量

BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT COMMIT TRAN --只提交一个事务 PRINT @@TRANCOUNT COMMIT TRAN PRINT @@TRANCOUNT

设计表建议:
  尽可能的添加数据完整约束,例如:非空约束,默认值约束,check约束,唯一约束,外键约束等,这些约束的添加降有助于数据库关系引擎分析执行计划
  使用尽可能小的字段类型,特别是大表,尽量小的空间将可以带来更佳的性能
  表结构的设计应考虑业务需求带来的操作及频率,尽可能的使业务逻辑实现简洁,使用简单的SQL语句,可避免过多的表关联
编写SQL语句建议:
  编写语句前,先明确已经完全理解了业务需求,并知道表的用途及用法
  确定业务需要用到的过滤字段能否使用索引,是否有必要在字段上添加索引
  不要多有索引的字段进行任何的计算,包括函数,因为这会导致无法使用索引进行数据检索,从而导致扫描操作
  小表操作优先,以小表驱动大表,使其尽量使用NESTED
LOOP-嵌套循环(NESTED
LOOP是表关联操作的一种物理操作方式,它使用foreach的方式以较小数据量的数据集为驱动,内嵌foreach循环较大的表进行对比,其效率比其他几个关联操作高,)
  只查询需要的字段,避免*
  尽量使用简单的SQL语句来实现业务功能,如果功能过于复杂,可以考虑将其拆分成若干个简单的SQL语句
简单的SQL:
  关联的表最多不超过4个
  没有复杂的过滤条件,只有2到3个过滤条件,可以使用索引查找操作

结果:图片 1

 

ROLLBACK

查询设计原则:
  1.不要在过滤字段上使用任何的计算,包括:函数,逻辑计算,普通的计算等,因为这些计算公式的加入,将造成查询优化器无法使用相应字段的索引
  2.尽量使用有索引的字段进行排序,特别是排序的数据量比较大时,这可以很大程度上降低排序操作带来的成本开销
  3.填写查询表时,尽量使用Join关键字连接表,这样的语句清晰,易于阅读,不易缺失关联条件

PRINT @@TRANCOUNT
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
ROLLBACK TRAN --回滚所有事务
PRINT @@TRANCOUNT

排序优化:
  在加入排序计算以后,尽量控制排序的数据量,尽量使排序操作能在额定的内存空间中完成,避免使用Tempdb。数据量较大时,可以考虑在排序字段上添加索引来避免执行排序操作。
  查询大量的数据会造成CUP资源消耗,并且,在查询数据超过可以分配的内存大小时,会把查询的中间数据存放在Tempdb数据库中,这将增加I/O操作,导致语句的性能大大下降
  在进行排序操作时,如果工作区内存不足,就需要使用Tempdb数据库来完成数据的排序,此时排序操作产品的部分中间数据将被写入Tempdb中,
  由于有了磁盘I/O操作的开销,排序操作将受到影响,这时不在由单纯的内存操作那样快速了,而且当Tempdb比较繁忙时,若并发量达到一定量级,也会对排序操作造成影响

结果:图片 2

 

  由上可知BEGIN TRAN
开始事务,使事务数量加一

分组优化:
  Group by
和distinct需要进行哈希或排序计算。与排序相同,哈希计算需要使用大量的内存空间,当工作区内存不足时,会将一部分中间数据存放到Tempdb中。
  因此,使用哈希计算时同样需要注意数据量的控制。当然,如果有必要,可以在Group
by的字段上建立索引,以避免哈希计算

      COMMIT TRAN
使事务减一,提交最新开辟的事务

 

      ROLLBACK
使事务数量直接减为0,也就是回滚

更新优化:
  由于update语句对数据加的是更新锁或排他锁,更容易并发阻塞,因此,我们应尽量保证更新语句高效,以减少阻塞的影响
  在默认事务级别中,update语句进行查询时会添加更新锁,若找到了需要更新的数据,就会将更新锁转换为排他锁,由于更新锁和排他锁的兼容性弱,
  在需要较长执行时间的更新语句或事务中,容易造成阻塞。为了避免造成大量阻塞,应尽量保证更新语句的效率,适当建立索引。
  长时间的更新维护通常会造成表被锁,从而影响业务功能,无法正常运作,不得不停机操作,解决办法:预先计算好结果,然后分批更新,这样就可避免在更新物理表时再进行计算,从而影响更新时间

事务分类:

过滤条件:
  是否有合适索引可供使用
  字段上是否有函数计算
  返回的结果集是否过大
  是否紧查询需要的字段

  1.显示事务

在语句存在性能瓶颈下,如果有扫描操作或者是标签查找操作,都会被认为是有问题的,问题产生的原因是索引缺少,或者索引没有真正的被覆盖到语句中。

  2.隐式事务模式

查询设计原则
  使查询结果尽可能小.例如:Top ,分页
  返回大量的查询结果可能意味着需求存在问题
  尽量避免表扫描和索引扫描
  充分使用索引
  扫描并不总是有害的

  3.自动提交事务

尽量使用有索引的字段进行排序,特别是排序的数据量比较大时,这可以很大程度上降低排序操作带来的成本消耗

 

 

发表评论

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