Ian Posner

SQL Server, BI and C# High Performance Architect, Developer and Troubleshooter

Summary

Hardcore development consultancy since 1992. Specialist in high performance application development & troubleshooting utilising Microsoft technologies, including SQL Server, VB, C# and .NET (both ASP.NET & WinForms). Proper Object Oriented Analysis and Design

Portfolio

International Financial Data Services

http://www.ifdsgroup.com/

Engaged to enhance performance of a new wealth management platform system. Analysed and reported on major design changes required to increase scalability and concurrency, the scope of which covered client code, database design, server configuration, topology and hardware specification and storage subsystems. Conducted in-depth evaluation of specialised high-performance flash subsystems. Designed and implemented high-availability infrastructure topology, including full risk identification, impact analysis and mitigation to fulfil business continuity requirements. Identified need for centralised performance monitoring. Conducted multi-vendor evaluation, resulting in implementation of chosen solution. Authored corporate Solution Building Block strategy for Microsoft SQL Server, providing a path to fulfil goal of enterprise server consolidation. Wrote and implemented automated builds for database server deployments. Developed a number of utilities to process data at high speed.

skills used: SQL, SQL Server, Consultancy, C#

IMGroup

http://www.imgroup.com/

Full analysis, report and recommendations of existing Counterparty Risk Platform for Tier 1 Investment Bank using SQL Server, SSAS, SSIS and C# code. Quantitative performance analysis and implementation of major performance enhancements including incremental cube partition definition and processing, ETL rationalisation and performance enhancement. Development of additional components for high performance processing including SQL Server Partitioning and Partition Switching using SMO and SSIS processing of PNL Explains, processing millions of explains per minute using shared caches and extensive buffer parallelisation. Cube optimisation including dimension rationalization of correlated dimensions. Advice on hardware specification and advanced configuration.

skills used: SQL, SQL Server, C#, Consultancy

UBS Investment Bank

http://www.ubs.com/

Designed and built a datawarehouse for loading and analysis of 2.5 billion records (load rate: c.1,000,000 rows/second) of structured credit risk data to calculate the bank’s Comprehensive Risk Measure: Using SQL Server 2008, Integration Services (SSIS), Analysis Services (SSAS) and Analysis Management Objects (AMO), the system was designed to store 6TB of compressed data calculated by a grid of 17,000 processor cores. Was pivotal in planning of database and cube partitioning, both in design and coding of maintenance routines for programmatically controlling pruning of ageing partitions and setting partitions to read only to facilitate filegroup backup within maintenance windows. Full integration with Autosys scheduling and Netcool monitoring. Also designed and coded a fully thread-safe multithreaded file parser for identifying and filtering good rows from bad in C# 3.5 and OLAP Partition Pruner for deleting SSAS partitions according to date criteria with separate retention periods of month-end and non-month-end data. Later designed the Structured Credit Risk Warehouse – a 250 table database integrating Risk, PNL (Estimated and Actuals) and Valuations. Defined strongly-typed XML Schemas (XSDs) for Credit Valuation and Risk data including regular expression restrictions. Wrote multi-threaded regular expression flat file parser in C# for mass high-speed verification of multiple files simultaneously on multi-core servers. Developed Olap Partition Pruner in C# for partition pruning according to variable date criteria for use across the bank’s systems.

skills used: .NET, C#, SQL Server

QSoft Consulting

http://www.gaydar.com/

Repeat business for a company with one of the busiest adult dating sites in the world: Over 50,000 simultaneous users at peak times, QSoft’s main site was experiencing a problem with a newly introduced feature, resulting in periodic refreshes of “who’s online in your area” taking 30 seconds, causing HTTP 500 errors on web servers. As a result of query plan analysis, created additional indexes, performed some modification of existing indexes and modified the query to cut the time from 30 seconds to 0.176 of a second (170 times faster).

skills used: ASP, SQL Server, ASP.Net, C#, .NET

Lloyds Banking Group

http://www.lloydsbankwholesale.com/

