January 7, 2004 at 3:14 pm
Using SQL Server 2000 running under W2K server with two procs and 2GB of RAM.
Server load is minimal, less than 50 users connected (using connection pooling so physical connections are much less).
Database is small (under 500MB).
sProc in question is very simple SELECT with two inner joins. Primary table is about 150K records, each record is under 2K. Table has approximately 200 records added per business day (records are never deleted). Weekly maintenance plan is run with 'Reorganize data and index pages'.
Normally this sProc returns in under 50ms. Every 14-21 days, a speed degradation becomes apparent, increasing as time passes where it is over 1000ms.
If I simply recompile the sProc, performance again returns to sub 50ms.
Only thing I can think of is that reorganizing the data and index pages during the weekly maintenance is the culprit - is this not something you should do regularly and if this is the cause, should you routinely recompile all of your stored procedures as well.
January 8, 2004 at 9:30 am
As more data be added into your tables and changing data in indexed columns everyday, the original query plans used to access its tables may not be effecient than before. At this time, The SPs should be recompiled.
Do you have the execution plans when it runs in normal and poor performance? By review the execution plans, we may find more information whether it keeps use the original plan or the query has been re-optimized by SQL Server after 14-21 days.
January 8, 2004 at 9:43 am
Are your stats being updated? Check the db properties and be sure that update stats is checked.
January 8, 2004 at 9:53 am
Yes, Update Stats is checked for this db.
This is the only sProc that appears to have this issue. I had been under the impression that MSSQL would recompile the sProcs as needed as records were added/removed/modified vs it being a manual process.
As for execution plans: No, I did not keep one from the lower performance, but I will next time (if no solution is found in the interim).
January 9, 2004 at 12:46 am
Run Profiler, add EventSubClass column and you should be able to find the reason of recompilation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q308737
January 9, 2004 at 7:37 am
chopeen's suggestion is what I use........
January 9, 2004 at 8:02 am
Thanks, I will try that next time I experience the issue, however - does not MSSQL normally recompile sProcs as the stats change and dictate? I would imagine the problem is simply with the added records and reorganization during maintenace, the plan is no longer effiicent. Most of my tables have many more records and I have not had to manually recompile sProcs for them.
January 9, 2004 at 9:27 am
Yes, MS SQL Server does recompile a SP when statistics change. Profiler can tell you if this is the reason of a particular recompilation.
EventSubClass Reason for recompilation
-----------------------------------------------------------------------
1 Schema, bindings, or permissions changed between compile
or execute.
2 Statistics changed.
3 Object not found at compile time, deferred check to
run time.
4 Set option changed in batch.
5 Temp table schema, binding, or permission changed.
6 Remote rowset schema, binding, or permission
changed.
January 9, 2004 at 9:31 am
I understand, however the problem is that it is not being recomplied by SQL Server, I am having to do it manually.
January 9, 2004 at 10:10 am
Oh, OK, I did not fully understand you.
I'm not sure if this will help you, but take a look at:
January 26, 2004 at 3:06 pm
This problem still plagues me, but I do have additional info.
The sproc contains 4 different SELECT statements that returns different resultsets of the same data based upon a specified input parameter.
Of these 4 Selects only one is effected-
This select uses TOP 100 eg:
SELECT TOP 100 Col1, Col2, Col3 FROM Table WHERE...
Plus the sproc use IF...Else with the input param.
The other selects are similar (only the WHERE changes) and do NOT include the TOP 100.
Once narrowed to an issue with TOP, further research found suggestions to
1) use "WITH RECOMPILE" within the sproc (not my first choice since this sproc is heavily used).
2) Recode and get rid of the IF...ELSE
3) A post from Celko stated: "SELECT TOP (n)is a poorily defined, strictly proprietary "feature", but it was easy to implement in SQL Server because of the use of a file system under the covers." He goes on to list two suggestions on recoding the query without the use of TOP.
I am currently testing these in reverse order 3, then 2 then 1 and will post my results in the hopes this may be of benefit to someone else.
I appreciate any additional suggestions anyone may have.
February 11, 2004 at 10:50 am
Final results:
I modified the code so that the sProc with the SELECT TOP 100... is in a separate stored procedure. This removed the issue of a potential problem with a branching IF/ELSE with multiple SELECTs in a single sProc.
However, this did not resolve the issue.
The problem appears to be related solely to the use of the SELECT TOP 100.
I also tried recoding as suggested in the Celko post, but that required the use of SELECT Distinct and the pefromance was demonstrably worse then using WITH RECOMPILE.
So, does anyone know of any inherint issues using SELECT TOP that would cause this type of problem?
February 16, 2004 at 4:53 am
remove TOP 100 and use following
SET ROWCOUNT 100
-- ur select query here
SET ROWCOUNT 0
What is performance impact of this ? Well as per BOL
If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:
-- Amit
February 16, 2004 at 6:08 am
I have not tried that, will give it a shot and see if the plan stays updated, thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply