Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-26 : 14:31:33
|
Glenn writes "I think the following should be a challenging question (the kind you guys like) as I am having a very difficult time finding any information at all on MSDN, web, etc. related to this issue.
Thanks for taking a crack at this. Please email me if you have any ideas, or information whatsoever on how to solve, what to look for, where to get additional information related to my problem, etc.
Thanks,
Glenn Mason
gmason@extremelogic.com
ps - if you need to see any SQL code, profiler trace screenshots, or anything else, please let me know and I will send you anything you need to help with this problem.
-----
I am currently seeing a problem with a stored procedure in our production database causing a time-out condition to occur. I eventually was able to pinpoint the source of the error and it is a SQL Server MSDB stored procedure called msdb.dbo.sp_sqlagent_get_perf_counters. This sp updates performance counter values for counters that are available for monitoring via Performance Monitor or creating SQL alerts.
I noticed that under a profiler trace, there are several calls to this stored procedure that occur in succession within the user- defined stored proc in our database that is the sp that is being invoked by our app component that reports these occassional timeouts to the Windows 2000 event log. The forth call to this stored procedure causes the timeout condition, thus causing the stored procedure and the component call to ultimately fail. I have run the perf counter sp repeatedly in Query Analyzer and it seems to be working fine. Examination of the master.dbo.sysperfinfo table reveals that the counters are being updated between calls to this sp. I also noticed that SQLAgent seems to execute this stored procedure every 15-20 seconds (which I am assuming is normal).
The problem is seemingly random on the production server although I have been able to get our development SQL Server to a point where the same timeout error is occurring in the stored procedure - sp_sqlagent_get_perf_counters. The database is under 30 MB and the test data volume is small. One puzzling aspect to this issue is that the database was tested using Mercury LoadRunner under a very heavy load that generated millions of test records, and this problem never reared its ugly head. I have included info that appears in MSDN on the error event #. There is also a more random set of warnings that I included below in case anyone might know if they are related to the problem I have described above.
If anyone has experienced this problem before and has any useful info regarding this error, I would love to hear from you!
Thanks,
Glenn
-------------------------
Windows 2000 event log entries:
Perflib Error - Event ID: 1015
The timeout waiting for the performance data collection function
"TermService" in the "C:\WINNT\System32\perfts.dll" Library to
finish has expired. There may be a problem with this extensible
counter or the service it is collecting data from or the system
may have been very busy when this call was attempted.
-------------------------
These next 2 event log warnings are happening with some frequency
(every few hours) and they always seem to ocurr together. I'm not sure
if these warnings have anything to do with the perflib error -
event #1015.
Perflib Warning - Event ID: 1016
The data buffer created for the "MSSQLServer" service in the
"C:\WINNT\System32\SQLCTR70.DLL" library is not aligned on an
8-byte boundary. This may cause problems for applications that
are trying to read the performance data buffer. Contact the
manufacturer of this library or service to have this problem
corrected or to get a newer version of this library.
occurs together with the following warning:
PerfDisk Warning - Event ID 2000
Unable to read the |
|
|
|
|
|
|
|
Performance counters update problem (msdb.dbo.sp_sqlagent_get_perf_counters)