0

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.

Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
  • Could you please tell more about your data which force performance issues - approximate record size and count, data types. Are you using manly views or SPs? – Serg Sep 19 '14 at 08:55
  • @Serg, It is not a kind of intense performance issue. It is confirmed that when ever a table is used, it will be accompanied with a WHERE Status_ID = 1 condition. In many execution plans, there are Key LookUps looking for just Status_ID. In cases , that I went though, I can see Key LookUps getting eliminsated, when StatusId is included in Associated indexes. – Jithin Shaji Sep 19 '14 at 09:04
  • Some tables have millions of data, half of the tables got less than 10k records. – Jithin Shaji Sep 19 '14 at 09:04
  • 1
    Thank you for your reply and information. Maybe this discussions will be interesting - http://stackoverflow.com/questions/231125/should-i-index-a-bit-field-in-sql-server and http://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes – Serg Sep 19 '14 at 09:12
  • 1.Alter all [Status_ID] columns to NOT NULL, and then adding them to PRIMARY KEY of that table. - only of you have 2 rows in each table - check the meaning of a primary key first 2.Adding [Status_ID] column to all the NON Clustered indexes in the INCLUDE PART for that table. - Definitely do that! If you'll run an estimated execution plan on each of these queries, SQL Server will most likely suggest you this kind of an approach. – Sergiu Sep 19 '14 at 13:24
  • May I also suggest a no. 3 Option here? Change your db design, if 90% of the cases you are interested only in the STATUS_ID=1 information, create shadow tables where you will keep the STATUS_ID=0 information. – Sergiu Sep 19 '14 at 13:28
  • @Sergiu, If in a table the primary key columns are 12, 13..The new Primary Key may be (12,1) and (13,1). I meant such an approach.. – Jithin Shaji Sep 19 '14 at 13:39
  • @Sergiu .Option 2 is what i Seriously want to hear about, and thanks about that. Option 3 is achievable at the moment. Thanks – Jithin Shaji Sep 19 '14 at 13:40
  • My bad, I misread that, I thought you wanted to make just the STATUS_ID column the PK of the table. I would still, if possible, go for option no3, but it's your call. – Sergiu Sep 19 '14 at 13:44
  • 1
    After some research, it turns out that the best thing to do in your situation is to add this column to all of your NCl indexes as well as in your clustered index. And always put it as the first column in your composite index. http://stackoverflow.com/questions/231125/should-i-index-a-bit-field-in-sql-server – Sergiu Sep 19 '14 at 13:56

1 Answers1

1

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

paparazzo
  • 44,497
  • 23
  • 105
  • 176