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(*) ASnumber_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个问题，意外之喜