Our current system has a few potential bottlenecks, both programmatic and hardware related. By scaling up our hardware we need to first gather some metric data to find the bottle necks and then fix these poor performing areas. By having access to both the existing (live) database and the new hardware we require some metric data to first explain the deficiencies and then later to prove that these deficiencies have been removed.

The final objective is to make sure that the new hardware can handle future growth and load.

Proof of experience is REQUIRED (ie. Case studies etc).

Requirements for Sql Server 2008 Database Performance Analysis and Maintenance.

1. Provide a detailed report of the current (production) base line metrics. Eg. Memory pages/sec, Data-disk/Log-disk % disk time, Data-disk/Log-disk disk queue, CPU % processor time, Network Interface bytes total/sec, SQLServer:Access Methods Full Scans/sec, SQLServer: Sql Statistics Batch requests/sec, etc. etc. (this is an example list only). Construct the full list and provide the report.

2. Assess and modify the current database schema to make it more efficient, including any index and/or view modifications. Metrics also provided to justify the changes.

3. Highlight top slow queries and to improve their query plan performance.

4. Highlight any top slow code queries (ie slow LINQ-to-Sql queries) and make suggestions for the dynamic Linq-to-sql to more efficient (this is the preference) or change them into stored procedures. Metrics also provided to justify the changes.

5. Provide metrics for the top 10 expensive queries and the top 20 common queries that can handle a large amount of requests a sec.

6. Suggestions to scale out the database.
7. Load stress the new hardware with our most expensive and most requested queries. A report with metrics, under load, to handle up to 3 million U.V.’s/month.

8. Provide a new report that compares the new hardware database to the (old) baseline with metric comparisons.

Stats about the database.

• Sql Server 2008 Standard Edition x64bit

• Approx 70 tables (A few contains a 1.5 million rows. The rest are not very big at all (a few thousand rows).

• Approx 400 stored procs (Many are not being used and can be ignored)

• DB is around 4.5Gig (uncompressed).

• OS is Windows Server 2008 Enterprise x64

Personal requirements
• Would like to have interactive access to the client – either via IM or SKYPE, especially in the early stages to help familiarize the system with the candidate.

• English speaking.