Posts Tagged ‘SQLBits’

SQLBits V: Highlights from Friday Afternoon

Having attended the SQL Bits conference in Newport last month I decided to collate the little tips and tricks I learned at the event, in the challenging post-lunch session on Friday Thomas Kejser gave a talk on Designing I/O Systems for SQL Server – my notes from this talk are either short, sharp facts or references to thing I plan to research further…

The most important metrics are Throughput (measured in MB/sec or IOPS) and Latency (milliseconds).

A 10K RPM disk will give 100-130 IOPS, a 15K RPM disk will give 150-180 IOPS.

Performance can be increased by short-stroking disks, if you build a single partition on a drive using only 10% of it’s capacity this partition will reside physically on the outer edge of the disk.  Doing this reduces the amount of head movement required to read the data and improves performance.

Windows Server 2008 handles disk alignment automatically but for earlier versions the  offset should be 1024kb.

For testing the effect of configurations on performance use SQLIO.EXE or IOMeter, for stability testing use SQLIOSIM.

Check out the Pre-Deployment Best Practices Whitepaper from Microsoft.

The last talk of the day was by Allan Mitchell and was initially titled Common Integration Services Problems but was changed late-on to a more introductory Solving Problems in ETL using SSIS.  The only take-away I got from the talk was to check out a tool called BIDS Helper, I have no criticism of Allan though – he’s a good speaker but in this case I already understood the bulk of the content so it was hard to stay focused.

SQLBits V: Highlights from Friday Morning

Having attended the SQL Bits conference in Newport last month I decided to collate the little tips and tricks I learned at the event, here are my highlights from Friday morning’s talks.

Simon Sabin kicked off the talks with a session on SQL Server 2008 Development Features, I’ve not had a good chance to try them out myself since I’m not on 2008 yet but the real highlight is the addition of the DATE and TIME data types – see my other post DATE and TIME Data Types in SQL Server 2008.

Management Studio now includes Intellisense which as been available in Visual Studio for a long time, it will automatically offer suggested keywords as well as table/object names whilst you type and can dramatically speed up your SQL writing.  Also new in SSMS is the ability to debug your code and step through it line by line, the debugger will even step into and out of stored procedures and user-defined-functions – this will be a life saver for anyone working with a complex web of SPs.

Following Simon’s talk I headed into the intriguingly names T-SQL Tuning with Colin Chapman, Enzo Ferrari, and The Stig by Brent Ozar – with a title like that how could I not go?  The talk turned out to be a great one and Brent’s a very entertaining speaker but all the time you get the feeling that he really knows what it feels like to be a DBA with production issues.  Most of my notes from Brent’s talk don’t type-up well but he’s got some excellent resources on his site at http://brentozar.com/go/faster so please do check those out, my personal ‘take homes’ from his talk were to mine the DMVs for performance data, to use mirroring as a way to manage uptime during OS/SQL patch cycles, use a text-file to build a change log on servers and to read the Microsoft whitepaper on index defragmentation.

SQLBits V: Keynote and Sponsor Talks

In November the fifth SQLBits conference took place in Newport, this time around the format differed slightly in that the regular ‘training day’ took place on a Thursday, there was a paid conference day on the Friday (only £99 for early takers) followed by the usual free Saturday conference.  If you’ve never been to one of these events they’re well worth attending and it’s great to be in the company of so many people who understand the sort of environment you work in and who freely offer help and advice.  More as a reminder for myself I thought I’d put together my personal highlights and little bits of information I learned whilst I was there.

The Keynote was given by Donald Farmer and showed took us on a journey through the challenges of BI from the perspective of a businessman from the 1920s who shared many of the same problems that we do in business today, it was quite an interesting angle and an amusing talk all-round.  The address culminated in a demonstration of PowerPivot, a new BI technology centred around fetching large sets of data into a cube sitting behind Excel then allowing the users to perform Excel-like calculations as well as joining the data to other tables/spreadsheets.  I believe that the tool is aimed at analyst-level users but the whole idea of putting large data sets in the hands of users (in the demo it was a million rows) and expecting them to manipulate it seems a little like a backwards step – isn’t the heavy lifting meant to be done on the server?  Don’t get me wrong, if the demo was anything to go by I’m not too worried about performance – more concerned that many users just aren’t equipped to (and don’t want to) perform this level of analysis.  I’ll watch with interest but colour me sceptical.

On both the Friday and Saturday I attended the Sponsor talks by Solid Quality Mentors, both given by Mark Whitehorn and both excellent.  From past experience the sponsor talks vary between product demos and general advice but Mark’s talks took a “let’s think about something different” approach.  Friday’s talk was about Social Data and considered what sort of social data large companies such as eBay and Facebook collect, how they can get the most out of it and in some cases why it can be considered more valuable than transactional data.  We also had a bit of a discussion around the moral implications of collecting social data and whether or not it was legal or ethical in certain circumstances to use the data for marketing purposes or perhaps sell it on.

In addition to working with Solid Quality Mentors Mark is also a lecturer at the University of Dundee where there will soon be an MSc in Business Intelligence, and Saturday’s talk was based around some academic research he had been involved in (published in Nature).  The research had involved manually collecting data from around 5,000 plant specimens originally sourced by John Stevens Henslow, a friend and lecturer of Darwin at Cambridge.  The study used modern BI techniques including data mining and even a Bing Maps mashup to demonstrate that Henslow had been studying variation before Darwin had arrived at his theory and was quite likely to have been a strong influence on Darwin’s own thoughts on the subject that made him one of the world’s most famous scientists.