SQL server - high buffer IO and network IO

Question

I have a a performance tuning question on SQL server.

I have a program that needs to run every month and it takes more than 24hrs to finish. I need to tune this program in the hope that I can decrease the running time to 12 hrs or less.

As this program isn't developed by us, i can't check the program content and modify it. All i can do is just open the SQL server profiler and activity monitor to trace and analyze the sql content. I have disabled unused triggers and did some housekeeping, but the running time only decreased 1 hr.

I found that the network I/O and buffer I/O are high, but i don't know the cause and meaning of this ?

Can anyone tell me the cause of these two issues (network I/O and butter I/O)? Are there any suggestions for optimizing this program?

Thank you!

Answer 1

• . According to your descriptions, I think your I/O is normal, your question is only one:one procedure is too slowly.
  the solution:
  1.open the SSMS
  2.find the procedure
  3.click the buttton named "Display estimated execution plan"
  4.fix the procedure.
  

Answer 2

To me it seems like your application reads a lot of data into the application, which would explain the figures. Still, I would check out the following:

• Is there blocking? That can easily be a huge waste of time if the process is just waiting for something else to complete. It doesn't look like that based on your statistics, but it's still important to check.

• Are the tables indexed properly? Good indexes to match search criteria / joins. If there's huge key lookups, covering indexes might make a big difference. Too many indexes / unnecessary indexes can slow down updates.

  You should look into plan cache to see statements responsible for the most I/O or CPU usage

• Are the query plans correct for the most costly operations? You might have statistics that are outdated or other optimization issues.

• If the application transfers a lot of data to and/or from the database, is the network latency & bandwidth good enough or could it be causing slowness? Is the server where the application is running a bottleneck?

If these don't help, you should probably post a new question with detailed information: The SQL statements that are causing the issues, table & indexing structure of the involved tables with row counts and query plans.