--逻辑读最高 -- Top Cached SPs By Total Logical Reads (SQL 2008 only). -- Logical reads relate to memory pressure SELECT TOP ( 25 ) p.name AS [SP Name] , deps.total_logical_reads AS [TotalLogicalReads] , deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] , deps.execution_count , ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS [Calls/Second] , deps.total_elapsed_time , deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] , deps.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[object_id] = deps.[object_id] WHERE deps.database_id = DB_ID() ORDER BY deps.total_logical_reads DESC;
另外:目前单个数据库文件很大,达18G,LDF也有600多M,跟这个有关系吗?
SQL SERVER 2008 R2 64位
另外:目前单个数据库文件很大,达18G,LDF也有600多M,跟这个有关系吗?
SQL SERVER 2008 R2 64位这个程度算不进“大”,我这边都百G为单位的。用Profiler抓一下你登录的时候都用了什么SQL语句
另外:目前单个数据库文件很大,达18G,LDF也有600多M,跟这个有关系吗?
SQL SERVER 2008 R2 64位这个程度算不进“大”,我这边都百G为单位的。用Profiler抓一下你登录的时候都用了什么SQL语句
其实也不光是“首慢后快”这个现象,在进行其他操作时也慢,比如保存。(用友ERP,登录时SQL一时还抓不到,现在用的人多)
任务管理器那里把process id显示出来,然后在Profiler单独抓这个
从那个object报表来看的话排前面的全是都是msdb啊
只抓 某个pc、某个应用、费时超过多少毫秒 的SQL服务器的内存是多少?
库是18G,也不算大
-- Top Cached SPs By Total Logical Reads (SQL 2008 only).
-- Logical reads relate to memory pressure
SELECT TOP ( 25 )
p.name AS [SP Name] ,
deps.total_logical_reads AS [TotalLogicalReads] ,
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
deps.execution_count ,
ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
GETDATE()), 0) AS [Calls/Second] ,
deps.total_elapsed_time ,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
deps.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[object_id] = deps.[object_id]
WHERE deps.database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC;
从那个object报表来看的话排前面的全是都是msdb啊找CPU、逻辑读、写最高的那些
前天重启服务器无效,昨天公司停电,今天来电后又恢复正常!靠!
个人认为对于Application DBA, 你能做的事情很少, 一旦出现性能问题,首先就要把 vendor 找来. 先跑一下楼上的 top logical read query; 然后让用友去看下哪句 query是关于打开软件的.
作为Application DBA, 你是否做到了基本维护:
1. Index Statistic 维护: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
2. integrity check : http://ola.hallengren.com/sql-server-integrity-check.html最容易检查的是block and lock
exec sp_who2; -- 用友软件是否有blockUser [your database name]
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
前天重启服务器无效,昨天公司停电,今天来电后又恢复正常!靠!
如果有条件,建议把TEMP数据库和LOG文件放在10上面~~U8报表很多都是依靠临时表~~~索引整理建议对所有数据库做~~包括U8的那几个系统数据库~~下次出问题,重启数据库服务看还有问题没有~~有可能是TEMP数据库的问题~~
内存和CPU都正常,就差一个IO了。lz查一下IO看看正常不?