我发现在查询分析器中执行一个select top 200000 * from tb1 order by id 也出现多个同进程id的纪录,原表中有2000W+数据。是不是数据量太大造成的?
估计是死锁吧,sp_who 查看下
ID列自增,条件也是ID。我想问的是为什么开这么多同ID的进程。
这jop在执行会有死锁的现象,你可以将其中的一些语句设置成锁等待,等前面的执行完毕后在打开
我写的是整段的sql语句,不是按顺序执行的吗?
求助个存储过程的问题 我想在页面上做个排序的选择菜单。 通过.NET将参数传递个存储过程,那个存储过程怎么些。 就排序那快。@Paixu varcharselect top (@Pagesize) * from table where ziduan=(@D_Lei) and ziduan=(@X_Lei) order by @Paixu desc应该怎么些,在线等,谢谢!
sqlserver死锁检查工具 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_who_lock] GO /*************************************************************************** // 创建 : fengyu 邮件 : [email protected] 日期 :2004-04-30 // 修改 : 从http://www.csdn.net/develop/Read_Article.asp?id=26566学习到并改写 // 说明 : 查看数据库里阻塞和死锁情况 ***************************************************************************/ use master go create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message-- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_whoreturn 0 end
select top (@Pagesize) * from table where ziduan=(@D_Lei) and ziduan=(@X_Lei) order by @Paixu desc可以用 exec(@strsql)或者sp_executesql
--table variables CREATE table tttt ( id int identity(1,1), c1 int, c2 int, c3 int ) --insert insert into tttt select 1,2,3 Union all select 1,2,3 Union all select 1,2,3 Union all select 1,2,3 Union all select 4,5,6 Union all select 7,8,9 Union all select 7,8,9 declare @strsql nvarchar(4000) declare @Paixu nvarchar(20) SET @Paixu='id' SET @strsql='select top 2 * from tttt order by '+@Paixu+' desc' exec(@strsql)
也出现多个同进程id的纪录,原表中有2000W+数据。是不是数据量太大造成的?
我想在页面上做个排序的选择菜单。
通过.NET将参数传递个存储过程,那个存储过程怎么些。
就排序那快。@Paixu varcharselect top (@Pagesize) * from table where ziduan=(@D_Lei) and ziduan=(@X_Lei) order by @Paixu desc应该怎么些,在线等,谢谢!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who_lock]
GO
/***************************************************************************
// 创建 : fengyu 邮件 : [email protected] 日期 :2004-04-30
// 修改 : 从http://www.csdn.net/develop/Read_Article.asp?id=26566学习到并改写
// 说明 : 查看数据库里阻塞和死锁情况
***************************************************************************/
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end -- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_whoreturn 0
end
--table variables
CREATE table tttt
(
id int identity(1,1),
c1 int,
c2 int,
c3 int
)
--insert
insert into tttt
select 1,2,3
Union all
select 1,2,3
Union all
select 1,2,3
Union all
select 1,2,3
Union all
select 4,5,6
Union all
select 7,8,9
Union all
select 7,8,9
declare @strsql nvarchar(4000)
declare @Paixu nvarchar(20)
SET @Paixu='id'
SET @strsql='select top 2 * from tttt order by '+@Paixu+' desc'
exec(@strsql)