| Why is it important for your pages to load fast you | | | | Typically when moving from a single server to |
| might ask. The answer is easy; if your pages load | | | | multiple servers you will put in a new server and only |
| slowly you will loose sales. From a consumer | | | | put your database on that new server. |
| perspective you have a fraction of a second to | | | | Next I'll show you a configuration with three servers |
| show activity. Your page doesn't have to load | | | | that has worked out exceedingly well for me: |
| completely but they certainly need to see some | | | | Each server has SQL and IIS installed. We have one |
| activity right away. Consumers are impatient and | | | | master server and two slave servers. The master |
| have been trained by the major sites that pages | | | | server has our entire database on it, the websites |
| start displaying immediately. If you don't have fast | | | | can write orders to this database and our staff can |
| loading pages, most consumers will go to the next | | | | process orders using this database and we keep |
| site. It's not just consumers that value speed, | | | | vendor inventory records up to date on this server. |
| major search engines have stated that it is important | | | | The master server is the only server that gets |
| for pages to load fast if you want them indexed. | | | | backed up. The two slave servers are setup so |
| The faster page loads, the more pages you will get | | | | that only the parts of the websites and database |
| indexed. Some search engines have come out and | | | | that are needed for the public to use our websites |
| stated that they will even downgrade your paid | | | | are replicated. Therefore any change that is made |
| search marketing if your pages load slowly. What | | | | to the master server is replicated to the slave |
| can you do to increase page load speed? Below are | | | | servers within 15 min. of the change. Because the |
| the techniques I used to successfully decrease the | | | | public websites write back to the master server the |
| time it takes to load web pages. | | | | slave servers can run in a "read only" mode which |
| | | | | allows them to process an enormous number of |
| Web Server Optimization | | | | requests very fast. Because our master server only |
| | | | receives writes from the public websites when |
| SQL Server OptimizationThe first thing to check is | | | | orders are being taken, it only has to handle a small |
| your disk using PerfMon (Windows Performance | | | | amount of our public traffic. As our traffic grows |
| Monitor). Use this to determine if your physical disk | | | | we simply add more read only servers. In our case |
| is keeping up. Using PerfMon, select the PhysicalDisk | | | | we have enough websites that we simply split them |
| object, Avg. Disk Queue Length counter, then the | | | | up between servers. If however you have a single |
| instance for the drive your SQL database is installed | | | | website, you can still load balance using a DNS round |
| on. This indicator count will vary based on if the | | | | robin or in more extreme situations an appliance like |
| drive you are monitoring is part of an array but it's | | | | F5 Big IP which can perform smart load balancing |
| best to keep the average below 1 if possible. See | | | | based on current server load. |
| Microsoft's documentation on this for more | | | | Another popular method is to setup multiple SQL |
| information especially when calculating for drive | | | | servers (assuming this is the main bottleneck) with |
| arrays. | | | | separate data. For instance if you keep your |
| Next you want to check SQL memory usage. By | | | | images in SQL, you may want to move them to |
| right clicking the server in Enterprise Manager and | | | | they're own server then make a connection to one |
| selecting properties you can view the Memory tab. | | | | server for some of your data and to the other |
| Use this tab to give as much memory as you can | | | | server for retrieving images. |
| afford to SQL. The more memory you give the | | | | Web Page Design |
| SQL the more data it can cache, thus returning | | | | Java Script and CSS Files |
| results faster. Does your server have more than | | | | Putting Java Script and CSS in files instead of inline |
| 2GB memory? If so use this guide to configure | | | | allows your browser to cache them instead of |
| your server to see all that available memory then this | | | | reading them each time. Since your browser has to |
| guide to configure SQL to see more available | | | | download each file separately, do your best to keep |
| memory. To make sure SQL is using available | | | | all your Java Script in a single file and do the same |
| memory don't use task manager as it may report | | | | with CSS |
| incorrectly. Instead use PerfMon Process Object, | | | | Image OptimizationOptimizing images is very |
| Working Set Counter, sqlservr Instance. Give your | | | | important in page load time. Convert your images |
| server time to build up its cache after restarting | | | | to PNG. Don't use HTML to resize your images, |
| before checking this counter. | | | | resize and optimize them prior to uploading. Try to |
| You want to make sure your CPU can keep up. | | | | keep your larger images to a file size under 30K if |
| Caution this counter can be misleading. Although you | | | | possible. There are some advanced optimizations |
| may be recording sustained high CPU utilization this | | | | that can be done with inline images, sprites, and |
| may not mean you need a faster CPU, the lack of | | | | image maps but I am not experienced with them for |
| adequate disk speed and memory may be artificially | | | | the most part and in many instances management of |
| increasing CPU utilization. If CPU utilization is | | | | this can be a nightmare. Additionally there may be |
| averaging over 80% for an extended period of time | | | | browser support issues. |
| and you have already ruled out memory and disk as | | | | CachingCaching is one way to drastically reduce page |
| an issue you may need to upgrade your CPU(s). | | | | load time, especially for popular pages. Even if your |
| Use PerfMon to track CPU utilization using the | | | | page is dynamic, you may be able to cache it for 4 |
| Processor Object, % Processor Time Counter, and | | | | hours or at least 15 min. |
| applicable instance if you have more than one CPU. | | | | You can enable browser caching by using the HTML |
| If your server has more than one CPU and you can | | | | Expires heading for any pages that can cache. This |
| afford to let SQL use more than one, right click on | | | | type of caching tells the visitors browser that if it |
| the server in Enterprise Manager, select Properties, | | | | returns to this exact page it's doesn't need to go |
| and then the Processor tab. This will allow you to | | | | back to the web server and retrieve the page |
| select multiple or all CPUs for SQL utilization. | | | | again. This will help when people are browsing your |
| Bandwidth is a little more obvious but still needs to be | | | | site and come back to the same page multiple |
| mentioned. If you have sustained high bandwidth | | | | times. If the page is more static, like an article page |
| utilization this will become a bottleneck causing | | | | for instance, you may be able to set it to cache for |
| slowdowns. Bandwidth utilization can be a bit harder | | | | 30 days. This will allow the same person to go to |
| to check. If you are using a hosting company they | | | | that page different days without having to hit your |
| should be able to tell you (typically with some type | | | | server. The downfall of this caching is that it only |
| of real time monitor that you can log into) what you | | | | helps if people visit your page more than once within |
| are averaging with bandwidth as well as spikes in | | | | the caching time. |
| bandwidth. You will want to make sure you have | | | | Another type of caching is server side page |
| plenty of available bandwidth from your hosting | | | | caching. This allows you to cache a page on the |
| company especially during the busy part of your | | | | server instead of the browser, therefore when |
| day. If your hosting company cannot provide these | | | | defining the caching time, anyone that requests the |
| reports you can utilize a hardware or software | | | | page inside of the cache time will receive the cached |
| sniffer to determine bandwidth usage. The key is | | | | page. Server side caching will allow your web server |
| to make sure your connection can handle much more | | | | to cache the page in memory (given there are |
| than you typically use. If you are averaging 80% | | | | enough memory resources available) which means it |
| utilization you should consider upgrading your | | | | does not have to go to the disk to retrieve the page |
| connection. | | | | or query the database. Let's say for example the |
| Now that you have checked the major hardware | | | | front page of your website has a tree view menu |
| bottlenecks put your DBA hat on and make sure | | | | that makes multiple database queries to build the |
| your indexes are up to date. If you're not an | | | | page. If this page is requested many times an hour |
| experienced DBA that knows the best way to | | | | it will generate a lot of resource strain on your |
| manage your indexes, use SQL Profiler (Tools, SQL | | | | servers. If you set this page to cache for 15 min. |
| Profiler from Enterprise Manager). Create a new | | | | you now only create that resource strain once every |
| trace and select the SQLProfilerTuning template. | | | | 15 min. allowing your server to respond better to |
| Make sure the time you record is long enough to | | | | other requests and your page will display much |
| collect data on regular activity. Additionally if you | | | | faster. Of all the things you can do to make your |
| can generate additional traffic (especially traffic that | | | | site faster, this may have the biggest impact. |
| causes slowdowns) you will want to do as much of | | | | Similar to server side page caching is partial page |
| this as possible while collecting data. Once you have | | | | caching. This is a little more complicated but allows |
| finished collecting data, click Tools, Index Tuning | | | | you to cache parts of your page. Let's use the |
| Wizard. Use the profile you just created and make | | | | example above again but this time the criteria |
| sure to select the database in question. When | | | | mandates that we can't cache everything in the |
| finished running apply any changes suggested. You | | | | page. In this case we may want to just cache the |
| may need to run this multiple times until there are not | | | | menu as the menu is relatively static and is a large |
| recommended changes. Additionally as you make | | | | resource drain on the servers and slows down page |
| changes to your database such as adding new fields | | | | delivery. |
| you will want to run this entire process again to find | | | | Separate Domains |
| and apply new recommendations. I like to run this | | | | Some browsers will open multiple connections to |
| process on the regular basis just make sure | | | | download files simultaneously but will have a limit to |
| everything is running smooth. As a side note, make | | | | how many per domain it will concurrently download. |
| sure every table in your database has a primary | | | | To get around this you could move files like Images, |
| key. | | | | CSS, and Java Script to separate domains thus |
| Once you have optimized your indexes run SQL | | | | allowing the browser to download more files |
| Profiler again to check what kind of activity you are | | | | simultaneously. |
| having on your SQL server. Do you have long running | | | | Progressive Rendering |
| queries that could be changed to run more | | | | Progressive rendering is when you visit a page and |
| efficiently? Do you have queries that are being run | | | | you start seeing parts of the page display right away |
| over and over that you could cache inside your | | | | instead of displaying the entire page at once. This |
| website application? As you make upgrades and | | | | process gives the user feedback right away and |
| additions to your web pages you many times will add | | | | they know more is coming. If your page takes a |
| extra queries to your database. Go through your | | | | second or so before it renders to the browser, the |
| pages and find out if you can combine queries to | | | | user may thing there is a problem and instead of |
| return information with one query instead of two. If | | | | waiting will move to a different page. Progressive |
| you have to make multiple queries, try to make one | | | | rendering can also give the illusion that the page is |
| connection to the database, and then use that same | | | | loading faster even though it takes the same amount |
| connection for all the queries. This way you are not | | | | of time to completely render the entire page. To |
| making repetitive connections to the database one | | | | progressive render your page you may need to tell |
| after the other. Making a database connection can | | | | your back end programming language to pass parts |
| be time consuming and resource intensive. | | | | of your page as they render. Additionally with |
| The way you partition your database across drives | | | | components like an ASP.NET Gridview you cannot |
| on your SQL server can help if you are suffering | | | | progressively render that component, instead it will |
| from high disk utilization. The following configuration | | | | render than entire object all at once. From an HTML |
| has worked well for me note that each partition is on | | | | perspective you will want to make sure style sheets |
| a separate physical drive: | | | | are loaded in the page head and Java Script is loaded |
| C: Operating System and SQL executables | | | | at the end of the page. Not doing this can prevent |
| D: Database File - RAID 5 | | | | your entire page from progressively rendering. |
| E: SQL Log Files | | | | Large ClassesOne of the benefits ASP.NET is using |
| F: Temp Database | | | | extremely rich classes like the grid view. You could |
| G: Backups | | | | dedicate a complete book (and I'm sure someone |
| Using RAID 5 on the D drive allows very fast reads | | | | has) to explore the features and inner workings of |
| because multiple drives can respond to the request. | | | | the grid view. It's extremely flexible and easy to |
| Additionally requests do not have to wait for writes | | | | use. With a couple simple queries you can define a |
| to the Log Files or Temp database. Because the D | | | | simple editable grid view. One cost of using the grid |
| drive is for read data, SQL can more effectively use | | | | view is that because of all that's built in it carries a |
| memory to cache frequent requests. There are a | | | | heavy payload. Many times this is not an issue but |
| lot of write requests to the SQL log files and temp | | | | you will notice a speed increase for simple display grid |
| database so giving them they're own separate drive | | | | views if you generate the HTML and render it in a |
| reduces contention. It goes without saying that | | | | table or some other rudimentary display manner. |
| using drives with higher RPM, more throughput, and a | | | | Compression |
| larger cache will assist your SQL server to run faster. | | | | Remove excessive white space in your rendered |
| Web Server Optimization | | | | code will reduce the size of the data being |
| Since almost every page the typical Internet retailer | | | | transferred, thus speeding up the page load time. |
| website has will make some type of database | | | | Additionally configurations can be made to most web |
| connection, your bottleneck will probably be retrieving | | | | servers to support GZIP (a GNU Compression Utility) |
| data from SQL. If this is the case you probably | | | | this can easily cut the size of what you are |
| won't need to do much optimization for your web | | | | transferring in half or more. This involves extra CPU |
| server. Generally speaking you will want to make | | | | cycles on the server to compress and on the |
| sure the server has plenty of CPU, memory, and disk | | | | browser to decompress but unless your server is |
| resources available to your web server. | | | | running sustained high CPU utilization you shouldn't see |
| Increasing memory to allow more web page caching | | | | much of a CPU hit or slowdown because of this. On |
| (see caching section of this article) is one area you | | | | a side note, utilizing this compression could cut the |
| can really boost page load time. Additionally if you | | | | bandwidth portion of your hosting bill in half or at |
| find your disk having problems keeping up try some | | | | least free up extra bandwidth. |
| of the following: | | | | Website ErrorsErrors will slow everything down, not |
| 1 Add memory and increase caching | | | | to mention annoying your customers. Every time |
| 2 If you have logging enabled, move the log file | | | | your back end code or web server has to engage |
| locations to another drive. | | | | error processing it takes extra resources away from |
| 3 Make sure your web pages are being loaded from | | | | your server. Capture coding errors using error |
| a RAID 5 partition. | | | | control is the first step. As a common practice I |
| Database Backups | | | | have all errors emailed to me so I am forced to see |
| I won't go into all the specifics of database backups | | | | them and mentally acknowledge them. This also |
| as that requires an article of its own. From a | | | | helps me judge the frequency of the errors. |
| question of performance you will want to schedule | | | | Because I am always seeing them it's usually on the |
| your backup to run when you have the most | | | | top of my list to get them fixed. Over time coding |
| available resources. If you are backing up to | | | | in fixes for all the possible errors will prevent error |
| another server, consider backing up to a local drive | | | | control from having to engage as often thus |
| first, and then backing up across the LAN. This can | | | | increasing overall performance. The other errors |
| help complete the backup faster because it's done | | | | you will want to keep track of are the web server |
| locally, then when transferring the backup to another | | | | errors such as 404 (Page Not Found). You can |
| server you won't need to be concerned with how | | | | track the occurrence of these errors in your server |
| long it takes to transfer to another server. Another | | | | web logs. If for instance, there is an old link to a |
| option is to setup a separate server that you mirror | | | | page that no longer exists, instead of letting the web |
| data to, then you can simply backup the mirrored | | | | server generate a 404 error, place a file (same name |
| server instead of the primary. This mirrored server | | | | and location of the missing one) with a friendly "does |
| can also be utilized as a fail over server in the case | | | | not exist" message or a script to redirect to another |
| the primary server fails. | | | | page on your website. |
| Load Balancing/Multiple Servers | | | | There are certainly many additional things you can do |
| If you are still having problems after you have | | | | that don't fit the scope of this article. This is meant |
| optimized your web server, database server, web | | | | as a practical guide for the small to mid size Internet |
| pages, and server hardware you can consider adding | | | | retailer. I hope you have found this informative and |
| servers and load balancing these servers. Since | | | | at lease consider each one of these techniques |
| every situation is different it's impossible to list every | | | | before exploring more in-depth or expensive |
| possible configuration. Below are some examples | | | | solutions. Here is a free Web Page Load Time |
| but it's important for you to do in depth research to | | | | Tracker that you can use to monitor the speed at |
| determine exactly what resources are lacking and | | | | which your pages load over time. This resource is |
| what is utilizing those resources so you can make the | | | | unique in that it uses an actual web browser to |
| best decisions on what type of servers to put up | | | | render the page including downloading and displaying |
| and how to load balance. | | | | CSS, Images, and JavaScript. Set this up now on all |
| If you currently run a single server, determine what | | | | your different pages so you have data to chart |
| resources are lacking, probably disk or memory, | | | | against when you make changes to your site. |
| maybe CPU and what is using these resources. | | | | |