Performance of MS SQL Server 2000/2005 stored procedures

The situation is considered when stored procedures can degrade query performance.


When compiling stored procedures in MS SQL Server 2000, stored procedures are placed in a procedural cache, which can increase performance when they are performed by eliminating the need for parsing, optimizing and compiling stored procedure code.
On the other hand, in storing the compiled code of the stored procedure lies pitfalls that can have the opposite effect.
The fact is that compiling a stored procedure compiles the execution plan of those statements that make up the procedure code, respectively, if the compiled stored procedure is cached, then its execution plan is cached, and therefore the stored procedure will not be optimized for the specific situation and query parameters.
Do a little experiment to demonstrate this.

STEP 1 . Creating a database.
For the experiment we will create a separate database.
')
CREATE DATABASE test_sp_perf
ON (NAME = 'test_data', FILENAME = 'c: \ temp \ test_data', SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
LOG ON (NAME = 'test_log', FILENAME = 'c: \ temp \ test_log', SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)

STEP 2. Creating a table.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))

STEP 3. Filling the table with test lines. Duplicate rows are intentionally added to the table. 10,000 lines with numbers from 1 to 10,000, and 10,000 lines with numbers 50,000.

DECLARE @i int
SET @ i = 1
WHILE (@i <10,000)
BEGIN
INSERT INTO sp_perf_test (column1, column2) VALUES (@ i, 'Test string #' + CAST (@i as char (8)))
INSERT INTO sp_perf_test (column1, column2) VALUES (50000, 'Test string #' + CAST (@i as char (8)))
SET @ i = @ i + 1
END

SELECT COUNT (*) FROM sp_perf_test
GO

STEP 4. Creating a nonclustered index. Since the execution plan is cached along with the procedure, the index will be used in the same way for all calls.

CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test (column1)
GO

STEP 5. Creating a stored procedure. The procedure simply performs a SELECT statement with the condition.

CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE column1 = @ param
GO

STEP 6. Run the stored procedure. When you start a vulnerable procedure, a selective parameter is specifically used. As a result of the procedure, we get 1 line. The execution plan indicates the use of a non-cluster index, since The query is selective and this is the best way to extract a string. A procedure optimized for sampling a single line is stored in the procedural cache.

EXEC proc1 1234
GO



STEP 7. Run the stored procedure with a non-selective parameter. The value of 50000 is used as a parameter. Rows with this first column value of about 10,000, respectively, use the nonclustered index and bookmark lookup operation inefficiently, but since the compiled code with the execution plan is stored in the procedure cache, it will be used. The execution plan shows this, as well as the fact that bookmark lookup was performed for 9999 lines.

EXEC proc1 50000
GO



STEP 8. Execute selection of rows with the first field equal to 50000. When executing a separate query, the query is optimized and compiled with a specific value of the first column. As a result, the query optimizer determines that the field is duplicated many times and decides to use the table scan operation, which in this case is much more efficient than using a nonclustered index.

SELECT column1, column2 FROM sp_perf_test WHERE column1 = 50000
GO



Thus, we can conclude that the use of stored procedures can not always improve query performance. You should be very careful about those stored procedures that work with results with a variable number of rows and using different execution plans.
You can use the script to repeat the experiment on your MS SQL server.