Database – Constraints – Foreign Keys and Indexes

Background

This is a follow-up post to our initial discussion on Candidate Keys.  In this post we will discuss Foreign Keys index requirements.

 

Source Control – Github

The SQL Source code files have been placed into Github.

The URL is https://github.com/DanielAdeniji/MicrosoftSQLServerForeignKeysIndexRequirements

 

List of Files

Here is the list of files:

 

File Name What is it
DataModel / CommerceLogicalModel.png Logical Data Model – Created using Database Diagram in Microsoft SQL Server Management Studio – Screen Shot
Query Plan / deleteProduct – ClusteredIndexScan.png Query Plan – Clustered Index Scan ( pre-creation of index )
Query Plan / deleteProduct – NonClusteredIndexSeek.png Query Plan – Non-Clustered Index ( post-creation of index )
Query Plan / deleteStore.png Query Plan – Delete Store
Script / DDL / DDLCreate.sql Create all database objects
Script / DDL / DDLDrop.sql Drops all database objects
Script / DDL / commerce.orderDetail.idx_ProductID.sql Not included in batch of objects initially created; created once we realized we need an index
Script / DML / deleteRecordsFromTableProduct.sql Delete records from Products table;  this is not part of initial objects created; used when evaluating model
 Script / DML / deleteRecordsFromTableStore.sql Delete records from Store table;  this is not part of initial objects created; used when evaluating model
 Script/ DML / 1.PopulateShipmentMethod.sql  Scripts 1 through 8 are the sets of scripts that inserts data into our tables
 Script/ DML / 2.PopulateStore.sql
 Script/ DML / 3.PopulateCustomer.sql
 Script/ DML / 4.PopulateCustomerAddress.sql
 Script/ DML / 5.PopulateProduct.sql
 Script/ DML / 6.PopulateOrder.sql
 Script/ DML / 7.PopulateOrderDetail.sql
 Script/ DML / 8.PopulateOrderShipment.sql
Script / metadata / ForeignKeysMissingIndexes.sql  List Foreign Keys missing indexes on the Referencing Objects
 Script / metadata / listForeignKeys.sql  List foreign keys
 Script / metadata / listPrimaryKeys.sql List primary keys

 

 

 

Data Model

Data Model – Logical

Here is a logical view of our Data Model.

 

CommerceLogicalModel

 

Metadata

 

Constraints – Primary Keys

To list the primary keys we will run a script similar to what we have below.

 

select 
	  SCHEMA_NAME(tblCP.schema_id) 
		as SchemaName

	, OBJECT_NAME(tblCP.parent_object_id)
		as ObjectName
			
	, tblCP.name as [Constraint]
		
	, tblInd.name as [Index]
		
	, tblInd.is_disabled as isDisabled

from    sys.key_constraints tblCP

	   inner join sys.indexes tblInd
			
	      on tblCP.parent_object_id = tblInd.object_id
	      and tblInd.is_primary_key = 1

order by				
	   SCHEMA_NAME(tblCP.schema_id) 

	 , OBJECT_NAME(tblCP.parent_object_id)
				

 

Output:

listPrimaryKey

Constraints – Foreign Keys

List foreign keys and there corresponding indexes.

select 
		  SCHEMA_NAME(tblFK.schema_id) 
			as SchemaName

		, OBJECT_NAME(tblFK.parent_object_id)
			as ObjectName
			
		, tblFK.name as [Constraint]

		, OBJECT_SCHEMA_NAME(tblFK.referenced_object_id)
			as ReferencedSchema

		, OBJECT_NAME(tblFK.referenced_object_id)
			as ReferencedObject

		, tblInd.name as indexName
				
		, tblFK.key_index_id

		, tblFK.is_disabled as isDisabled		

		, tblFK.is_not_trusted as isNotTrusted		
		
		
from    sys.foreign_keys tblFK

		inner join sys.indexes tblInd
			
			on tblFK.parent_object_id = tblInd.object_id
			and tblFK.key_index_id = tblInd.index_id


order by				
	  SCHEMA_NAME(tblFK.schema_id) 
	, OBJECT_NAME(tblFK.parent_object_id)

 

Output:

listForeignKey

Review Referencing Tables Delete

 

commerce.store

We will issue a delete statement against [commerce].[store].

 

SQL Statement:

 

use [DBLabCommerce]
go

begin tran tranSave

	delete  tblStore
	from    [commerce].store tblStore
	where   tblStore.storeID = 10

rollback tran tranSave

 

 Query Plan – Diagram:

 

deleteStore

 

 Query Plan – Tabulated

 

Operation Target Percentile
Clustered Index Delete store.PK_Store.tblStore 80%
Clustered Index Seek  [order].[PK_Order]  20%
 Nested Loops
 Assert

 

 

Explanation:

