Stored Procedure Runtime

During performance tests in both single-user and multiple-user modes, I received some confusing results. A particular stored procedure generally runs in 5 seconds; however, when multiple users run the query at the same time, it takes nearly five times as long to run all the queries. After the first user receives the results, the others come back one right after another. Does SQL Server let only one person execute a stored procedure at any given time?

SQL Server lets many people run the same stored procedure at the same time. I can't troubleshoot performance problems such as yours in a completely reliable manner without a lot more information, but your question gives me a few important clues to help me point you in the right direction.

First, it's important to keep in mind the difference between response time and throughput. I could write an entire article about this topic, but here's a short definition for each. Response time is measured by the end user and equals the time that it takes to execute a single query. Some experts measure response time from the time it takes for the first row of a query to be returned to the client versus the time it takes for the entire query to run. I measure response time from the perspective of the ultimate end user—the time it takes to return the entire result set or a partial result set to the client, depending on how the application works. Throughput is a measure of what happens when many people start to execute queries at the same time. Generally speaking, a system is scalable if the response time for queries doesn't begin to degrade rapidly as more users run queries.

With these definitions in mind, let's look at your original question: Why does the stored procedure's response time take five times as long when you have multiple users running the procedure? You said that all the queries return results after the first query has finished. This clue points to two potential problems for you to investigate. You might have a blocking problem, or you might have a more generic waittype problem. Here's a place for you to start troubleshooting. The system process ID (SPID) is the ID for a particular query connection. You'll know that you have a blocking problem if you query the master..sysprocesses table while the queries are running and see a value other than 0 in the column that's blocked for any SPIDs that are responsible for running one of your queries during the performance test. You can obtain the SPID for each connection by running

SELECT @@spid

If you see a nonzero value in the blocked column for a row in sysprocesses, the connection you're looking at is being blocked by the connection whose SPID is equal to the nonzero value in the blocked column.

You'll also want to check the master..sysprocesses table to diagnose a waittype problem. Determine the SPIDs that are running the queries in your test, then run a SELECT * FROM master..sysprocesess for each SPID that's running one of your queries. You'll want to look carefully at the lastwaittype and waittime columns. If a SPID's waittime isn't zero, the SPID is currently waiting for some sort of resource. You can determine this resource by using a combination of the lastwaittype and waitresource columns. Troubleshooting waittypes can be a complex process, so I can't provide many details about doing so here. I encourage you to read Tom Davidson's article "Opening Microsoft's Performance-Tuning Toolbox" at http://www.windowsitpro.com/articles/index.cfm?articleid=40925, which includes detailed information about interpreting all the waittypes that exist in SQL Server. However, I predict that the queries that finish as soon as the first query is done will show a waittype pageiolatch_sh. This waittype denotes a wait on a disk-to-memory buffer transfer and generally indicates that your system is under memory pressure that's caused by reading large amounts of data in from the disk. See my article "Pageiolatch Values in Sysprocesses" at http://www.windowsitpro.com/articles/index.cfm?articleid=42902 for more information about the meanings of pageiolatch values.