SQL Server Performance Forum – Threads Archive

Interpreting import monitoring results

On a SQL2000/Win2003 server with 1GB RAM(my test server), I imported data from a text file and ran PerfMon on another server to monitor the process. At the time of import no one was using the database. I have interpreted the results based on the document "How to Perform aSQL Server Performance Audit" by Brad M. McGehee in www.sql-server-performance.com. But there are some misunderestanding matching my data with the document. Here are the results of my PerfMon: 1. The "SQL Server Buffer: Buffer Cache Hit Ratio" counter has:
Ave 99.5
Min 86.7
Max 99.89
How we can interpret the Ave. high buffer cache hit ratio? Note that my work was only import. 2. The "Physical Disk: % Disk time" counter has:
Ave 135
Min 0
Max 23430
What does the 135 or 23430 mean? How a disk can be busy more than 100% of the time? 3. The “Memory: Pages/sec” counter has:
Ave 21
Min 0
Max 2023
Is Ave. of 21 acceptable? What are the acceptable limits for this measure? Is there any official documentation for this?
During the import there are two 5 minutes spikes with more than 100 pages/sec. Can I call it bottleneck? 4. The “Processor: % Processor Time” counter has:
Ave 7.45
Min 0
Max 25.55
If earlier counters show a bottleneck, shouldn#%92t this counter be higher? Shouldn#%92t the processor spend time idling waiting on IO?
CanadaDBA

---

I found % Disk Time May Exceed 100 Percent in the Performance Monitor MMC article inhttp://support.microsoft.com/default.aspx?scid=kb;en-us;310067&sd=tech
CanadaDBA

---

Let me see if I can explain your data. When SQL Server works with data, it does so only from the buffer cache. In other words, SQL Server has to load the data into the buffer cache before it can be used. So when you imported the data, it was moved into the buffer cache before it was then written to the transaction log and your database. As you have already discovered, the physical disk counter can produce odd data. This is not unusual. Whenever SQL Server imports data from a local disk, the pages/sec will spike, which is normal. This is a reading produced by the OS, not SQL Server. Whenever any large file is read or written to disk, such as a backup or restore, pages/sec will spike. This is not a bottleneck. The Processor time seems very reasonable. A short spike is very normal. When performing testing like this, keep in mind that measuring specific tasks like this is not really very useful. Only measuring consistent activity over a period of time is really useful. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Thanks Brad! Whenever SQL Server imports data from a local disk, the pages/sec will spike, which is normal. This is a reading produced by the OS, not SQL Server. Whenever any large file is read or written to disk, such as a backup or restore, pages/sec will spike. This is not a bottleneck.
Even if the spike takes 5 minutes or more? When performing testing like this, keep in mind that measuring specific tasks like this is not really very useful. Only measuring consistent activity over a period of time is really useful.
I am going to determine how much pressure will the imports force to the server. These imports occur only once a day very early in the morning. Do you suggest using different counters or approaches? CanadaDBA

---

How are you importing the data from text file, is it DTS or BULK INSERT?
ALso confirm the indexes on the involved tables. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

The imports are done through DTS and there are no index on the target tables. p.s. Is BULK INSERT better than DTS? I mean faster or more optimum?
CanadaDBA

---

DTS also uses the bulk insert topology, but for better working you can use BULK INSERT directly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>