Developed high-performance object-oriented loading libraries for credit risk and PNL SQL Server 2005 database: Libraries included: OO data-access: Logical data entities with high performance unique key caching and lookups (100,000 lookups per second) and encapsulated database synchronisation; Two-way source destination cached mapping translation library. Developed SQL Server CLR scalar and aggregate functions including unique bitwise binary hashing algorithm to enforce uniqueness of row combinations and orders. Designed and implemented high bandwidth IO subsystem increasing backup speed by 2.75 times to 715MB/s. New database server hardware rolled out to database of 0.75TB with only seconds of downtime. Analysed and Re-Indexed fact tables, increasing database backup speed to 820MB/s. Developed SQL Server Analysis Services (SSAS) Cubes for PNL and Risk, focusing on dimensional model design to accelerate cube build times. Addressed issues of deleting specific loads from cubes and ensuring consistency with transactional systems by selectively defining limited selection of partitions with ROLAP storage. Installed, tested and documented performance and rollout options for migration to SQL Server 2008. Designed and coded inactive login tracker for DBA team to ensure SOX compliance using .NET 3.5, Active Directory for group membership expansion and SQL Server SMO to interrogate SQL Server error logs and logins. Created additional functionality to existing SQL Server Integration Services (SSIS) packages. Advised DBA team on SSIS and SSAS deployment and management. Advised DBA and Server teams on use of virtual machines using VMWare ESX platform including ensuring service levels.

skills used: C#, SQL Server, .NET

WestLB

http://www.westlb.com/

Working in the Front Office Structured Credit team, analysed, designed and developed reporting system for MarkIT data into a single, normalised schema using SQL Server 2005. Bond, ABS, Loan and Entity-Only CDS data handled with respective indexes, including series, version and tranche handling. Developed automated data cleansing and load process using SQL Server Integration Services (SSIS), including performance optimisation. Developed prototype cubes using SQL Server Analysis Services. Developed MarkIT download console application and object-oriented extensible report-definition library to support unusual http post report retrieval method defined by MarkIT and to facilitate the easy addition of new reports. Specified new server hardware including quantitative assessment of IO throughput of raid subsystem.

skills used: SQL Server, C#

Hewlett Packard

http://www.hp.com/

Repeat business for Hewlett Packard Business Services â Successfully planned and implemented SQL 2000 ï  2005 upgrade. Redesigned PNL and Risk database to cope with a 5TB database receiving Summit feeds including normalisation, denormalisation for performance enhancement using indexed views. Programmed Integration Services package to migrate data from legacy to new schema. Programmed functions for hierarchy and network expansion. Developed standard build automated SQL Server 2005 install for group DBA team, including auto-detection of OS versions, multiple configuration choices and post-install hardening for x86 and x64 platforms. Identified and solved major infrastructure IO bottleneck boosting IO performance seven-fold replacing SAN mirroring to DR site with asynchronous database mirroring. Use of Windows Powershell. Identified hardware bottlenecks, analysed and suggested alternative hardware capable of 4 Gigabytes per second throughput! SQL query optimisation (e.g. 31 second process reduced to <15ms; 12 min report reduced to 0.1 sec)

skills used: Database, Database, SQL Server 2005

Gaydar

http://www.gaydar.com/

Designed new high-performance database schema for major adult website with 40,000 simultaneous users (within a 15 minute session-timout period) on SQL Server 2005 x64 and coded support and administration program in .NET 2.0 Windows Forms including image manipulation (scaling, cropping rotating). Code included smtp mail integration, LDAP & SQL queries to integrate database with corporate security infrastructure, compound user controls and full object-oriented n-tiered design with separate class hierarchies for data access, business objects, forms and controls. Coded mass image mirroring console program in C# .NET 2.0 and wrote ASP.NET 2.0 image upload & image processing components. Ported several Java classes to C#. Wrote complex T-SQL DDL scripts for high-performance smooth rollout of major database changes to 60GB database within tight time window. Specified and documented build of HP DL-585 database server utilising Windows Server 2003 64 bit Enterprise Edition.

skills used: Perl, E-Commerce, ASP.Net, .NET WinForms, SQL Server 2005

Virgin Management Ltd

http://www.virginmanagement.co.uk/

