When you have different developers, customers and databases your database collation is bound to get out of sync.  Making sure you have matching collation is important because it can cause problems when it comes to running SQL queries and can impact performance if you are joining text fields.  There isn't an easy way to change the collation across an entire server because the collation is stored against the fields in each table.  I looked at a number of different scripts but found the following script was the easiest way to fix the problem.  Simply set @toCollation to the collation you want in your database, and run the query.  The query will output a List of ALTER COLUMN SQL Queries which you can copy into a query window and execute to fix your database.

declare  @toCollation sysname 
SET    @toCollation = 'Latin1_General_CI_AS' --  Database default collate
SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL' 
END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
 and COLLATION_NAME <> @toCollation

I just had a customer contact me asking for read/write IOPS per user so they could correctly specify a SAN.  IOPS means I/O Operations per second, so they want to know how many disk operations occur under normal usage on a server.  

How do we find this out for a dotnet based system?  Well basic really when you think about it.  I logged into our development server and Started performance monitor and logged the background Disk Read/Write /Sec and wrote down the average values when our system wasnt being used.  I cleared the display and then started to use our web based system in anger, flipped back over to performance monitor and wrote down the new average Disk Read/Write /Sec values.  Subtracting the values I had just written down from the background usage of the server gives you the IOPS per user.

If you have a number of optional parameters in a stored procedure (that are disabled with boolean logic) that will dramatically change the way the query works depending on the parameter it is best to add (WITH COMPILE) to the stored procedure, to ensure that the best query plan is always used.  The first time a stored procedure is executed, the parameters you have passed will determine the way it is executed.  If you first run the stored procedure against an empty database, used some duff test fields, or you have a report based stored procedure that works in a different way depending on parameters the first call may not be the ideal query plan.  By forcing the query to be recompiled for these optional varied you will massively boost performance.  This feature should be used sparingly, but there are often places where it needs to be essential (Some reporting stored procedures/stored procedures used to search)

ALTER Procedure [dbo].[Report_ExportReworkData]
    @ProductOption as nvarchar(50),
    @SerialNumber as nvarchar(50),
    @WorksOrder as nvarchar(50)

-- Make sure this gets compiled each time to avoid incorrect parmeter sniffing
WITH RECOMPILE

as
select
    ......
where
    (len(@ProductOption) = 0 or WorksOrders.PartNo = @ProductOption) and
    (len(@WorksOrder) = 0 or WorksOrders.WorksOrderNo = @WorksOrder) and
    (len(@SerialNumber) = 0 or  MfgRecords.SerialNumber = @SerialNumber)