andyfrench.info

I have used NHibernate for some time now but have recently started to reengage with Microsoft’s Entity Framework. I came across Julie Lerman’s course Entity Framework in the Enterprise on Pluralsight. This is an interesting course and deals with Repositories and the Unit of Work pattern, two concepts I have used extensively with NHibernate. But Julie also deals with a DDD concept – the Bounded Context. It’s been a while since I’ve dealt with DDD so I thought I’d spend some time re-familiarising myself the Bounded Context.

Firstly, let’s see what Eric Evans had to say about Bounded Contexts in his book, “Domain-Driven Design, Tackling Complexity in the Heart of Software”.

“Multiple models coexist on big projects, and this works fine in many cases. Different models apply in different contexts.” [1]

“Multiple models are in play on any large project. Yet when code based on distinct models is combined, software becomes buggy, unreliable, and difficult to understand. Communication among team members becomes confused. It is often unclear in what context a model should not be applied.” [2]

“A model applies in a context. The context may be a certain part of the code, or the work of a particular team. For a model invented in a brainstorming session, the context could be limited to that particular conversation.” [2]

“A BOUNDED CONTEXT delimits the applicability of a particular model so that team members have a clear and shared understanding of what has to be consistent and how it relates to other CONTEXTS. Within that CONTEXT, work to keep the model logically unified, but do not worry about applicability outside those bounds. In other CONTEXTS, other models apply, with differences in terminology, in concepts and in rules, and in dialects of the UBIQUITOUS LANGUAGE. By drawing an explicit boundary, you can keep the model pure, and therefore potent, where it is applicable. At the same time, you avoid confusion when shifting your attention to other CONTEXTS. Integration across the boundaries necessarily will involve some translation, which you can analyze explicitly.” [3]

This makes perfect sense. You can see how the concept of a user, for example, would have different meanings to different departments within a business. If we tried to model a single user that has all the attributes required to satisfy all departments we might end up with a bloated and confused user entity. By separating the model into different bounded contexts, for different departments for example, and having the user defined separately within each context the result is more sharply focused and less confusing models.

The question that might remain is, how could we keep different bounded contexts synchronised? For example, if a user is created in one department in our hypothetical business application, how would we also create it in another at the same time? This could be accomplished using Domain Events or some kind of correlation ID for the same entity in different contexts.

 

See also

• Shrink EF Models with DDD Bounded Contexts, Julie Lerman

 

References

[1] Eric Evans, “Domain-Driven Design, Tackling Complexity in the Heart of Software”, ISBN 0-321-12521-5, p335.

[2] Eric Evans, “Domain-Driven Design, Tackling Complexity in the Heart of Software”, ISBN 0-321-12521-5, p336.

[3] Eric Evans, “Domain-Driven Design, Tackling Complexity in the Heart of Software”, ISBN 0-321-12521-5, p336-357.

Read More

This post really just for me. You may get something from it but don’t expect very much! It’s been a while since I visited SQL Server indexing fundamentals so I thought I’d do a bit of revision and this post is really just my notes.

Pages

“The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.” [1]

• The page size is 8 KB
• This means databases have 128 pages per megabyte
• Each page begins with a 96-byte header including:
  • Page number
  • Page type
  • Amount of free space on the page
  • Allocation unit ID of the object that owns the page

 

 

Extents

“Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.” [1]

 

Heaps

“A heap is a table without a clustered index.” [2]

“If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row.” [2]

• Do not use a heap when
  • the data is frequently returned in a sorted order
  • the data is frequently grouped together
  • ranges of data are frequently queried from the table
  • there are no nonclustered indexes and the table is large

 

Indexes

“An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.” [3]

 

Clustered Indexes

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order. [3]

• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. [3]

 

• Clustered indexes
  • have a row in sys.partitions (with index_id = 1)
  • determine the physical order of data
• Consider using when
  • There are a large number of distinct values in a column
  • On columns that are
    • frequently accessed
    • frequently searched for a range of values
  • Queries return very large result sets

 

Nonclustered indexes

• Nonclustered indexes
  • do not affect the physical order of the data rows
  • have a structure separate from the data rows
  • contain the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value
• The pointer from an index row in a nonclustered index to a data row is called a row locator

 

Primary keys

• A primary key automatically creates a clustered index, except when
  • a nonclustered primary key has been explicitly specified
  • a clustered index already exists

“Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table and identify a particular column to be the primary key, the Database Engine automatically creates a PRIMARY KEY constraint and index on that column.” [3]

 

Unique indexes (unique constraints)

• A unique index is created when you create PRIMARY KEY or UNIQUE constraint. [4]
• You can create a unique index independent of a constraint or when you create an indexed view

 

Filtered indexes

“A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.” [5]

• Use a filtered index when a well defined subset of results are part of a select statement.

 

Index Maintenance

Reorganise

• Physically reorganises the leaf nodes of the index only

Rebuild

• Drops the existing index and recreates it

Tips

• Avoid DBCC SHRINKDB because it increases index fragmentation
• Put the clustered index on the column that is distinct and increasing in value to avoid fragmentation
• Check index fragmentation and then
  • If fragmentation is >5% <= 30% then reorganise the index
  • If fragmentation >30% then rebuild the index [6]

 

Beware

• Too many indexes on a table can reduce performance
  • Execution plan can be less efficient
  • Queries can be less efficient
  • Queries may use the wrong – less efficient – index
• Duplicate indexes offer no advantages and can reduce performance during inserts, updates and deletions
• Unused indexes may also reduce performance during inserts, updates and deletions

 

References

[1] Understanding Pages and Extents – SQL 2008 documentation

[2] Heaps (Tables without Clustered Indexes) – SQL 2012 documentation

[3] Clustered and Nonclustered Indexes Described – SQL Server 2012 documentation

[4] Create Unique Indexes - SQL Server 2012 documentation

[5] Create Filtered Indexes - SQL Server 2012 documentation

[6] Reorganize and Rebuild Indexes - SQL Server 2012 documentation

Read More

I recently came across 2 fantastic resources for understanding and implementing the essentials of maintaining SQL Server databases.

The Accidental DBA

As a software developer you often encounter situations where you become the DBA whether you want to or not. It is not uncommon to be working in an environment where there is no dedicated DBA but the applications under development rely on SQL Server as a back-end data store.

So, what do you need to know to keep the SQL Server database up-and-running and to prevent queries from slowing to a standstill? Well, it turns out there is an excellent series of blog posts on www.sqlskills.com called the Accidental DBA, offering 30 days of top tips. You can find the series here:

• http://www.sqlskills.com/help/accidental-dba/

 

SQL Server Maintenance Solution

Something referred to by the Accidental DBA series is the SQL Server Maintenance Solution put forwards by Ola Hallengren. This awesome set of scripts allows you to run backups, perform integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

• http://ola.hallengren.com/

 

UPDATE 24/12/2013 – Replacement for sp_helpindex

Also on sqlskills.com, Kimberly Tripp has a replacement for the standard sp_helpindex stored procedure.

• New SQL Server 2012 rewrite for sp_helpindex

Read More