SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I am given a task to troubleshoot performance issue


I am given a task to troubleshoot performance issue

Author
Message
NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 812
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.

Attachments
Sales.sqlplan (10 views, 3.00 MB)
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74799 Visits: 8900
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 812
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
NewBornDBA2017
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 812
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
ScottPletcher
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74799 Visits: 8900
?? 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search