USE [你的库] go SELECT name,growth FROM sys.database_files
你把这个select语句写出来吧,应该可以优化。
Select ID,Off_Name,Off_City,Off_Shop,Off_IsLock,Off_IsDel,Off_HouseType from [FC_Office] o where Off_Shop='城西*紫金' and Off_IsLock=1 and Off_IsDel=0 AND Off_City='杭州' and Off_HouseType <> '100713160720'
语句没啥好改的,那你的索引情况呢?fc_office这个表SELECT ID , Off_Name , Off_City , Off_Shop , Off_IsLock , Off_IsDel , Off_HouseType FROM [FC_Office] o WHERE Off_Shop = '城西*紫金' AND Off_IsLock = 1 AND Off_IsDel = 0 AND Off_City = '杭州' AND (Off_HouseType < '100713160720' OR Off_HouseType > '100713160720')
Select ID, Off_Name, Off_City from [FC_Office] o where Off_Shop='城西*紫金' and Off_IsLock=1 and Off_IsDel=0 AND Off_City='杭州' and Off_HouseType <> '100713160720' order by case (( select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id )+( select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shoph_isagent=0 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end, case (( select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=1 and SellH_InforNum>0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id )+( select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shophc_islock=1 and shoph_isagent=1 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end,Off_PublishTime desc,Off_AddTime desc这个是完整的这段的SQL语句
你这order by也太!!!!!!!
order by那么复杂的话其实就是把结果集整个反来复去,开销不小,不过从执行计划来看应该问题不大。
那就神奇了,我通过 SQL Server Profiler 建立跟踪 CUP占用高的其中之一就是这个,达到218 类型是:RPC:Completed 语句是:declare @p1 int set @p1=1 declare @p6 real set @p6=5 declare @p7 real set @p7=146 exec sp_prepexecrpc @p1 output,N'dbo.pager',N'Select ID,Off_Name,Off_City from [FC_Office] o where Off_Shop=''武林*朝晖'' and Off_IsLock=1 and Off_IsDel=0 AND Off_City=''杭州'' and Off_HouseType <> ''100713160720'' order by case ((select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id)+(select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shoph_isagent=0 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end, case ((select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=1 and SellH_InforNum>0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id)+(select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shophc_islock=1 and shoph_isagent=1 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end,Off_PublishTime desc,Off_AddTime desc',1,35,@p6 output,@p7 output select @p1, @p6, @p7
不过我用SQL Server Profiler 跟踪了一下
发现很多语句暂用了100%,有的语句甚至占用了800%的CPU
是不是就是这些语句影响了?
/*
开销较大的查询
*/
SELECT ss.SUM_execution_count ,
t.text ,
ss.SUM_total_elapsed_time ,
ss.sum_total_worker_time ,
ss.sum_total_logical_reads ,
ss.sum_total_logical_writes
FROM ( SELECT S.plan_handle ,
SUM(s.execution_count) SUM_Execution_count ,
SUM(s.total_elapsed_time) SUM_total_elapsed_time ,
SUM(s.total_worker_time) SUM_total_worker_time ,
SUM(s.total_logical_reads) SUM_total_logical_reads ,
SUM(s.total_logical_writes) SUM_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY S.plan_handle
) AS ss
CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
ORDER BY sum_total_logical_reads DESC
go
SELECT name,growth FROM sys.database_files
Select ID,Off_Name,Off_City,Off_Shop,Off_IsLock,Off_IsDel,Off_HouseType from [FC_Office] o
where Off_Shop='城西*紫金' and Off_IsLock=1 and Off_IsDel=0 AND Off_City='杭州'
and Off_HouseType <> '100713160720'
Off_Name ,
Off_City ,
Off_Shop ,
Off_IsLock ,
Off_IsDel ,
Off_HouseType
FROM [FC_Office] o
WHERE Off_Shop = '城西*紫金'
AND Off_IsLock = 1
AND Off_IsDel = 0
AND Off_City = '杭州'
AND (Off_HouseType < '100713160720' OR Off_HouseType > '100713160720')
这里是链接:
http://bbs.csdn.net/topics/390119197
Select ID,
Off_Name,
Off_City
from [FC_Office] o
where Off_Shop='城西*紫金'
and Off_IsLock=1
and Off_IsDel=0
AND Off_City='杭州'
and Off_HouseType <> '100713160720'
order by case ((
select count(1) from fc_ohouse oo
left join [fc_users] u on u.users_id=oo.users_id
where sellh_islock=1
and sellh_isagent=0
And u.users_power > 1
and users_lock = 1
and users_isdel = 0
And oo.office_Id=o.id
)+(
select count(1) from fc_shouse_contact sc
left join [FC_users] uu on uu.users_id=sc.users_id
left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id
where shoph_islock=1
and shoph_isagent=0
And uu.users_power > 1
and users_lock = 1
and users_isdel = 0
And sc.off_Id=o.id))
when 0 then 1 else 0 end,
case ((
select count(1) from fc_ohouse oo
left join [fc_users] u on u.users_id=oo.users_id
where sellh_islock=1
and sellh_isagent=1
and SellH_InforNum>0
And u.users_power > 1
and users_lock = 1
and users_isdel = 0
And oo.office_Id=o.id
)+(
select count(1) from fc_shouse_contact sc
left join [FC_users] uu on uu.users_id=sc.users_id
left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id
where shoph_islock=1
and shophc_islock=1
and shoph_isagent=1
And uu.users_power > 1
and users_lock = 1
and users_isdel = 0
And sc.off_Id=o.id))
when 0 then 1 else 0 end,Off_PublishTime desc,Off_AddTime desc这个是完整的这段的SQL语句
那就神奇了,我通过 SQL Server Profiler 建立跟踪
CUP占用高的其中之一就是这个,达到218
类型是:RPC:Completed
语句是:declare @p1 int
set @p1=1
declare @p6 real
set @p6=5
declare @p7 real
set @p7=146
exec sp_prepexecrpc @p1 output,N'dbo.pager',N'Select ID,Off_Name,Off_City from [FC_Office] o where Off_Shop=''武林*朝晖'' and Off_IsLock=1 and Off_IsDel=0 AND Off_City=''杭州'' and Off_HouseType <> ''100713160720'' order by case ((select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id)+(select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shoph_isagent=0 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end, case ((select count(1) from fc_ohouse oo left join [fc_users] u on u.users_id=oo.users_id where sellh_islock=1 and sellh_isagent=1 and SellH_InforNum>0 And u.users_power > 1 and users_lock = 1 and users_isdel = 0 And oo.office_Id=o.id)+(select count(1) from fc_shouse_contact sc left join [FC_users] uu on uu.users_id=sc.users_id left join [FC_Shouse] fs on fs.shoph_id=sc.shoph_id where shoph_islock=1 and shophc_islock=1 and shoph_isagent=1 And uu.users_power > 1 and users_lock = 1 and users_isdel = 0 And sc.off_Id=o.id)) when 0 then 1 else 0 end,Off_PublishTime desc,Off_AddTime desc',1,35,@p6 output,@p7 output
select @p1, @p6, @p7
最大的可能还是SQL有问题
经过测试,如果页面的SQL语句比较简单,数据少的就很快,就是列表页面慢
1.楼主看下用户连接数是多少
2.在3楼的图例里,找到“性能-按总CPU排在前面的查询”,看下到底哪些语句消耗了CPU。
3.10楼已经提供了i/o的分析,试试减少靠前语句返回的行数,在表名后加上 with(nolok)