MSSQL Experience & Optimization
- Software Developer (~5 years)
- Hands-on with database design, indexing and performance tuning.
- Example project: HR Management System, Room Booking System, etc.
MSSQL
.NET Web API
Indexing
Performance Tuning
HR Management System
- HRMS stores employee profiles, departments, attendance, claims, etc.
- Read-heavy workload: HR queries, filters, monthly salaries, etc.
- My responsibility: make queries fast and consistent using MSSQL features.
Indexing – Improving Query Speed
- An index is a data structure that helps SQL Server find rows faster, similar to a book index.
- Critical for HR queries that filter by EmployeeId, DepartmentId or AttendanceDate.
- Benefits: faster SELECT/JOIN/ORDER BY and reduced I/O.
- Trade-offs: extra storage and additional work on INSERT/UPDATE/DELETE operations.
Clustered vs Non-Clustered Index
- Clustered index defines the physical order of rows in the table; usually on the primary key such as EmployeeId; great for range queries.
- Only one clustered index per table, so the choice must reflect the main access pattern.
- Non-clustered index is a separate structure with key + pointer; multiple can exist.
- In HRMS, non-clustered indexes were added to EmployeeCode, DepartmentId and AttendanceDate to speed up filtering and reporting.
SQL Profiler – Finding Real Bottlenecks
- SQL Profiler captures live activity: query text, duration, CPU, reads and errors.
- Used to trace slow HR reports and high-frequency attendance or claim queries.
- Helped identify which stored procedures and statements needed indexing or refactoring.
Execution Plan – How a Query Runs
- The execution plan is SQL Server's roadmap for executing a query.
- Shows operations such as Index Seek/Scan, Key Lookup, Filter and Join type.
- I used it to verify that critical HRMS queries were using the right indexes and to spot expensive scans.
- Plans were compared before and after changes to confirm real performance gains.
Index Seek vs Index Scan
- Index Seek: SQL Server jumps directly to matching rows when the predicate is selective and matches the index; ideal for queries like WHERE EmployeeId = @Id.
- Index Scan: SQL Server reads many or all rows in the index or table when there is no suitable index or the filter is not selective.
- In HRMS, I reviewed execution plans to replace unnecessary scans with seeks by adding or adjusting non-clustered indexes.
Room Booking System
- System allows employees to reserve meeting rooms via a web interface.
- Multiple users may try to book the same room and time slot concurrently.
- Goal: avoid double-bookings and keep data consistent even under concurrent access.
Transactions & Locking
- Used database transactions to group room availability check and booking insert into a single atomic unit.
- Relied on SQL Server locking (shared and exclusive locks) to prevent conflicting writes on the same room/time slot.
- If a booking transaction was in progress, competing bookings for the same slot would wait or be rejected based on business rules.
Summary – Reliable & Fast MSSQL Design
- HRMS: applied indexing, execution plans and SQL Profiler to improve query performance.
- Ensured key HR queries used index seeks instead of table scans where it mattered.
- Room Booking: used transactions, locking and appropriate isolation to avoid double-bookings.
- Overall approach: combine performance tuning with strong data consistency guarantees.
