SQLServer效能優化一則小例項（2010-07-21）

首先找到 最耗CPU的top50 SQL
SELECT
      total_cpu_time,
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
      (SELECT TOP 50
            SUM(qs.total_worker_time) AS total_cpu_time,
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements,
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset,
            --MAX(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

有一條SQL執行效率非常差，大概30秒~80秒不等，而且佔據了所有CPU消耗時間的20%，並且執行次數也很多
select f_getconcatname(id) from table where id>1 and id<100

如果單個ID，速度還是很快的，初步判斷是ID未作索引的問題
後來檢視了一下該表的定義，已經做了索引，索引可以排除掉
在接著判斷是函式的問題
檢視了一下函式的定義，這個只是大概的函式
create function f_getconcatname(@id)
as
DECLARE cur_concat CURSOR FOR SELECT Name FROM tableB where id=@id
declare @ret varchar(200),@tmp varchar(20)
set @ret='';
set @tmp=''
OPEN cur_concat
FETCH NEXT FROM cur_concat into @tmp
WHILE @@FETCH_STATUS = 0
BEGIN
   set ret=@ret+@tmp+','
   FETCH NEXT FROM cur_concat into @tmp
END

CLOSE contact_cursor
DEALLOCATE contact_cursor

end

原以為是函式內遊標迴圈太慢就修改了一下該函式
create function f_getconcatname(@id)
as
declare @ret varchar(200)
set @ret='';
SELECT @ret=@ret+Name+',' FROM tableB where id=@id
return @ret
end

沒想到竟然比遊標還慢
後來檢查了一下tableB關於ID的定義，發現ID沒有定義為索引
新增索引後再次嘗試，發現速度提高到0~1秒
OK，解決該問題

同時在CPU 消耗總量的TOP 50中居然有25問題與之有關，一連串順帶解決了25個問題，意外之喜

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-668659/，如需轉載，請註明出處，否則將追究法律責任。