When we issue a delete again the Store table, it checks the referencing tables, in our case the Order table.  As the check against the referenced table is performed using a Clustered Index Seek, we are good.

 

 

commerce.store

We will issue a delete statement against [commerce].[product].

 

Clustered Index Scan

SQL Statement:

 

begin tran tranDeleteProduct

	delete  tblProduct
	from    [commerce].[product] tblProduct
	where   tblProduct.PRODUCT_ID = 40

rollback tran tranDeleteProduct

 

Query Plan:

deleteProduct

 

 Query Plan – Tabulated

 

Operation Target Percentile
Clustered Index Delete product.PK_Product.tblProduct 80%
Clustered Index Scan  [orderDetail].[PK_OrderDetail]  20%
 Nested Loops
 Assert

 

Explanation:

When we issue a delete again the Product table, it again checks the referencing tables, in our case the OrderDetail table.  It checks the referenced table using a Clustered Index Scan.

Clustered Index Scan” is a catch-all index; let us see if we can create a more specific index.

Non-clustered Index Seek

Let us go create an index:

 

use [DBLabCommerce]
go

if (
	OBJECT_ID('commerce.orderDetail') is not null
		    
	and not exists
		(
		  select 1
		  from   sys.indexes tblIdx
		  where  tblIdx.object_id = OBJECT_ID('commerce.orderDetail')
		  and    tblIdx.name = 'idx_ProductID'  
		)
	)
					
begin

	print 'Creating index commerce.orderDetail.idx_ProductID ....'
	
		create index idx_ProductID
		on [commerce].[orderDetail]
		(
			[Product_ID]
		)

	print 'Created index commerce.orderDetail.idx_ProductID'	

end			
	

 

SQL Statement:

Let us re-run our query.

 

begin tran tranDeleteProduct

	delete  tblProduct
	from    [commerce].[product] tblProduct
	where   tblProduct.PRODUCT_ID = 40

rollback tran tranDeleteProduct

 

 

Output:

deleteProduct - NonClusteredIndexSeek

 

 

 Query Plan – Tabulated

Operation Target Percentile
Clustered Index Delete product.PK_Product.tblProduct 80 %
Index Seek  [orderDetail].[idx_ProductID] 20 %
 Nested Loops
 Assert

 

We now have a nice index seek.  Interestingly enough the percentile is still same at 20%.

In follow-up posts we will dig a bit further into how to better compare the before and after scenarios.

 

 

Identify missing Foreign Key Indexes

In this section we will quickly introduce a script that can be used to identify Foreign Key Constraints that are are missing indexes on the referencing tables.

 

declare @filterOnlyIncludeEntriesMissingIndexes bit
declare @orderBy sysname


set @filterOnlyIncludeEntriesMissingIndexes = 1
set @orderBy = 'SourceTable'
--set @orderBy = 'SourceNumberofRecords'


