Software guru, Inc

Real World Solutions

The Guru's Blog

Top Ten: SQL Server Optimizations Everyone Should Do.

Jan 11

Written by:
1/11/2013 5:30 PM  RssIcon

  1. Always use a 64 bit version of SQL on a 64 bit machine.
    1. Primarily because you want all the memory your server can eat.
  2. Data files and Log files should be on separate disks for any database of any real size
    1. I'm amazed at people who have multiple gigabyte databases for their applications and keep data and logs in the same folder on the same disk.
    2. This is probably the cheapest/easiest thing one can do to increase performance.
  3. There is almost no such thing as too much memory.
    1. To calculate how much memory you should be using per SQL Server Instance add up the size of all of your active user databases on that instance. 
      1. Data Files only, not logs.
      2. Only count unused space if you anticipate filling that space before your next budget cycle/opportunity to increase RAM.
      3. That's the base number.  The amount of RAM you need to fit all of your databases in RAM
    2. If you have the RAM, and it's within the limits of your edition of SQL Server, SQL will eventually load the entire database into RAM to optimize performance.
        1. Note that SQL Server 2012 (and 2008) Standard/BI/Web supports a maximum of 64 Gig per instance of SQL Server.  (Express and local is 1 GB)
        2. Enterprise will take as much memory as you have.
        3. If you can't fit the rather hefty cost of SQL Server Enterprise in your budget, but you have the RAM, consider using disk caching software to put disk files into RAM.
          1. Admittedly it gets a bit tricky to determine what files/drives to cache because, ideally, you don't want to double cache what SQL already has in RAM.  At least not from a read-caching perspective, if your goal is also write-caching, then not such a problem.
          2. SQL will load the most active data into RAM to its capacity, so determine what that is (Query Analyzer/Trace) and then look for the data files just outside of that and cache their drives/volumes.
          3. For this case, I'm primarily talking about read caching.  This is because the data that SQL loads into itself is also for read purposes.
          4. See below for discussion of using write-caching which also applies to the drives that SQL loads into memory.
    3. Next, you should have tempdb on a RAMDisk (more on this later) so if you have RAMDisk Software allocate enough RAM for the maximum anticipated size of your tempdb.
    4. If you have multiple instances of SQL Server on the same machine, repeat the last two calculations for each instance.  Each instance of Standard/BI/Web can take 64 GB.
    5. Add memory for any applications other than SQL Server that might be running on the machine.  This includes SQL Server Agent as well as Analysis and Reporting Services if you are using them.
    6. I then add at least 10 GB for the OS and some headroom.  
      1. Windows has this paranoia factor, it will suck up more memory for the OS if it thinks there might not be enough.
      2. Oddly by ensuring you have free memory, the OS will use less RAM.
    7. The sum of all of the above is the RAM you should have on your machine.  Up to the artificial Edition limits, and the maximum current sizes of your databases, SQL will use all it can get.  Anything less will decrease performance.
  4. Use a RAMDisk for tempdb.  
    1. Tempdb is recreated from scratch every time you start SQL Server, therefore it's absolutely perfect for a RAM disk.
    2. If you have the RAM, add the log file to the RAMDisk as well.  But don't short change yourself, if there is any possibility of overflow, make sure you have a second tempdb and log file on a real disk with plenty of space.
  5. Consider using a RAMDisk for Index filegroups.
    1. Putting non-clustered indexes on a RAMDisk will greatly improve performance
    2. Be absolutely sure that your RAMDisk either backs itself up to disk, or at least saves and loads itself from a real disk on shutdown and startup.
      1. This can significantly increase shutdown/startup/reboot times.
      2. I also recommend doing regular SQL backups of these filegroups.
    3. If the system crashes, recovering the database becomes a lot harder.  Which is why backups are very important.
    4. This is something I would only recommend for a full time/onsite DBA who knows what they are doing.
  6. It's all about the FILES and SPINDLES
    1. Here are some file rules to follow
      1. SQL Server is highly multi-threaded; however only a single thread can access any given file at any one time.
      2. Within some reason, you should have 1 file per filegroup for each processor on your machine.  (Up to the limit of your version of SQL Server)
      3. Of course this also depends on the application/database itself.  There isn't much sense in splitting a 10 Meg filegroup into 16 files for a 16 processor machine.   
    2. For spindles:
      1. You want multiple hard drives to avoid disk contention.  Spread your filegroups and files over multiple disks.  
      2. If your logical volumes contain multiple disks, treat the non-redundant disk count as the spindle count.
      3. This is actually where a smart SAN, that auto-optimizes physical disk usage to logical disk usage really shines.  Dell Equalogics are a great base level SAN for doing this.  With these you can create a single logical volume and put a large number of files on it and the Equalogic worries about spreading the data over multiple disks and multiple SAN units to avoid contention.
      4. Again, obviously there is some thought that should go into this in terms of what files can reside on the same disk, what files will be used at the same time, etc.
        1. As an example, you probably want tables or parts of tables that are being joined to be on different disks and in multiple files to maximize SQL's ability to pull data together.
        2. And of course, this becomes less important for READS if you have enough memory.  if you have enough memory that SQL can load the database into RAM, then for reads, disk contention is not so big an issue.  However, it will still help for those times when SQL needs to read the database into RAM.
        3. For writes disk contention is a very big issue and these rules can become more useful.
          1. Writes are a case where you can use third party Disk Caching software (note this is different from RAM Disk software) to stage hard disk writes.
          2. SSD's are the other critical place for Disk Write Caching.  You can improve the lifetime and decay rate for an SSD by optimizing writes via Disk Caching.
          3. Write Caching however is somewhat risky.  Be sure your machine is extremely stable and not prone to BSOD's for any reason; and be sure it's on a good and tested UPS, preferably with multiple circuits.
          4. If the machine crashes for any reason, you can loose some data and corrupt a database if you are using Disk Caching.
  7. TempDB
    1. TempDB is the temporary database where all "scratch" work is done.
    2. TempDB is deleted every time SQL Server is stopped.  It is recreated when SQL Server is started
    3. Up until about SQL 2005, there was an option to load SQL into RAM.  MS now says this is not needed.  OK.  Agree to disagree.
      1. In current versions of SQL you need to relocate the tempdb to a RAMDrive.
      2. This is done the same way as moving any database file.
        1. Create a RAM drive using your favorite sofware (e.g. SuperSpeed, DataRAM)
        2. Since it's a "normal drive" just move it using the alter database statement or the SQL Server Management Studio application and the Properties!Files of tempdb.
    4. TempDB is routinely used: 
      1. Automatically when there is not enough RAM to stage data.
      2. When explicitly invoke by a programmer using # or ## as a precedent to any table. (e.g. #mytable)
    5. For a programmer a RAM based TempDB is great
      1. TempDB (even on a fast disk) is generally faster than RAM variable tables (go figure) i.e. #mytable is generally faster than @mytable on most systems
      2. Use TempDB for processing/number crunching when the cost of a select statement in an select into, or insert * into #table select * statement to stage data into RAM is less than than doing multiple updates on a disk table.
        1. Specifically is the cost of 100 updates in a RAM TempDB on a 1 Million row disk based table, plus a single disk update to a table is less than the cost of a select statement (into RAM is fast) and then 100 updates on a RAM table plus a single update to a disk based table?
        2. If you can overcome the cost of the initial select Into a RAM or TempDB table, doing 100 updates there is far faster than to a disk table, then post the final results to the disk table.
        3. BTW TempDB, even on a real disk, albeit a fast one, is often faster than using table variables, although this depends on the memory available to the machine to some extent.
    6. If you aren't moving TempDB to RAM then:
      1. TempDB should be on a RAID 0 (striped) array.
      2. Split TempDB up into as many files as you have processors, but within reason based on disk space (all files need to be able to grow and you don't want them interfering with each other)
  8. When it doubt, use RAID 10 for data drives and for log drives.
    1. Data drives should always be on fast redundant storage.
    2. Log files should be on very fast storage.  If you are doing regular log backups, then use a striped array.
  9. If you are using SQL Server Enterprise: Use Compression
    1. SQL Server Enteprise supports both page and row compression of data within tables.
    2. Data is compressed before being written to disk and uncompressed after reading.
    3. Yes, this results in some overhead, but given modern CPU horsepower, this is trivial compared to what you save by reading and writing to disk.  Disk reads and writes are a huge bottleneck for most servers.
  10. If you are using Enterprise: Don't fear Partitioning.
    1. Partitioning is your friend.  Yes, you have to understand your data before partitioning, but no more so than when manually creating indexes.
    2. A combination of partitioning and good spindle management can radically improve the performance of large sequential data tables.
    3. Not all applications and data benefit from partitioning, you need to know your data, however if there is a significantly sequential key/index field on your database that's useful for searching on?  Use that.
    4. Time series data is the number one choice in my book for data partitioning.  
      1. I love to partition trade data.  
      2. It's the simplest way to make a 2+ Terabyte table searchable, short of getting a chunk the US Defense budget.  
      3. (OK, we may need to talk about your Data Model if you have a single 2+ Terabyte table...but it does happen)

Post Script:  


I've had some inquiries into what I'm talking about in terms of disk caching software.  Some people asked if I was talking about adjusting the Windows internal caches, etc.  I'm actually talking about third party RAM disk and Disk Caching software.

I've been working with SuperSpeed SuperCache and RAMDiskPlus for well over 10 years and they're the first people I go to for an Enterprise solution.  There are a few RAM disk providers out there, (but very few Disk Caching) and most of those are geared to the home and power user, not the enterprise market.  Superspeed has been designing both client and server solutions for a long time (longer than the 10-12 years I've used them) they tune their products for SQL and Exchange usage as well as VMWare and Hyper-V.

8 comment(s) so far...


Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Why just not using a physical RAM drive, like RAMsan?
(heck, didn't even know they where bought by IBM in 2012 before looking for them right now)

By Vomitorium on   11/27/2015 3:22 AM
Gravatar

Re: Top Ten: SQL Server Optimizations - RAM Disk

Hello
We're in process of selecting a RAM Disk vendor since our application is very tempdb intensive.
I posted questions on forums (dba.stackexchange.com/questions/160948/sql-tempdb-on-ram-disk), to see who else was using it, but didn't get much response.

Superspeed doesn't seem to support their RAM Disk product anymore, based on comments and articles on internet.
Do you know if they do, or any other WHQL certified vendor you recommend ?

By sql d-_-ba on   1/18/2017 11:36 AM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Hi,

RamDisk+ 11 is still on their website, the product page was last updated a year ago, however, it's a very stable product. I am pretty sure you can buy it.

What I believe to be the case from the last time I talked to them is that they emphasize SuperCache over the pure RAM disk because if you cache 100% of the drive in memory, it works exactly like a ram disk, but won't be lost, thus it's a more versatile product.

In fact if I recall correctly, they told me it's the same program/code, they just disable the write to disk function for RAMDisk+ and since they disable that function, they can charge you less.

In a server environment, RAMDisk is ideal for tempdb, but not so useful for anything else due to data loss. Thus they recommend spending a little bit more for SuperCache, which you can then use both for a RAMDisk and for partial caching of other data files.

So for example say I have a DB server with 256 Gb RAM and use SuperCache I create a volume t for tempdb and make it 40 GB and set it to 100% Cache with long delayed writes (since we don't care about that for tempdb)

Now for my data drives say I want to cache 50% of my MDF's which are 100 Gb, with SuperCache I can do that and thus move my most accessed data into memory. I am now using 160 GB out of 256 and leave the rest for SQL Server.

If I only had RAMDisk, I couldn't cache the data files (at least not safely) so for $100 more you get something more versatile.

Now, that being said, if you have a lot of memory, SQL Server x64 will eventually drag all of your datafiles into memory anyway, so that is not quite so critical.

Where using Super Cache is more important is when you can't fit all your data into memory, and you want to control what data is loaded into memory, that's where SuperCache's selective volume caching comes into play.

Now, maybe you don't need that at all. You can fit all your data into RAM on your SQL Server, and you really only care about tempdb. In that case, RAMDisk+ would be fine and you can save yourself some money because you'll never need to cache a disk.

But otherwise the functionality in terms of a RAM disk is the same, you just have more options with SuperCache so they say "buy this instead, it's a better deal/product"

By Jerry Langland on   1/18/2017 11:53 AM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Yes, we were almost about to purchase RAMDisk+ from Superspeed, but their support process seems non-existent.
I tried calling their number 1-978-443-5106 extension 3. it says their 'voicemail box is full'. I emailed them at support@superspeed.com , but no response.

Also found this article - www.ripoffreport.com/r/superspeed-software-inc/millbrook-park-sudbury-massachusetts-01776/superspeed-software-inc-ramdisk-plus-superspeedcom-fraudulent-practices-ramdisk-plus-bew-1236354

We only want WHQL certified vendors, which is why we tried them first, but as an Enterprise customer, we expect full support, so lack of it would be a show-stopper.

By sql d-_-ba on   1/19/2017 9:35 AM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Hmm,

Very odd. I never had any trouble getting support from them. Now to be fair, I moved on from the last place I used it, so I haven't talked to them in 3 or 4 years.

It sounds like they may have gone belly up. I will try contacting a guy I know there, who had been there from the beginning.

By Jerry Langland on   1/19/2017 10:12 AM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Hi,

So I emailed the guy I knew there and heard nothing back. Making me think Superspeed is toast.

What's weird those is that I am still getting advertising links from them, after visiting the site.

I have to wonder how those ads are paid for?

Very bizarre.

I am going to look for some alternative suggestions.

By Jerry Langland on   1/30/2017 2:56 PM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

I've been using soft perfect's ramdisks for awhile - Overall, it was one of the faster ramdrive there a couple years back. These days, I heard good things about AMDs ram drive too but I haven't tried it so I can't say for sure.

By Jean-Luc Ferland on   7/18/2017 10:00 PM
Gravatar

Re: Top Ten: SQL Server Optimizations Everyone Should Do.

Oh and I forgot to mention, pcie nvme drives might be a good option now days. 😁

By Jean-Luc Ferland on   7/18/2017 10:05 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
You must be logged in and have permission to create or edit a blog.