Serious Performance Issue

[tman24m]

I've got SQL 8.0 running on an IBM server with an 850mhz zeon proc. The box has 640MB of RAM. The server is under an IBM cover with 7 other servers sharing a 53 disk array. Upon original installation everything seemed to be working fine. Lately however, when I try to get properties for a database, it takes up to and sometimes more than a minute. What could be causing this? Any ideas? The databases are anywhere between 2MB and 2GB, not very large at all.

Signed,
Very Frustrated

 

[tman24m]

should also mention that I am running SP2

 

[foxdev]

I'm thinking that if you crank up the SQL Server Profiler and don't filter anything, you may get clues by what activity is taking place (and the time they take) when you click the Properties option.

Named Pipes or TCP/IP?

 

[tman24m]

I'm not familiar with using SQL Profiler, but here's what I came up with.

Most of the duration seems to be spent on exec sp_helpreplicationdboption. It's taking between 14 and 16 seconds to run and looks like it's running 3 times.

Any ideas? Tried MSDN, no luck as usual.

 

[foxdev]

sp_helpreplicationdboption
That seems bizarre; this SP is fairly light-weight, so I can't imagine why that would be the bottleneck.

Just for giggles, try running this SP directly using Query Analyzer and see if it takes a long time.

There aren't slowdowns in "normal" online operations (querying, inserting, etc)? Only when you are trying to get the database properties in Enterprise Manager?
(I'm assuming the EM part)

 

[tman24m]

That's exactly right. Most queries seem to run fine. However I've also seen a table with no records take 5 or 10 seconds to open.

I ran sp_helpreplicationdboption in query analyzer and it takes between 13-16 seconds. I know something is definately wrong. I just can't figure out what. I've tried breaking the sp down into smaller chunks but am not having much luck.

 

[foxdev]

This is a real puzzler. I guess if I were in your shoes, I'd run the NT/Win2K Performance Monitor on the server to look for bottlenecks at that level.

The unfortunate thing is that oftentimes when folks have these weird, unexplainable performance issues, the culprit is often found to be something seemingly unrelated. I don't envy your situation.

 

[tlbroadbent]

I recommend using Performance Monitor to view the following SQL Counters.

SQLServer: Buffer Manager - Buffer Cache Hit Ratio

System: %Total Privileged Time
System: %Total Processor Time
System: %Total User Time

I suspect that you may find that the Buffer Cache Hit Ratio is low during these processes. SQL Server with only 640MB of memory can't load retain many data pages in cache. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tman24m]

SQLServer:Buffer Manager - Buffer Cache Hit Ration stays between 99 and 100

The others seem to stay between 20 and 40

What does all this mean?

 

[tlbroadbent]

Cache Hit Ratio shows the precentage of page requests that were found in cache memory. If SQL Server can find a data page in cache, it pretrieve it much faster than if it must be read from disk.

%Total Privileged Time is the average percentage of time spent in Privileged (or kernel) mode by all processors. Services often run in Privileged Mode in order to gain access to system-private data. System I/O runs in priveleged mode so this counter can be a good indicator of the amount of time spent processing I/O. Usually this number is well below 20%. A higher number cann indicate high I/O times.

%Total Processor Time measures CPU time for all processes.

%Total User Time measures CPU time (all prcessors) for user applications.

Check the folowing links for help in monitoring performance.

Tips for Using Performance Monitor Counters

http://www.sql-server-performance.com/performance_monitor_counters.asp

SQL Performance

http://tlbroadbent.home.attbi.com/sql_performance.htm

Monitoring SQL Server with Performance Monitor

http://www.swynk.com/friends/goldman/PerfMonTips.asp

If you have several replicated databases, the table master.dbo.MSreplication_options may be fragmented. Recreate the index to see if perfroamnce improves. This table is usually small and rebuilding indexes usually doesn't help but it waon't hurt to try. You may also want to run DBCC CHECKDB on master. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.