Objective to analyse existing MS SQL Server 4.21a database system with clients connected by ISDN with a view to increasing stability and performance through migration to version 6.5. Identified performance bottlenecks in networking and application design responsible both for instability and poor performance. Led redevelopment of front-end application utilising server-side processes to minimise effects of network latency and bandwidth. Analysed usage patterns and query performance using SQL Trace utilities to prioritise work schedule. Examined query plans and optimised where necessary with indexing, query redesign and optimiser hints. Result: increased application performance by up to 10 times.

skills used: Database, VB, SQL Server 2000

First Data

http://www.firstdata.com/

Senior consultant for management of five production database servers for First Data Resources, the worldâs largest processor of credit card accounts. Responsible for a) cheque clearing data warehouse for the Bank of England (c.90Gb) processing £200m daily; b) call centre server for Cardholder Services department (120 users, 18 hours/day operation); c) two servers for merchant services department (image storage and workflow); d) central accounts server. Synchronized sort orders/code pages of servers throughout FDR by BCP of data out and in. Initiated review of server hardware infrastructure to improve resilience and performance. Performance tuning of all SQL Servers.
Redesigned the data warehouse for the Bank of Englandâs Cheque Clearing Reporting System and in so doing, cut the size of the database in half through use of appropriate data types and dimensional modelling. (At the time of rollout, the previous data warehouse was over 100Gb). Datawarehouse facilitated building of Cubes using OLAP Server (Analysis Services) and subsequent MDX queries. Entity-Relationship database modelling using Erwin.
Advised PC Development department on methods of data access to SQL Server (RDO/ADO), database design, performance enhancement and reduction of locking contention throughout all major projects. Additionally demonstrated use of system and database catalogue tables to enable deeper integration of clientsâ security model with integrated security features of SQL Server. Reduced query response times by conducting indexing reviews of slow processes (in one case, reducing query time from 8 minutes to 1 second), and enhancing transaction control by back-end implementations. Analysis of 3rd party client/server applications using SQL Trace for performance enhancement.
Enhanced Crystal Report performance by simplifying and moving processing to the backend, increasing speed by up to 20 times.
Established and documented database design standards. Programmed file pre-processing in PERL to improve speed and resilience of data import for redesign of massive database. Programmed high performance (1000 rows/sec) random data generator in PERL for stress testing of prototype database designs.
Provided detailed design input to three tier intranet solution â integration of SQL Server with Internet Information Server using Integrated Security and NT Authentication of users through the web server.
Planned rollout of new versions of software and hardware, advising on database design issues. Enabled major increases in performance through back end processing, re-indexing, implementing insert-row-level-locking or reclustering to reduce lock contention and pinning of selected tables in RAM to avoid cache flushing of frequently used small tables.

skills used: Database, Database, VB, SQL Server 2000

Going Places

http://www.goingplaces.co.uk/

Hired to provide strategic consultancy for the worldâs largest replicated SQL Server scenario: Central database replicating to 715 SQL Servers (5000 clients) situated throughout shops in the UK. Advised on replication optimisation for data transfer across 64kbps frame relay connections. Liaised with 3rd party software houses, establishing standards for phased upgrades of database structure. Advised on proposals for accumulation of distributed data in the data centre, including use of Microsoft Message Queue Server.
Identified weaknesses in change management control and developed a Change Management system to handle this. Analysis, design and programming using SQL Server 7.0 beta 3, Visual Basic 5 and ADO 2.0.
Provided 3rd line support identify sources of problems encountered during rollout, including extended blocking resolution and deadlock analysis. Specified hardware requirements for 2nd tier replication servers for distribution databases.
Team eventually disbanded as a result of smoothness of rollout!

skills used: Database, Database, VB

Visa International

http://www.visa.com/

Provided performance troubleshooting of SQL Server database using snowflake dimensional model. Tuned SQL Server instigated full backup and housekeeping standards. Analysed deficiencies and attributed lack of performance to data access methodologies, front-end tools, indexing strategy and base table design. Proved data access time could be reduced from 30 seconds to 15ms by realistic volume prototyping using star schema instead of snowflake, efficient indexing and alternative data access techniques.

skills used: VB, SQL Server 2000

http://www.csfb.com/

