July 11, 2005 at 7:22 am
I am working in an enviroment where the main production box is running SQL Server 2000 Enterprise and Windows Server 2000 Data Center as the OS.
Recently we have had performance issues where all the normal load indicators (both SQL and OS) looked normal but our application was crawling. The problem turned out to be that delays had been introduced rather than an added load to the system.
In Case 1, someone had left SQL Profiler running pointed the main database server from another server, and this slowed the main database considerably.
In Case 2, for some reason a recent Mcafee virus defn. data file update introduced a delay even though on-access scanning had been disabled. We had to stop the Mcafee services manually. I'm not exactly sure what happened here as the same data file update went through to all of our other servers and we didn't have this problem, so it's some subtle interaction between the OS/SQL server versions and the last update.
In both cases while we were in the process of tracking the causes of the performance degradation, we looked at all the normal OS and SQL load indicators (CPU Usage, Network traffic, Physical I/O, SQL Data and procedure cache hits, etc) and they all looked normal.
My question is this, is there a systematic method of tracking down causes of performance degradation caused by delays rather than increasing the system load? We tracked down the causes of the 2 cases above by a tedious and time consuming trial and error process of elimination. And time is something we don't have in a production environment.
Any advice or pointers would be appreciated.
Thanks!
Tom.
July 11, 2005 at 9:13 am
Tom,
Probably not much of an advice, but did you try the following:
1. There are known issues with a cluster and Anti-virus. Is your server a cluster?
http://support.microsoft.com/?id=250355
2. Use Current Activity window.
3. Establish WMI baselines for as many counters as you can script and tell code to send you an email if baselines are exceeded by 25% Do not run continiously, may affect performance.
4. In the Current Activity windows search for the processes that are outside of your application. See Application field of Process Info window.
5. Look for blocking - last 2 fields in Process Info
6. Profiler: Statement Started / Statement Completed, see where the delay is: between statements or when running a statement. Based on your example, use Profiler with as much filters as possible to minimize affecting performance
Yelena
Regards,Yelena Varsha
July 11, 2005 at 9:25 am
Yelena,
Forgive my ignorance of Windows admin as I have just moved over from a Unix environment.
Where is the "Current activity" windows? Is this another name for one of the screens on the "Task Manager"?
Tom.
July 11, 2005 at 9:33 am
Tom,
It is OK, at least I will know now where to go with Unix questions 🙂
I assume you are running SQL Server 2000.
Start SQL Server Enterprise Manager (EM) from the start menu:
Start ->Programs->Microsoft SQL Server ->Enterprise Manager.
Check if your SQL Server is already registered, you can see the server name in the left pane of EM. If you are on the SQL Server machine itself the entry may say (Local) (Windows NT) - this is even on Windows 2003 box.
Expan your server name node.
Expand Management node
Expand Current Activity node
Click on Process Info. See entries in the right pane. Use Scroll Bars. Let me know if you have other questions. One thing: these windows do not refresh by default, click back on Current Activity entry, right-click, refresh.
Do you know about WMI and Profiler?
Yelena
Regards,Yelena Varsha
July 12, 2005 at 1:45 am
Also if you are not sure whether your performance issue is coming from SQLServer the you can check the system activity in either the performance monitor (perfmon.exe) or the task manager (process tab), where you could add several other columns (like I/O, CPU, memory...) and you can check the situation by process.
Once identified the issue you can close down your research
Bye
Gabor
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply