Am doing performance measurement for an multi-threaded application that fetches data from a SQLServer database. When I vary the no. of threads fetching data the network utilization(data sent) on the SQL Server machine also varies.
Is there any tool which will allow me to measure and also find the bottleneck. I need to figure out whether the bottleneck is at the SQL server while writing to the Network, or the application that is not able to accept and process the data and limits the SQL Server machine to send data.
Could someone send me a link or reference of some material which will help me understand how this works.
Amit J Thakur
Joined: Sep 26, 2011
As a first step I am working towards understanding the various perf counters for SQL Server. I would see if by looking at the perf counters I can get to the root cause.
Just as a thought, SQL server provides various counters, so it may become kind of easy. But how does one identify such bottlenecks. Whether the sender is not sending the data to the capacity because of some processing taking place on the machine or the receiver is processing data at a slower rate. I am considering a case where both ends have the same adapter speed.
Are there any tools to identify this. If yes what counters or metrics should be looked at. I have a java application on Linux connecting to SQL Server on Windows.
The jamon jdbc driver should be useful. Also AppDynamics has a free tool that you can download that should also be helpful.
Also, looking at user and system cpu would be helpful. Is user busy doing things or waiting on the OS? Does your user cpu become less busy when you increase your threads indicating that it is further waiting on resources such as network or the database?
Attached is a screen snapshot of the type of information that is available via jamon jdbc driver. Time (avg, min, max, total columns) are in milliseconds. You have stats for how long each query takes as well as any exceptions thrown and time per jdbc method call (some of this is configurable). (oops the forum software failed on upload of the image snapshot)
Really appreciate you taking out time. What I missed telling is that application is running on two different linux servers pulling data from SQL Server, and there is a Disk IO i.e. I am writing data back on the disk as csv files. So, I was just wondering whether the writing to disk is taking time i.e. the application is not able to process data at a rate transferred by the SQL Server or there is some problems on the SQL server not flushing the data to the network. The problem is that the NIC cards on both ends are of 1Gbps, and I am seeing a throughput of only 25% on the SQL server machine.
Is there a way to figure this out. What metrics should I be looking at. I was looking at few tools i.e. vmstat and iostat on linux, but am not sure what metrics should be seen.
I am not sure if I am able to explain it well . Am new to perf engineering and hence so many questions and thoughts .