Performance Change In 1 Day, How Can I See, What??

Hi,
We have an application runs on SQL Server 2000 online (win 2000). Yesterday,
I saw that an T-SQL which runs in 3 seconds, is running now in 30 seconds
(After 1 day). Then I saw that windows update installed patches and restart
windows at night.
Now there is not any problems in sql server log, I tried theese:
DBCC CHECKDB
DBCC DBREINDEX
No problem seen in this checks. But performance is bad. How can realize what
is the problem?
My t-sql uses UDFs. I tested sql with UDFs. Than I replace UDF call with
T-SQL which is called in UDF. Results:
With UDF call: 4843 ms
With T-SQL call: 77 ms
I think this difference is abnormal.
Finally anything was changed my system performance. But what'
Thank you in advance.
______________________________
Senol AkbulakHello Senol,
Thank you for using Microsoft MSDN newsgroup!
I understand that after installing some Windows update, the performance of
your SQL Server becomes slow. You identified the issue is caused by UDFs.
If I'm off-base, please let me know.
To know the issue better, I'd like to know the following information:
1. What UDFs do you use? Does it only occur for a specific UDF? Is it
scalar function? Did you check the Execution plan of the query? Also,
please check the actual time of query by using the folowing option in QA.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
2. Does the issue still occur if you use UDF on a different table?
3. Check if the issue occurs after clean cache and update statistics
dbcc freeproccache
dbcc dropcleanbuffers
sp_updatestats
Please note this may have performance impact for some time if it is a
production server.
You may set following option in the session to see if it makes any
difference.
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Also, you may want to use profiler to trace the execution with Execution
plan class selected so that you could see the execution plan from the
trace.
More related information
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
More information can be found in the following BOL topics:
Query Tuning
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_536v.asp
Query Tuning Recommendations
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_33lf.asp
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Please let me know if you have any update and I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Senol,
Just want to check in if you have further questions on the issue. Please
feel free to post back if need any help.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Senol,
How things are going there? Please feel free to post back if you need
further assistance.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================