Table of Contents

Search

  1. Preface
  2. Part 1: Introduction
  3. Part 2: Samples and Examples
  4. Part 3: Tools and Utilities
  5. Part 4: SDK Resources

Benchmark for Microsoft SQL Server

Benchmark for Microsoft SQL Server

Compare the results of the Microsoft SQL Server database performance test against the following Informatica benchmark for performance tests run on the database machine:
07-20-2015 09:09:12:823 : *********** TestIO Utility version 1.4 ********************** 07-20-2015 09:09:12:823 : Number of java threads : 10 07-20-2015 09:09:12:947 : Database Type : SqlServer 07-20-2015 09:09:12:947 : Host Name : localhost 07-20-2015 09:09:12:947 : Port : 1433 07-20-2015 09:09:12:947 : Database Name : cmx_ors 07-20-2015 09:09:12:947 : User Name : cmx_ors 07-20-2015 09:09:12:947 : Password : ********** 07-20-2015 09:09:12:947 : JDBC Url generated : jdbc:sqlserver://localhost:1433;databaseName=cmx_ors;user=cmx_ors;password=!!cmx!! 07-20-2015 09:09:12:947 : Number of records: 60000000 07-20-2015 09:09:12:947 : ************************************************************* 07-20-2015 09:09:12:947 : before 10 JDBC connections .... 07-20-2015 09:09:13:400 : after 10 JDBC connrections .... 07-20-2015 09:09:13:415 : JDBC driver version is: 4.0.2206.100 07-20-2015 09:09:13:415 : Temp Table name - c_repos_test_841 07-20-2015 09:09:13:415 : DML operations are going to be run in multiple threads with thread count = 10 07-20-2015 09:09:13:415 : Start create table and indexes 07-20-2015 09:09:13:415 : CREATE TABLE c_repos_test_841 ( id int NOT NULL, create_date date, last_update_date date, column1 varchar(255), column2 varchar(255), column3 varchar(255), column4 varchar(255), column5 varchar(255), column6 varchar(255), column7 varchar(255), column8 varchar(255), column9 varchar(255), column10 varchar(255), column11 varchar(255), column12 varchar(255), column13 varchar(255), column14 varchar(255), column15 varchar(255), column16 varchar(255), column17 varchar(255), column18 varchar(255), column19 varchar(255), column20 varchar(255), column21 varchar(255), column22 varchar(255), CONSTRAINT [PK_c_repos_test_841] PRIMARY KEY CLUSTERED (id)) 07-20-2015 09:09:13:431 : CREATE INDEX c_repos_test_841_index1 on c_repos_test_841(column1) 07-20-2015 09:09:13:431 : CREATE INDEX c_repos_test_841_index2 on c_repos_test_841(column2, column3, column4) 07-20-2015 09:09:13:431 : Done create table and indexes 07-20-2015 09:09:13:431 : Inserting rows in threads begin.... 07-20-2015 09:09:13:431 : Start insert rows through thread number 0 07-20-2015 09:09:13:431 : Start insert rows through thread number 1 07-20-2015 09:09:13:431 : Start insert rows through thread number 2 07-20-2015 09:09:13:431 : Start insert rows through thread number 3 07-20-2015 09:09:13:431 : Start insert rows through thread number 4 07-20-2015 09:09:13:431 : Start insert rows through thread number 5 07-20-2015 09:09:13:431 : Start insert rows through thread number 6 07-20-2015 09:09:13:431 : Start insert rows through thread number 7 07-20-2015 09:09:13:431 : Start insert rows through thread number 8 07-20-2015 09:09:13:431 : Start insert rows through thread number 9 07-20-2015 09:59:04:050 : Done insert rows through thread number 9 07-20-2015 09:59:22:130 : Done insert rows through thread number 3 07-20-2015 09:59:26:420 : Done insert rows through thread number 7 07-20-2015 09:59:43:284 : Done insert rows through thread number 5 07-20-2015 10:00:00:226 : Done insert rows through thread number 1 07-20-2015 10:00:07:963 : Done insert rows through thread number 6 07-20-2015 10:00:09:196 : Done insert rows through thread number 4 07-20-2015 10:00:32:892 : Done insert rows through thread number 8 07-20-2015 10:00:39:428 : Done insert rows through thread number 2 07-20-2015 10:01:45:978 : Done insert rows through thread number 0 07-20-2015 10:01:45:978 : Inserting rows in threads end.... 07-20-2015 10:01:45:978 : Updating rows in threads begin.... 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 0 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 1 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 2 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 3 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 4 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 5 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 6 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 7 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 8 07-20-2015 10:01:45:978 : Start update rows for non-indexed columns through thread number 9 07-20-2015 10:01:57:132 : Done update rows through thread number 3 07-20-2015 10:01:57:195 : Done update rows through thread number 6 07-20-2015 10:01:57:195 : Done update rows through thread number 1 07-20-2015 10:01:57:210 : Done update rows through thread number 7 07-20-2015 10:01:57:226 : Done update rows through thread number 5 07-20-2015 10:01:57:241 : Done update rows through thread number 2 07-20-2015 10:01:57:257 : Done update rows through thread number 8 07-20-2015 10:01:57:257 : Done update rows through thread number 9 07-20-2015 10:01:57:351 : Done update rows through thread number 4 07-20-2015 10:01:57:444 : Done update rows through thread number 0 07-20-2015 10:01:57:444 : Updating rows in threads end.... 07-20-2015 10:01:57:444 : Updating rows in threads begin.... 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 0 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 1 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 2 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 3 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 4 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 5 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 6 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 7 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 8 07-20-2015 10:01:57:444 : Start update rows for indexed columns through thread number 9 07-20-2015 10:02:14:027 : Done update rows through thread number 3 07-20-2015 10:02:14:183 : Done update rows through thread number 5 07-20-2015 10:02:14:199 : Done update rows through thread number 9 07-20-2015 10:02:14:199 : Done update rows through thread number 1 07-20-2015 10:02:14:230 : Done update rows through thread number 6 07-20-2015 10:02:14:230 : Done update rows through thread number 4 07-20-2015 10:02:14:245 : Done update rows through thread number 2 07-20-2015 10:02:14:261 : Done update rows through thread number 8 07-20-2015 10:02:14:261 : Done update rows through thread number 7 07-20-2015 10:02:14:292 : Done update rows through thread number 0 07-20-2015 10:02:14:292 : Updating rows in threads end.... 07-20-2015 10:02:14:292 : Selecting rows in threads begin.... 07-20-2015 10:02:14:292 : Start select rows through thread number 0 07-20-2015 10:02:14:292 : Start select rows through thread number 1 07-20-2015 10:02:14:292 : Start select rows through thread number 2 07-20-2015 10:02:14:292 : Start select rows through thread number 3 07-20-2015 10:02:14:292 : Start select rows through thread number 4 07-20-2015 10:02:14:292 : Start select rows through thread number 5 07-20-2015 10:02:14:292 : Start select rows through thread number 6 07-20-2015 10:02:14:292 : Start select rows through thread number 7 07-20-2015 10:02:14:292 : Start select rows through thread number 8 07-20-2015 10:02:14:292 : Start select rows through thread number 9 07-20-2015 10:02:25:009 : Done select rows through thread number 3 07-20-2015 10:02:25:025 : Done select rows through thread number 1 07-20-2015 10:02:25:072 : Done select rows through thread number 7 07-20-2015 10:02:25:072 : Done select rows through thread number 9 07-20-2015 10:02:25:165 : Done select rows through thread number 6 07-20-2015 10:02:25:181 : Done select rows through thread number 0 07-20-2015 10:02:25:197 : Done select rows through thread number 2 07-20-2015 10:02:25:228 : Done select rows through thread number 4 07-20-2015 10:02:25:259 : Done select rows through thread number 8 07-20-2015 10:02:26:679 : Done select rows through thread number 5 07-20-2015 10:02:26:679 : Selecting rows in threads end.... 07-20-2015 10:02:26:679 : Deleting rows in threads begin.... 07-20-2015 10:02:26:679 : Start delete rows through thread number 0 07-20-2015 10:02:26:679 : Start delete rows through thread number 1 07-20-2015 10:02:26:679 : Start delete rows through thread number 2 07-20-2015 10:02:26:679 : Start delete rows through thread number 3 07-20-2015 10:02:26:679 : Start delete rows through thread number 4 07-20-2015 10:02:26:679 : Start delete rows through thread number 5 07-20-2015 10:02:26:679 : Start delete rows through thread number 6 07-20-2015 10:02:26:679 : Start delete rows through thread number 7 07-20-2015 10:02:26:679 : Start delete rows through thread number 8 07-20-2015 10:02:26:679 : Start delete rows through thread number 9 07-20-2015 10:02:50:281 : Done delete rows through thread number 3 07-20-2015 10:02:50:359 : Done delete rows through thread number 1 07-20-2015 10:02:50:359 : Done delete rows through thread number 9 07-20-2015 10:02:50:359 : Done delete rows through thread number 6 07-20-2015 10:02:50:375 : Done delete rows through thread number 5 07-20-2015 10:02:50:391 : Done delete rows through thread number 4 07-20-2015 10:02:50:391 : Done delete rows through thread number 2 07-20-2015 10:02:50:406 : Done delete rows through thread number 7 07-20-2015 10:02:50:406 : Done delete rows through thread number 0 07-20-2015 10:02:50:422 : Done delete rows through thread number 8 07-20-2015 10:02:50:422 : Deleting rows in threads end.... 07-20-2015 10:02:50:422 : Temp Table2 name - c_repos_test_5923 07-20-2015 10:02:50:422 : Start create table as select and indexes 07-20-2015 10:02:50:422 : SELECT * INTO c_repos_test_5923 from c_repos_test_841 07-20-2015 10:06:01:195 : ALTER TABLE c_repos_test_5923 ADD CONSTRAINT c_repos_test_5923_id UNIQUE (id) 07-20-2015 10:06:15:827 : CREATE INDEX c_repos_test_5923_index1 on c_repos_test_5923(column1) 07-20-2015 10:06:37:917 : CREATE INDEX c_repos_test_5923_index2 on c_repos_test_5923(column2, column3, column4) 07-20-2015 10:07:01:988 : Done create table as select and indexes 07-20-2015 10:07:01:988 : Start drop table c_repos_test_841 07-20-2015 10:07:02:066 : Done drop table c_repos_test_841 07-20-2015 10:07:02:066 : Start drop table c_repos_test_5923 07-20-2015 10:07:02:097 : Done drop table c_repos_test_5923 07-20-2015 10:07:02:097 : ************************** Summary ************************** 07-20-2015 10:07:02:097 : All DML operations ran in multiple threads with thread count = 10 07-20-2015 10:07:02:097 : All the timings are in hour:min:second:millisecond 07-20-2015 10:07:02:097 : Total distinct database interactions: 9 07-20-2015 10:07:02:097 : 0- operation type: CREATE, count:1 07-20-2015 10:07:02:097 : 1- operation type: INSERT, count:60000001 07-20-2015 10:07:02:097 : 2- operation type: UPDATE, count:200000 07-20-2015 10:07:02:097 : 3- operation type: UPDATE_INDEX, count:200000 07-20-2015 10:07:02:097 : 4- operation type: SELECT, count:200000 07-20-2015 10:07:02:097 : 5- operation type: DELETE, count:200000 07-20-2015 10:07:02:097 : 6- operation type: CTAS, count:1 07-20-2015 10:07:02:097 : 7- operation type: DROP, count:1 07-20-2015 10:07:02:097 : 8- operation type: DROP, count:1 07-20-2015 10:07:02:097 : Total time for the test: 0:57:48:682.0 07-20-2015 10:07:02:097 : Number of operations: 60800005 07-20-2015 10:07:02:097 : Average time for all operations: 0:0:0:0.05705068609780542 07-20-2015 10:07:02:097 : Average transactions/second for all operations: 17528 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of create operations: 1 07-20-2015 10:07:02:097 : Total time for create operations: 0:0:0:16.0 07-20-2015 10:07:02:097 : Average time for all create operations: 0:0:0:16.0 07-20-2015 10:07:02:097 : Average transactions/second for all create operations: 62 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of create table as select operations: 1 07-20-2015 10:07:02:097 : Total time for create table as select operations: 0:4:11:566.0 07-20-2015 10:07:02:097 : Average time for all create table as select operations: 0:4:11:566.0 07-20-2015 10:07:02:097 : Average transactions/second for all create table as select operations: 0 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of drop operations: 2 07-20-2015 10:07:02:097 : Total time for drop operations: 0:0:0:109.0 07-20-2015 10:07:02:097 : Average time for all drop operations: 0:0:0:54.5 07-20-2015 10:07:02:097 : Average transactions/second for all drop operations: 18 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of insert operations: 60000001 07-20-2015 10:07:02:097 : Total time for insert operations: 0:52:32:547.0 07-20-2015 10:07:02:097 : Average time for all insert operations: 0:0:0:0.05254244912429251 07-20-2015 10:07:02:097 : Average transactions/second for all insert operations: 19032 07-20-2015 10:07:02:097 : Total bytes of data inserted: 1158196632 07-20-2015 10:07:02:097 : Average bytes of data inserted/second (bytes/second): 367384 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of update operations in non-indexed table: 200000 07-20-2015 10:07:02:097 : Total time for update operations in non-indexed table: 0:0:11:466.0 07-20-2015 10:07:02:097 : Average time for all update operations in non-indexed table: 0:0:0:0.05733 07-20-2015 10:07:02:097 : Average transactions/second for all update operations in non-indexed table: 17442 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of update operations in indexed table: 200000 07-20-2015 10:07:02:097 : Total time for update operations in indexed table: 0:0:16:848.0 07-20-2015 10:07:02:097 : Average time for all update operations in indexed table: 0:0:0:0.08424 07-20-2015 10:07:02:097 : Average transactions/second for all update operations in indexed table: 11870 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of select operations: 200000 07-20-2015 10:07:02:097 : Total time for select operations: 0:0:12:387.0 07-20-2015 10:07:02:097 : Average time for all select operations: 0:0:0:0.061935 07-20-2015 10:07:02:097 : Average transactions/second for all select operations: 16145 07-20-2015 10:07:02:097 : 07-20-2015 10:07:02:097 : Number of delete operations: 200000 07-20-2015 10:07:02:097 : Total time for delete operations: 0:0:23:743.0 07-20-2015 10:07:02:097 : Average time for all delete operations: 0:0:0:0.118715 07-20-2015 10:07:02:097 : Average transactions/second for all delete operations: 8423 07-20-2015 10:07:02:097 : *************************************************************

0 COMMENTS

We’d like to hear from you!