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

  • 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.

  • 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
  • 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

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

  • 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.

  • Hi Grasshopper, Please instruct me how to do this.

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

  • 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

  • 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
  • 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
  • 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?

  • 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
  • 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]

  • 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]

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

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