--建议楼主关注磁盘活动及耗时查询
--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
--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
http://blog.csdn.net/claro/archive/2008/10/09/3040302.aspx
http://blog.csdn.net/claro/archive/2008/10/09/3040271.aspx
http://blog.csdn.net/claro/archive/2008/12/16/3531230.aspx
2.sql profiler 找出耗时的sql查询,看是否可以优化
3.打开windows性能计数据,看看io等待有多少逐一排查,解决问题。
已经找到等待的类型了,还叫我用windows性能计数据去找,老大,看清楚我的问题再回答好不好
你说的这些方法,有点sql常识的人都会啊
堵塞,耗时sql都会造成SQLTRACE_BUFFER_FLUSH
我问的是LATCH_EX过高是否正常!如果不正常,LATCH_EX又是怎么造成的?
有点常识的人都不知道LATCH_EX是怎么造成的?.....无语了.
CXPACKET是查询并行的等待,LATCH_EX比这个值还要大,系统存在严重资源竞争
闩属sql内部存储引擎锁,不是很好找,等待高手!
一般来讲,数据库变慢,需要去监控目前或说当时的情况,不是去看从上次重启服务到现在的一个状态累积。
我说的三个办法配合,能分析出大多数情况下的问题瓶颈。