This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(272, 23%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPJ%3C/text%3E%3C/svg%3E)
Hi all :
when test SQL server TPC-C performance use Hammerdb , The jitter rate is very high. Please see the follow picture.
about the TPC-C test, could you please give me some adives?
Thanks,
Regards
spring
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @puchun jiang ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Hi @puchun jiang ，
Just checking in to see if above information was helpful. If you have any further updates on this issue, please feel free to post back. If you think my reply is helpful to you, please remember to mark it as an answer. Warm thanks.
Best regards,
Seeya
The TPM = transaction per minutes looks well so far.
The test is over just short 3 minutes and you worry about some peaks and some vales?
Is the storage dedicated for SQL Server database files you tested on or are other systems using the storage, too?
Hi ,
yes , i'm worry about the peaks.
the storage dedicated for SQL Server database files .
maybe the cpu and disk queue related to the peak，and I have change the OS Power Options to High performance.
I
Hi @puchun jiang ,
These peaks and troughs in the graph that would indicate bottlenecks due to configuration issues.
Is it still like this even after a while?
If yes, you need to check your database, OS and hardware configuration.
Best regards,
Seeya
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hi ,
yes after run 1 hour still like this .
change the OS Power Options to High performance, and the DDR speed to 3200. disk use nvme ssd .
about the database, can you please give me some advices?
Regards,
Spring
Hi @puchun jiang ,
Performance issues boil down to CPU, IO or lock contention.
It seems that you have a problem with lock contention in SQL Server.
If you can execute sp_who2 when the query times out, you can use the BlkBy column to trace back to the lock that everyone else is waiting for.
sp_who2 usually helps you to track / kill deadlocks in your database.
Best regards,
Seeya
Hi Seeya
The query results as follows:
about this shall i change database file's Autogrowth or others?
You should certainly not have 10% autogrow for the log file, but that should be a fixed size. I think a common recommendation is 250 MB.
Furthermore, you should size your log in advance. Particularly, when you are running a benchmark like this, you should make a note of what is the final log size, and then redo the test with that log size from the start. As you may know, when SQL Server has to grow the log file, it must zero out the allocated area. So, possibly, that dip in CPU that you have in your graph, could be due to autogrow.
Hi, follow your advices the test result looks more smooth.
![130618-image.png][1]
![130672-image.png][2]
Thanks,
Regards
Jiang
Hi @puchun jiang ,
Agree with Erland.
It should be set to grow at a fixed size, not proportional. In this way, unnecessary troubles caused by too much or too little growth at a time can be avoided.
Set the maximum file size to prevent SQL Server files from running out of disk space due to self-growth and affecting the operating system.
Because SQL Server writes log records in strict order. SQL Server will automatically grow in a log file, which is also to ensure log continuity. This is different from data files. Relying on automatic growth, it is difficult to enjoy the effect of I/O load balancing.
Best regards,
Seeya