22,209
社区成员
发帖
与我相关
我的任务
分享
客户反应在保存数据的时候非常慢,有时候新建一个客户要等待2-3分钟时间
根据sys.dm_os_wait_stats发现排列最前面的好似LATCH_EX(闩等待),
请问这样的等待是不是算正常?该怎么解决,给点建议
LATCH_EX 414770093
CXPACKET 353055875
SQLTRACE_BUFFER_FLUSH 220693421
SOS_SCHEDULER_YIELD 59583146
LCK_M_IX 90181593
--用sys.dm_exec_query_stats 动态管理查看查询最耗 IO 资源的语句
--查询数据库中表空间使用状况
--建议楼主关注磁盘活动及耗时查询
--1查看数据库内表使用的硬盘空间分配
SELECT a3.name AS [Schema 名称],
a2.name AS [表名称],
a1.rows as 记录条数,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],
a1.data * 8 AS [数据使用空间(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
THEN (a1.used + ISNULL(a4.used,0)) - a1.data
ELSE 0 END) * 8 AS [索引使用空间(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
ELSE 0 END) * 8 AS [未用空间(k)],
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) 平均每条记录长度
FROM
(
SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY [保留空间(K)] DESC