平时有些数据不多且正常的表会变得异常慢

 公司老版的业务使用了sqlserver数据库，所以新的业务也在支持使用。有时查询数据时会突然发现平时有些数据不多且正常的表会变得异常慢，然后查询资料开始解决。

       1、 出现问题的第一感就是数据库有问题了，怀疑是数据库临时表和缓存导致服务器内存不足导致，因为以前就出现过内存不足导致新增数据索引无法创建，且数据库表查询异常的问题。使用清除缓存和临时表语句发现没有任何变化。缓存清理语句：

USE blog   ----blog为需要清除日志的数据库名称
GO
DBCC SHRINKFILE (N'blog_Log' , 11, TRUNCATEONLY) ----blog_Log为需要清除日志的数据库名日志名称
GO
USE [master]
 GO

 ALTER DATABASE blog   SET RECOVERY FULL WITH NO_WAIT ----blog为需要清除日志的数据库名称

 GO

ALTER DATABASE blog   SET RECOVERY FULL --还原为完全模式   ----blog为需要清除日志的数据库名称

      2、接下来我怀疑时锁表导致，百度等差资料后使用锁表语句来验证我的猜测。

先来了解sqlserver锁表种类：

锁定数据库的一个表的区别
　　SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除
　　SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

SELECT语句中的各项“加锁选项”以及相应的功能说明。
NOLOCK（不加锁）
此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
HOLDLOCK（保持锁）
此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
UPDLOCK（修改锁）
此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。
TABLOCK（表锁）
此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。
PAGLOCK（页锁）
此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。
PAGLOCK 在使用一个表锁的地方用多个页锁
TABLOCKX（排它表锁）
此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。 TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表
READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使用行锁
UPLOCK 强制在读表时使用更新而不用共享锁

接下来就是查找被锁表是否包含刚才的异常表。

查看被锁表：
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'

spid 锁表进程
tableName 被锁表名
--释放被锁的表进程ID

KILL  106   数字表示spid,即锁表的

1. --查看目前正在被使用的表，这个可以在没有实时日志的情况下猜测下大概执行到了哪里

2. select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'   ORDER BY TABLENAME

--查看表的索引情况
SELECT a.name  
,c.name  
,d.name  
,d.colid  
FROM   sysindexes   a  
JOIN   sysindexkeys   b   ON   a.id=b.id   AND   a.indid=b.indid  
JOIN   sysobjects   c   ON   b.id=c.id  
JOIN   syscolumns   d   ON   b.id=d.id   AND   b.colid=d.colid  
WHERE   a.indid   NOT IN (0,255)  
-- and   c.xtype='U'   and   c.status>0 --查所有用户表  
AND   c.name='Fact_Trade_log' --查指定表 
 
--查看数据库负载
SELECT
substring (a.name,0,20) as [数据库名],
[连接数] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid),
[阻塞进程] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE
a.dbid = b.dbid AND
blocked <> 0),
[总内存] = ISNULL((SELECT SUM(memusage)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[总IO] = ISNULL((SELECT SUM(physical_io)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[总CPU] = ISNULL((SELECT SUM(cpu)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0),
[总等待时间] = ISNULL((SELECT SUM(waittime)
FROM
master..sysprocesses b
WHERE
a.dbid = b.dbid),0)
FROM master.dbo.sysdatabases a WITH (nolock)
WHERE 
DatabasePropertyEx(a.name,'Status') = 'ONLINE'
ORDER BY [数据库名]

其余查询cpu等sql:

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY avg_cpu_cost DESC


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


select      type
        , sum(virtual_memory_reserved_kb) VM_Reserved
        , sum(virtual_memory_committed_kb) VM_Commited
        , sum(awe_allocated_kb) AWE_Allocated
        , sum(shared_memory_reserved_kb) Shared_Reserved
        , sum(shared_memory_committed_kb) Shared_Commited
        --, sum(single_pages_kb)    --SQL2005、2008
        --, sum(multi_pages_kb)        --SQL2005、2008
from    sys.dm_os_memory_clerks
group by type
order by type

sqlserver 命令行使用：

远程导入数据：

osql -S 129.28.33.3 -U sa -PXXX -i  "D:\rs.sql"   

具体使用可使用help查看。