I am given a task to troubleshoot performance issue

Question

I am given a task to troubleshoot performance issue

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
March 5, 2018 at 2:52 pm

#410290

I am given my first task to troubleshoot a performance related issue. I have a very small DB with some tables and there is a query which takes 7+ seconds to run. I am not even sure if it is normal. When I ran it, I had statistics time, io + the execution plan so I understood the statistics details with scan and reads but I just don't know where to even start from? What exactly I can provide here in this forum to get some help on where to start?
Does this help?
SELECT Company , SUM(mastertable.jan1) as January, SUM(mastertable.Feb1) as February, SUM(mastertable.Mar1) as March , SUM(mastertable.Apr1) as April, SUM(mastertable.May1) as May, SUM(mastertable.June1) as June , SUM(mastertable.July1) as July, SUM(mastertable.Aug1) as August, SUM(mastertable.Sept1) as September , SUM(mastertable.Oct1) as October, SUM(mastertable.Nov1) as November, SUM(mastertable.Dec1) as December ,IssYr,MediaGroup, SplitEmpID, AccountID from ( select B.company_name as Company ,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1 ,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1 ,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1 ,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1 ,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1 ,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1 ,issyr,MediaGroup, splitempid, accountid from [dbo].[FiscalBilling_SalesAdj] A join tbl_master B on A.AccountID = B.master_id where A.RevenueType <> 'Sales Adj' group by B.company_name, IssMth,issyr,MediaGroup, splitempid, accountid
UNION ALL
select B.company_name as Company ,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1 ,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1 ,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1 ,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1 ,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1 ,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1 ,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1 ,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1 ,issyr,MediaGroup, splitempid, companyid from [dbo].[RevenuePending_With_Split] A join tbl_master B on CompanyID = B.master_id group by MediaGroup,B.company_name,companyid,splitempid,issyr, IssMth
) mastertable where mastertable.SplitEmpID is not null group by Company, issyr, mediagroup, splitempid, accountid order by Company
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 453 ms, elapsed time = 461 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(31884 row(s) affected)
Table 'tbl_master'. Scan count 24, logical reads 5180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_detail'. Scan count 18, logical reads 13119, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_pub_type'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_media_group'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_emp'. Scan count 6, logical reads 11268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_pub'. Scan count 12, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_fiscal_month'. Scan count 6, logical reads 247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_register'. Scan count 6, logical reads 351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_master_contact'. Scan count 6, logical reads 2122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_iss'. Scan count 12, logical reads 2210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_invoice'. Scan count 12, logical reads 5600, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_io'. Scan count 12, logical reads 9822, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_io_detail'. Scan count 12, logical reads 40346, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_io_split_commission'. Scan count 12, logical reads 2878, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 2073, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_account_type'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_position'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_payroll_deadline'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_type'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_adsize'. Scan count 6, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_sect'. Scan count 6, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_edition'. Scan count 6, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_io_status'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 8960 ms, elapsed time = 2905 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

Copy the :
where SplitEmpID is not null
check into both the inner queries so that it execs before the UNION A-L-L, not after.

If you at all can, change the GROUP BY in the second UNIONed query to be company first, so that it matches the other query and the outer grouping:

group by B.company_name,MediaGroup,companyid,splitempid,issyr, IssMth

) mastertable

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 2

ScottPletcher - Monday, March 5, 2018 3:47 PM
Copy the :
where SplitEmpID is not null
check into both the inner queries so that it execs before the UNION A-L-L, not after.
If you at all can, change the GROUP BY in the second UNIONed query to be company first, so that it matches the other query and the outer grouping:
group by B.company_name,MediaGroup,companyid,splitempid,issyr, IssMth
) mastertable

I still don't a change unfortunately

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 3

I was thinking about creating a temp table (denormalize the data) which can be used to display the report but I am running into an issue and it has something to do with IssYr. Even when I change the datatype to int, it still gives me an error.
This is the error

Msg 206, Level 16, State 2, Line 22
Operand type clash: int is incompatible with date

This is the SQL.create table #Test
(
    Company varchar(200)
    ,January int
    ,February int
    ,March int
    ,April int
    ,May int
    ,June int
    ,July int
    ,August int
    ,September int
    ,October int
    ,November int
    ,December int
    ,IssYr Date
    ,MediaGroup varchar (100)
    ,SplitEmpID int
    ,AccountID int
)

insert into #Test

SELECT Company , SUM(mastertable.jan1) as January, SUM(mastertable.Feb1) as February, SUM(mastertable.Mar1) as March
             , SUM(mastertable.Apr1) as April, SUM(mastertable.May1) as May, SUM(mastertable.June1) as June
             , SUM(mastertable.July1) as July, SUM(mastertable.Aug1) as August, SUM(mastertable.Sept1) as September
             , SUM(mastertable.Oct1) as October, SUM(mastertable.Nov1) as November, SUM(mastertable.Dec1) as December
             ,IssYr,MediaGroup, SplitEmpID, AccountID
from
(
select B.company_name as Company
,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1
,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1
,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1
,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1
,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1
,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1
,issyr,MediaGroup, splitempid, accountid
from [dbo].[FiscalBilling_SalesAdj] A
join tbl_master B on A.AccountID = B.master_id
where A.RevenueType <> 'Sales Adj'
group by B.company_name, IssMth,issyr,MediaGroup, splitempid, accountid

UNION ALL

select B.company_name as Company
,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1
,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1 ,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1
,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1 ,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1
,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1
,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1
,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1
,issyr,MediaGroup, splitempid, companyid
from [dbo].[RevenuePending_With_Split] A
join tbl_master B on CompanyID = B.master_id
group by MediaGroup,B.company_name,companyid,splitempid,issyr, IssMth

) mastertable
where
mastertable.SplitEmpID is not null
group by Company, issyr, mediagroup, splitempid, accountid order by Company

select * from #Test

ScottPletcher SSC Guru Points: 100942 More actions · Answer 4

?? Nothing's changed in that query.

... from ( select B.company_name as Company ,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1 ,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1 ,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1 ,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1 ,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1 ,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1 ,issyr,MediaGroup, splitempid, accountid from [dbo].[FiscalBilling_SalesAdj] A join tbl_master B on A.AccountID = B.master_id where A.RevenueType <> 'Sales Adj' and splitEmpID is not null group by B.company_name, IssMth,issyr,MediaGroup, splitempid, accountid

UNION ALL

select B.company_name as Company ,SUM(CASE WHEN IssMth = 1 then (CashOnly) else 0 end) as Jan1 ,SUM(CASE WHEN IssMth = 2 then (CashOnly) else 0 end) as FEB1 ,SUM(CASE WHEN IssMth = 3 then (CashOnly) else 0 end) as Mar1 ,SUM(CASE WHEN IssMth = 4 then (CashOnly) else 0 end) as Apr1 ,SUM(CASE WHEN IssMth = 5 then (CashOnly) else 0 end) as May1 ,SUM(CASE WHEN IssMth = 6 then (CashOnly) else 0 end) as June1 ,SUM(CASE WHEN IssMth = 7 then (CashOnly) else 0 end) as July1 ,SUM(CASE WHEN IssMth = 8 then (CashOnly) else 0 end) as Aug1 ,SUM(CASE WHEN IssMth = 9 then (CashOnly) else 0 end) as Sept1 ,SUM(CASE WHEN IssMth = 10 then (CashOnly) else 0 end) as Oct1 ,SUM(CASE WHEN IssMth = 11 then (CashOnly) else 0 end) as Nov1 ,SUM(CASE WHEN IssMth = 12 then (CashOnly) else 0 end) as Dec1 ,issyr,MediaGroup, splitempid, companyid from [dbo].[RevenuePending_With_Split] A join tbl_master B on CompanyID = B.master_id where splitEmpID is not null group by B.company_name,MediaGroup,companyid,splitempid,issyr, IssMth

) mastertable where mastertable.SplitEmpID is not null ...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.