SQL server performance, maximum transfer rate 1

[anationalacrobat]

I confess from the start here that I'm not a SQL Server guru. Here's the situation: we're running FR-100, a fund-raising and gift-tracking product from Sage Software. It uses SQL Server as a database but appears to make minimal use of stored procedures. The architecture seems to be essentially peer-to-peer with the application on each workstation performing huge data pulls to put reports together, no processing done on the server to cut down on the amount of data passed back and forth.

So, I'm trying to determine where our bottleneck is. There are three options I can think of:
1) Workstations might not have the horsepower
2) Network might not have the bandwidth to handle the pulls
3) SQL Server cannot feed the data as fast as the rest of the system can handle.

Well, the CPU performance meter in task manager is not spiking too badly, I can run other applications when FR-100 is chugging away with reports. I don't think the desktop hardware is a limitation here.

My workstation has a 100mbit network connection and usage rarely passes 5mbit when running reports.

So, I'm wondering if SQL Server might be a limiting factor here. Connection to the database is via ODBC. I know very little about how SQL Server is configured. How does it allocate resources to keep a greedy report from hosing the database? Is it like a web server where you are given maybe 10% of the server's bandwidth to each connection which can then be spoofed by a download accelerator?

I don't have admin rights on the server and thus can't go poking around and I'm not guessing accurately at the right sort of names for this feature (if it exists) to find anything on Google. Help would be appreciated! Thanks.

 

[mrdenny]

There is no limiting going on within SQL Server. If a single connection needs all the resources that the SQL Server has to offer then SQL will allow that connection to take all the CPU and memory that it needs. SQL doesn't do any sort of limiting of network resources. It needs the data to the network just as quickly as it can.

Often the bottleneck will be that the disks are to slow. Either because the hardware just isn't fast enough, or because of poor table design.

Fire up SQL Profiler and run it against the system while users are running reports. This will tell you what SQL Commands are being run and if they are using a lot of CPU, or disk resources.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog