The best configuration to new server with 2 SAS disks

• romulocpd

  Grasshopper

  Points: 12

  More actions

  December 22, 2010 at 9:32 am

  #144036

  Hello.

  Since last 6 months I've got several problems with performance in my SQL Server. My server is a Core i7 with 8 GB RAM and disk SATA 500 GB. My database's size is 20 GB. I've got about 150 users.

  So, the server is very very slow.

  Now I have to change my database to a new Dell server. The configuration is Dell Xeon, 16 GB RAM, Hard disks: 1.5 TB SATA 2 and two disks SAS 300 GB. The SAS discks are in the same controller.

  I am with several douts about how can I configure the filegroup in the new server. Reading some sites and tutoriais on internet, I saw that is the best common practice let TempDB and Logfiles in a disk SAS and all of tables and indexes in a other SAS disk. It's true?

  In another place I read that is the best practice let TempDB, Logfiles and some tables in a SAS disk and the most used tables, or the bigger tables, in another SAS disk.

  So, I need performance and I don't know how I must distribute my files on disks.

  Thanks a lot!!

  Romulo

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  December 23, 2010 at 9:16 am

  #1265982

  have you checked for missing indexes?

  i dump my physical_stats index DMV to a table and run the missing index DMV's once a day and then create a report with the data to see which tables are missing indexes and on which columns

• romulocpd

  Grasshopper

  Points: 12

  More actions

  December 23, 2010 at 10:39 am

  #1266016

  Hi,

  Yesterday I get some scripts and I saw exatly what you said. Every day I'm looking for problems. Thanks!

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  December 24, 2010 at 8:53 am

  #1266214

  romulocpd (12/22/2010)

  ---

  Hello.

  Since last 6 months I've got several problems with performance in my SQL Server. My server is a Core i7 with 8 GB RAM and disk SATA 500 GB. My database's size is 20 GB. I've got about 150 users.

  So, the server is very very slow.

  Now I have to change my database to a new Dell server. The configuration is Dell Xeon, 16 GB RAM, Hard disks: 1.5 TB SATA 2 and two disks SAS 300 GB. The SAS discks are in the same controller.

  I am with several douts about how can I configure the filegroup in the new server. Reading some sites and tutoriais on internet, I saw that is the best common practice let TempDB and Logfiles in a disk SAS and all of tables and indexes in a other SAS disk. It's true?

  In another place I read that is the best practice let TempDB, Logfiles and some tables in a SAS disk and the most used tables, or the bigger tables, in another SAS disk.

  So, I need performance and I don't know how I must distribute my files on disks.

  Thanks a lot!!

  Romulo

  1) Unless you mirror the SAS drives, you have no redundancy and could lose data with a single drive failure.

  2) It is hard to offer guidance without knowing more about your app, especially the tempdb usage paterns. My guess would be tempdb data on SATA, data and indexes on SAS 1 and all tlogs on SAS 2.

  3) If you could double the memory to 32GB your entire database would always sit in RAM and your perf problems would be zippo assuming no other databases on the server.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service