March 27, 2012 at 11:18 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.
My Work Around:
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 4:25 am
This was removed by the editor as SPAM
March 28, 2012 at 5:40 am
Please don't cross post. It just fragments replies and wastes people's time
No further replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1274059-391-1.aspx
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply