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 |
|
CanadaDBA
583 Posts |
 Posted - 2005-02-28 : 15:09:36
|
| I ran the Profiler and now, I have 560,000 row of the following columns:EventClass, TextData, DatabaseID, HostName, ApplicationName, LoginName, SPID, Duration, StartTime, EndTime, Reads, Writes, CPU, ObjectsId, DBUserName I am going to write some SELECT statements with different WHERE clauses or different ORDER BY. How should I analyse the information? For example, my highest Writes is 14175, highest Reads is 3532708 (Which both belong to Import DTS packages). My question is that when these numbers are too much. Does CPU over 5000 means too much or what? Write me your experience and the way you work with such a data. Thanks,Canada DBA |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-28 : 15:50:29
|
| Each read or write is causing 8k of disk IO.So, 14175 * 8k = 116,121,600 bytes or about 113MB's of disk IO. That seems like quite a bit to me.Now, you have to ask yourself a few questions about that particular query:1. Can it be optimised?2. Does it run a bunch during the day, or only once?If it only runs once a day at an off-peak time, it's probably not affecting system performance that much. If that query is run every 30 seconds all day long, you better find a way to optimise it, or make it run less often.Make sense?Find the queries that run the most often with the most number of reads. I don't think you'll be able to optimise the writes too much.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-03-01 : 08:05:01
|
Thank you Michael; I was looking for such analysis. The 8k was good information. How about CPU or Duration? quote:
Originally posted by MichaelP
Each read or write is causing 8k of disk IO.So, 14175 * 8k = 116,121,600 bytes or about 113MB's of disk IO. That seems like quite a bit to me.Now, you have to ask yourself a few questions about that particular query:1. Can it be optimised?2. Does it run a bunch during the day, or only once?If it only runs once a day at an off-peak time, it's probably not affecting system performance that much. If that query is run every 30 seconds all day long, you better find a way to optimise it, or make it run less often.Make sense?Find the queries that run the most often with the most number of reads. I don't think you'll be able to optimise the writes too much.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Canada DBA |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-01 : 10:20:47
|
| CPU I'm not sure on. I don't know that I've ever really measured CPU because it's never been my bottleneck :)Duration.... I like to try to keep everything sub 15 seconds. I think duration might be measured in milliseconds, so that would be 15000ms.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Profiler results