March 27, 2012 at 11:24 pm
I got an performance issue that "Query that usually takes approx 5 seconds currently running at 12 seconds '" .User want DBA to analyzing tempdb usage/access and check what type of waits are occuring .
SQL SERVER 2008 sp1.
a).I have found some of the indexs are highly fragemented and informed the user about the rebuilt index.
b).i have checked the tempdb usage (select * from sys.dm_db_file_space_usage) at current.
database_idfile_idunallocated_extent_page_countversion_store_reserved_page_countuser_object_reserved_page_countinternal_object_reserved_page_countmixed_extent_page_count
2 1 163152 0 48 32 160
2 5 148416 0 0 88 32
But I note that there are four processors and only two tempdb files .I think there should be one tempdb file per CPU.
Can you please suggest whether i have to add multiple data files per CPU cores. Will it improve the performance.
c).For tempdb, User want to look at the wait statistics SQL collects.
Can you please tell me how to collect the wait statistics .Please suggest me whether i have to ask Client any particlular time when have to collect the wait statistics
Please suggest me whether i have to use profiler or DMV to collect the wait stat...
March 28, 2012 at 12:12 am
SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
hope this helps
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 28, 2012 at 12:56 am
Thanks for providing the Script.
I have executed the query but there was no out put displayed at present.
Can you please tell me do i need to collect the data at time when the preformance is occuring on the server.
And also please let me know wheather i have add datfiles to tempdb as we r having four processors .Is it recommended in sql server 2008 sp1 that there should be one tempdb file per CPU.
Please help me..
March 28, 2012 at 1:05 am
yes,
execute -- reset wiat stats
dbcc sqlperf('sys.dm_os_wait_stats',clear);
go
wait for 1 hr-2hrs
select wait_type,waiting_task_count,wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_stats
and check the stats
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 28, 2012 at 1:16 am
I have exectued the Below query which i found in the goolge
"
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', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
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
INNER 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 < 99 OPTION (RECOMPILE); -- percentage threshold
"
and got the below output
wait_typewait_time_spctrunning_pct
FT_IFTSHC_MUTEX93405838.5138.51
BROKER_EVENTHANDLER816743.6733.6772.18
PAGEIOLATCH_SH205528.128.4780.65
SOS_SCHEDULER_YIELD185489.297.6588.3
RESOURCE_SEMAPHORE168525.876.9595.25
ASYNC_NETWORK_IO27180.711.1296.37
OLEDB14262.880.5996.95
PAGEIOLATCH_EX13092.540.5497.49
TRACEWRITE12967.820.5398.03
LCK_M_SCH_M9906.820.4198.44
LCK_M_U7842.510.3298.76
ASYNC_IO_COMPLETION5070.40.2198.97
LCK_M_IX4248.90.1899.15
can any one help me out here..what analaysis i have to specify to client.
March 28, 2012 at 2:08 am
bala2 (3/28/2012)
can any one help me out here..what analaysis i have to specify to client.
No idea, what does the client want to know?
If you have no idea where to start, get this and read at least chapter 1: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
But I note that there are four processors and only two tempdb files .I think there should be one tempdb file per CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply