Why to look at "Wait Statistics" to troubleshoot SQL Performance

CSS Wait Type Repository

MSDN

Wait Type – Day

ALTER TABLE frmuser_0315 ADD useralias AS (account+'.net') PERSISTED

计算列可用于：
1，对于复杂的计算自动保存值 
2，复杂查询利用计算列checksum取值，避免like %%

注：默认是不持久的，即列并不物理存在，只是逻辑上的，不能建立索引。
指定在指定列中添加或删除 PERSISTED 属性。 该列必须是由确定性表达式定义的计算列。 对于指定为 PERSISTED 的列，数据库引擎将以物理方式在表中存储计算值；并且，当更新了计算列依赖的任何其他列时，这些值也将被更新。 通过将计算列标记为 PERSISTED，可以对确定（但不精确）的表达式中定义的计算列创建索引。

相关视图：

SELECT TOP 50 * 
FROM sys.computed_columns

Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch ='%london%' 
set @Database_Selected= 'Northwind'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0   
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam') 
  drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name 

open @Table_Name 

Fetch Next from @Table_Name into @Table

WHILE @@FETCH_STATUS = 0
 BEGIN 
  set @Count_Table =@Count_Table+1
  --Second cursor opened
  --print 'Select column_name from  '+@informationName +' where table_name= '''+@Table+''''s
  Exec('SELECT column_name,data_type INTO Column_Nam FROM  '+ @informationName +' where table_name = '''+@Table+'''')
  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column
 
  set @Table=@Database_Selected+'.[dbo].['+@Table+']'
  WHILE @@FETCH_STATUS = 0
   BEGIN
    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
     begin 
      set @Result=null
      if @column like '%-%' begin  set @Result =('SELECT ''' + @column +''' FROM ' + @Table  
                +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
        else
		 set @Result =('SELECT ' + @column +' FROM ' + @Table  +' Where ' + @column + ' Like '''+@keytosearch+'''')

      insert into #SearchTestResult exec(@Result)

      set @ID=0
      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

	  if @ID is not null 
	   begin
        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
            if @ID_inserted = @ID 
             begin 
              print '' 
             end 
           else  
	        insert into #SearchResult values (@Table,@column)
       end  
    end
    FETCH NEXT FROM @Column_Name INTO @column 
   END  
  CLOSE @Column_Name  
  DEALLOCATE @Column_Name
  --Second cursor closed

  drop table Column_Nam

  Fetch Next from @Table_Name into @Table 
 End 

close @Table_Name  

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult

参考：Search for a Value Throughout Your Database

DECLARE @Value AS VARCHAR(20) = 'ABCC' 
DECLARE @NoOfChars AS INT = LEN(@Value)
DECLARE @Permutations TABLE (Value VARCHAR(20)) 
 ;

WITH NumTally
AS (
 --Prepare the Tally Table to separate each character of the Value.
 SELECT 1 Num

 UNION ALL

 SELECT Num + 1
 FROM NumTally
 WHERE Num < @NoOfChars
 ),
Chars
AS (
 --Separate the Characters
 SELECT Num,
  SUBSTRING(@Value, Num, 1) Chr
 FROM NumTally
 )
 SELECT *
 FROM Chars

日志错误：
Service Broker needs to access the master key in the database 'BRM_TEST'. Error code:32. The master key has to exist and the service master key encryption is required.

原因：
SqlDependency在清理服务，队列，存储过程时需要一个转换，这个转换需要数据库的master key去存储产生的会话key,虽没多大影响，但会产生错误日志。解决方法是创建一个key给他用。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

参考：SqlDependency问题大全
 

SELECT 
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

在创建索引，数据库恢复等耗时操作时，可通过以下方法查看剩余时间

1. SELECT
2.     d.PERCENT_COMPLETE AS [%Complete],
3.     d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
4.     d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
5.     d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
6.     d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
7.     s.text AS Command
8. FROM    sys.dm_exec_requests d
9. CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
10. WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
11. ORDER   BY 2 desc, 3 DESC    

12. 可统计为以下命令完成的工作的百分比：

    • ALTER INDEX REORGANIZE
       
    • AUTO_SHRINK 选项（带 ALTER DATABASE）
       
    • BACKUP DATABASE
       
    • CREATE INDEX
       
    • DBCC CHECKDB
       
    • DBCC CHECKFILEGROUP
       
    • DBCC CHECKTABLE
       
    • DBCC INDEXDEFRAG
       
    • DBCC SHRINKDATABASE
       
    • DBCC SHRINKFILE
       
    • KILL (Transact-SQL)
       
    • RESTORE DATABASE,
       
    • UPDATE STATISTICS.