SQL Server performing slow responce (SQL 2000,2005)
-
15 เมษายน 2555 12:42
Recently application team raised case against us since last week SQL Server performing very slow....they confirmed no changes happened in last month.
we checked at SQL level...
1. No blocking.
2. taken some query thru sys.dm_exec_sql_stats (order by total_worker_time desc) and sys.dm_exe_query_text dmvs and get the fragmentation details plus statistics but everything was OK.
3. used DTA but did not get solution.
4. added two more data files now total 4 data files (4 core processors) but still same slow issue.
----
can some one send me query 1) that will find out the memory use by SQL Query and SQL jobs . 2) how do we check need to upgrade SQL Memory.
1. what is impact of full\log backup in performance ?
2. can we do point in recovery in Bulk logged recovery if we run BCP\Index operation ?
india
ตอบทั้งหมด
-
15 เมษายน 2555 13:34
1.Analyze waits at the instance level.
2.Correlate waits with queues.
3.Determine a course of action.
4.Drill down to the database/file level.
5.Drill down to the process level.
6.Tune indexes/queries.What does the below return?
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms /1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT','SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'XE_DISPATCHER_JOIN')
)
--filter out additional irrelevant waits
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2))AS pct,
CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn<= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
ORDER BY W1.rn;
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
-
15 เมษายน 2555 15:26
for one instance
TRACEWRITE 1902075.52 44.73 44.73
OLEDB 1578037.14 37.11 81.83
ASYNC_NETWORK_IO 442082.64 10.40 92.23for another instance
TRACEWRITE 1902099.67 44.73 44.73
OLEDB 1578037.14 37.11 81.83
ASYNC_NETWORK_IO 442082.64 10.40 92.23india
-
16 เมษายน 2555 5:32
Do you have a trace file (default or not) that perform excessive wites
SELECT * FROM sys.traces
Regarding ASYNC_NETWORK_IO read my blog http://dimantdatabasesolutions.blogspot.com/2009/04/how-to-eat-asyncnetworkio.html
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- เสนอเป็นคำตอบโดย amber zhangModerator 16 เมษายน 2555 6:34
- ทำเครื่องหมายเป็นคำตอบโดย amber zhangModerator 23 เมษายน 2555 8:51
.png)
