I’m super excited that two of my sessions have been selected for SqlBits (http://sqlbits.com/) on the 29th –31st March, 2012. SqlBits is all lined up to be the largest SQL event in Europe this year!

If you go to only one SQL event this year, this is the one. Now if you go to two events you must of course come to our very own first ever Sql Saturday which is also the Irish Technical launch for SQL 2012. its on the 23rd march and is free http://www.sqlsaturday.com/105/eventhome.aspx

here are my two sessions.

Load Testing Analysis Services (Friday 30th March)

http://sqlbits.com/Sessions/Event10/Load_Testing_Analysis_Services

Ever deployed an Analysis Services cube that worked perfectly well with one user on the development server, only to find that it doesn’t meet the required volumes of user concurrency?

This session focuses on tools and methodology to load test Analysis Services in highly concurrent environments. Including how to locate resource bottlenecks and the appropriate configuration settings that can help improve performance.

Sample source code will be supplied to help you load test analysis services. We will be focused on the MOLAP engine, but techniques are equally applicable to the new tabular data model available in BISM.

SQL 2012 Always On-Deep Dive (Sat 31st March)

http://sqlbits.com/Sessions/Event10/SQL_2012_Alwasy_On-Deep_Dive

Always On offers a huge leap forward in terms of high availability. This sessions is a demo based introduction to the high availability changes in Denali emphasising key features and benefits.

During the demonstration we will show how to seamlessly upgrade a database from SQL 2005+ to Denali with ZERO down time and then use the Always on features to simplify reporting, availability and disaster recovery.

This session will be useful for anyone who is working in a high availability environment or currently using replication to provide reporting and looking for a neater solution.

This session scored really highly in the last SQL Bits so I hope to improve the scores this time.

http://blogs.prodata.ie/post/Speaker-feedback-from-SqlBits-9-Scores-on-Doors.aspx

Thanks to everyone who attended my session. Here are some links that might be useful for further reading:

- Presentation Deck (attached to this blog)

- Microsoft Assessment and Planning Application Toolkit

 http://www.microsoft.com/downloads/en/details.aspx?FamilyID=67240b76-3148-4e49-943d-4d9ea7f77730&displaylang=en

- Sql Consolidation Planning Add-In for Excel

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=eda8f544-c6eb-495f-82a1-b6ae53b30f0a

- Demo of using MAP and Excel to produce IO Histogram

http://blogs.prodata.ie/post/Using-MAP-Tool-and-Excel-to-Analyse-IO-for-SQL-Consolidation-Part-I-e28093-basic-distribution.aspx

- Demo of using MAP and PowerPivot to analyse IO for a SQL Estate

http://blogs.prodata.ie/post/Using-MAP-Tool-to-Analyse-IO-for-SQL-Consolidation-Part-II-e28093-Using-Power-Pivot.aspx

Some  Great Questions from the audience which I’ll try to summarise here

Q: What is the maximum recommended CPU limit to decide what SQL Servers to virtualise?
In Hyper-V this is an easy question to answer as it only supports 4. VMware supports more cores, BUT the general recommendation is NOT to use more CPU’s than are available on a single NUMA node which is either 4 or 8. The reasoning is that your virtual operating system will not be “NUMA aware” and having some of the CPU’s on one NUMA node and some on another will be very expensive. There is not a lot of research yet on this type of scenario, so most people avoid it. Your mileage may vary, but we avoid crossing NUMA boundaries with virtualisation.

Q: Does the MAP tool account for growth ?

No, it only captures a snapshot of resource usage. My concern is not usually the growth in CPU usage and IOPS of workloads, but the fact that as time goes on MORE workloads will be added to the virtualised environment. You need to figure out what spare capacity will be allocated for this on the virtual hosts in terms of CPU, IOPS and disk space.

How much disk space you need to park for “growth” depends on how you are virtualising. If you are only moving physical SQL Servers and moving drives like for like, then they may already have had capacity planning for X time.

If you are doing SQL consolidation (moving data and log files to new environment), then this is trickier. It is possible to run the MAP Inventory tool at different months to measure % growth in databases to help plan for growth.

Q: Why don’t we ask for IOPS based on the MAX amount used.?

This would be cool to do for only one server as you get guaranteed performance no matter how busy it is! However imagine you have 100 servers each doing an avg 50 IOPS, 80% percentile at 80 and a max 250 IOPS.

Commissioning 25,000 IOPS at 8ms could be quite expensive, but 8,000 iops might be more practical.

Note we are not saying that the maximum IOPS we would ask for is 8,000. Just that we want 8,000 at avg 8ms latency. Hopefully the storage will be able to spike above 8,000 IOPS.

Its unlikely that all 100 servers will need their “max” at the same time, so we can save some costs with shared storage.

Thanks. Do post any follow up questions