CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-designnormalization
Jithin Shaji
Jithin Shaji

Reputation: 6073

DB Design: Looking for Performance improvement when a BIT Column from every table used in every SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running stored procedures.

The database designed is highly normalized. In all the tables we have a BIT column as [Status_ID]. In every Stored procedures, For every tsql query, this column is involved in WHERE condition for all tables.

Example:

Select      A.Col1,
            C.Info
From        dbo.table1 A
Join        dbo.table2 B On A.id = B.id
Left Join   dbo.table21 C On C.map = B.Map
Where       A.[Status_ID] = 1
And         B.[Status_ID] = 1
And         C.[Status_ID] = 1
And         A.link > 50

In the above sql, 3 tables are involved, [Status_ID] column from all 3 tables are involved in the WHERE condition. This is just an example. Like this [Status_ID] is involved in almost all the queries.

When I see the execution plan of most of the SPs, there are lot of Key lookup (Clustered) task involved and most of them are looking for [Status_ID] in the respective table.

In the Application, I found that, it is not possible to avoid these column checking from queries. So

Will it be a good idea to

  1. Alter all [Status_ID] columns to NOT NULL, and then adding them to PRIMARY KEY of that table.Columns 12,13.. will be (12,1) and (13,1)
  2. Adding [Status_ID] column to all the NON Clustered indexes in the INCLUDE PART for that table.

Please share you suggestions over the above two points as well as any other.

Thanks for reading.

Upvotes: 0

Views: 108

Answers (1)

paparazzo
paparazzo

Reputation: 45096

If you add the Status_ID to the PK you change the definition of the PK
If you add Status_ID to the PK then you could have duplicate ID
And changing the Status_ID would fragment the index Don't do that
The PK should be what should make the row unique

Add a separate nonclustered index for the Status_ID
And if it is not null then change it to not null
This will only cut the workload in 1/2

Another option is to add [Status_ID] to every other non clustered. But if it is first it only cuts the workload in 1/2. And if is second it is only effective if the other component of the index is in the query

Try Status_ID as a separate index
I suspect the query optimizer will be smart enough to evaluate it last since it will be the least specific index

If you don't have an index on link then do so

And try changing the query
Some times this helps the query optimizer 

Select      A.Col1, C.Info
From        dbo.table1 A
Join        dbo.table2 B 
  On        A.id = B.id 
 AND        A.[Status_ID] = 1
 And        A.link > 50
 And        B.[Status_ID] = 1
Left Join   dbo.table21 C 
  On        C.map = B.Map      
 And        C.[Status_ID] = 1

Check the fragmentation of the indexes

Check the type of join If it is using a loop join then try join hints

This query should not be performing poorly

If might be lock contention
Try with (nolock)
That might not be an acceptable long term solution but it would tell you is locks are the problem

Upvotes: 1

Related Questions