August 18, 2003 at 3:26 am
Hi,
I've got some problems with SQL Server 2000 SP3: some stored procedures have got strong degradation in performance; the problem seems to be solved by recreating indexes twice a day with dbcc dbreindex. Does someone know how I can preserve the indexes ?
I've got anther prolem: some stored procedure sometimes have got degradation and they don't improve if I recompile them: probably the execution plan change and I have to try to modify the stored procedure in order to have better performance; sometimes the execution of the stored procedure is much more fast if I copy and paste the code in the query analyzer and I execute it.
Thank you in advance,
Marco
August 18, 2003 at 3:50 am
Correct me if I am wrong. I am not a SQL Server Guru
1. It seems to be your index is being defragmented quickly. Try to change the order of columns. If the indexes used by particular sps are primary indexes, think about having another key as primary key (clustered) and have this key as unique only.
2. If your tables remain same recompilation won't improve performance. Actually you need addtional time for re compiling. So a slight degration of performance, which you may not notice.
3. After running the sp if you copy the code and run from QA, it will be fast, because the code, execution plan etc are available in memory (cached). Is this the case?
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
August 18, 2003 at 3:58 am
If you are using more than one column in the where clause, try using covering indexes
i.e. composite index on both the columns.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 18, 2003 at 4:04 am
Hi Marco,
quote:
I've got some problems with SQL Server 2000 SP3: some stored procedures have got strong degradation in performance; the problem seems to be solved by recreating indexes twice a day with dbcc dbreindex. Does someone know how I can preserve the indexes ?I've got anther prolem: some stored procedure sometimes have got degradation and they don't improve if I recompile them: probably the execution plan change and I have to try to modify the stored procedure in order to have better performance; sometimes the execution of the stored procedure is much more fast if I copy and paste the code in the query analyzer and I execute it.
it would be interesting and maybe more helpful, if you can post some example code
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 18, 2003 at 4:30 am
An example:
I've got a stored procedure with 15 select queries. Normally its execution is two seconds; sometimes it becomes 20 seconds. The last time it happend I solved the problem changing the where clause (in all th select statements) from:
WHERE (CodArticolo = @CodArticolo OR @CodArticolo IS NULL)
In
WHERE CodArticolo = CASE WHEN @CodArticolo IS NULL THEN CodArticolo ELSE @CodArticolo END
The time became 2 seconds like the day before but now I don't know if the situation is stable or not. How does I can maintain the execution plan of a stored procedure when its execution is fast avoiding that SQL server change it?
Sometimes calling a stored procedure from Query analyzer the execution is 30 seconds while copying and pasting the code in Query analyzer the execution is 3 seconds.
August 18, 2003 at 5:22 am
Are you sure no one else is updating the same set of records during the time you are executing the queries.
I mean in a multi-user scenario this might be the case.
Sorry if I am completely off the track.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 19, 2003 at 3:18 am
Just wanted to add something regarding recompilation of stored procedures:
If your data changes heavily and you've got a lot of rows, recreating statistics could change your access plan. In that case recompiling stored procedures will definitely have an effect on performance.
For very specific cases like that, you could add the WITH RECOMPILE clause to your CREATE PROCEDURE statement, but use it with care!
best regards,
chris.
August 19, 2003 at 5:35 am
There are significant reasons for the difference in performance of the following code:
a) WHERE (CodArticolo = @CodArticolo OR @CodArticolo IS NULL)
b) WHERE CodArticolo = CASE WHEN @CodArticolo IS NULL THEN CodArticolo ELSE @CodArticolo
In a) the OR clause is forcing SQL to AVOID using a index on CodArticolo. If this is the only WHERE clause in your query then you will be doing a table scan. The difference in performance could easily be due to the number of rows found in the buffer cache.
In b) you are allowing SQL to use an index on CodArticolo if one exists. If a suitable index does exist then your query will run more consistently.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 27, 2003 at 4:54 am
Don't know if this is the case but in a live situatiuon you can encounter a long lasting lock and this can affect queries....
To get rid of the lock try :
Set Transaction isolation level read uncommited
So you can read past locks and get a better indication of performance....
Also if there were any changes in the DB arouind the time of the application of SP 3 i would invest those VERY seriously.
Almost everybody is Using SP3 and i haven't heard of this before..... So the chances of SP3 beeing the real cause are small.
Maybe SP3 is the cause but then i would investigate the SP and see if any item mentioned in SP3 affects your situation.
Maybe they changed something in the protocol layer and is the network slower.
Look in the profiler to see if the query actually takes up more processor time.
Maybe another process is taking more processor time on the server ????
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply