If your SQL server is experiencing not so high CPU, but queries are getting timed out, it may be worthwhile to look at activity monitor resource waits (compilation), or run the following SQL

select * from sys.dm_os_wait_statswhere wait_type like ‘resource%’order by wait_type

If the wait time very high (e.g the first in the list of resource waits), you might want to look at recent queries and see what’s being executed (Activity Monitor -> Processes, right click then details)

Chances are you will see quite a fair number of queries which looks more or less the same.

One easy way to solve this issue is to run dbcc freeproccache, which should effective drop your cpu to normal values once the query is completed. However in the long run, its best to look at the query causing this and see how to make it better

The usual cause of this is when there is alot of SQL which looks more or less the same being executed at the same time. But because the parameters are hard coded, it causes SQL Server to create an execution plan for each and every query.

To solve this, you can either create plan guides, or else find ways to parameterize your queries so that it does not have so many recompiles

SQL Server database mirroring is cool, especially in the areas of cost savings. However setting up a proper database mirroring is not as straightforward as it seems, although it is much much easier than doing a clustering.

Things you need to do

Connection String: The databases are in 2 different instances, make sure you use the latest SQL Client with the failover partner parameter to ease the failover“Provider=SQLNCLI;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;”

SQL Accounts: Mirror the login accounts too!
Microsoft has a KB (http://support.microsoft.com/kb/918992) which teaches you how to create a stored procedure which prints out a create user sql which you can run on the backup server to create an exact duplicate of the user account in the primary server.
i’ve attached the script here for easy reference

— obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

Backup Strategy: Because database mirroring sets the mirror db to be in a perpentual Restoring state, your normal backup jobs won’t work on it. Note this is fixed in SQL Server 2008 maintenance plan, you just need to tick the ignore if not online check box
Here’s a sample backup script to get you going