Microsoft® SQL Server® 2008 Internals (Developer Reference)

I would suggest you get a copy of SQL Server 2008 Internals by Delaney, Randal, Tripp and more. Excellent book on the internal workings of SQL Server.

http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243/ref=sr_1_1?s=books&ie=UTF8&qid=1289565465&sr=1-1

If you are really interested in how SQL Server does what it does, please read this book: http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&s=books&qid=1267033666&sr=8-1

Whilst I agree with the accepted answer that Full Text Indexing would be the best solution and am by no means advocating the use of leading wildcard searches if they have to be performed then there are potential steps that can be taken to make the performance of them less bad.

Kalen Delaney in the book "Microsoft SQL Server 2008 Internals" says:

Collation can make a huge difference when SQL Server has to look at almost all characters in the strings. For instance, look at the following:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%'

This may execute 10 times faster or more with a binary collation than a nonbinary Windows collation. And with varchar data, this executes up to seven or eight times faster with a SQL collation than with a Windows collation.

You're going to end up with a full table scan anyway.

The collation can make a big difference apparently. Kalen Delaney in the book "Microsoft SQL Server 2008 Internals" says:

Collation can make a huge difference when SQL Server has to look at almost all characters in the strings. For instance, look at the following:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%'

This may execute 10 times faster or more with a binary collation than a nonbinary Windows collation. And with varchar data, this executes up to seven or eight times faster with a SQL collation than with a Windows collation.

The SQL Server transaction log isn't meant to be "human readable". It's meant to support SQL Server, allowing transactions, read consistency, etc etc.

SUGGESTION: If you really want to understand SQL Server internals (including how the transaction log works), I strongly encourage you to get a copy of this book:

SQL Server 2008 Internals, Kalen Delaney

It's an excellent book; you will learn a LOT of practical and important information. Satisfaction guaranteed!

In simplistic terms, if you are talking about an OLTP database, your throughput is determined by the speed of your writes to the Transaction Log. Once this performance ceiling is hit, all other dependant actions must wait on the commit to log to complete.

This is a VERY simplistic take on the internals of the Transaction Log, to which entire books are dedicated, but the rudimentary point remains.

Now if the storage system you are working with can provide the IOPS that you require to support both your Transaction Log and Database data files together then a shared drive/LUN would provide adequately for your needs.

To provide you with a specific recommended course of action I would need to know more about your database workload and the performance you require your database server to deliver.

Get your hands on the title SQL Server 2008 Internals to get a thorough look into the internals of the SQL Server transaction log, it's one of the best SQL Server titles out there and it will pay for itself in minutes from the value you gain from reading.

Here are three great books, if you could buy only one and wanted a reference I'd recommend the first

SQL Server 2008 Internals http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243/ref=sr_1_1?s=books&ie=UTF8&qid=1330914927&sr=1-1

2008: T-SQL Programming http://www.amazon.com/Inside-Microsoft%C2%AE-Server%C2%AE-2008-Pro-Developer/dp/0735626022/ref=pd_bxgy_b_text_b

2008: T-SQL Querying http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030/ref=pd_bxgy_b_text_c