Foreign key indexing

Foreign keys are an essential part of a relational database. Foreign keys helps ensuring the data integrity at the logical level. However many time we forget to create proper indexes that support the foreign keys work effectively. So is there any benefits of indexing the foreign key columns?  It can give… Continue Reading Foreign key indexing

Ad hoc update to system catalogs is not supported

While preparing the demo for my previous post “Optimize for ad hoc workloads – SQL 2008“, I encountered an issue and that’s the topic for this blog. I executed the sp_configure followed by RECONFIGURE and it returned me an error. sp_configure ‘optimize for ad hoc workloads’, 1; reconfigure The error message stated the ad hoc updates… Continue Reading Ad hoc update to system catalogs is not supported

Optimize for ad hoc workloads – SQL 2008

SQL Server 2008 had introduced a new feature that helps optimizing ad hoc workloads. SQL server generates an execution plan for each and every statement that’s executed. The reason is that the SQL server would be able to re-use the execution plan when the statement run next time. However, there… Continue Reading Optimize for ad hoc workloads – SQL 2008