Here is a useful post by Mitchel Sellers that lists information about each of the tables within a specific database. The output lists the total number of rows, reserved data size, used data size, index size, and unused space in each table.

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx
This is very useful when performing maintenance such as data purges for size management or row analysis for performance improvements.
 
He wrote this as a stored procedure but if you leave the top two lines off the SQL you can run it from within SQL Server Management Studio (or Query Analyzer) as desired without saving it as a stored procedure - though you might find it useful enough to store to run as needed in the future.
 
Here is the SQL he wrote, without the top two lines, that you can copy/paste and run:
 

Hard drives have a few different metrics related to their performance statistics. One of those metrics is the drives revolutions per minute - or RPMs.

Consumer systems, especially portable computers, often have hard drives that spin at 5,400 RPM.*

Server systems often have a few different options, including SATA drives that commonly spin at 7,200 RPM, and SCSI or SAS options that spin at either 10,000 or 15,000 RPM.*

(* The newest type of drive is a solid state drive (SSD), which does not spin at all, so doesn't fit into a discussion about revolutions per minute. These type drives are also still rather expensive and limited in system configuration options - especially at the enterprise/server level. They are insanely fast though and I'm sure will work their way into the mainstream over the next few years.)

What do these numbers mean?
Let's crunch the numbers a little bit to see if we can make them useful.

One way to consider the data is to ponder how many revolutions each drive can make in one second - revolutions per second (RPS). Dividing the total RPM by 60 easily gives this number. Here is that number and also the percentage gain in RPS from one speed to the next faster rotational speed.

Those are pretty large increases in spins per second. And of course jumping more than one rotational speed - for example from 7,200 RPM to 15,000 RPM - gives huge gains - an increase of 108% in the 7,200/15,000 example.

Another way to consider the data is the inverse of this calculation. Rather than revolutions per second, let's look at how long it takes each drive to complete one revolution. We'll calculate this by dividing 60 by the RPM of each drive.That's obviously a very small number so let's take it a step further and multiply by 1,000 to reflect how many milliseconds each revolution takes.

As expected, those are fairly decent variances - a 52% faster revolution spin speed if considering the change from a 7,200 RPM SATA drive and a 15,000 RPM SCSI or SAS drive.

Is RPM all that matters?: Let's get one clarification out of the way because I suspect this article will generate a criticism otherwise - the spin speed of a drive is only one of several factors determining individual drive performance. Two other factors are the data transfer rates and how fast the read/write head of the drive can move from one part of the disk to another. This post is specifically about RPM though, so perhaps I'll touch on those other two points another time.

What do these numbers mean to me?
It seems obvious that the RPM speed of drives has some impact on performance of the drives, but let's discuss it a bit further and consider the implications.

To understand the impact of RPM speed specifically, let's assume other factors of the drive are the same.

If the system - whether notebook, desktop, or server - requests data that is just behind the read/write head so that the drive needs to spin one complete revolution for the data to be accessed, this would be a maximum rotational delay. The numbers above reflect those potential rotational delays varying from 4 ms to 11 ms.

Even 11 ms is very fast, so should you care? If your system has light usage or your primary disk operations are big sequential files then you might not care. If you run database services, a wiki, or other type of disk-intensive applications, those speed differences might add up to a substantial performance impact. The more random disk access your server performs, the bigger difference you'd notice.

Consider the difference between SAS/SCSI and SATA in those rotational delay times - a SATA drive can potentially take twice as long to find a specific location (file) on disk. That adds up to substantial performance differences under anything but very light user load.

With the cost differences between SATA and SAS/SCSI diminishing in recent years, I strongly believe it is worth considering a SAS/SCSI disk configuration for your server - for immediate benefit and to handle future growth smoothly.

At ORCSWeb our managed dedicated hosting servers are all deployed with SAS or SCSI disk configurations (and RAID) for the best performance and growth options for our clients.

Paul Glavich and Chris Farrell have written a book on performance testing and optimization for .Net and it's available as a free eBook download from the Red Gate website. The price cannot be beat - FREE! You should go check it out.

When working with client prospects to understand the differences between ORCS Web's dedicated solutions and cheap host options, many things come to light preventing an easy initial apples-to-apples comparison.

One of the common differences we see is that most hosts include a single hard drive with their standard server packages. Every dedicated solution that ORCS Web deploys has a hardware based RAID solution though. If the term RAID is unfamiliar to you, please see "What is RAID" for some background information.

Having multiple hard drives setup in a RAID configuration benefits both performance and redundancy. Server hard drives spin at a tremendous speeds and are the most likely hardware components of a server system to fail. With a RAID solution, the server stays online with no interruption and the failed hard drive can be hot-swapped while the server is online - no disruption to the hosting at all.

Personally, I would not deploy a production application to a server that did not have a RAID hard drive configuration. But, admittedly, not everyone shares this viewpoint. I've had some interesting dialog with a couple of people who say "who cares?". Their thought is that if their server drive fails, they'll just get the host to pull out the drive, put in a new one, restore the data, and get the server back online.

Well, let's consider some points for a non-RAID drive failure in a production server...

Do you have backups? I'm a HUGE believer in backups for all systems. I'll craft a future post about this point. But, if you are going to consider deploying to server with a single hard drive, you better be sure you have backups. Either manually export all your data to your personal PC on a regular basis, or be sure that your host is backing up your server.

When was the last backup? Any data written or changed on the drive between the most recent backup and the failure is going to be lost. In some situations this can be a huge deal. Lost orders; lost user accounts; lost records - all gone and never to be seen again.

How easy is the restore? If the drive that fails is just hosting standard web content only, or image files, or some other simple file type, the restore process might be fairly quick and easy. If the drive is holding the OS, or data files, or dozens of other types of complex or transactional data, it's a totally different situation. The time to actually run the restore process (getting the data back onto a new hard drive) is only part of the work. There is often a decent amount of manual work still required to get the system fully functioning again with the data current.

Swap the drives... Depending on the type of server you are running, the hard drive swap process can be really simple - pull a drive from the front of the system and slide in a new drive. Or it could be rather cumbersome - un-rack the server, open it up, pull the drive, put in a new one, close everything back up, re-rack the server. Also, if considering this option, best be sure the host can assure you they have the exact physical size, data size, and speed hard drive that you'll need, or you could be stuck without a quick replacement.

Considering those points - If the server was being backed up, and if there are spare drives sitting around, and if the restore process goes smoothly... you would still be looking at a few hours of downtime - maybe many hours depending on the specifics of the situation. And even after the server is restored you could be dealing with the potential fallout of data loss and the impact to your organization. How will your clients react? How about site visitors that stopped by and couldn't access the server?

Is it worth the risk to save a negligible amount of money on your hosting cost each month? Maybe in some situations where the users are extremely tolerant of downtime and data loss is not a concern. In the reality of today's world though, fewer and fewer Internet sites or applications have users that would tolerate such a situation when it could be so easily avoided.

I say go RAID and be safeguarded against hard drive failure.

Here is a useful post giving an introduction to Microsoft's WCAT - which can be used to generate load against a web application.

http://blogs.msdn.com/alikl/archive/2008/03/09/stress-test-asp-net-web-application-with-free-wcat-tool.aspx