Post reply

Activity Monitor--Guidance

  • March 15, 2010 at 12:25 am

    #230862

    I took some of the screeshots of my Activity Monitor and sp_who2 stored procedure.....

    May be you guys can guide me more on the issues which i have to concentrate to improve my performance

    I know one of the issue is Wait type of CXPACKET which can be resolved by changing the maximum parallelism setting(not sure whether it is good idea to change the maximum parallelism settings)

    Do i have to resolve the blocks created by Application .NET SQL Client Data Provider to improve the performance???

    Here is the link for the screenshots

    http://picasaweb.google.com/sandip.vs/DropBox?authkey=Gv1sRgCM-SvoXNgdGkvwE&pli=1&gsessionid=MRxc2ox35Lqout3reUom9A#5448664009442863970

    Regards,

    Sandeep

  • March 15, 2010 at 6:25 am

    #1133635

    Only looking at pictures like this of the sp_who2 output, it's hard to know enough to make good suggestions. From what I can see, you don't have any blocked processes, so you need to concentrate on gathering performance metrics across the board, and this is not best done with sp_who2. Instead, I'd suggest you begin capturing trace events on your server, so that you can aggregate the performance metrics of the queries against the machine. I'd also suggest you start gathering the information available in sys.dm_os_wiat_stats to see what's causing things to slow down within your system. You could also use perfmon to gather basic memory, cpu, etc., counters.

    With all this, you can begin to decide what's going on in your system. Before I'd start adjusting your max DOP or anthing else, I'd understand what's happening.

    Just as an aside, and I'm not suggesting you do this before you've gathered performance metrics, but frequently the default value for the parallelism threshold is too low on most systems. Adjusting that up is frequently a better choice than turning off parallelism itself.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 16, 2010 at 9:23 am

    #1134544

    I think you can look at the query parralesim parameter in the server.

  • March 16, 2010 at 10:24 am

    #1134616

    ESAT ERKEC (3/16/2010)

    I think you can look at the query parralesim parameter in the server.

    Which one specifically?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 16, 2010 at 10:59 am

    #1134639

    Thanks guys.....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply