Watch Those Indexes

Kennie Nybo Pontoppidan explains why that one university registration system was always throwing errors:

Remember that myITU was implemented on an Oracle database. Unlike on SQL Server, all tables in an Oracle database are physically represented as heaps, unless explicitly specified otherwise. That means no indexes. And I didn’t know anything about database performance back then, so I didn’t add any. Effectively any query against the course table would give a full table scans in the EnumerationType and EnumerationValue tables as a side effect. Fast forward to course enrolments…

At ITU, we had implemented a selection algorithm, which considered both the study program you were enrolled into as well as how early you registered for a course. Early birds got the course, so students would be ready when course enrolment was opened at 12pm. And at 12.05 myITU would start failing with 500 Internal Server Errors.

This is a pretty common occurrence, followed up by the “let’s add all the indexes” phase.

Related Posts

Index Design When Handling Sorts

Erik Darling walks us through some of the nuance of index deisgn: When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access […]

Read More

Retrieving Server And Database Permissions

Kenneth Fisher wants you to know about your SQL Server’s permission setup: Our host for T-SQL Tuesday this month is Jens Vestergaard (b/t) and he has asked about our favorite SSMS tool. My initial thought was to talk about using solutions in SSMSbut I’d already written about that. My next thought was to write about sp_DBPermissions and sp_SrvPermissionswhich of […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31