;with NumberofRecords AS 
    (  
          
        SELECT 
            t.object_id as Object_ID,
            s.NAME as SchemaName,
            t.NAME AS TableName,
            i.name as indexName,
            p.[Rows],
            sum(a.total_pages) as TotalPages, 
            sum(a.used_pages) as UsedPages, 
            sum(a.data_pages) as DataPages,
            (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
            (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
            (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
        FROM sys.tables t
			INNER JOIN sys.indexes i 
				ON t.OBJECT_ID = i.object_id
			INNER JOIN sys.schemas s 
				ON t.SCHEMA_ID = s.schema_id
			INNER JOIN sys.partitions p 
				ON  i.object_id = p.OBJECT_ID 
				AND i.index_id = p.index_id
			INNER JOIN sys.allocation_units a 
				ON p.partition_id = a.container_id
        WHERE t.NAME NOT LIKE 'dt%' 
        AND   i.OBJECT_ID > 255 
        AND   i.index_id <= 1
        GROUP BY 
		  t.object_id
		, s.NAME
		, t.NAME
		, i.object_id
		, i.index_id
		, i.name
		, p.[Rows]


    )

select 
		  tblFK.name
		
		, object_schema_name(tblFKC.parent_object_id)		
			referencingSchema
			
		, object_name(tblFKC.parent_object_id) 
			as referencingObject
		
		, col_name(tblFKC.parent_object_id
					, tblFKC.parent_column_id) 
			as referencingColumn

		, object_schema_name(tblFKC.referenced_object_id)		
			as referencedSchema

		, object_name(tblFKC.referenced_object_id) 
			as referencedObject

		, col_name(tblFKC.referenced_object_id
				, tblFKC.referenced_column_id) 
			as referencedColumn

		, tblNR.[rows] as NumberofRecords		
		
		, tblIC.indexName as indexName
		
		, tblIC.isDisabled as indexIsDisabled
		
		, tblIC.columnName as columnName						
		
		, tblIC.index_column_id as indexColumnID

		
from  sys.foreign_keys tblFK

		INNER JOIN sys.foreign_key_columns tblFKC
		
			ON tblFK.object_id = tblFKC.constraint_object_id

		LEFT OUTER JOIN
				
			(
			
				select 
					  tblI.object_id as object_ID
					, tblI.name as indexName
					, tblI.is_disabled as isDisabled
					, tblC.name as columnName						
					, tblIC.index_column_id

				
				from   sys.indexes tblI

					inner join sys.index_columns tblIC				
						 
						on tblI.object_id = tblIC.object_id
							
					inner join sys.columns tblC
							
						on tblIC.object_id = tblC.object_id
						and tblIC.column_id = tblC.column_id
			) tblIC														
					
			on tblFKC.parent_object_id = tblIC.object_id
			and tblFKC.parent_column_id = tblIC.index_column_id

		inner join NumberofRecords tblNR						
		
			on tblFKC.parent_object_id = tblNR.Object_ID

where
        (
            
                (
                        (@filterOnlyIncludeEntriesMissingIndexes =0) 
                     or (
			      (@filterOnlyIncludeEntriesMissingIndexes =1) 
			   and ( tblIC.indexName is null)
			)							
                )
        )			
        
order by 

     case

	 when (@orderBy = 'SourceNumberofRecords') then
                 replicate('0', 20 - len(cast(tblNR.[rows] as varchar(30))))
                    + cast(1E10 - tblNR.rows  as varchar(30))

         else object_schema_name(tblFKC.parent_object_id)		
				+ object_name(tblFKC.parent_object_id) 
				+ col_name(tblFKC.parent_object_id, tblFKC.parent_column_id) 


      end 

go

 

Dedicated

This topic has been covered by so many good and in-depth SQL Developers.  I started off using Kirsten K. Benzel’s public gift @ http://www.ksql.co/missingix_supportingfk.html.

Have a lot of respect for her upon meeting her at the last two “San Francisco Microsoft SQL Server User Group” meetings.

I will as well be remiss if I do not include a couple of Oracle bloggers and writers who did an handsome job writing on same subject. As everyone knows there are very sturdy workmen in the Oracle & DB/2 camp, as well.

I enjoyed Tom Kytes’ Q/A and Franck Pachot’s presentation. Both are noted in the Reference section.

In summary, that is why we write, to quickly give credit and not pretend it came on their own ( nor affirmed by others).

Listening to ..

Listening to :

Javier Colon – How Many People Can Say That
https://www.youtube.com/watch?v=yFtg6Buv-Zw

Summary

There is a lot to be said about blind spots in Database design.

In this case, we touched on the fact that referenced tables need to have primary or candidate keys.

On the other-hand, the referencing tables might need indexes, as well; especially when the referenced key is being deleted or changed and the referencing objects needs to be checked for records that might end up being orphaned.

 

References

 

Constraints – Default

 

Adding data

 

Foreign Key Indexes

 

Scripts

Scripts – Random

 

Data Models

 

Oracle

 

Database – Relational Model – Candidate Keys

 

Prelude

This is the first of hopefully a few postings on database modeling concepts.  In this post we will discuss Candidate Keys.

 

Background

Let us create a couple of tables.  A customer table and an email subscription table.

 

set noexec off
go

use master
go

if db_id('DBLab') is null
begin

    print 'create database DBLAB ...'

        exec ('create database [DBLAB]')
    
    print 'created database DBLAB'
end
go


use [DBLab]
go

if schema_id('commerce') is null
begin

    print 'create Schema commerce ...'

        exec ('create schema [commerce]')
    
    print 'created schema commerce'
end
go




if object_id('[commerce].[customer]') is null
begin

    create table [commerce].[customer]
    (

          [id]              bigint not null 
                              identity(1,1)
        , [customerName]    nvarchar(100) not null

        , [emailAddress]    nvarchar(200) not null

        , constraint PK_Commerce_Customer
            primary key
            (
              [id]
            )
    )


end
go


if object_id('[commerce].[emailSubscription]') is null
begin

    create table [commerce].[emailSubscription]
    (

        [emailAddress]    nvarchar(200) not null
                
      , [promotionEmail]  bit not null
          constraint constraintDefaultPromotionEmail 
            default (0)

      , [3rdPartyEmail]   bit not null
          constraint constraintDefault3rdPartyEmail 
            default (0)

      , constraint PK_Commerce_EmailSubscription
          primary key
             (
                [emailAddress]
             )

    )


end
go

 

Diagram

 

DataModel - Initial

 

 

Problem

We can not see any relationship between the two entities.

 

Address

 

Create Foreign Keys

Let us address by creating foreign keys ..

 

alter table [commerce].[emailSubscription]
    add constraint FK_EmailSubscription_Customer
        foreign key
            (
                [emailAddress]
            )
        references [commerce].[customer]
            (
                [emailAddress]
            )
go

 

We quick get a very helpful error from SQL Server…


Msg 1776, Level 16, State 0, Line 90
There are no primary or candidate keys in the referenced table 'commerce.customer' that match the referencing column list in the foreign key 'FK_EmailSubscription_Customer'.
Msg 1750, Level 16, State 0, Line 90
Could not create constraint or index. See previous errors.


 

Create Candidate Keys on Referenced Table

Let us go create a candidate key on our Reference Table (commerce.customer)

/*
    Create Constraint CONSTRAINT_COMMERCE_CUSTOMER_UNIQUE_EmailAddress
*/
alter table [commerce].[customer]
    add constraint CONSTRAINT_COMMERCE_CUSTOMER_UNIQUE_EmailAddress
        unique
            (
                [emailAddress]
            )
go

 

 

Retry Foreign Key Creation Step

 

/*
    Create Foreign Keys
*/
alter table [commerce].[emailSubscription]
    add constraint FK_EmailSubscription_Customer
        foreign key
            (
                [emailAddress]
            )
        references [commerce].[customer]
            (
                [emailAddress]
            )
go

 

No more errors…

 

 

Diagram

Let us recreate our Database Diagram

 

DataModel - ForeignKey

 

Candidate Keys – What good?

  • Better understanding of the entities and their relationship
  • SQL Queries are easier to write as one is better able to know which columns to include when joining tables
  • As artificial keys proliferate, it blindsides database records uniqueness understanding

Conclusion

Candidate keys are themselves fairly straight forward concepts.

But, we do get bogged down sometimes within our tools.

  • For example, one is unable to visually see Foreign Key Relationships when we target SQL Server Express Engine

 

References

 

How to

 

Q/A

 

Francis Collins – “Language of God” Speech

Background

A couple of weeks ago, I listened on as Francis Collins spoke a bit about his book “Language of God“.  The discussion was at the Veritas Forum at California Institute of Technology ( CalTech ) in Pasadena, CA.

The presentation is available at :

Francis Collins – The Language of God: A Scientist Presents Evidence of Belief
https://www.youtube.com/watch?v=EGu_VtbpWhE

Quote

He touched on the importance of using Augustine’s writing as a backdrop when attempting to resolve seemingly incompatible thoughts; i.e space things out a bit.

 

Augustine’s Quote

The Literal meaning of Genesis – 400 AD

http://college.holycross.edu/faculty/alaffey/other_files/Augustine-Genesis1.pdf

In matters that are obscure and far beyond our vision, even in such as we may find treated in Holy Scripture, different Interpretations are sometimes possible without prejudice to the faith we have received. In such a case, we should not rush in headlong and so firmly take our stand on one side that, if further progress in the search of truth justly undermines this position, we too fall with it. That would be to battle not for the teaching of Holy Scripture but for our own, wishing its teaching to conform to ours, whereas we ought to wish ours to conform to that of Sacred Scripture.

 

Introduction

His introduction was brief and informative.  Areas he touched on includes:

  • Let us reason together
  • Started out as a reductionist and “material”
  • DNA Sequencing problem causing cancer
  • Saw a lady dying and she was at peace with it

 

Question

Faith’s journey invigorated by seeing a dying lady who had come to peace with it.  The lady shared her life and faith with him.  And, thereafter said to him “I told you about my faith, now Doctor what do you believe?“.

 

Francis Collins – “Nature Pointers to God”

Finding pointers in Nature:

  • There is something instead of nothing
  • The unreasonable effectiveness of Mathematics
  • The Big Bang
  • The precise tuning of physical constants in the Universe

 

Francis Collins – Possibilities

He iterated possible explanations for the preciseness of Science.

  • Constant – Aporia reason due to relationship – There might be relationships between them
  • Multiverse – There are other universes just like this one
  • Intentional

And, how when one is faced with myriad choices one should apply “Occam Razor“; that is choose the simplest reason.  Which leads us to choosing the the intentional option.  This appears to be where Einstein’s ended up.

Since Einstein’s day, there are more fine tune arguments.

In summary we now have found a Deist God, but not necessary Theist God.

 

Francis Collins – Moral Law

  • We humans are under a moral law
  • It is a Universal Law
  • Is it completely explained by altruism?
  • Reason for Altruism
    • Family
    • Reciprocal – Reproductive reason
    • Martin Nowak – Prisoner Dilemma – Harvard Professor – Entire Group
  • But, all the reasons means that one still has to be hostile to people outside of the group
  • How does one explain extension to other groups
    • Mother Theresa
    • Wesley Autrey, Construction worker – New York Subway
  • Reason to Look further
    • If one is looking for a God that is good and holy

In summary, Mr. Collins arrived at a Theist God via trying to grasp the fullness of a Universal Moral Law.

 

Francis Collins – “Loving God with all your mind

  • No longer have reasons to resist
  • Reasons behind the decision
  • Love God with all your mind
  • Talks about Mark Noll’s book – “Scandal of the evangelical mind

Francis uses  Luke 10:27 “And he answering said, Thou shalt love the Lord thy God with all thy heart, and with all thy soul, and with all thy strength, and with all thy mind; and thy neighbour as thyself“, to affirm that there is no conflict between Mind/Science and worshiping God.

 

Life Sciences

  • DNA – Digital Code
  • DNA Theory is fundamentally correct
  • Evolution – Shared Ancestry of humans and other organisms*
    • Fossil record
    • DNA
      • Compared DNA Sequencing
      • pseudogene ( RIP in humans i.e. GULO – Humans unable to make Vitamin C on their own, while other animals might not have problem )

 

Richard Dickens

Richard Dickens – argument

  • Has a book known as “God’s Delusion”
  • Arguments against God

Richard Dickens – Rebuttal

  • Universal Negative ( Atheism is the most daring of all dogma, for it is the assertion of a universal negative – G.K. Chesterton)
  • Category Error ( God is outside of Nature and thus shall not be placed exclusively in nature)
  • Science is only able to comments on things that are in nature and not on things outside of nature.  Silence has to remain silence in Nature )
  • Richard Dickens “If earth was created by God. it must be a God more complicated than the God we are talking about
  • One cannot be an Atheist and argue that Science totally supports that conviction
  • 40% of Science believe in God
  • God’s role in Evolution
    • God’s planned for evolution
      • Neurological house (brain)
      • Free will
      • Soul
      • Made in God’s image – Mind VS Body
      • Our free will lead to disobedience to God
      • Humans realization of violation of Moral code
    • No violence to faith nor science
    • Theistic Evolution ( BioLogos )
      • Bios = Life
      • Logos = The Word

 

Objections to BioLogos

  • Bacterial Flagellum

 

 

Books

  • Darrel R. Falk – Coming to Peace with Science
  • Karl W. Giberson – Saving Darwin
  • N. T. Wright – Resurrection of Son of God

 

 

Q/A

Here is the Q/A Transcript. Where available I have placed the time-slot where the Question began and Answer ended in parenthesis.

 

Importance of Prayer

  • Mode of listening
  • In prayer, one might get an insight into one self ( Mystical )

 

Evolution Maturity

  • Age of Universe – 13.7 billion years
  • Age of the Earth – 4.55 billion years
  • The God of Deception
  • If one does not read from Scripture a requirement to postulate a young earth, then one might have to go with what Science is telling us about earth’s age

 

Post Humanist

  • Stephen Hawkins
  • Messing with Human’s germ life safety?
  • What is improvement
  • Who gets access
  • God’s Image thought

 

Sloppiness of DNA ( 1:11:57 )

  • Mitochondria DNA
  • DNA Transition to Nuclear from Genome?
  • Ancient Event / Variability of the event / Recent event
  • He is outside of the data

 

Physical vs Spiritual Death

  • Augustine’s discussion of Genesis
  • Christianity has not been forced to revise its understanding even after aggressive growth in Science
  • Evolution vs Pauline Canon
  • Where does Death come from?
    • Scripture – Death is result of human science
    • Evolution – Death is integer to creation
  • Death integral to creation
  • Scripture
    • Romans 5
    • 1st Corinthians 15
  • Death ? Does it exclude animal death
  • Not a theologian
  • Physical vs Spiritual death

 

Movie – Expelled

  • Prosecution in Academia
  • Whip up resentment against Academic
  • Speak bluntly about those things

 

Miracle

  • Q/A
    • Nature Miracle (Manna from Miracle, Jesus feeding hundreds with bread)
    • Is God contradictory or disorder
    • God is almighty
    • Why did not God not intervene during Holocaust
  • Answer
    • Whether one believes in God and whether one believes that God is the author of nature
    • Moments of great significance
      • Jesus Resurrection

 

 

Book – Born to be good – The Science of a meaning Life

  • Q/A
    • Is there a gene for altruism
  • Answer
    • Neurological Pathway
    • Free will decisions
    • Neurological Pathway
    • Divinely inspired action that needs a path to be carried out
    • Emotions such as Deep prayer life bring about emotion

Pre-commitments Role ( 1:24:30 – 1:27:15 )

  • Q/A
    • The evidence seems to support both a Theistic and Naturalistic viewpoint
    • Pre-Commitment to a world view, conceptual scheme
    • Evidence gains its meaning
    • Internal consistency within a conceptual view
    • Preconceived conclusion – Is one not just as good as the other?
    • If one is better, how does one move from being a Naturalist to been a Theist
  • Answer
    • One has to choose one or the other ?
      • Being other or the other improvises you
      • Placing a High firewall between them
      • Having to choose one is a recent idea and a bit flawed
      • One can find God in the Laboratory or the Cathedral

 

Agnostic Atheism

  • Q/A
    • Mental Crutch of having to believe in a God
    • Physics
    • Free will
  • Answer
    • Physics
      • Predetermination of Classical mechanics
      • Quantum brings more fluidity
    • Agnostics
      • Principled Agnostic
      • Others it might suggest I do want to think about it

Summary

Once again, there is a lot to learn out there.  Veritas Forums, Ted Talks, and many others facilitate important and meaningful discussions.

And, thanks to Google and YouTube unusually smart and “gifting” people are a lot closer than they would otherwise be.  They are well read and do not hide it; in actuality they share their sources.

They are also pen to meaningful reproach.

As Ravi Zachariah likes to say, the Q/A part is where the fireworks happen.

 

Reference

Occam Razor

 

Deism

 

Theism

 

Martin Nowak

 

Wesley Autrey

 

Mark Noll

 

Stephen L. Carter

 

Richard Dawkins

 

Bacteria Flagellum

 

Pau(line) Doctrine

 

God vs Science

Microsoft – Windows – Start Up Warning – There was a problem starting “c:\Users\AppData\Local\Conduit\BackgroundContainer\BackgroundContainer.dll The specified module could not be found”

 

Prelude

My parent’s called last night and I knew I had to call them back.  They don’t call much as I am usually the one who needs them and initiate calls.

The only time they ever need help me is when they have computer problems.

 

Error Message

 

So yes they do have a problem.  Upon logging on they get an error stating:

There was a problem starting c:\Users\<user>\AppData\Local\Conduit\BackgroundContainer\BackgroundContainer.dll
The specified module could not be found.


Configuration

System Configuration – Autoruns

 

Background

Autoruns is published by Microsoft’s SysInternals “subsidiary”.   To fetch it, please access http://technet.microsoft.com/en-us/sysinternals/bb963902.aspx and click on the “Download Autoruns and Autorunsc” link.

 

Scheduled Tasks

Let us review and possibly modify the Scheduled Tasks; to do so, please:

  • Run Autoruns
  • Access the “Scheduled Tasks” Tab
    • Look closely at any items that have “File not found” in their “Image Path” column
    • If you find any of them, please un-check them by clicking on the checkbox checkbox

 

ScheduledTasks

 

  • Access the “Logon” Tab
    • Look closely at any items that have “File not found” in their “Image Path” column
    • If you find any of them, please un-check them by clicking on the checkbox checkbox

 

Logon

 

 

  • Find everywhere
    • Access the Application Menu
    • Click on the menu item File \ Find…
    • In the Find dialog, enter container and make sure that “Matchwhole word only” and “Match case” are unchecked
    • Initiate the search by clicking on FindNext
    • Unchecked any found items

MenuItem--File-Find

 

 

Microsoft – Windows – Task Scheduler

Access Task Scheduler by do doing the following

  • From the Start menu, access “Control Panel”
  • In Control Panel, launch Administrative Tools and “Task Scheduler
  • In Task Scheduler, transverse to “Task Scheduler Library
  • On the top menu bar, click on the Triggers menu bar to order by the Triggering event
  • Ordering by the Triggering event will allow us to narrow down on the more likely “source” events, which are
    • At log on any user
    • At log on of <user>
    • At system startup
  • Our culprit probably has empty for “Last Run Result”
  • Jot down each likely item, right click on it, and from the drop-down menu select disable

 

 

Initial

TaskSchedulerLibrray

 

Disabled

Once disabled you will notice that have Disabled in their status column.

 

TaskSchedulerLibrray-Disabled

 

 

Summary

Please try to restart your system and see if the problems goes away.

 

 

References

Question & Answer (Q/A)

 

Microsoft – Windows – Startup Apps

 

 

 

 

Microsoft – SQL Server – Query Comparison Using SQL Profiler

Prelude

In a previous post we touched on the fact that we can use Quest Software’s Benchmark Factory and SQL Sentry’s Plan Explorer (  http://danieladeniji.wordpress.com/2014/09/01/microsoft-sql-server-query-comparison-using-sql-profiler/ ) to compare competing queries and query plans.

 

Background

Once again to get a generalized database, we downloaded SQL Server 2014 AdventureWorks from http://msftdbprodsamples.codeplex.com/releases/view/125550 and restored it.

 

SQL Objects

 

Once our AdventureWorks database is available, we created a  couple of Stored Procedures that we can use to retrieve data from it.

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_1

 

Here is the first SP – dbo.usp_PersonFetchFilteredOnName_1

 

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]


    from   [Person].[Person] tblPerson

    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

Here is the second Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]
    where
            (

                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_3

 

Here is the third Stored Procedure ( dbo.usp_PersonFetchFilteredOnName_3 )

 

if object_id('dbo.usp_PersonFetchFilteredOnName_3') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_3 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_3
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]

    where
            (

                    (
                        Firstname = 
                                    case @Firstname
                                        when null then Firstname
                                        when '' then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case @LastName
                                        when null then Lastname
                                        when '' then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

 

SQL Management Studio

 

Here is the Query for invoking the Stored Procedures; along with the “Statistics IO” & “Statistics Time” measured.

 

Invoke SP

 

Here is the queries for invoking the SP.

 

exec dbo.usp_PersonFetchFilteredOnName_1
        @Firstname = 'Laura'
      , @Lastname  = 'Norman'


exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

    
exec dbo.usp_PersonFetchFilteredOnName_3
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

 

 

Statistics IO

 

StatisticsIO

 

 

Statistics Time

 

SQLServerTime

 

 

 

 

 

 

SQL Profiler

 

Here are the events we focused on using SQL Profiler.

 

Events Selection

 

Events Selection: Image

EventsSelection

 

Events Selection: Tabulate

 

Item Events & Columns  Item
Events & Columns
Stored Procedures
RPC:Completed
RPC:Starting
SP:Recompile
SP:Starting
 SP:StmtCompleted
Columns
TextData
CPU
Reads
Writes
SPID

 

 

 

 

 

Output

 

Output: Image

 

Here is the SQL Profiler Image.

SQLProfiler

 

Output:Tabulate

 

Metric SP-1 SP-2 SP-3
CPU 0 16  15
Reads 124 136  136
Writes 0 0  0
Duration 217 255  288

 

 

From the Screen-shot above, we can see that the Stored Procedures that use the “Case Clause” are a weeny bit more expensive.

 

Summary

 

Found that SQL Server Profiler when properly configured offers a slightly more concise view of a query’s performance data when compared to SQL Server “Set statistics time on”.

 

 

Quest Software – Benchmark Factory – v7

Prelude

Back in May 2014, Kevin Stern of Quest reached out to me and let me in on the fact that they have released a new version of Quest Benchmark Factory.

This was through a comment he posted @ http://danieladeniji.wordpress.com/2014/05/10/technical-quest-software-benchmark-factory-for-databases/.

Since then I have wanted to download the new version and kick it around a bit.

In this post, I will talk a bit about my experience.

 

Database – Preparation

 

Database – Sample

We will use the AdventureWorks Database.  Unfortunately, our target database is SQL Server Express and that product version does not ship with sample databases.  And, so we downloaded a version from Microsoft; specifically https://msftdbprodsamples.codeplex.com/releases/view/125550.

 

Restore Database

USE [master]
GO
RESTORE DATABASE [AdventureWorks2014] 
FROM DISK = N'C:\Downloads\AdventureWorks2014.bak' WITH FILE = 1
  , MOVE N'AdventureWorks2014_Data' TO N'E:\DATA\AdventureWorks2014_Data.mdf'
  , MOVE N'AdventureWorks2014_Log' TO N'F:\LOG\AdventureWorks2014_Log.ldf'
  , NOUNLOAD
  , STATS = 5


 

Database – Objects

As we have an entire pre-written database for our use, we will need very minimal modifications.
Actually, we will simply add two stored procedure that serve same purpose; which is to query the person table.

The Stored Procedures will accept two arguments firstname and lastname and will filter against the person.person table based on the argument contents.

 

 

dbo,usp_PersonFetchFilteredOnName_1

 

Here is SP 1, it exclusively uses the or clause.

 

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin
    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    select *
    from   [Person].[Person]
    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

 

 

dbo.usp_PersonFetchFilteredOnName_2

 

Here is SP 2, it uses the case clause.

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    select *
    from   [Person].[Person]
    where
            (

                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

Database – Sample Data

To get representative test data, we will use bcp to get data out of our database.

 

bcp "select '\"' + Firstname + '\"' as Firstname, '\"' + Lastname + '\"' as Lastname, Count(*) as Cnt from [AdventureWorks2014].[Person].[Person] group by Firstname, Lastname order by count(*) desc " queryout person.txt -S.\SQLEXPRESS_V2014  -E -T -c -t","

if not exist c:\sqlserver\data mkdir c:\sqlserver\data
xcopy C:\Personal\DanielAdeniji\Blog\QuestSoftware\BenchmarkFactory\v7\DatabaseScripts\GenerateTestData\person.txt  c:\sqlserver\data /Q /y /D

 

 

ODBC Data Source

System Data Source

We created a System ODBC Data Source.

 

SelectADriver

 

Data Source Name and SQL Server

 

DatasourceNameAndSQLInstance

Database Connection Detail

 

DatabaseConnectionSettings

Quest Benchmark Factory

 

Script

 

Introduction

Here is a table that shows our Benchmark Factory (BF) Script:

 

Scenario Parameter  Value
Transaction 1
 SQL exec AdventureWorks2014.dbo.usp_PersonFetchFilteredOnName_1  ?,?
 Param1=@Firstname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,1)
 Param2=@lastname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,2)
Transaction 2
 SQL exec AdventureWorks2014.dbo.usp_PersonFetchFilteredOnName_2  ?,?
 Param1=@Firstname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,1)
 Param2=@lastname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,2)

 

 

 

