SqlServer 数据库学习笔记跨连接进程死锁.docx
- 文档编号:9792884
- 上传时间:2023-02-06
- 格式:DOCX
- 页数:15
- 大小:113.85KB
SqlServer 数据库学习笔记跨连接进程死锁.docx
《SqlServer 数据库学习笔记跨连接进程死锁.docx》由会员分享,可在线阅读,更多相关《SqlServer 数据库学习笔记跨连接进程死锁.docx(15页珍藏版)》请在冰豆网上搜索。
SqlServer数据库学习笔记跨连接进程死锁
1判断数据库是否存在
ifexists(select*fromsys.databaseswherename='数据库名')
PRINT'存在'
else
PRINT'不存在'
2判断表是否存在
ifexists(select*fromsysobjectswhereid=object_id(N'[表名]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
PRINT'存在'
else
PRINT'不存在'
3判断存储过程是否存在
ifexists(select*fromsysobjectswhereid=object_id(N'[存储过程名]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
PRINT'存在'
else
PRINT'不存在'
4判断临时表是否存在
ifobject_id('tempdb..#临时表名')isnotnull
PRINT'存在'
else
PRINT'不存在'
5判断视图是否存在
--判断是否存在'MyView52'这个试图
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_NAME=N'MyView52')
PRINT'存在'
else
PRINT'不存在'
6判断函数是否存在
-- 判断要创建的函数名是否存在
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[函数名]')andxtypein(N'FN',N'IF',N'TF'))
PRINT'存在'
else
PRINT'不存在'
7获取用户创建的对象信息
SELECT[name],[id],crdateFROMsysobjectswherextype='U'
8判断列是否存在
ifexists(select*fromsyscolumnswhereid=object_id('表名')andname='列名')
altertable表名dropcolumn列名
9判断列是否自增列
ifcolumnproperty(object_id('table'),'col','IsIdentity')=1
print'自增列'
else
print'不是自增列'
SELECT*FROMsys.columnsWHEREobject_id=OBJECT_ID('表名') ANDis_identity=1
10判断表中是否存在索引
ifexists(select*fromsysindexeswhereid=object_id('表名')andname='索引名')
print '存在'
else
print '不存在'
11查看数据库中对象
SELECT*FROMsys.sysobjectsWHEREname='对象名'
查看数据库【表列】详细信息
SELECT
表名=CASEWHENA.COLORDER=1THEND.NAMEELSE''END,
表备注=CASEWHENA.COLORDER=1THENISNULL(F.VALUE,'')ELSE''END,
列序号=A.COLORDER,
列名称=A.NAME,
列备注=ISNULL(G.[VALUE],''),
标识=CASEWHENCOLUMNPROPERTY(A.ID,A.NAME,'ISIDENTITY')=1THEN'√'ELSE''END,
主键=CASEWHENEXISTS(SELECT1FROMSYSOBJECTSWHEREXTYPE='PK'ANDPARENT_OBJ=A.IDANDNAMEIN(
SELECTNAMEFROMSYSINDEXESWHEREINDIDIN(
SELECTINDIDFROMSYSINDEXKEYSWHEREID=A.IDANDCOLID=A.COLID)))THEN'√'ELSE''END,
类型=B.NAME,
字节=A.LENGTH,
长度=COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),
小数位=ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),
允许空=CASEWHENA.ISNULLABLE=1THEN'√'ELSE''END,
默认值=ISNULL(E.TEXT,'')
FROM
SYSCOLUMNSA
LEFTJOINSYSTYPESBONA.XUSERTYPE=B.XUSERTYPE
INNERJOINSYSOBJECTSDONA.ID=D.IDANDD.XTYPE='U'--ANDD.NAME<>'DTPROPERTIES'
LEFTJOINSYSCOMMENTSEONA.CDEFAULT=E.ID
LEFTJOINsys.extended_propertiesGONA.ID=G.major_idANDA.COLID=G.minor_id
LEFTJOINsys.extended_propertiesFOND.ID=F.major_idANDF.minor_id=0
whereD.NAME='tmp_fwz_gd_itemgs'--查询这个表
ORDERBYA.ID,A.COLORDER
SqlBulkCopy
将一个DataTable对象插入到指定名称的数据表中
在使用中,我也碰到了一些“莫名其妙”的问题,在此记下,以备查询。
1.SqlBulkCopy可以将一个DataTable对象插入到指定名称的数据表中,但是,这个DataTable的架构必须和数据库表一样,尤其要注意一点,DataTable中列的顺序必须和数据库表一样,而且不允许间隔。
比如:
数据库表中有A、B、C三列,其中B列有默认值,这时用于插入的DataTable不能只有A、C两列,如果只有这两列,就会把DataTable里C列的数据插入数据库表B列里,而把DBNull插入C列里。
如果是C列有默认值,DataTable可以只有A、B两列。
2.SqlBulkCopu的默认超时时间是30秒,30x4000=1.2万,无法满足我一次性插入单张表的需要,改成3600秒。
1.private static void DataTableToSQLServer( DataTable dt)
2.{
3. string connectionString = GetConnectionString();
4.
5. using (SqlConnection destinationConnection =new SqlConnection(connectionString))
6. {
7. destinationConnection.Open();
8.
9. using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destinationConnection))
10. {
11.
12.
13. try
14. {
15.
16. bulkCopy.DestinationTableName = "T_EIInformation";//要插入的表的表明
17. bulkCopy.ColumnMappings.Add("Email", "Email");//映射字段名 DataTable列名 ,数据库 对应的列名
18. bulkCopy.ColumnMappings.Add("author", "author");
19. bulkCopy.ColumnMappings.Add("Title", "Title");
20. bulkCopy.ColumnMappings.Add("Type", "Type");
21. bulkCopy.ColumnMappings.Add("confName", "confName");
22. bulkCopy.ColumnMappings.Add("Language", "Language");
23. bulkCopy.ColumnMappings.Add("Publicationyear", "Publicationyear");
24. bulkCopy.ColumnMappings.Add("Conferencelocation", "Conferencelocation");
25. bulkCopy.WriteToServer(dt);
26. }
27. catch (Exception ex)
28. {
29. Console.WriteLine(ex.Message);
30. }
31. finally
32. {
33. // Close the SqlDataReader. The SqlBulkCopy
34. // object is automatically closed at the end
35. // of the using block.
36.
37. }
38. }
39.
40.
41. }
42.
43.}
跨数据库服务器连接查询
Select*FromOPENDATASOURCE(
'SQLOLEDB',
'DataSource=服务器地址;UserID=登陆名;Password=密码'
).数据库名.dbo.表名
insertintoaa_report
Select*FromOPENDATASOURCE(
'SQLOLEDB',
'DataSource=13.26.28.168;UserID=1010;Password=Y1222'
).Manage_test.dbo.reportwhererptcodenotin(selectrptcodefromaa_report)
可能会出现组件被禁用的情况
SQLServer的AdHocDistributedQueries组件被禁用
1.开启AdHocDistributedQueries组件,在sql查询编辑器中执行如下语句:
execsp_configure'showadvancedoptions',1
reconfigure
execsp_configure'AdHocDistributedQueries',1
reconfigure
2.关闭AdHocDistributedQueries组件,在sql查询编辑器中执行如下语句:
execsp_configure'AdHocDistributedQueries',0
reconfigure
execsp_configure'showadvancedoptions',0
reconfigure
筛选索引
表中建筛选索引,表示不为空的,其它值只能唯一
CREATE UNIQUE NONCLUSTERED INDEX idx_st_warehouse_erpwhcode
ON st_warehouse(erpwhcode)
WHERE erpwhcode is not null
GO
CREATE UNIQUE NONCLUSTERED INDEX MyIndexName
ON MyTable(C1)
WHERE C1 is not null
GO
开启逻辑读取次数显示
setstatisticsioon
sqlserver性能分析--执行sql次数和逻辑次数
目前在做一个项目优化时,想通过数据库层分析sqlserver系统性能,查了一下网上代码,修改了一下标题和DMVs代码,以下代码可以用来分析系统运行一段时间后,那些语句是系统忙的sql语句。
做为参考。
另类使用:
一次在分析一个对账功能时,查看系统代码,看了半天,写得太不规范,又不写注释,看不明白。
最后用了下面一个小技巧,和大家一起分享:
就是在测量功能时,先以下命令清除sqlserver的缓存:
dbcc freeProcCache
在点击某个按钮,执行完后,在执行下面语句,就可以知道系统运行什么sql和多少次,其主要慢的语句是那些了。
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
, execution_count N'执行次数'
, total_worker_time/1000 N'所用的CPU总时间ms'
, total_elapsed_time/1000 N'总花费时间ms'
, (total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
已超过了锁请求超时时段。
(MicrosoftSQLServer,错误:
1222)
这个错误,刷新以后,右击某张表或者库,发现里面的表全部消失了或者查询不到。
这是因为sql进程死锁,资源被抢占,要解决这个问题,得杀死关闭死锁的进程,下面介绍解决方案:
杀死进程的前提是找到那个死锁的进程,
SELECTblocking_session_id'阻塞进程的ID',wait_duration_ms'等待时间(毫秒)',session_id'(会话ID)'FROMsys.dm_os_waiting_tasks
这句sql就可以查询到当前阻塞进程的ID
下面只要杀死这些进程就可以了
killspid(进程ID)或者exec(‘kill’‘spid’)
这样就可以了重新刷新下库一切正常了
顺便介绍下相关内容的另外两个sql:
--查询当前活动的锁管理器资源的信息
SELECTresource_type'资源类型',request_mode'请求模式',request_type'请求类型',request_status'请求状态',request_session_id'会话ID'FROMsys.dm_tran_locks
--查询数据库进程(where筛选库)
selectspIdfrommaster..SysProcesses
wheredb_Name(dbID)='text'andspId<>@@SpIdanddbID<>0
--数据库死锁2016年7月30日
下面这段可以查看死锁进程及sql
use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
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
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
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 )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
另外:
1、在查询按ctrl+1可以查看死锁进程,观察blk大于0的,即死锁进程,用kill“进程id(spid)”即可杀死进程
2、selectrequest_session_idspid,OBJECT_NAME(resource_associated_entity_id)tableName
fromsys.dm_tran_lockswhereresource_type='OBJECT'
这个也可以查询死锁进程
3、dbccinputbuffer(82)--查看一起死锁的语句内容
2016年8月17日微软学习
execshow_statistics(表名、统计信息名称)
看法:
从右往左,从上往下
查询需要关注两个东西:
开销、执行计划
索引覆盖
Setstatisticsiono打开io,
Setstatisticstimeno打开时间
Select(*)from=Select
(1)from没有非聚集索引,等于表扫描。
有非聚集索引,会找一个最窄的非聚集索引进行扫描
Select(aa1)from这种要看列类型是否为notnull,查找aa1不为null值的有多少行
下面查询哪些索引没有使用的,基本可以删除了
--所有的索引
selectOBJECT_NAME(object_id,7),*fromsys.dm_db_index_usage_stats
wheredatabase_id=db_id()anduser_seeks=0anduser_scans=0anduser_lookups=0
--单独表的索引
select*fromsys.dm_db_index_usage_stats
wheredatabase_id=db_id()andobject_id=object_id('sh_sp_main')
--关联sys.indexes得到索引名称
selectaa.*,bb.namefromsys.dm_db_index_usage_statsaainnerjoinsys.indexesbbonaa.object_id=bb.object_idandaa.index_id=bb.index_id
wheredatabase_id=db_id()andaa.object_id=object_id('sh_sp_jsitem_mx')
--强制使用索引
select*fromchargewith(index(idx_provider_no))whereprovider_no=500
查看索引
sp_helpindexcc_crm_customer
Execsp_helpindexcharge查看索引信息
Dbcc
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SqlServer 数据库学习笔记跨连接进程死锁 数据库 学习 笔记 连接 进程 死锁