SQL Server 2008 R2 is Sending too much Data through the network

Question

SQL Server 2008 R2 is Sending too much Data through the network

SQL Geek

SSC Veteran

Points: 243
More actions
June 5, 2013 at 12:56 am

#278286

I am using SQL Server 2008 R2 on Windows Server 2008 R2 machine and the SQL is sending too much data from the network causing the website to perform slow. Please suggest what to do.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL will send the application the data that it requested, it won't send random stuff without some app querying it. If the application is requesting too much data, tune the queries and/or change the application design

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

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

SQL Geek (6/5/2013)
I am using SQL Server 2008 R2 on Windows Server 2008 R2 machine and the SQL is sending too much data from the network causing the website to perform slow. Please suggest what to do.

How did you come to that conclusion? Are you noticing network_io waits ?

Regards

hvermaak Say Hey Kid Points: 672 More actions · Answer 3

Run a trace to see what is causing the "traffic"? Analyse the results or post an extract for assistance

SQL Geek SSC Veteran Points: 243 More actions · Answer 4

Yes, You are right but its happening since last 3-4 days only, everything before that was working absolutely fine. We have not changed the queries or anything. Its now utilizing the CPU to nearly 90-100%. I think I have missed some SQL Patch.

SQL Geek SSC Veteran Points: 243 More actions · Answer 5

Hi Grasshopper, Please instruct me how to do this.

SQL Geek SSC Veteran Points: 243 More actions · Answer 6

Hi SQLSACT, There is no wait only the CPu utilization is 90-100%.

hvermaak Say Hey Kid Points: 672 More actions · Answer 7

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

select -> enable trace stop time 10 minute of data should be sufficient move it up 30 minutes if you need. The more data you gather the more needs to be analysed.

After you have collected the trace data. Open the trace file in the database tuning advisor

on the general tab select your database and trace file. Start anaylsis.

The queries that are affecting the system will be show in the results as well as suggestions of possible index optimisation

Hope this help

SQLSACT SSC-Insane Points: 21838 More actions · Answer 8

Have a look at these articles

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQL Geek (6/5/2013)
Hi SQLSACT, There is no wait only the CPu utilization is 90-100%.

CPU usage at 100% in no way indicates or suggests that there's too much network traffic.

In contrast, it's an indication that one or more queries are performing inadequately, either due to changes to the query, the tables or the data.

Identify what queries are using the CPU, tune them to work more efficiently

Chapter 3: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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, MVP, M.Sc (Comp Sci)
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

hvermaak Say Hey Kid Points: 672 More actions · Answer 11

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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, MVP, M.Sc (Comp Sci)
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

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 13

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,
[font="Verdana"]Sqlfrenzy[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 14

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,
[font="Verdana"]Sqlfrenzy[/font]