Hired to ensure investment bankâs first mission critical system on SQL Server v.7.0 (Intellimatch) met requirements of speed, scalability and robustness. Quantitative performance analysis using SQL Profiler to identify frequent, long running SQL statements to prioritise database tuning. SQL and NT Server analysis using Performance Monitor to log and monitor performance statistics ensuring acceptable NT paging, CPU utilisation, queue lengths on RAID controllers/network cards, etc.
Within 3 weeks, system beating performance requirements by 500% processing 350,000 trades per hour slaughtering previous performance record by Sybase 11 on Unix.
Configuration of disaster recovery servers using hot standby techniques (transaction log shipping) and multi-step jobs. Support of business critical servers in highly complex multi-domain structure with tight security lockdown of servers including lockout of NT Administrators. Integration with Unix based monitoring tools and troubleshooting of Unix/NT issues.
Advised on specification and configuration of Compaq Proliant Servers, focusing on I/O subsystems with emphases on resilience and performance.
Performed installations of SQL Servers (6.5 & 7.0) including configuration of automated email notification of failed jobs, as well as upgrades of 6.5 to 7.0 including advice to developers of compatibility and architectural differences. Regular DBA duties of dumping/restoring/monitoring on both 6.5 and 7.0 and support to external development teams on version 7 enhancements and idiosyncrasies as well as advice on technical architecture and design.
Installation and support of 3rd party packages on SQL Server 7 and 6.5 including Microsoft Site Server, Sentry, ABC LAN Licenser, ArcServe, Euroclear (Euclid), NetIQ.

skills used: Database, SQL Server 2000

Ericsson Telecom

http://www.ericsson.com/

Mission: Increase billing system performance from 10 to 60+ transactions per second on worldâs first voice phone system over IP in Madrid, Spain. After analysis revealed bottlenecks, lead total redesign in C++ of data access resulting in performance gains such that sufficient load could not be generated to cause further bottleneck! To date, system stressed to around 150 transactions per second.

skills used: SQL Server 2000

Sungard

http://www.epic.sungard.com/

Mission: Provide consultancy on migration of product to SQL Server 7.0 and work to improve performance and resilience of product range targeted at banking industry. Improved and upgraded documentation. Troubleshooting production problems. Analysed bottlenecks of existing processes (Performance Monitor, SQL Profiler) and coded proofs of concept for alternative designs. Reported on ways to reduce support costs and raise revenue by refocusing development efforts and restructuring teams and development methodologies. Devised and implemented method of building single SQL Server 7 servers with multiple code page/sort orders for supporting different customer configurations. Designed and implemented tests for Microbank in association with Compaq (sequential and random read and write, DBCCs, backup) for optimal hardware configuration of Compaq Proliant 8000 for SQL 7 concentrating on IO configuration (RAID 0+1 vs RAID 5 using 8k and 64k striping and SmartArray Controller Cache configuration). Test results documented and utilised by Compaq for SQL Server 7 configuration.

skills used: VB, SQL Server 2000

Deloitte & Touche

http://www.deloitte.com/

Assignment to investigate poor performance of specific web pages for international dealership sales automation system for Renault. Technology based upon Trilogy Java Servlet connected by JDBC to SQL 7 backend. Analysis revealed specific proportions of load times attributable to each component and report produced.

skills used: VB, E-Commerce, SQL Server 2000

MoneyGator

http://www.moneygator.com/

Lead technical redesign of entire website. Identified weak points of current system and initiated proof-of-concept to quantitatively test four approved Microsoft architectures (IIS-ADO-SQL, IIS-XML-SQL, IIS-MTS/COM+-SQL (with and without MTS transactions)) against criteria of scalability (contention & performance), ease of deployment and code maintenance using stress testing tool (LoadRunner). Testing found weaknesses in two of the Microsoft architectures one of which would have been catastrophic to project. Results and conclusions documented. Analysed user requirements, redesigned database including Session management by database server with follow-me-cookies, full request logging and 2ms database overhead. Other design goals included easing ability to create affinity deals and to offer new types of product. Design incorporated Cascading Style Sheets, SQL-XML data access, session-less state management using encrypted query strings to enable non-sticky load balancing, DTS transformations using regular expressions with PerlScript and SQLMail integration. Technologies: Windows 2000, SQL 2000, IIS 5, COM+, VB, ASP and Perl. Personally coded all stored procedures, user-defined functions (scalar and table), triggers and DTS packages. Reduced deadlocks on current site from frequent to none utilising trace flags to capture deadlock information and re-indexing appropriately. Further increased scalability of current site by increasing concurrency and reducing duration of MTS transactions (average 17 sec down to 3.5 for worst case SQL) utilising SQL Profiler and block analysis stored procedures.

skills used: VB, E-Commerce, SQL Server 2000

South Bank University

http://www.sbu.ac.uk/

Mission: To investigate stability problems of main web server. Analysis revealed inappropriate IIS configuration, hacking and lack of resilience. Implemented âhardeningâ of web server, restricting file permissions, removal of all but essential services, disk mirroring, consolidation of disk partitions. IIS config hardened by removing unnecessary website features. Analysed ASPs to identify inefficient code hogging resources and notified developers. Reported on weaknesses of current architecture and solution.

skills used: VB, E-Commerce, SQL Server 2000

CallServe

http://www.callserve.com/

Mission: To analyse the existing Datawarehouse design and to suggest methods of optimisation to enhance scalability by reducing load and cube generation times, and to enhance MDX query performance. Analysed hardware config, underlying table structures, indexes and redesigned largest fact and dimension tables, reviewing data granularity. Demonstrated pros and cons of indexed views. Consolidated multiple dimensions into multiple levels to enhance query performance. Analysed DTS transformations and package logs to determine critical tasks and demonstrated methods of increasing performance through bcp out and fast mode bulk inserts and task parallelisation on SMP servers. Suggested conforming dimensions, reducing dimensions per cube where data correlation would reduce cube sparsity. Use of virtual cubes to replace larger high sparsity cubes with multiple high density cubes. Also fact table and associated cube partitioning to aid in maintenance.

skills used: Database, SQL Server 2000

GFInet

http://www.gfigroup.com/

Called in to deal with instability of the Front Office trading system. Tuned SQL Server 2000 and analysed VB6 code to identify poorly performing components. SQL Server stability problems successfully resolved and code weaknesses reported to development manager for resolution.

As a result of earlier successful worked, called in again, this time to identify recent blocking issues and report on remedial action. Analysis revealed inappropriate use of server-side cursors, unnecessary table scans, long running transactions and excessive round-tripping.

Mission: To rewrite large portions of a front office electricity commodity trading system built around SQL Server 2000, to increase stability, reduce inter-process database contention and locking through shortening transaction times and reducing round-tripping. Extensive high performance interface redesign of including use of fire-hose cursors, advanced T-SQL (inner join substitution with derived tables where statistics showed a more efficient query plan) and Windows API calls to freeze screen updating during large grid fills. Additional programming on other projects using Data Transformation Services (DTS), VB and T-SQL.

skills used: SQL Server 2000

Bloomsbury Publishing

http://www.bloomsbury.com

Tasked with reviewing and breaking the first stage of an intranet/extranet, reviewed SQL Server database design, indexing, ADO data access and ASP, identified security vulnerabilities through penetration testing using SQL injection, data consistency issues due to lack of transaction isolation. Web page performance increased by up to 10 times. ASP configuration and coding optimised (reduction in script/html context switching, string handling, buffering, data access, data caching, Response.Redirect vs Server.Transfer, etc). Advised on data migration strategy and hardware selection. Extensive report prepared detailing remedial action, performance enhancement, project risk assessment and future upgrade to .NET.

skills used: Database, E-Commerce, SQL Server 2000

TD Securities

http://www.tdsecurities.com/

Designed and developed object-oriented C# Windows Forms / SQL Server 2000 application for credit derivative / credit risk management & reporting system (CDOs/CDSs/CreditEvents). Full management of credit ratings, collateralised debt obligations, credit default swaps, and generation of complex reports in Excel using DTS.
OO inherited-form component based architecture utilising databinding to datasets with SqlClient. Full OO model written with call-backs implemented by custom delegates as well as EventHandlers. Wrote customised control library of both inherited and user controls based upon Microsoft and ComponentOne technologies. Inherited form design permitted rapid form development such that only SQL statements, additional controls and databinding needed in subforms, resulting in new forms being generated in as little as five minutes. Centralised exception handling and logging. User preferences held in Isolated Storage permitting roaming profiles (using XML serialisation). Application designed for no-touch-deployment using web servers. Expertise gained in .NET Security (Runtime Security Policies, Applicaton Configuration, etc). Use of GSApps DataGenerator to generate bulk random data and Red Gateâs SQLCompare to prepare database migration scripts.