Transaction Mix

Here is our Transaction Mix

 

EditJob

 

Transaction SQL

StoredProcedure-SQL

 

Transaction Bind Parameters

 

StoredProcedure-BindParameters

 

 

 

 

User-load

 

Here is what our User-load looks like.

We will start with a single user, and move on to 5, 10, 15, and 20 users.

 

userload

 

 

Agent

 

Before

Here is what the Agent Screen looks like initially.

 

Agent-Before

 

 

After

 

Agent-After

 

 

The change between the Before and After is that we checked our computer name to indicate it is part of our test.

 

Warning

If you do not make that test, you will see a warning that reads

 

Missing Agents To Use For Testing

 

MissingAgentsToUseForTesting

 

 

 

Results

 

Picture

Our test result is pasted below:

 

Userload 20 Results

 

Tabulated

 

Metric SP-1 SP-2
 Avg Response Time  0.014  0.018
 Avg. Transaction Time  0.014  0.018
 Executions  1771  1766
 Rows  8712  8707
 Errors  0  0

 

 

Quick Explanation

 

  • The second stored procedure is a bit slower
  • SP 1 executed 1771 times, while SP2 executed a slightly smaller 1766; so when allocated identical time scope we were able to accomplish slightly more with SP1
  • As we iterated a bit more with SP1, we returned a bit more records

 

 

SQL Sentry Plan Explorer

In all truth, though second Stored Procedure is a tad bit slower, I still could not tell why.

And, so I installed and launched Huntersville’s own SQL Sentry Plan Explorer.  See how I little know it took a young lady on Bart to tell me that Huntersville is a suburb of Charlotte, North Carolina.

 

Estimated Plan

 

EstimatedPlan

 

 

Actual Plan

 

To get the Actual Plan I entered the query.

 

 Test Query:

declare
      @Firstname nvarchar(50) 
    , @Lastname  nvarchar(50) 

select 
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

exec dbo.usp_PersonFetchFilteredOnName_1
      @Firstname = @Firstname
    , @Lastname  = @Lastname

exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = @Firstname
    , @Lastname  = @Lastname

 

Actual Plan

Actual Plan

ActualPlan

 

 

 

Tabulated

 

Metric SP-1 SP-2
Estimated Cost 6.2% 93.8%
Duration 14 20
CPU 15 32
Reads 124 128
Writes
Estimated I/O  5.5%  94.5%
Est Rows  1  632
Actual 124 128
Writes
Estimated I/O  5.5%  94.5%
Est Rows  1  632
Actual Rows 5 5
Key Look up 1 1
Index Scan  1 1

 

 

In summary, SQL Server thinks the second query is much worse than it ended up been.

 

 

Conclusion

There are so many tools one can use to dig a bit deeper into queries.  Quest Software certainly earns its honorable role and belong in the tool-bag of SQL aficionados.

 

Microsoft – SQL Server – Profiler – Database ” cannot be opened because it is offline

Background

For some odd reason, I ended up in Microsoft SQL Server Profiler land looking at session traces.

 

Database cannot be opened

As I dug deeper, I noticed quite a few repeating entries bearing:


Database 'DBLabInMemory' cannot be opened because it is marked offline.

 

DatabaseCannotBeOpened

 

 

Diagnostic

Got me thinking where is the error coming from?

  • Another Application on my machine
  • SQL Server Agent
  • Internet Parlance

 

I tried everything:

  • Using “SQL Server Configured Manager”, disabled TCP/IP Network Protocol for the SQL Instance
  • Using Services Management Console, determined that SQL Sever Agent is not enabled to run
  • Using MS Network Monitor, captured network traffic – No 1433
  • Using Netstat.exe, captured and analyzed network traffic
  • Reviewed SQL Server Error Log – Knowing that Microsoft has done a very good job writing much more detailed connectivity errors into its errorlog

 

Nada.  Nothing lined up.

 

Hunch

 

Create a new database

Created a new database unknown to the world and no other application

create database [DBOffline];

alter database [DBOffline] set offline;

 

Monitor SQL Server Profiler

Monitor SQL Server Profiler and see if same issue occurs for this new database.

DatabaseCannotBeOpened - DB Name -  DBOffline

And, yes it does.

 

Interpretation

 

Column Interpretation
Login Name = sa sa
spid < 50 System process has spid less than 50
ClientProcessID is blank Internal Session

 

It is an internal SQL Server Session and not anything we are doing.  One of the Internal processes is trying to perform ‘housekeeping’ and it is not checking the status of the DB, before trying to access\open it.

 

Summary

It is my humble opinion that SQL Server Internal processes should not surface errors caused by an Administrators’s intentional act of having a database offline.