CODEWITHSUNDEEP
sql-serverdatabaseperformanceio-buffering
Tisa
Tisa

Reputation: 11

SQL server - high buffer IO and network IO

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 ?

enter image description here

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!

Upvotes: 0

Views: 5918

Answers (2)

James Z
James Z

Reputation: 12317

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.

Upvotes: 0

Potato
Potato

Reputation: 511

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

Upvotes: 0

Related Questions