Database write speed - please help

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database write speed - please help

Author

Topic

iamagui
Starting Member

5 Posts

Posted - 2011-05-04 : 12:03:09

Hi,
I am newbie. I want to find out the database write speed. I have a MSSQL 2008 R2 on my desktop and I use Visual Studio to execute simple queries. I want to find out the write speed as the rows and columns increases to determine the optimum speed. Please help. Kindly let me know if I am not clear and need more information, thanks in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-04 : 13:24:29

Using perfmon, you'll want to monitor avg disk sec/Write

iamagui
Starting Member

5 Posts

Posted - 2011-05-04 : 17:36:45

Thanks Russell. But I am looking for some query to create data and load to the database and check the time. Right now, I am doing
{
1. DateTime
2. Insert few rows to the table in database
3. DateTime
}

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-04 : 19:29:44

But (1) you already have that apparently and (2) it doesn't prove anything -- unless that query is the only thing happening on the server. Plus, that isn't measuring write speed. It's measuring query execution duration.

Better to measure reads, writes and cpu time for the query if you want it to be query specific. You can do this via profiler if you want, or just set statistics io and statistics time on and execute it a few times.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-05 : 01:44:34

The only thing that inserting a few rows into a table measures is the speed of inserting a few rows into that table. It's not measuring time to write to disk (changes are made in memory) and it's not a time measurement that can be extrapolated to anything else

--
Gail Shaw
SQL Server MVP

iamagui
Starting Member

5 Posts

Posted - 2011-05-05 : 12:59:07

Thanks Russell and Gail, I will get back to you guys after running few things.

iamagui
Starting Member

5 Posts

Posted - 2011-05-09 : 15:12:46

Hi Russell and Gail,

I am trying to find the number of rows written per second. What I am doing now.

1. Imported an excel sheet, say test.xls, to the db according to http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx
2. Deleted all the data in the db

in C#

3. At start, write the time to a text file, say timeDB.txt
4. Bulk copy test.xls to db according to http://sujitkumar.wordpress.com/2008/09/06/export-excel-data-into-sql-server-using-c/
5. At finish, write the time to the same text file
6. Deleted all the data in the db
7. Repeat steps from 3-6 by increasing number of rows.

If you could provide me some tips/advice/suggestions, I would appreciate it, thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-09 : 19:00:28

I'd suggest monitoring Disk Writes/Bytes per second instead.

Rows per second is arbitrary and somewhat meaningless.

If you feel you must have it, know that it will change from time to time, but you can submit it in batches of known # of rows and use profiler to capture duration.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-10 : 04:23:17

In addition to what Russell said, rows/sec is unrelated to disk writes, as the rows are inserted into cache and flushed to disk sometime later.

--
Gail Shaw
SQL Server MVP

iamagui
Starting Member

5 Posts

Posted - 2011-05-11 : 12:45:46

Many thanks again Russell and Gail, I will let you know if I have any questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources