Sql-server – Help to create indexes to improve query performance and reduce report generation time

database-designsql-server-2008

The database in SQL Server 2008 has these tables below – the main table being the Master:

Master

A_ID (PK)
SYSSerialNumber
Bootlog
SequenceNumber
FileName
Cfg
Unique(serialnumber,sequencenumber)

SYSTEM (Master:SYSTEM -1:1)

SYS_ID
A_ID (FK)
SYS_Product
SYS_Source
SYS_iVer

MEMORY (Master:Memory – 1 to N)

MEM_ID
A_ID (FK)
MEM_Instance
MEM_Name
MEM_Size
MEM_CE
MEM_UE
unique(ahs_id,mem_instance)

Processor (Master:Processor – 1 to N)

PROC_ID 
A_ID (FK)
PROC_Instance
PROC_FamilyID
PROC_Version
unique(a_id,proc_instance)

BC (Master:BC – Master.SYSSerialnumber = BC_SerialNumber )

BC_ID
BC_WWSiteName
BC_SerialNumber

The view was created for tables Master,System, BC and Memory: MSBC_Mem

select 
    M.a_id, M.SYSSerialNumber, M.FileName, M.Cfg,
    M.SYS_Product, M.SYS_Source, M.SYS_iVer,
    B.BC_WWSiteName, B.BC_SerialNumber,
    MEM_Instance, MEM_Name, MEM_Size, MEM_CE,MEM_UE
from 
    dbo.BC AS B 
LEFT OUTER JOIN 
    dbo.MASTER AS M ON M.SYSSerialNumber = B.BC_SerialNumber 
LEFT OUTER JOIN 
    dbo.SYSTEM AS S ON M.A_ID = S.A_ID
LEFT OUTER JOIN 
    dbo.Memory AS Mem ON M.A_ID = Mem.A_ID_
WHERE     
    (M.A_Cfg = 1)

Using the above view as a base for all queries for the report:

  1. Query:

     Select 
    count(distinct(sysserialnumber)), BC_WWSitename
 from dbo.MSBC_Mem
 where sys_product = 'xyz'

    My question: SYS_Product & SYS_Source are commonly used in the WHERE clause in almost all the queries. What kind of index do I have create on the parameters?

  2. To get a normalized values based on MEM_Name for each SYS_Product where MEM_CE > 0 :

View: NRMEM_NameCE

SELECT     
    A.SYS_Product, A.MEM_Name AS NMCEMEM_Name, A.MEM_NameCount, 
    CAST(A.MEM_NameCount AS float) / B.MEM_NameCount * 100 AS VendorPercentage, 
    CAST(A.MEM_NameCount AS varchar(8)) + ' / ' + CAST(B.MEM_NameCount AS varchar(8)) AS somestring, 
    C.MEM_NameCount AS TotalUnits
FROM         
    (SELECT     
        SYS_Product, MEM_Name, 
        SUM(CASE WHEN MEM_CE > 0 THEN 1 ELSE 0 END) AS MEM_NameCount 
     FROM         
        dbo.MSBC_Mem
     WHERE       
        (MEM_Name IS NOT NULL)
     GROUP BY
        SYS_Product, MEM_Name) AS A 
INNER JOIN
   (SELECT     
       SYS_Product, MEM_Name, COUNT(MEM_Name) AS MEM_NameCount
    FROM         
       dbo.MSBC_Mem
    WHERE      
       (MEM_Name IS NOT NULL)
    GROUP BY 
       SYS_Product, MEM_Name) AS B ON A.MEM_Name = B.MEM_Name AND A.SYS_Product = B.SYS_Product 
INNER JOIN
    (SELECT     
        SYS_Product, COUNT(MEM_Name) AS MEM_NameCount
     FROM         
        dbo.MSBC_Mem
     WHERE      
        (MEM_Name IS NOT NULL)
     GROUP BY 
        SYS_Product) AS C ON A.SYS_Product = C.SYS_Product

From the results above:

select * from NRMEM_NameCE where SYS_Product='xyz'

The above result which is :

**SYS_Product   NMCEMEM_Name    MEM_NameCount   VendorPercentage somestring TotalUnits**  
DL          DIMM             10 0      0              0 / 79    32909  
DL          DIMM             4  2      0.04419        2 / 4525  32909  
DL          DIMM             8  0      0              0 / 995   32909

This entire query takes long since there are no indexes on the Memory and System tables.

Could you please suggest as to how I can include indexes on the memory table and improve the performance?

Thanks.

Best Answer

As you are running a lot of (MEM_Name IS NOT NULL) against the memory table I would look to create a filtered index on this table to begin with.

CREATE NONCLUSTERED INDEX Memory_MEM_Name
ON MEMORY (Index Fields here)
WHERE MEM_Name IS NOT NULL

in most occations every table should have a least 1 Index.

You should capture a work load trace via Profiler and then run it through the database tuning advisor this will tell you where you should see gains with indexs (and even create them for you)

http://msdn.microsoft.com/en-us/library/ms181091.aspx

When running the DTA it might be a good idea to run it against a test system if you have one rather than an online system. Make the changes it sugests and then run it again to see if the improvements appear.

Note - I would not have called a table master. Master should be reserverd for the important master database.

Related Question