Database Administrator Senior
Resume:
MARK SHVARTS
Brooklyn, NY *****
************@*****.***
Objective: Position as SQL Server Database Administrator or Database Developer
Summary:
** ***** ** *** ****** database administration and development.
Worked with versions 7.0, 2000, 2005, 2008, 2014, 2016, 2019.
Database administration and development. Database design, Transact SQL scripts, stored procedures, triggers, functions, performance tuning, database maintenance and troubleshooting, DMV views, horizontal table partitioning, data compression, extended events, server-side tracing, database mirroring, replication, AlwaysOn Availability Group, backup and restore, files and space management, blockings and deadlocks resolution, resources consumption, capacity planning, server and databases migration, SSIS packages, SSRS reports.
Experience:
Bank of New York Mellon Mar 2022 – Mar 2023
New York, NY
Development DBA (consultant from Softcom)
MS SQL Server 2016, 2019
Managed Dev, Test, QA, UAT database servers. Performed various backups and restores between servers. Managed security access and permissions. Created scheduled jobs to monitor space, long-running open transactions, file utilization, server errors of high severity, procedure execution statistics, index usage, unauthorized database access.
Created Extended Events sessions to capture long running stored procedures along with their parameter values, as well as blockings, deadlocks and heavy processes. Created SSIS package to load Extended Events log files from production file system, parse XML values and upload them to UAT database tables. Used Query Store information as supplemental to performance tuning.
Optimized complex long-running stored procedures by adding new or modifying existing indexes, rewriting SQL code using various techniques like splitting intermediate results to temp-tables and subsequently joining them, replacing large views with extracted underlying tables. Performed page compression for large tables and indexes to reduce physical I/O.
Conducted SQL Server migration from 2016 version to 2019. Ran SQL Server Migration Assistant (SSMA) against all databases in lower environment to detect all incompatibility issues. Fixed all of them (incompatible data types, deprecated join and sort order syntax) for all databases prior to migration, tested them for performance for compatibility level 150 (2019), dropped unused and uncompilable objects. Performed dry-run migration for 8 databases with overall size of 18 TB.
Agadia Systems (Software development firm for healthcare industry) Sep 2020 – Jan 2022
Parsippany, NJ
Database Administrator/Developer (consultant from The Judge Group)
MS SQL Server 2014, 2019
Used SentryOne to find slow performing stored procedures, tuned them for performance by adding new indexes and modifying existing ones, rewriting SQL code using various techniques like changing types of joins, changing procedures with multiple search conditions to dynamic code, replacing large views with underlying tables. Participated in setup and configuring of AlwaysOn Availability Group.
Created aggregate tables to store summarized historical values for further using in real-time data queries, introduced data compression, updated queries and procedures for notification agent jobs, created a process to clone production databases with the purpose to compare Dev environment to a database skeleton from production. Created custom reports in SSRS.
Created a process to clone production databases with no data to compare to lower environments. Created a process to synchronize production objects with TFS version control manager. Created deployment scripts and conducted actual deployments to production.
Mizuho Investment Bank May 2019 – Apr 2020
New York, NY
Development DBA (consultant from EValueServe)
MS SQL Server 2014
Administered Portfolio Risk database on Dev., UAT, SIT database servers for credit risk applications. Created tables and procedures for SCCL (Single Counterparty Credit Limit) reports and applications. Tuned SQL code for better performance by re-writing SQL code, changing tables physical design and adding indexes. Used Profiler to find slow stored procedures and SQL code.
Created procedure to refresh lower environments with data from production for given schemas, table lists and range of dates. Created scripts to dynamically collect daily and monthly checksum values to compare to production to ensure data integrity.
Created scripts to perform hardware assessment for capacity planning. Created a process for deployment database objects to production including version manager (PVCS) and periodical database restores from production to lower environments. Created dynamic code to access and import Oracle data through linked server. Created SSIS packages to import data between environments.
Utilized dynamic management views to detect long running and most resources consuming queries and stored procedures, debugged them for performance. Created a process and SQL code to compare database objects vs. version manager with the goal to synchronize them. Guided developers to writing more efficient SQL code; established a set of better practices for database developments.
Barclays Capital Oct 2018 – Mar 2019
New York, NY
Development DBA (consultant from Enterprise Engineering Inc)
MS SQL Server 2008, 2016
Administered highly concurrent Stockloan OLTP database. Resolved blockings by tuning stored procedures and setting appropriate locking mechanism. Created procedure and scheduled job to capture open transactions. Converted application to a new design by creating a set of scripts to move data from a set of tables to newly designed schema. Created scripts to dynamically collect checksum values before and after conversion to ensure data integrity.
Arranged sequence of full/differential/log backups and created set of scripts to restore database to a point-in-time. Created daily jobs to collect database, tables, indexes size and usage metrics for future capacity planning for decommissioning unused tables and merging overlapping indexes to reduce maintenance time. Participating in testing of SQL Server migration from version 2008 to 2016.
Nomura Securities Apr 2017 – Aug 2018
New York, NY
Development DBA (consultant from Princeton Information)
MS SQL Server 2014
Administered Risk development database with a size of 14 TB. Modified archiving job that moves partitions to an archive database with size of 48 TB by utilizing partition switch out, data transfer and switch in to archive database. Purged old partitions from Archive by switch-out – truncate process. Unpartitioned small tables. Created job to merge empty daily partitions. Set all partitioned tables to auto lock escalation level to utilize partition level locking to minimize blockings of ETL processes.
Enhanced update statistics process to bypass indexes that have been modified since last update statistics using built-in counters. Created process that parses deadlock XML files obtained from Extended Events and enters data into database tables for future trend analysis.
Rewrote slow queries and stored procedures to improve their performance, simplified reconciliation stored procedures. Created a process to find all heaps and rebuilt them as clustered indexes, tuned and refactored queries and stored procedures that are vulnerable to Cardinality Estimator flaw.
Eton Park Capital Management (Hedge fund) Dec 2013 – Apr 2017
New York, New York
Senior Database Administrator (consultant from Truision)
MS SQL Server 2008 R2, 2014
Performed horizontal partitioning of large tables and indexes on multiple databases with setting older partitions to read-only; updated backup and defragmentation processes to bypass read-only archived partitions. Created daily partitioning for extensively large tables and switch out/in processes to facilitate their ETL. Updated defragmentation process to maintain fragmentation history of tables and indexes; used this metrics to dynamically change fill-factor.
Configured transactional replication for 11 databases based on backup-restore methodology with the purpose to unload all reporting processes to replicated server. Created scripts to build primary keys on heap tables based on identity columns, unique and clustered indexes; scripts to drop all constraints from subscriber, to drop all articleless tables. Created process to monitor performance of replication by sending tracer tokens.
Created set of scripts to find unused tables and indexes, duplicate and overlapping indexes based on built-in dynamic management views statistics. Created a script to find tables with large identity values in order to convert them to bigint and a dynamic script to drop and recreate foreign keys.
Created process to collect procedures with high memory consumption, optimized their SQL code. Created procedure to find all the objects that do not compile, jobs to periodically purge job and backup history tables from msdb. Used server-side tracing to monitor deadlocks, DDL changes, performance and troubleshootings, used replayable traces for production data load analysis. Created backup and restore procedure for Litespeed backups. Created dynamic script to restore a chain of full/differential/log backups to a point in time.
Created processes to analyze SQL Server error log, to find dormant SQL statements that holding open transactions and to send email for critical failures. Created scripts to analyze contents of buffer and procedure caches in order to eliminate non-parameterized calls to database. Analyzed VLFs for performance to create optimally sized transaction log files. Created reports for database, table sizes and growth. Researched columnstore indexes, AlwaysOn availability groups and participated in SQL Server migration to version 2014. Created a solution to solve vulnerability of Cardinality Estimator.
Used Solarwinds Database Performance Analyzer (formerly Confio Ignite) to find slow procedures and queries across all databases, debugged them for performance using query execution plan. Used dynamic management views to diagnose performance problems. Refactored and rewrote various stored procedures. Created relational models in SQL Server Analyses Services for Form PF reports and Clearing Brokers using DAX formula language.
Microsoft Corporation Sep 2012 – Aug 2013
Microsoft Enterprise Services
New York, NY
Senior Database Administrator (consultant from Computer Aid Inc.)
MS SQL Server 2008 R2
Administered Production, UAT, SIT, Development database servers for Domain Awareness System. This system is being developed by Microsoft for New York Police Department and is aimed to fight and prevent crime and terrorism. Performed SQL Server installations on clustered and standalone environments. Administered production servers that use mirroring atop of clustered servers. Created transactional replication with synchronization from backup to multiple subscribers.
Created maintenance plans for periodic backups. Performed various emergency and planned backup and restores. Created scripts to record snapshots of DMV views of file and drive I/O statistics, database growth, space usage for capacity planning, log file size for optimization of backup solutions, blocking information for troubleshooting processes, waits and index usage.
Created a process to constantly tracing production server and a job to periodically load default and custom traces into database table for the purpose of performance research. Created various administrative scripts for performance tuning, detection of failed jobs, error log, replication and mirroring monitoring, capturing blocking information. Created server wide triggers to capture information about all DDL changes to production and UAT environments.
Created centralized stored procedure to defragment indexes across all databases on a server. Used dynamic management views to detect long running and most resources consuming queries and stored procedures, debugged them for performance. Created new monthly partitions by splitting partitioning function. Developed various stored procedures and jobs for data processing and reporting solutions.
Bank of America / Merrill Lynch May 2010 – May 2012
Global Credit Risk and Technology
Jersey City, NJ
Senior Development DBA (consultant from Diversant LLP)
MS SQL Server 2008
Participated in development of reconciliation of trades between trading books and accounting ledgers. Created Credit Data Validation (CDV), Staging, Reporting databases in development, SIT, QA, UAT environments. The largest database size is over 6 TB. Created horizontal table partitioning for large staging and fact tables utilizing 28 storage mount points in a round robin way. Implemented sliding window by setting aged partitions to read/only, dynamically creating new, with the purpose to facilitate partial backup.
Created procedures to analyze performance of reconciliation process by taking snapshots before and after reconciliation process of dynamic management views with performance counters and missing indexes information. Used SQL Trace to find poor performing stored procedures, debugged them by analyzing execution plans and missing indexes snapshot; significantly reduced running time of reconciliation process. Created generic error handling procedure that logs errors and sends email notifications while process is running, a deadlock trapping procedure that re-executes the process from a point of failure.
Created process to archive data from staging schema in main database to archive database. Created various scripts for blocking monitoring, storage space management, index usage. Created maintenance jobs to defragment indexes, update statistics, checkdb. Implemented process of version managing in TFS, deployment to upper environments.
Created a process of Full/Differential/Log backup from production database with copying backup files to a file server. Created a job that dynamically scripts out all users permissions from CDV databases in lower environments, restores copy of production from remote filer, and applies permissions back to restored databases. Created various scripts for capacity planning by calculating future space consumption by each mount point. Provided metrics and participated in making decisions for hardware planning.
Bloomberg LP Dec 2006 – Jan 2010
Internal Systems / Sales Support Systems
731 Lexington Ave,
New York, NY
Senior Database Engineer
MS SQL Server 2005
Performed migration from legacy Progress and company proprietary databases to SQL Server 2005. Migrated SDSK (System Help-Desk), Sales Support Systems of total size of 300 GB. Wrote Progress 4GL code and Unix shell scripts to load data from Progress, FTP it from Unix to Windows and BCP it to staging area of SQL Server databases.
Created T-SQL scripts to transfer data from staging area to data warehouse databases. Researched data for performance tuning, created indexes, computed columns, auxiliary entities. Created scripts for database tuning, for monitoring buffer and procedure caches. Used SQL Profiler to resolve performance issues.
Developed dynamic stored procedures for Fast Search for Prospect, SDSK, UUID databases for various searches for Internal Systems, used XML to accept multiple input parameters and process them. Participated in development and performance tuning of SQL code for various applications and reports: Other Reps Notes, Lead Notes, Contacts with Competing Products.
Participated in development of Sales Dashboard, which gathers, loads and analyzes users function hits. This application is used to monitor client activities and behavior in order to generate a customized list of recommended functions and predict removals of terminals. Performed horizontal partitioning of large fact tables and created scheduled job that dynamically creates weekly partitions.
Setup database mirroring with high safety mode for numerous production databases for high availability purpose. Created linked server objects, job to generate snapshots on mirrored databases for read-only access for reporting purposes, data loading jobs that aware of mirroring partner databases, event notifications about partners changing mirroring state.
Guided developers for writing efficient T-SQL code. Created specifications for new production server hardware for SQL Server. Created design, coding, naming conventions for SQL Server development for whole department. Conducted design and code review, implemented best practices for database development.
IntelliRisk Management Corp. Jan 2005 – Dec 2006
(Debt collection company)
New York, NY
Database Administrator
MS SQL Server 2000, 2005
Participated in design of relational datawarehousing database. This database serving for reporting purposes and contains data about debt collection payments loaded from OLTP host systems. Created scripts to transform data from Staging to Reporting database and to validate initial conversion. Conducted in-place upgrade to SQL Server 2005.
Administered 1.4 Terabyte of data. Created scripts to monitor the health of database, defragment data, update statistics, collect space and indexes usage data from system tables, views, DBCC commands, and data management views. Performed tables and indexes partitioning. Created script to monitor large server resources consumption processes. Conducted database performance tuning using SQL Profiler, query plan. Provided recommendations for new indexes, resolved parameter sniffing issues.
Designed SSIS package to load Portfolio Master List from Excel file and distribute it in normalized form among SQL Server database tables. Created database snapshot prior to loading data. Created scripts to cleanse and massage data such as orphans and duplicate records, scripts to import and export data into and from various databases. Supported financial research analysts by providing data reports.
Created jobs, stored procedures, functions and scripts for auditing and analytical reports. Created automated weekly audit report that compares data between host systems and Data Depot and signifies differences between them. Created scripts and procedures for state tax report, to logically delete debtors and payments, dynamic search stored procedure for archived debtors’ information.
JP Morgan Chase & Co. May 2004 – Oct 2004
Investment Bank Division
New York, NY
Database Administrator (consultant from Modis)
MS SQL Server 2000
Consolidated 16 databases of total size of 220 Gbyte onto one server running SQL Server 2000. These databases serving for purpose to analyze and provide data for credit reports and analysis. Wrote various scripts to import logins along with their encrypted passwords and settings to server roles, re-map users to logins, consolidate database objects.
Analyzed stored procedures and jobs for performance, compiled a list of recommendations to create new indexes. Verified, tested and updated DTS packages and jobs running in conjunction with up-stream and down-stream systems.
Designed and implemented disaster recovery plan based on full, differential, log backups of databases and file-groups. Rebuilt development and QA servers with production data. Troubleshooted production servers, created alerts and jobs querying input buffer and system processes.
Concord Financial Technology Dec 2002 – May 2004
New York, NY
Acquired by First Data Corporation in Jan 2004
Database Developer (consultant from Celerity IT)
Oracle 8i database
Participated in design and development of datamart for internet-based platform for stored value card and payment processing. Developed packages for cumulative data transfer from production OLTP to stage using dblink. Developed views and procedures to populate facts and dimensions tables.
Developed various packages in datamart for reporting purposes for various layouts (character delimited, XML, flat file). Developed procedures to detect fraud attempts, package to parse XML data and upload it onto database tables in a batch.
Tuned and re-engineered existing PL/SQL packages and procedures for performance. Developed procedure which dynamically generates code for all packaged procedures for OLTP database. Created scripts for quality assurance and implemented testing strategies.
Personal: Citizen of the United States of America.
Contact this candidate