SQL Bits 9

SQL Bits logoLast month I attended the three day SQL Bits 9 conference held in Liverpool. It was a great opportunity to meet other people in the SQL Server community and gave me a chance to see the city for the first time.

I started working at Bluewire Technologies this summer after graduating from University, and so far I have been doing a lot of work with Microsoft SQL Server. The timing of the SQL Bits conference came at just the right moment for me as I have spent several months getting into SQL Server and I’m now ready to broaden my understanding. It was also a great opportunity to meet other SQL users and pick up tips.

The conference was my first ‘proper’ professional event I have attended, and It gave me the opportunity to experience working away from the office and introduced me to networking with other professionals. I set myself some goals for my time at SQL Bits, which were:

  • Experience: To enjoy the opportunities that occurs when working out of the office
  • Learn: Deepen and broaden my understanding of Microsoft SQL Server
  • Network: Take advantage of the opportunity to meet a large variety of different people

Part of my job is to deal with execution plans, deadlocking issues, query performance problems and the reporting services (SSRS) component of SQL Server, so I was keen to develop my knowledge of these areas at the conference. Topics of particular interest to my work included:

  • Execution Plans: Understand how they are created and can be manipulated
  • Lock behaviour: Discover how transactions and threading affects a database
  • Integration Services: Gain overview and explore relationship with Reporting Services
  • Performance: Be aware of queries to avoid, why they bad and possible resolutions
  • Reporting Services: Increase my level of expertise for creating SQL reports

Day 1: SSIS

The full day session that I attended on Thursday looked at SQL Server Integration Services (SSIS). This session primarily focused on how SSIS can be used to support extract, transform and load (ETL) process. The beginning of this session provided a good introduction into using SSIS, though later demonstrations showed how reporting can be used alongside it.

Key points I took away from this session:

Buffers and Settings: Developed understanding how settings like buffer size affect performance
Control flows: Seen how tasks, block sets and looping can be used to execute data flows
Data flows: Identified how sources, transformations and destinations can be configured
Performing ETL: Identified how flat files can be used to perform incremental loads into SQL Server

Day 2: SQL Performance

SQL Performance: Looked at SQL queries, identified performance issues and walked through the solutions.

Transactions and Locking: Developed understanding of how implicit and explicit transactions behave and which concurrency locks are not compatible. Deadlock prevention mechanisms were also discussed – identified that NOLOCK should rarely be used and uses of database snapshots.

Day 3: Advanced Reporting Techniques

Advanced Reporting Techniques: Broadened understanding through looking at examples to perform reporting. This included been shown how to develop themes for per user basis, creating Gantt charts, heat maps and the effects of using tables and sub-reports.

Execution Plans: Improved my knowledge surrounding execution plans and their effect in a database – am more familiar with behaviour of joins and aggregates.

Map-Based reporting: Seen how map data can be created and used inside SSRS

PowerPivot and QlikView: A comparison and contrast of two business intelligence tools that allow database data to be brought into either Microsoft Excel or Qlikview’s visualisation interface.

SQL Service Broker: Looked at how the SQL service broker can be used to achieve certain functionality.

What I have gained from SQL Bits

The conference has given me more confidence when It comes to solving problems and understanding the performance of our SQL Server databases. I’m also spending more time experimenting with SQL and using the information I took from the sessions which is great.

I am also planning on attending the Bristol SQL User Group as I think it will give me more opportunities to learn and share knowledge.

Resources

Below I have included a set of resources that belong with each session. There should be videos of these talks being made available as part of session list for SQL Bits.

Advanced Reporting Techniques: This presentation was primarily based upon the work on the milestone scheduler. Other useful resources include SSRS reports examples and SSRS Tutorial.

Execution Plans: Slides and SQL code.

Map-Based reporting: Slides, reports, database backups and demo notes are available.

PowerPivot and QlikView: Has provided the slides alongside relevant resources: Powerpivot workbook and Qlikview script.

SQL Performance: Link to SQL code used in the session.

SQL Service Broker (and Gathering Performance Data with PowerShell): Video of session.

Transactions and Locking: There are slides from this session as well as a notes from 2008.