0

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!

Rolan
  • 2,924
  • 7
  • 34
  • 46
Tisa
  • 11
  • 2
  • 4
  • Check here for a possible solution [https://stackoverflow.com/questions/48425245/what-can-cause-high-buffer-i-o-on-my-clients-server](https://stackoverflow.com/questions/48425245/what-can-cause-high-buffer-i-o-on-my-clients-server) – DJSampat Dec 06 '19 at 04:13
  • Are there other indexes (besides primary) on updateable tables? Disabling unnecessary indexes may increase update performance a lot. How big is network latency to SQL server? Every update is network roundtrip to SQL; program needs to wait for statement completion, including network time. – Arvo Feb 06 '20 at 14:56

2 Answers2

0
  • . 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.
Potato
  • 491
  • 1
  • 4
  • 13
  • thanks for your answer. There is no procedure in this program. It just run the query one by one, no SP – Tisa Feb 19 '16 at 03:25
  • i tried and i know the main cause is too many request and update queries (the program updates each row one by one rather than using batch updating). as i mentioned above, i cannot modify the progam and query. i have rebuilded and update the statistics again. – Tisa Feb 19 '16 at 06:41
  • why give me -1? the solution available is fix the procedure or your many query. the action on rebuilding and statistics is useless. you need to fix the query,then it's will run faster as you expect. – Potato Feb 19 '16 at 07:44
  • The SSMS has a feature Named Profiler, It can collect all your program request querys, and you collection the querys,and create indexes on those table. – Potato Feb 19 '16 at 07:59
  • If you can't fix this badly written program all you can do is add memory and use faster disks – Nick.Mc Feb 20 '16 at 02:32
  • Hi Nick, may be you are rigt.... but it cannot solve the root cause if i can't modify this program. – Tisa Feb 22 '16 at 03:04
0

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.

Community
  • 1
  • 1
James Z
  • 12,209
  • 10
  • 24
  • 44
  • Thanks for your opinion. Now i will increase the virtual memory (i found that now just around 3G Ram) to this DB server. And will also check the blocking status. – Tisa Feb 22 '16 at 02:59
  • I think it is not related to the index. Because most of this program's queries aren't complicated (e.g update tableA set xxx= 'aaa' where id = 123 , the id is primary key). What do you mean about the query plan? – Tisa Feb 22 '16 at 03:03