niuniu的专栏

.net技术

项目中常用的SQL语句 - 建SNAPSHOT,性能监视等

 ----创建snapshot从snapshot恢复 

Declare @INT_DB_NAME varchar(30),@INT_DB_NAME_SNAPSHOT varchar(40),@SQL varchar(1000)
set @INT_DB_NAME = 'test'
set @INT_DB_NAME_SNAPSHOT = @INT_DB_NAME+'_SNAPSHOT'
IF EXISTS (SELECT name FROM sys.databases WHERE name = @INT_DB_NAME_SNAPSHOT) exec ('DROP DATABASE '+@INT_DB_NAME_SNAPSHOT)
set @SQL = 'create database '+@INT_DB_NAME_SNAPSHOT+' ON'+char(13)
select @SQL=@SQL+'(Name = '+DB_FILE.name+',FileName = "'+REPLACE(REPLACE(upper(DB_FILE.physical_name),'.MDF','_snapshot.mdf'),'.NDF','_snapshot.ndf')+ '")'+char(13)+','from master.sys.databases DB join master.sys.master_files DB_FILE on DB. database_id= DB_FILE. database_id where DB.name = @INT_DB_NAME and upper(DB_FILE.type_desc)<>'LOG'
set @SQL = left(@SQL,len(@SQL)-1)+'AS SNAPSHOT OF '+@INT_DB_NAME
exec (@SQL)

 

use master
Declare @INT_DB_NAME varchar(30),@INT_DB_NAME_SNAPSHOT varchar(40),@SQL varchar(1000)
set @INT_DB_NAME = 'test'
set @INT_DB_NAME_SNAPSHOT = @INT_DB_NAME+'_SNAPSHOT'
set @SQL='ALTER DATABASE '+@INT_DB_NAME+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
exec (@SQL)
RESTORE DATABASE @INT_DB_NAME from DATABASE_SNAPSHOT = @INT_DB_NAME_SNAPSHOT
set @SQL='ALTER DATABASE '+@INT_DB_NAME+' SET MULTI_USER'
exec (@SQL)


 

 

==========================================================

Performance monitoring

 

显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
–关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF
–显示[返回有关语句执行情况的详细信息,并估计语句对资源的需求]
SET SHOWPLAN_ALL  ON 
–关闭[返回有关语句执行情况的详细信息,并估计语句对资源的需求]
SET SHOWPLAN_ALL  OFF

 

sql server性能分析--执行sql次数和逻辑次数
  
目前在做一个项目优化时,想通过数据库层分析sql server系统性能,查了一下网上代码,修改了一下标题和DMVs代码,以下代码可以用来分析系统运行一段时间后,那些语句是系统忙的sql语句。做为参考。

   另类使用:

     一次在分析一个对账功能时,查看系统代码,看了半天,写得太不规范,又不写注释,看不明白。最后用了下面一个小技巧,和大家一起分享:

 就是在测量功能时,先以下命令清除sql server的缓存:

dbcc freeProcCache

在点击某个按钮,执行完后,在执行下面语句,就可以知道系统运行什么sql和多少次,其主要慢的语句是那些了。   

 

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%'
ORDER BY  total_elapsed_time / execution_count DESC;


 
use mydb
select dm_tran_locks.request_session_id, 
            dm_tran_locks.resource_database_id,
            db_name(dm_tran_locks.resource_database_id) as dbname,
            CASE 
                  WHEN resource_type = 'object'
                        THEN object_name(dm_tran_locks.resource_associated_entity_id)
                  ELSE object_name(partitions.object_id)
            END as ObjectName,
            partitions.index_id,
            indexes.name as index_name,
            dm_tran_locks.resource_type, 
            dm_tran_locks.resource_description, 
            dm_tran_locks.resource_associated_entity_id, 
            dm_tran_locks.request_mode, 
            dm_tran_locks.request_status
from sys.dm_tran_locks
left join sys.partitions on partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
join sys.indexes on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id
where resource_associated_entity_id > 0
  and resource_database_id = db_id()
order by request_session_id, resource_associated_entity_id



 

阅读更多
个人分类: 项目分享-Alliance
想对作者说点什么? 我来说一句

经典SQL语句

2013年10月22日 93KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