A thanks from Art @ House Parts

A recommendation from Art @ House Parts, USA

This letter is written in appreciation for the assistance that Matthew provided 5/14/2010.  John Short helped to diagnose that Sysprodb was down.  Matthew helped with the recovery of a backup database and the creation of a new virtual machine.  Matthew was extremely efficient and thanks to him, a crash that could have crippled our business was averted.  Throughout the 3 hours, Matthew was supportive and knowledgeable.  Any system crash is a stressful experience, however Matthew was able to guide us thru the process and he helped to limit the stress.

I wanted to thank Matthew personally for his timely assistance with our recent database problems.

Art Cummings, I.T. Support

House Parts

House Parts Logo

http://www.houseparts.com

479 Whitehall St. SW

Atlanta, GA 30303

(Number Available on request)

SQL server storage principles

An international client of mine had server hard drive failure late last Friday.

The company, albeit large, was running this smaller application, and its SQL server on external hard drive because of previous failure the month before. The hardware was brand new, but workstation grade, not server grade. The management had disallowed any new purchases until the budget would be formally allocated.

Needless to say, the hardware failed, and their management fails to understand that the problem has been created by them. Their IT department requested a letter stating the cases for their management. So here it is:

As discussed, with your agreement, during the recovery of your SQL server, database applications are very hard applications on the wear rates of hard-drives.  Typically hard drives are created for either the workstation (or home user) market, or the server market. Hard drives usually come with warranties ranging from 1 year to 5 years, depending on the design. These warranties usually indicate that their components are of different grade, and that their MTBF (most electronic equipment has a Mean-Time-Before-Failure estimation) is significantly different.

The problem with using workstation grade hard drivers in a database application that server many users, is that the busiest parts of the database will over time usually be located over the same contiguous physical sectors, and these sectors are likely areas for failure. A typical home user might take several years to create the number of reads and writes to a particular sector that a busy database application would cause in just one week.

The risk of data corruption is high. It results in lost data of the past, and labour hours which need to re-occur to re-capture data, as well as reconcile data. In addition, during recovery time, labour hours are lost. Therefore both past work is last, and future work is created, and staff are often demoralised by such an event. There is also the risk of debtors invoice information being lost, and the opportunity for untrustworthy staff to take advantage of a situation where they can see the system might have reconciling difference that need to be written off anyway, the perfect time for theft.

To mitigate the risk  of such failure, two principles are best adhered to, firstly adequate backups (which given the hardware constraints), the best was already being done, and secondly, trying to reduce the requirement for ever needing those backups.

Hard drives are where the information is kept, so it’s best to use server-grade hard drives, which also boast additional speed, as well as server grade architectures for storage, such as RAID 5 or RAID 6 arrays which allow a single (RAID 5) or two (RAID 6) hard drives to fail without losing any data – and without creating any “down-time”. Hard drives are one of the most common failure points because of their mechanical nature, they are “doomed” over time to fail. Using a RAID array, allows a company to mitigate such risk.

SQL functions

It happens all the time, but a client phoned me with an urgent SQL query problem.

Their query was taking way too long to run. Sure, its got millions of records to run through, but it only need to retrieve a small result set, and the WHERE clause was seeking on an indexed fields. Or where they?

A quick look at the query:

Wait… before you read that, the only thing you need to know, if you are putting your index fields inside a function… you are slowing it down. Rather limit the results in a inner query, and filter those further with your functions.

The reason: The query optimiser cannot tell what the outcome of parsing the value through the function will be. So it needs to build a temp table with the parsed values (or some other tedious operation that goes through every single record, instead of selecting only a sub set or rows).

So, functions in a WHERE clause are a bad idea, unless the record set being drawn from is already small.

The code is below. And for a more technical background: see this article http://www.databasejournal.com/features/mssql/article.php/3845381/T-SQL-Best-Practices–Dont-Use-Scalar-Value-Functions-in-Column-List-or-WHERE-Clauses.htm

Bad Query:

DECLARE @fdate as datetime
DECLARE @tdate as datetime
SET @fdate = '2009-10-01'
SET @tdate = '2009-10-31'
SELECT cat.Descrip,

SUM(((invl.Quantity*invl.SellingPrice)-((invl.Discount/100)* (invl.Quantity*invl.SellingPrice)))*invl.tax) As Total

FROM Invoice inv
JOIN InvoiceLine invl ON inv.InvoiceID=invl.InvoiceID
JOIN Products prod  ON invl.ProductID=prod.ProductID
JOIN CategoryForSales cat ON invl.salescat=cat.CategorySalesID
WHERE
  inv.cid=@cid
 AND (convert(varchar(10),inv.InvoiceDate,111)
  between  convert(varchar(10),@fdate,111) AND  convert(varchar(10),@tdate,111))
GROUP BY cat.Descrip

Obviously the user is trying limit the time of the transaction out of the Where clause.
It would have been better in this case to simply set @fdate to the date at 12am, and @tdate set to 23:59.59.998 or 12am of the next day.

DECLARE @fdate as datetime
DECLARE @tdate as datetime
SET @fdate = '2009-10-01 00:00:00.000'
SET @tdate = '2009-10-31 23:59:59.998'
-- OR SET @tdate = '2009-11-01 00:00:00.000'
SELECT cat.Descrip,

SUM(((invl.Quantity*invl.SellingPrice)-((invl.Discount/100)* (invl.Quantity*invl.SellingPrice)))*invl.tax) As Total

FROM Invoice inv
JOIN InvoiceLine invl ON inv.InvoiceID=invl.InvoiceID
JOIN Products prod  ON invl.ProductID=prod.ProductID
JOIN CategoryForSales cat ON invl.salescat=cat.CategorySalesID
WHERE
  inv.cid=@cid  AND inv.InvoiceDate between  @fdate AND  @tdate
  --Hey look how much simpler the where clause looks now... and its that much simpler for the query optimiser too!
GROUP BY cat.Descrip