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 |
|
avmreddy17
Posting Yak Master
180 Posts |
 Posted - 2006-03-21 : 18:15:51
|
| I am doing a simple IO Test with the below script ... Just wanted to keep things simple and to check how many Inserts I can do on a given SQL Server. I am running the below script from QA for 1 minute and then divide the No or rows inserted by 60. Will it give me approximate results by duing this? Actually the datafiles .MDF files are sitting on a single drive where the manufacturer specs shows that it will handle 130 IO's per disk. With the below script I am getting around 147 Inserts per second. But my boss says that he is getting 2000 inserts per second on his laptop from a ...Am I missing some thing? DECLARE @lnRowCnt INT SELECT @lnRowCnt = 100000 WHILE @lnRowCnt > 0 BEGIN SET NOCOUNT ON INSERT INTO CTMessages..Iotest SELECT @lnRowCnt , 'VENU' , REPLICATE ( 'V' , 4000 ) , 1000000 SELECT @lnRowCnt = @lnRowCnt - 1 END Thx Venu |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-21 : 18:20:33
|
| You can not calculate this with one script. You would need multiple entities doing inserts in order to come up with a more accurate calculation. Perhaps the best way to check this is to have lots of entities running the inserts (have nothing else on the system), check transactions/sec counter in the SQLServer:Databases performance object in Performance Monitor.Tara Kizeraka tduggan |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-03-21 : 19:03:41
|
| Also be aware of write caching one the disks, and or Storage Controller (SCSI Card, Fibre HBA, ETC) as well.If you want to measure Disk IO, use IOMeter. It has lots of options for finding the sustained read, sustained write, and a "balanced database" option of 65%/35% read/write.http://www.iometer.orgKeep in mind that IO meter should not be run on a system that's being used for anything other than the performance test. It will push your disks to the limit, so not much else can use the disks during the time of the tests.With your "130 IO's per second" what have they defined as an "IO"? I'm pretty sure that 1IO is not the same as 1 INSERT statment unless the number of bytes moved by each IO is exactly the same.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-03-22 : 10:02:59
|
| When measuring performance never mix apples and oranges. 1. Your boss gets 2000 inserts per second is he using the same script as you? Same database as you? Do you both get to the server via the same network path?2. Allow the insert to be simple pure I/O, ie don't use functions like replicate3. Depending on your connection type you could be running all of your inserts in 1 transaction so it is continually building up until you are done. Be sure you isolate each insert into 1 transaction. If the transaction continues to build you could be dealing with issues of lock escalation and a growing temp_db to hold the data until the transaction is commited which is slowing down your performance.4. The speed of 5 inserts, each a seperate transaction is going to be slower than something like "Insert into table select field_value, field_value union all select field_value, field_value" etc. 5. So whatever the situation you and your boss have to do exactly the same thing or you'll get different results for a variety of other reasons.Per others, 1 query in 1 session won't give you your true performance capabilities. You have to be sure you know exactly what you are doing, be sure that whatever the query is, is optimized for what you are trying to do and isn't being hung up by locking issues, index issues etc, and you are running it from multiple connections, multiple machines if you are really trying to force your server to be I/O bound to measure it's performance. Hope it helps,Dalton |
|
|
|
|
|
|
|
|
No Of Inserts