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

Mixing VARCHAR And NVARCHAR

Solomon Rutzky walks through some of the nuance of mixing VARCHAR and NVARCHAR data types with respect to your collation: There are two types of Collations in SQL Server: SQL Server Collations and Windows Collations: SQL Server Collations (those starting with SQL_) are older Collations that were the only ones available prior to SQL Server 2000. […]

Read More

Data-Driven Fill Factors

Tracy Boggiano has an Extended Event and a procedure to track page splits and then modify index-level fill factors accordingly: So what is the solution?  Well it really depends.  But the solution I wrote walks you through Setting the default fill factor Tracking page splits Lowering the fill factor Read on to learn more.

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31