A friend and co-worker of mine operates his own website for a business he has started. We have talked a lot about database design for performance, query stats and so on, but one of his difficulties is that he is currently using a hosting service for both web and database, thereby limiting his ability to access the SQL Server and really make some underlying performance enhancements – lucky for him, his site is growing, so he should be able to change that soon.

In hosted situations, IO is going to suffer. For most basic plans, you are sharing a SQL Server with many, many, many other people and you will have absolutely no say in how that server is maintained, operated and configured. I hate to say it, but very few hosting providers have done a good job providing SQL service. I have used and interacted with over 20 of the larger host providers over the past two years, and of those, only three even kept their SQL Servers adequately patched.

Disk setups are basic in these situations, not that I am blaming these guys, you get what you are paying for, and most people just need a basic service – it’s is really only a pain point for those who have a good product and are experiencing growth and the performance pain with that growth – so onto the point of this post:

In a hosted situation, your options to debug for IO performance is limited as you cannot run traces or even know the disk configuration, but what you can do is debug your queries inside of SSMS to understand the IO statistics given the configuration you are using. By using SET STATISTIC IO ON, you are telling SQL Server to print information in the messages tab:

So what does this information mean and how can you use it? From Books On Line:

  • Table: Name of the table.
  • Scan count: Number of index or table scans performed.
  • logical reads: Number of pages read from the data cache.
  • physical reads: Number of pages read from disk.
  • read-ahead reads: Number of pages placed into the cache for the query.
  • lob logical reads: Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache.
  • lob physical reads: Number of text, ntext, image or large value type pages read from disk.
  • lob read-ahead reads: Number of text, ntext, image or large value type pages placed into the cache for the query.

Scans are bad – if this number is large for one of your queries, you will want to look at how you wrote it, are you using aliases so SQL is not doing lookups, are your where clauses and joins hitting indexes? The number of scans will directly impact performance as it means that the query does not have a pointer into the B-tree, or that you are going against a table that is a heap.

The higher the logical reads, the more that query is hitting plan or data cache from other queries. Depending upon maintenance tasks run on the server, this will change easily from one day to the next.

Read ahead can be both a blessing and a curse. If you have a large number of columns in your set returned that are variable character columns, then you could be taking a performance hit. With data types such as nvarchar and varchar, the SQL Engine does not know how many pages it can exactly pre-fetch as all the rows are potentially different in length – whereas is can estimate with a fixed column of char(50). Because of this, SQL may either read ahead too many or too few pages. In some cases, it is best to take the space hit in setting a fixed character length for often used columns in order to have better performance in read ahead and pre-fetch scenarios.