SQL Server Performance Forum – Threads Archive
Performance Problem…
There was some performance problems in our production server. A report generation process took 5 hours. When i looked into the counters (monitored for 24 hours), Counter Name Avg, Max, Min—————————————-
Buffer Cache Hit Ratio 99, 99, 58
Available MBytes 48, 308, 3
User connections 3.5, 9, 2
Processor Queue Length 3.6, 24, 0
% Disk Time 86.487, 2524, 0
Average DiskQueue Length 0.865, 25.26, 0.86 The database is of 15 GB (D drive) and log file is of 2 GB (E drive). The server is configured with 1 processor and 1 GB Ram and SCSI disk drives of 4 partitions. here is what i did and i observed in the database… In our Production server, SQL Alert Engine is running continously for months (From July 06) and the cumulative CPU, Memory and I/O from sysprocesses are 6567924, 14882, 91122. I queried the SPID in DBCC INPUTBUFFER, and it showed the SP name sp_sqlagent_get_perf_counters. I looked into the waitresource and it showed a number 2:1:84 which when inputted to DBCC page showed me the sysindexes table in tempdb. The tempdb is in the same drive (D) where my database reside. and there is 8 GB of free space. The report generation uses 4 SPs which uses a lot of temp tables.
——————————–
I think this could be more of memory issue. Can anybody please confirm this? And i would like to know whether, shifting the tempdb to a different drive help solving the problem? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
This is really not much information to go on. Based on what you provided, it appears you have a lot of variability in your system resources. It is hard to analyze this without actually seeing the logs. Actually, what I would do first, to identify your issues, is to run a Profiler trace on your server to identify any poorly performing (long running) queries, and once you have identified them, see if you can correct them by adding missing indexes, rewriting the queries, and so on. Once you have identified and corrected the long running queries, and if you still have performance problems, then take a look at other things, like a lack of memory, etc. —————————–
Brad M. McGehee, SQL Server MVP
The problem is, this is a production issue and the report generation happens once in a week. So, i couldnt collect more. so, as per your suggestion, i think i have to wait one more week. Any more ideas?
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>