Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server 2008 R2 is Sending too much Data through the network Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 2:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
hvermaak (6/5/2013)
Open profiler,
File, new trace.
Connect to your sql server.
Use the template -> select Tuning
select save to file -> choose a location with sufficient space


I would strongly recommend not running Profiler GUI against a production server that's already under load. The GUI can have a severe impact on the target server.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1460075
Posted Wednesday, June 5, 2013 2:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 6:41 AM
Points: 25, Visits: 167
GilaMonster (6/5/2013)
hvermaak (6/5/2013)
Open profiler,
File, new trace.
Connect to your sql server.
Use the template -> select Tuning
select save to file -> choose a location with sufficient space


I would strongly recommend not running Profiler GUI against a production server that's already under load. The GUI can have a severe impact on the target server.


How would you approach the problem to find the query or the data is affecting the environment?
Post #1460076
Posted Wednesday, June 5, 2013 2:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
hvermaak (6/5/2013)
How would you approach the problem to find the query or the data is affecting the environment?


Server-side trace as explained in the two articles from Simple Talk.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1460080
Posted Wednesday, June 5, 2013 2:42 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,475, Visits: 1,633
SQL Geek (6/5/2013)
Hi SQLSACT, There is no wait only the CPu utilization is 90-100%.


do check which process is taking up high CPU.. is it sqlserver or any other process.


Regards,
Sqlfrenzy

Post #1460084
Posted Wednesday, June 5, 2013 2:44 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,475, Visits: 1,633
hvermaak (6/5/2013)
GilaMonster (6/5/2013)
hvermaak (6/5/2013)
Open profiler,
File, new trace.
Connect to your sql server.
Use the template -> select Tuning
select save to file -> choose a location with sufficient space


I would strongly recommend not running Profiler GUI against a production server that's already under load. The GUI can have a severe impact on the target server.


How would you approach the problem to find the query or the data is affecting the environment?


What if the GUI is connected through a different server/machine.. i.e. say from UAT/dev server. will the performance impact is same in this case?


Regards,
Sqlfrenzy

Post #1460085
Posted Wednesday, June 5, 2013 2:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
Sqlfrenzy (6/5/2013)
What if the GUI is connected through a different server/machine.. i.e. say from UAT/dev server. will the performance impact is same in this case?


Even worse.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1460087
Posted Wednesday, June 5, 2013 2:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:37 AM
Points: 1,379, Visits: 2,688
Sqlfrenzy (6/5/2013)
SQL Geek (6/5/2013)
Hi SQLSACT, There is no wait only the CPu utilization is 90-100%.


do check which process is taking up high CPU.. is it sqlserver or any other process.


Have you had a look at the 2 articles on Simple Talk?
Post #1460089
Posted Wednesday, June 5, 2013 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 5:02 AM
Points: 5, Visits: 13
Hi Sqlfrenzy, Its SQLServer which is consuming the CPU, I checked it on Resource Monitor.
Post #1460092
Posted Wednesday, June 5, 2013 2:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:37 AM
Points: 1,379, Visits: 2,688
SQL Geek (6/5/2013)
Hi Sqlfrenzy, Its SQLServer which is consuming the CPU, I checked it on Resource Monitor.


Please have a look at those 2 articles I posted earlier - That should get you on your way.

Post #1460094
Posted Wednesday, June 5, 2013 3:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 6:41 AM
Points: 25, Visits: 167
Hi SQL Geek,

read the two articles that GilaMonster gave, they will be the least invasive.
Post #1460108
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse