SQL Server Performance Tuning

 Hello Arvin,

 

Good question.

 

Firstly, for access on the system we can talk with AE if they have spare licenses for you.

 

Secondly, a good database design will always have Primary Keys on all tables to protect data integrity and they inherently function better than an index.

This makes Primary and Foreign keys perform really good when your queries have joins.

 

Thirdly, looking at HKDOAMSP you will see the query below that has the most waits.  On this instance it was 4473599451.

 

 

 

From here we now know its is caused by HKGDOAMSP.dbo.fn_GetEmployeewithNickname and the major wait is MEMORY/CPU taking 26 mins of the 1 hour span.

 

Investigate the query used on the function. Check for

1. Primary Keys
2. Foreign Keys
3. Indexes
4. Cartesian Products

 

Quick Note: Primary and Foreign keys are designed for business reasons. They should not created for performance gains.

 

 

Tuning Proper

 

Query 1:

select @result =trim(first_name) + ' ' + trim(last_name) + case when NickName is null then '' else ' ('+ NickName +')' end

from doams_SAPFeed  where SAPID = @empid

 

Upon investigation I saw that doams_SAPFeed has no PK.  So I asked Lex which should be unique in this table. (Hence the business reason part).

 

I have experienced that sometimes ETL loads from the source are wrong or it was not properly transformed prior to loading. On this case I don’t know which one.

This is where PK, FK, Constraints and Triggers comes in to play during database architecture.  The database architecture is the foundation for the business process  and then followed by the application/s

 

Moving on.

 

Take a baseline of the query on SSMS

 

Get the timing, io and the execution plan

 

(1 row affected)

Table 'doams_SAPFeed'. Scan count 1, logical reads 2683, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row affected)

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 17 ms.

 

 

 

This tells us it is reading 2683 from memory ( which is what we want, what we don’t want is to many physical reads from disk).

Also we a table scan but since the table is small it should not be a big of a deal.

 

Let’s see if we can improve it.

 

Next is to have a hypothesis on how to reduce the overhead of the query.

 

Hypothesis:  adding a PRIMARY KEY on sapid on doams_SAPFeed.

 

On the HKDOAMSP environment we have to clean it first since SAPID '33905','34006','34038'  are duplicates

 

So I created my own tables dxfeed and dxfeed2 which has a PK.

 

Test the hypothesis

 

Now run them side by side on SMS

 

select * from dxfeed where SAPID='36859';

select * from dxfeed2 where SAPID='36859' ;

 

Results

Table 'dxfeed'. Scan count 1, logical reads 2161, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row affected)

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 12 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

(1 row affected)

Table 'dxfeed2'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row affected)

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

Execution Plan

 

 

This exhibits that Query 2, which has the PK has zero cost compared to Query 1.

 

Conclusion:

 

Adding a primary key on SAPID on doams_SAPFeed is more efficient for the reasons that

1. Query 2 has 2,158 less logical reads than Query 1.
2. Query 2 used index seek as opposed to a table scan.

 

Recommendation:

 

1. Test it on a dev environment and check your application if there are unforeseen effects.
2. Validate with the business owners that SAPID should be unique in doams_SAPFeed.
3. Deploy to all applicable DOAMS environment.

 

This is a very used function it runs

 

5:00 PM-6:00 PM (7,798 execs)
6:00 PM-7:00 PM (87,515 execs)
8:00 PM-9:00 PM (92,800 execs)