skills used: SQL Server 2000, .NET WinForms

Oakley Internet

http://www.o-net.co.uk/

Troubleshot VB.NET/ASP.NET/SQL Server 2000 application stability and performance. Identified and rewrote several inefficient processes. Developed prototypes using SQL Server 2005 to demonstrate enhanced full-text searching using Thesaurus, as well as custom multiple thesauri and search misspelling correction subsystem. Corrected major problems of incompatible SQL Server collations. Recommended strategies for structured source code control, configuration management and task-resource allocation.

skills used: Database, SQL Server 2000

Bentley Motors/QData Europe

http://

Architected, designed and coded n-tiered content management extranet using C#, ASP.NET, SQL Server 2000. Architected database schema, class libraries, and process flows from use case definitions. Documents and images stored in database as BLOBs, design utilised highly extensible object oriented classes for command caching interrogating database metadata to automatically build single thread-safe copy of stored procedure objects, data-access (including self-configuring multi-table datasets) and document processing. Full-text indexing search, authentication by Windows Login or RSA SecurID token. Web deployment via automated proje

skills used: Database, C#, ASP.Net, SQL Server 2000

News Corporation

http://www.page3.com/mobile/

Mission: To increase performance of SMS text messaging content subscription service from 2 to 50+ incoming and outgoing messages per second. Designed and configured active/passive cluster using Windows 2000 Advanced Server and SQL Server 2000 Enterprise Edition. Analysed and partially rewrote existing application code (BEA Weblogic/Java J2EE and Empower SMS gateway). Identified bottlenecked code and rewrote with appropriate re-indexing and consolidation of database calls. Tuned Weblogic server connection pooling configuration and consolidated disparate databases to aid recovery. Resolved extended blocking and deadlocking problems. Rewrote complex Crystal Reports. Optimised IBM Netfinitty Fibre Array to RAID 0+1, optimising stripe size, write-back block size and NTFS cluster size as well as tuning array controller cache. Result: Application beat 50 transactions per second for 80% of test scenarios and increased 15 times for the remaining 20%. Analysed development processes contributing to weaknesses in current system design and documented these together with remedial action necessary.

skills used: Database, Database, E-Commerce, SQL Server 2000

Global Beach

http://www.essexford.co.uk

Complete database redesign. Performance enhancement of existing site. Data migration using Data Transformation Services (DTS). Proof-of-concepts in C# .NET.

skills used: Database, C#, Perl, Database, VB, E-Commerce, ASP.Net, SQL Server 2000

Microsoft MSN UK

http://shopping.msn.co.uk

Implementation of MSN Shopping. Work focused on analysis of existing US system to identify and mitigate inherent project and technical risks, redesign of server topology and infrastructure to increase internet security and publishing process robustness, risk analysis of SQL jobs and redesign with emphasis on resilience and performance including coding to enhance content distribution speed to front end web servers by 400%. Designed automated jobs using Data Transformation Services (DTS). Redesigned critical process for loading of merchant data using ASP, VBScript, Regular Expressions, ADO, SQLDMO, BCP and T-SQL stored procedures. Wrote proof-of-concept in Perl with COM automation of ADO for resilient bulk loading. All servers based upon Windows 2000 Advanced Server. Wrote ASP for mission critical failover of F5 BigIP Load Balancer. Configured services including Terminal Services, WinVNC, Rexecd and Telnet. Configuration of OLAP Server for data warehouse. Installation and configuration of Compaq Proliant 6400s including advanced performance configuration of SmartArray 4200 controllers (modification of stripe and cluster sizes) and quantitative performance testing of network libraries (Named Pipes vs. TCP/IP Sockets). All risk analysis, mitigating recommendations, server builds and code extensively documented.

skills used: Database, Perl, Database, VB, E-Commerce, SQL Server 2000

Work Flexibility

Location flexibility

Within London.
Within Greater London.
Within United Kingdom.
Would temporarily relocate.
Can telecommute.

Length of contract

Days.
Weeks.
Months.
Years.