How to make a memory / processor intensive test T-SQl ?

• David Bytheway

  SSC-Addicted

  Points: 457

  More actions

  June 4, 2007 at 7:53 am

  #172645

  In order to test SQL-2005 performance under various configurations etc. I need to create a T-SQL script which is server memory usage "heavy" but not I/O or processor intensive and another which is processor intensive and not either I/O or memry intensive.

  Any ideas?

  David Bytheway

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  June 4, 2007 at 8:55 am

  #710617

  for memory select a large number of rows and do an order by. something like 2 million rows should be a nice test

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  June 7, 2007 at 6:53 am

  #711396

  what do you hope to achieve and prove? The whole point about testing is that you must have  a result in mind.

  As you've not stated the edition and o/s or the physical server spec than you're not really giving us much to work on.

  Anyway for memory ( and I don't understand what you hope to prove ) , let's assume a server with 32Gb of ram ( chosen this amount specifically ) - you'll allocate between 28Gb and 30Gb to sql server, on 32bit you'll have no more than 2gb for pools and cache, the remainder being data cache - so you'll have to load from your database around 28gb of physical data to fill the cache - then you'll want to run only queries/operations which run from cache - so this would include loading the proc cache for example , you should be able to get around 1gb into this, now you want to make sure all the tests don't do physical io - ah so no updates or inserts - not a very good test unless your database is to be read only ?

  Sorry don't mean to preach, but you really need to define the tests and the expected results - then run your tests. Think you're following the wrong path.

  check out iosim and sqliosim for the subsystems - apologies if I've miss spelled the names.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/