• SQL优化技巧


    1.查看执行时间和cpu占用时间

    set statistics time on

    select * from dbo.Product

    set statistics time off

    打开你查询之后的消息里面就能看到啦。

    wpscb7e.tmp

    2.查看查询对I/0的操作情况

    set statistics io on

    select * from dbo.Product

    set statistics io off

    执行之后

    wpscb7f.tmp

    扫描计数:索引或表扫描次数

    逻辑读取:数据缓存中读取的页数

    物理读取:从磁盘中读取的页数

    预读:查询过程中,从磁盘放入缓存的页数

    lob逻辑读取:从数据缓存中读取,image,text,ntext或大型数据的页数

    lob物理读取:从磁盘中读取,image,text,ntext或大型数据的页数

    lob预读:查询过程中,从磁盘放入缓存的image,text,ntext或大型数据的页数

    如果物理读取次数和预读次说比较多,可以使用索引进行优化。

    如果你不想使用sql语句命令来查看这些内容,方法也是有的,哥教你更简单的。

    查询--->>查询选项--->>高级

    wpscb9f.tmp

    被红圈套上的2个选上,去掉sql语句中的set statistics io/time on/off 试试效果。哦也,你成功啦。。

    3.查看执行计划

    选中查询语句,点击

    wpscba0.tmp

    然后看消息里面,会出现下面的图例

    wpscba1.tmp

    首先我这个例子的语句太过简单,你整个复杂的,包涵啊。

    分析:鼠标放在图标上会显示此步骤执行的详细内容,每个表下面都显示一个开销百分比,分析站百分比多的的一块,可以根据重新设计数据结构,或这重写sql语句,来对此进行优化。如果存在扫描表,或者扫描聚集索引,这表示在当前查询中你的索引是不合适的,是没有起到作用的,那么你就要修改完善优化你的索引,具体怎么做,你可以根据我上一篇文章中的sql优化利器--数据库引擎优化顾问对索引进行分析优化。

    方法2.

    SET SHOWPLAN_ALL ON;

    clipboard

     

    4.sql server的优化建议

    Microsoft SQL Server 2008 >> 工具  >>  SQL Server Profiler。

    然后选择文件  >>  新建  >>  跟踪打开一个连接窗口,选择将要跟踪的服务器实例然后连接。打开如下“跟踪属性”对话框。

    1bf0ad4a6cf

     如果有许多跟踪,可以提供一个跟踪名称来帮助在以后进行分类。不同的跟踪模板可帮助建立用于不同目的的跟踪。

     打开跟踪属性窗口后,单击“事件选择”选项卡,为跟踪提供更详细的定义。

    8ab16232f64

    6a43bc1c2d6

    4、使用多个文件

    在大多数情况下,小型的数据库并不需要创建多个文件来存放数据。但是随着数据的增长,单个文件的弊端就会出现。

        - 首先,使用多个文件分布到不同的磁盘分区(多个硬盘)能够几大提高IO性能。

        - 其次,多个文件对于数据比较多的数据库来说,备份和恢复都会方便。

        - 但是,多文件需要占用更多的磁盘空间,因为每个文件中都有自己的一套B树组织方式和自己的增长空间。当然也有自己的碎片。

        - 总体来说,多个文件带来的优点是远远大于弊端的。

    5. 维护语句

    --日志收缩为1M

    USE [数据库名];

    ALTER DATABASE [数据库名] SET RECOVERY SIMPLE;

    DBCC SHRINKFILE ([数据库名_log], 1);

    ALTER DATABASE [数据库名] SET RECOVERY FULL;

    6. 维护计划

    点击工具栏,拖动到空白区域,如果有上一个任务,可以拖动箭头指向新添加的任务

    clipboard

    6.1. 执行T-SQL语句

    USE [Sogal.YHZS];

    -- 压缩日志到1MB

    ALTER DATABASE [Sogal.YHZS] SET RECOVERY SIMPLE;

    DBCC SHRINKFILE ([Sogal.YHZS_log], 1);

    ALTER DATABASE [Sogal.YHZS] SET RECOVERY FULL;

    -- 清除错误日志

    EXEC sp_cycle_errorlog;

    6.2.

    备份数据库

    clipboard

    “设置备份压缩” >> “压缩备份”,会比普通备份再RAR的压缩率低一些,不过可以自动进行,测试过普通压缩16g使用后未2G

    网上资料如下可参考

    clipboard

    6.3.

    清除历史记录

    clipboard

    PS:

    clipboard

    需要开启Sql sever Agent(sql server代理)的服务,并设置为开机自动启动

    问题2:在 sys.database_files 中找不到数据库 'XXX' 的文件 '[XXX_3_log]'。该文件不存在或者已被删除。

    clipboard

    分析:一定是从某个原始库backup然后restore过来的.这种情况下XXX的日志的逻辑文件名不一定叫'XXX_3_log',不信,你可以

    方法1:select type_desc,name from sys.master_files WHERE database_id=db_id('XXX');

    方法2:use XXX

    select * from sys.database_files

    可以看'name'列的输出,然后收缩那个名称里的日志文件就可以了.

    一般restore后,如果库名和以前不一样

    解决方法:

    方法1:改为正确的日志逻辑名称

    方法2:最好改下数据库和日志的2个逻辑文件名,保持后新明称一致.

    alter database xhtest modify file(name=xhtest_old, newname=xhtest) 

    alter database xhtest modify file(name=xhtest_old_log, newname=xhtest_log) 

    6. Log文件夹中的文件过多

    C:Program Files (x86)Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQL

    clipboard

    7. 死锁

    -- 查找死锁的进程

    select   

        request_session_id spid,  

        OBJECT_NAME(resource_associated_entity_id) tableName   

    from   

        sys.dm_tran_locks  

    where   

        resource_type='OBJECT'

    -- 干掉死锁的进程

    kill 53

    -- 查询该会话正在执行的Sql语句

    DBCC INPUTBUFFER (53)

    -- SQL Server Profiler查看死锁

     

    Microsoft SQL Server 2008 >> 工具  >>  SQL Server Profiler

    >> 文件 >> 模板 >> 导入模板 >> 点击开始监控

    Ctrl + F 查找“deadlock”找到死锁记录

    clipboard

    8. 查询耗时较长的语句

    SELECT  creation_time  N'语句编译时间'

            ,last_execution_time  N'上次执行时间'

            ,total_physical_reads N'物理读取总次数'

            ,total_logical_reads/execution_count N'每次逻辑读次数'

            ,total_logical_reads  N'逻辑读取总次数'

            ,total_logical_writes N'逻辑写入总次数'

            , execution_count  N'执行次数'

            , total_worker_time/1000 N'所用的CPU总时间ms'

            , total_elapsed_time/1000  N'总花费时间ms'

            , (total_elapsed_time / execution_count)/1000  N'平均时间ms'

            ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

             ((CASE statement_end_offset

              WHEN -1 THEN DATALENGTH(st.text)

              ELSE qs.statement_end_offset END

                - qs.statement_start_offset)/2) + 1) N'执行语句'

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

             ((CASE statement_end_offset

              WHEN -1 THEN DATALENGTH(st.text)

              ELSE qs.statement_end_offset END

                - qs.statement_start_offset)/2) + 1) not like '%fetch%'

    AND last_execution_time <'20170519 17:35'

    ORDER BY  total_elapsed_time / execution_count DESC;

  • 相关阅读:
    动手动脑及类的创建
    动手动脑-Java重载
    方法的动手动脑
    文本单词数测试
    动手动脑
    springmvc使用fastjson
    springmvc之发送json数据自动转Java对象接收
    springmvc之拦截器
    基于xml配置springmvc案例
    springmvc之异常处理
  • 原文地址:https://www.cnblogs.com/lydg/p/11928733.html
Copyright © 2020-2023  润新知