January 19, 2016 at 9:25 am
While, i was monitoring the sql performance through SQL trace, i found one query which is running with different parameter everyday(once in a 5 minute). This query is taking about 20-25 seconds to execute every single time. Is there any way, i can optimize this query for better performance.
I have attached query, structure, execution plan and all required information.
SELECT sample.accn_# + sample.samplenumber as 'Specimen Number',
min(tbleventSample.eventdate) as 'Contained Date',
WorkstationLocation.LocationCode as 'Location Code',
WorkstationLocation.LocationName as 'Location Name',
Mac.WorkstationID as 'Workstation Code',
Mac.Machine_Name as 'WorkStation Name',pksample
FROM tbl_container
INNER join tbl_containeritems
on tbl_containeritems.fkContainer= tbl_container.pkcontainer
INNER join tbleventSample
on tbleventSample.pkeventsample = ( SELECT max(pkeventsample) as pkeventsample
FROM tbleventsample
WHERE fkeventtype = 26 and fksample = tbl_containeritems.parentpkvalue)
INNER JOIN Mac
on tbleventSample.fkWorkStationid = Mac.WorkstationID
INNER join WorkstationLocation
ON WorkstationLocation.LocationID = Mac.LocationID
INNER join sample
on sample.pksample = tbl_containeritems.parentpkvalue
WHERE tbl_container.ContainerNo = 'S0000830615' And tbl_container.Active = 1 and tbl_containeritems.active = 1
GROUP BY sample.accn_# + sample.samplenumber,WorkstationLocation.LocationCode,
WorkstationLocation.LocationName,Mac.WorkstationID,Mac.Machine_Name,pksample
Thanks a lot for your help in advance.
January 19, 2016 at 9:51 am
Can you post the actual execution plan instead of the excel version? It shows a lot more detail which helps with the insight as to what can be done for performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 19, 2016 at 10:06 am
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version? It shows a lot more detail which helps with the insight as to what can be done for performance.
Please find attached execution plan.
January 19, 2016 at 10:07 am
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version?
SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 10:08 am
GilaMonster (1/19/2016)
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version?SQL 2000.
Ahh. Didn't realize which thread this was in. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 19, 2016 at 10:10 am
EasyBoy (1/19/2016)
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version? It shows a lot more detail which helps with the insight as to what can be done for performance.Please find attached execution plan.
Text file's nearly unreadable. Can you use SET STATISTICS PROFILE ON and copy the grid with the plan into excel? That includes the actual rows, which makes it a touch easier to work with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 10:25 am
GilaMonster (1/19/2016)
EasyBoy (1/19/2016)
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version? It shows a lot more detail which helps with the insight as to what can be done for performance.Please find attached execution plan.
Text file's nearly unreadable. Can you use SET STATISTICS PROFILE ON and copy the grid with the plan into excel? That includes the actual rows, which makes it a touch easier to work with.
Please find the updated SQLplan with PROFILE ON AND SET SHOWPLAN_ALL on.
January 19, 2016 at 11:51 am
EasyBoy (1/19/2016)
GilaMonster (1/19/2016)
EasyBoy (1/19/2016)
Sean Lange (1/19/2016)
Can you post the actual execution plan instead of the excel version? It shows a lot more detail which helps with the insight as to what can be done for performance.Please find attached execution plan.
Text file's nearly unreadable. Can you use SET STATISTICS PROFILE ON and copy the grid with the plan into excel? That includes the actual rows, which makes it a touch easier to work with.
Please find the updated SQLplan with PROFILE ON AND SET SHOWPLAN_ALL on.
To add a note: only ContainerNo value is changing in the where clause. Rest of the things remain same.
WHERE tbl_container.ContainerNo = 'S0000830615'
January 19, 2016 at 5:42 pm
CREATE NONCLUSTERED INDEX ix_tblEventSample_fkeventtype ON dbo.tblEventSample (fkeventtype, fksample) WITH FILLFACTOR = 90 ON [INDEXDBGROUP] --[PRIMARY]
That seems to resolve the index scan. See how long after that.
January 20, 2016 at 7:37 am
Andrew G (1/19/2016)
CREATE NONCLUSTERED INDEX ix_tblEventSample_fkeventtype ON dbo.tblEventSample (fkeventtype, fksample) WITH FILLFACTOR = 90 ON [INDEXDBGROUP] --[PRIMARY]That seems to resolve the index scan. See how long after that.
This index is already exist.
CREATE INDEX [ix_tblEventSample_FkSample_fkEventtype] ON [dbo].[tblEventSample]([fkSample], [fkEventType]) WITH FILLFACTOR = 97 ON [INDEXDBGROUP]
GO
January 20, 2016 at 8:33 am
EasyBoy (1/20/2016)
This index is already exist.
CREATE INDEX [ix_tblEventSample_FkSample_fkEventtype] ON [dbo].[tblEventSample]([fkSample], [fkEventType]) WITH FILLFACTOR = 97 ON [INDEXDBGROUP]
GO
No it doesn't.
Andrew recommended an index with the key fkeventtype, fksample. The existing index has the key columns the other way around. It's not the same index.
Can you create the index that Andrew suggested and post the revised plan?
I would also suggest you take the index ix_tblContainer_ContaineNo and add Active as a second key column, take the index ix_tblContainerItems_Fkcontainer and also add Active as a second column, and take the index ix_WorkStationID and add LocationID and Machine_Name as additional key columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2016 at 8:40 am
GilaMonster (1/20/2016)
EasyBoy (1/20/2016)
This index is already exist.
CREATE INDEX [ix_tblEventSample_FkSample_fkEventtype] ON [dbo].[tblEventSample]([fkSample], [fkEventType]) WITH FILLFACTOR = 97 ON [INDEXDBGROUP]
GO
No it doesn't.
Andrew recommended an index with the key fkeventtype, fksample. The existing index has the key columns the other way around. It's not the same index.
Can you create the index that Andrew suggested and post the revised plan?
I would also suggest you take the index ix_tblContainer_ContaineNo and add Active as a second key column, take the index ix_tblContainerItems_Fkcontainer and also add Active as a second column, and take the index ix_WorkStationID and add LocationID and Machine_Name as additional key columns
Thanks for the response.
Actually, i am not allow to create index on production server. Instead, i will create tables with some dummy data on test server and give it a try.
January 20, 2016 at 9:42 am
Rather get a backup restored somewhere you can test. Because a test table with dummy data will behave differently.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2016 at 9:50 am
GilaMonster (1/20/2016)
Rather get a backup restored somewhere you can test. Because a test table with dummy data will behave differently.
That's what i think earlier, but backup size is too large to restore.
Is there any way, i can just copy few tables from one sql server to other?
January 20, 2016 at 10:04 am
bcp will work for the data. You'll have to script out the tables, with keys and indexes and recreate then in the destination DB first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply