April 11, 2017 at 7:01 am
nadersam - Tuesday, April 11, 2017 3:04 AMThank you guys for all the replies, from what i see based on replies , it's recommended to do partitioning to solve a performance issue.
What i just need to understand please why doesn't partitioning help decrease scanning through historical data by just scanning through current(last) partition only.Thanks again
Nader
Partitioning doesn't prevent table scanning; that's what basic indexing is intended to do. At this point, post the DDL for your table, including indexes, and the SQL select statement that you observe is scanning the entire table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 11, 2017 at 7:39 am
nadersam - Tuesday, April 11, 2017 6:42 AMMay be i didn't elaborate on that, what i did is get a query that performs lots of reads on a table then from the execution plan get which index it uses(Covering index).
Then i created that same index exactly but with a filter condition that gets data only within the criteria of that query.
If you need me to send any details of database structure, please tell me what's needed and i will provide it.Thank you
Nader
I agree with Eric. We need to know what the table looks like, what the indexes look like, and what the queries are. If possible, it would also be helpful to see what the ACTUAL execution plans look like. Please see the 2nd link under "Helpful Links" for how to post the execution plans. Please generate the script for the CREATE TABLE statement and include the indexes (it's a setting in the script generation settings for SSMS).
As a bit of a sidebar, it sounds like a nonSARGable predicate is being used for the date criteria in the queries. I'm also thinking that if you have to resort to filtered indexes in this case, then something is very wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 8:24 am
nadersam - Tuesday, April 11, 2017 3:04 AMThank you guys for all the replies, from what i see based on replies , it's recommended to do partitioning to solve a performance issue.
What i just need to understand please why doesn't partitioning help decrease scanning through historical data by just scanning through current(last) partition only.Thanks again
Nader
If your queries are unnecessarily scanning through current and aged data then your indexing strategy requires a makeover.
"Unfortunately the index tuning path has already been exhausted" - there's a few people around here who can do amazing things with indexes. Give them a chance, you could end up saving yourself a ton of time & effort. In any case, as folks have pointed out, queries which might perform faster against a partitioned table are those which interrogate a single partition...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2017 at 2:04 am
Hi All,
I attached all details needed to check the design for that query.
1. Script.txt contains creation scripts for tables,indexes and views.
2. sql.txt is the sql i need to check.
3. I attached the execution plan as xml and sqlplan format.
4.In screen shot i am showing that SQL used index IDX_SAMPLE_DETAIL_1221, i was expecting it to use IDX_SAMPLE_DETAIL_1221Filtered which has same design exactly , only difference is where condition which i specified to be since beginning of year 2017.
Please let me know if anything is missing or you need any additional information, looking forward for your great ideas :).
Thanks
Nader
April 13, 2017 at 3:06 am
you do realize that your view is to be materialized due to all the functions you are performing in it to determin the content for colum C_Date, right ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2017 at 3:26 am
ALZDBA - Thursday, April 13, 2017 3:06 AMyou do realize that your view is to be materialized due to all the functions you are performing in it to determin the content for colum C_Date, right ?
I am sorry but not aware of the terminology "materialized"
Could you please give me more details.
Thanks
Nader
April 13, 2017 at 3:41 am
You are using a view which calculates the value of a column you are using as a predicate in your original query.
So to be able to filter the result set, it will first have to actually determine the value for that column in all your affected set of data.
That operations set of data is writen to tempdb and then used to perform the filter operation
This is the kind of implicit conversion views can hide from query writers.
Materialized view
your simple query actually has this query for its basis:Select distinct c_date
from (
SELECT CASE
WHEN Collection_Date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT( VARCHAR(50), Collection_Date, 6) AS DATETIME)
WHEN CollectDate IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), CollectDate, 6) AS DATETIME)
WHEN group_type = 1
AND complete_date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), complete_date, 6) AS DATETIME)
WHEN group_type = 2
AND complete_date IS NOT NULL
AND ( description <> 'comment' ) THEN CAST(CONVERT(VARCHAR(50), complete_date, 6) AS DATETIME)
WHEN Rejectdatetime IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Rejectdatetime, 6) AS DATETIME)
WHEN Receiveddate IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Receiveddate, 6) AS DATETIME)
WHEN group_type = 2
AND Rad_Start_Date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Rad_Start_Date, 6) AS DATETIME)
WHEN Group_Type = 2
AND scheddate IS NOT NULL
AND ( description <> 'comment' ) THEN CAST(CONVERT(VARCHAR(50), scheddate, 6) AS DATETIME)
WHEN Arrival_date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Arrival_date, 6) AS DATETIME)
WHEN CancelDate IS NOT NULL THEN CAST(CONVERT(VARCHAR(50), CancelDate, 6) AS DATETIME)
WHEN order_date IS NOT NULL THEN CONVERT(DATETIME, CONVERT(NVARCHAR(10), order_date, 102))
END AS C_Date
, patient_Id
, Episod_Key
, ResultDesc
, Group_Type
, 'Complete' AS d
FROM
(
SELECT INVESTIGATION.GROUP_TYPE
, INVESTIGATION.DESCRIPTION
, INVRESULTS.DESCRIPTION AS RESULTDESC
, INVORDERDET.INV_CODE
, INVORD_HDR.ORDER_STATUS
, INVRESULTS.ORDER1
, INVORD_HDR.ORDER_DATE
, SAMPLE_DETAIL.COLLECTDATE
, INVORD_HDR.COMPLETE_DATE
, SAMPLE_DETAIL.RECEIVEDDATE
, INVORDERDET.ARRIVAL_DATE
, INVORDERDET.START_DATE AS RAD_START_DATE
, INVORDERDET.ISVERIFIED
, INVORD_HDR.INVORD_KEY
, INVORD_HDR.TREATINGDOC
, INVORD_HDR.STAFFCONFIRMATIONKEY
, INVORDRES.RVALUE
, INVORDRES.NORMALTY
, EPISODE.EPISOD_KEY
, EPISODE.START_DATE
, EPISODE.END_DATE
, INVORDRES.RANGE
, INVORDRES.STATUS
, INVRESULTS.UNIT
, INVORD_HDR.ORDER_TIME
, SAMPLE_DETAIL.COLLECTTIME
, INVESTIGATION.SYS_KEY AS ORDER_1
, SAMPLE_DETAIL.COLLECTEDBY
, INVORD_HDR.CANCELREASON
, INVORD_HDR.CANCELBY
, INVORDRES.HIDE
, INVORD_HDR.COMPLETE_TIME
, INVORD_HDR.CANCELTIME
, INVORDRES.INVRES_KEY
, INVORDERDET.NOTES
, INVORD_HDR.CANCELDATE
, INVRESULTS.ISITTITLE
, INVORD_HDR.USERID
, EPISODE.PAT_EPISODE_TYP AS PATIENT_TYPE
, PATIENT.PATIENT_SEX
, INVORD_HDR.STAT
, PATIENT.PAT_BIRTHDATE
, NULL AS SAMPLEAMOUNT
, NULL AS DURATION
, NULL AS DEVICEKEY
, NULL AS DEVICESECTORNUMBER
, NULL AS DEVICECUPNUMBER
, NULL AS DEVICERECORDNUMBER
, NULL AS DEVICEDATETIME
, NULL AS LABDEVWLISTKEY
, NULL AS PBDATE
, NULL AS DEVICE_NAME
, NULL AS CORRECTPARAM
, NULL AS CRRECTFACTOR
, NULL AS DEVICERESKEY
, SAMPLE_DETAIL.SAMPLE_ID AS SAMPLEID
, NULL AS RESDATETIME
, NULL AS UPLOADDATETIME
, INVORD_HDR.PATIENT_ID
, SAMPLE_DETAIL.RECEIVEDTIME
, PATIENT.PAT_NAME
, NULL AS ALERTHI
, NULL AS ALERTLOW
, NULL AS PANICHI
, NULL AS PANICLOW
, INVRESULTS.SYS_KEY AS INVRESULTSKEY
, INVORDERDET.ORDDET_KEY
, INVORDERDET.SCHEDDATE
, INVORDERDET.SCHEDULEDBY
, INVORDERDET.SCHEDFROMTIME
, INVORDERDET.SCHEDTOTIME
, SAMPLE_DETAIL.RECEIVEDBY
, INVORDRES.MINRANGE
, INVORDRES.MAXRANGE
, INVORD_HDR.APPENDEDRESULT
, INVORDRES.RESAPPENDDATETIME
, INVORDRES.RCOMMENT
, LABSAMPLECOLLECT.REJECTDATETIME
, LABSAMPLECOLLECT.COLLECTION_DATE
, LABSAMPLECOLLECT.COLLECTION_TIME
, LABSAMPLECOLLECT.COLLECTED_BY
, LABSAMPLECOLLECT.SAMPLEREJECTED
, INVRESULTS.RESULT_COMMENT
, SHEET_KEY = CASE
WHEN INVORDRES.SHEET_KEY IS NULL THEN 0
WHEN INVORDRES.SHEET_KEY IS NOT NULL THEN INVORDRES.SHEET_KEY
END
, 0 AS TYPE_W
, SECOND_OPENION_STATUS
, REPETITIVE
, INVORDRES.RESULT_TYPE
, EMR_CANCEL
, INVESTIGATION.PARENT
, Second_Parent_key
FROM --LABSAMPLECOLLECT WITH (NOLOCK)
--RIGHT OUTER JOIN SAMPLE_DETAIL WITH (NOLOCK)
--RIGHT OUTER JOIN
INVRESULTS WITH (NOLOCK)
INNER JOIN INVORDRES WITH (NOLOCK)
ON INVRESULTS.SYS_KEY = INVORDRES.RESULT_KEY
INNER JOIN PATIENT WITH (NOLOCK)
INNER JOIN INVORDERDET WITH (NOLOCK)
INNER JOIN INVORD_HDR WITH (NOLOCK)
ON INVORDERDET.INVORD_KEY = INVORD_HDR.INVORD_KEY
INNER JOIN INVESTIGATION WITH (NOLOCK)
ON INVORDERDET.INV_CODE = INVESTIGATION.SYS_KEY
ON PATIENT.PATIENT_ID = INVORD_HDR.PATIENT_ID
INNER JOIN EPISODE WITH (NOLOCK)
ON INVORD_HDR.EPISODE_KEY = EPISODE.EPISOD_KEY
AND PATIENT.PATIENT_ID = EPISODE.PATIENT_ID
ON INVORDRES.ORDDETKEY = INVORDERDET.ORDDET_KEY
LEFT OUTER JOIN SAMPLE_DETAIL WITH (NOLOCK) /* converted from right join */
ON SAMPLE_DETAIL.INVORD_KEY = INVORD_HDR.INVORD_KEY
AND SAMPLE_DETAIL.ORDERDET_KEY = INVORDERDET.ORDDET_KEY
LEFT OUTER JOIN LABSAMPLECOLLECT WITH (NOLOCK) /* converted from right join */
ON LABSAMPLECOLLECT.ORDDET_KEY = INVORDERDET.ORDDET_KEY
WHERE episode.episod_key > 19959
AND ( INVESTIGATION.PARENT <> 0 )
AND (INVRESULTS.DESCRIPTION IS NOT NULL)
AND (BBPRETRANSFUSION IS NULL)
AND (INVESTIGATION.HIDDEN = 'FALSE'
OR INVESTIGATION.HIDDEN IS NULL)
) DIAGNOSTICVIEW
) GetDatesDiagnostic
WHERE Group_type = 1
AND PATIENT_ID = '57443'
AND C_Date <= '2017-04-10 23:59:59.000'
AND C_Date >= '2017-04-09 00:00:00.000'
AND c_date IS NOT NULL
GROUP BY C_Date
ORDER BY C_DATE DESC;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2017 at 3:58 am
ALZDBA - Thursday, April 13, 2017 3:41 AMYou are using a view which calculates the value of a column you are using as a predicate in your original query.
So to be able to filter the result set, it will first have to actually determine the value for that column in all your affected set of data.
That operations set of data is writen to tempdb and then used to perform the filter operation
This is the kind of implicit conversion views can hide from query writers.
Materialized viewyour simple query actually has this query for its basis:
Select distinct c_date
from (
SELECT CASE
WHEN Collection_Date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT( VARCHAR(50), Collection_Date, 6) AS DATETIME)
WHEN CollectDate IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), CollectDate, 6) AS DATETIME)
WHEN group_type = 1
AND complete_date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), complete_date, 6) AS DATETIME)
WHEN group_type = 2
AND complete_date IS NOT NULL
AND ( description <> 'comment' ) THEN CAST(CONVERT(VARCHAR(50), complete_date, 6) AS DATETIME)
WHEN Rejectdatetime IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Rejectdatetime, 6) AS DATETIME)
WHEN Receiveddate IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Receiveddate, 6) AS DATETIME)
WHEN group_type = 2
AND Rad_Start_Date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Rad_Start_Date, 6) AS DATETIME)
WHEN Group_Type = 2
AND scheddate IS NOT NULL
AND ( description <> 'comment' ) THEN CAST(CONVERT(VARCHAR(50), scheddate, 6) AS DATETIME)
WHEN Arrival_date IS NOT NULL
AND ( description <> 'comment' )
AND ( isittitle = 0 ) THEN CAST(CONVERT(VARCHAR(50), Arrival_date, 6) AS DATETIME)
WHEN CancelDate IS NOT NULL THEN CAST(CONVERT(VARCHAR(50), CancelDate, 6) AS DATETIME)
WHEN order_date IS NOT NULL THEN CONVERT(DATETIME, CONVERT(NVARCHAR(10), order_date, 102))
END AS C_Date
, patient_Id
, Episod_Key
, ResultDesc
, Group_Type
, 'Complete' AS d
FROM
(
SELECT INVESTIGATION.GROUP_TYPE
, INVESTIGATION.DESCRIPTION
, INVRESULTS.DESCRIPTION AS RESULTDESC
, INVORDERDET.INV_CODE
, INVORD_HDR.ORDER_STATUS
, INVRESULTS.ORDER1
, INVORD_HDR.ORDER_DATE
, SAMPLE_DETAIL.COLLECTDATE
, INVORD_HDR.COMPLETE_DATE
, SAMPLE_DETAIL.RECEIVEDDATE
, INVORDERDET.ARRIVAL_DATE
, INVORDERDET.START_DATE AS RAD_START_DATE
, INVORDERDET.ISVERIFIED
, INVORD_HDR.INVORD_KEY
, INVORD_HDR.TREATINGDOC
, INVORD_HDR.STAFFCONFIRMATIONKEY
, INVORDRES.RVALUE
, INVORDRES.NORMALTY
, EPISODE.EPISOD_KEY
, EPISODE.START_DATE
, EPISODE.END_DATE
, INVORDRES.RANGE
, INVORDRES.STATUS
, INVRESULTS.UNIT
, INVORD_HDR.ORDER_TIME
, SAMPLE_DETAIL.COLLECTTIME
, INVESTIGATION.SYS_KEY AS ORDER_1
, SAMPLE_DETAIL.COLLECTEDBY
, INVORD_HDR.CANCELREASON
, INVORD_HDR.CANCELBY
, INVORDRES.HIDE
, INVORD_HDR.COMPLETE_TIME
, INVORD_HDR.CANCELTIME
, INVORDRES.INVRES_KEY
, INVORDERDET.NOTES
, INVORD_HDR.CANCELDATE
, INVRESULTS.ISITTITLE
, INVORD_HDR.USERID
, EPISODE.PAT_EPISODE_TYP AS PATIENT_TYPE
, PATIENT.PATIENT_SEX
, INVORD_HDR.STAT
, PATIENT.PAT_BIRTHDATE
, NULL AS SAMPLEAMOUNT
, NULL AS DURATION
, NULL AS DEVICEKEY
, NULL AS DEVICESECTORNUMBER
, NULL AS DEVICECUPNUMBER
, NULL AS DEVICERECORDNUMBER
, NULL AS DEVICEDATETIME
, NULL AS LABDEVWLISTKEY
, NULL AS PBDATE
, NULL AS DEVICE_NAME
, NULL AS CORRECTPARAM
, NULL AS CRRECTFACTOR
, NULL AS DEVICERESKEY
, SAMPLE_DETAIL.SAMPLE_ID AS SAMPLEID
, NULL AS RESDATETIME
, NULL AS UPLOADDATETIME
, INVORD_HDR.PATIENT_ID
, SAMPLE_DETAIL.RECEIVEDTIME
, PATIENT.PAT_NAME
, NULL AS ALERTHI
, NULL AS ALERTLOW
, NULL AS PANICHI
, NULL AS PANICLOW
, INVRESULTS.SYS_KEY AS INVRESULTSKEY
, INVORDERDET.ORDDET_KEY
, INVORDERDET.SCHEDDATE
, INVORDERDET.SCHEDULEDBY
, INVORDERDET.SCHEDFROMTIME
, INVORDERDET.SCHEDTOTIME
, SAMPLE_DETAIL.RECEIVEDBY
, INVORDRES.MINRANGE
, INVORDRES.MAXRANGE
, INVORD_HDR.APPENDEDRESULT
, INVORDRES.RESAPPENDDATETIME
, INVORDRES.RCOMMENT
, LABSAMPLECOLLECT.REJECTDATETIME
, LABSAMPLECOLLECT.COLLECTION_DATE
, LABSAMPLECOLLECT.COLLECTION_TIME
, LABSAMPLECOLLECT.COLLECTED_BY
, LABSAMPLECOLLECT.SAMPLEREJECTED
, INVRESULTS.RESULT_COMMENT
, SHEET_KEY = CASE
WHEN INVORDRES.SHEET_KEY IS NULL THEN 0
WHEN INVORDRES.SHEET_KEY IS NOT NULL THEN INVORDRES.SHEET_KEY
END
, 0 AS TYPE_W
, SECOND_OPENION_STATUS
, REPETITIVE
, INVORDRES.RESULT_TYPE
, EMR_CANCEL
, INVESTIGATION.PARENT
, Second_Parent_key
FROM --LABSAMPLECOLLECT WITH (NOLOCK)
--RIGHT OUTER JOIN SAMPLE_DETAIL WITH (NOLOCK)
--RIGHT OUTER JOIN
INVRESULTS WITH (NOLOCK)
INNER JOIN INVORDRES WITH (NOLOCK)
ON INVRESULTS.SYS_KEY = INVORDRES.RESULT_KEY
INNER JOIN PATIENT WITH (NOLOCK)
INNER JOIN INVORDERDET WITH (NOLOCK)
INNER JOIN INVORD_HDR WITH (NOLOCK)
ON INVORDERDET.INVORD_KEY = INVORD_HDR.INVORD_KEY
INNER JOIN INVESTIGATION WITH (NOLOCK)
ON INVORDERDET.INV_CODE = INVESTIGATION.SYS_KEY
ON PATIENT.PATIENT_ID = INVORD_HDR.PATIENT_ID
INNER JOIN EPISODE WITH (NOLOCK)
ON INVORD_HDR.EPISODE_KEY = EPISODE.EPISOD_KEY
AND PATIENT.PATIENT_ID = EPISODE.PATIENT_ID
ON INVORDRES.ORDDETKEY = INVORDERDET.ORDDET_KEY
LEFT OUTER JOIN SAMPLE_DETAIL WITH (NOLOCK) /* converted from right join */
ON SAMPLE_DETAIL.INVORD_KEY = INVORD_HDR.INVORD_KEY
AND SAMPLE_DETAIL.ORDERDET_KEY = INVORDERDET.ORDDET_KEY
LEFT OUTER JOIN LABSAMPLECOLLECT WITH (NOLOCK) /* converted from right join */
ON LABSAMPLECOLLECT.ORDDET_KEY = INVORDERDET.ORDDET_KEY
WHERE episode.episod_key > 19959
AND ( INVESTIGATION.PARENT <> 0 )
AND (INVRESULTS.DESCRIPTION IS NOT NULL)
AND (BBPRETRANSFUSION IS NULL)
AND (INVESTIGATION.HIDDEN = 'FALSE'
OR INVESTIGATION.HIDDEN IS NULL)
) DIAGNOSTICVIEW) GetDatesDiagnostic
WHERE Group_type = 1
AND PATIENT_ID = '57443'
AND C_Date <= '2017-04-10 23:59:59.000'
AND C_Date >= '2017-04-09 00:00:00.000'
AND c_date IS NOT NULL
GROUP BY C_Date
ORDER BY C_DATE DESC;
Thank you for your reply.
Does that have any effect on query using the regular index not the filetered one?
April 13, 2017 at 6:04 am
First I believe someone just ran DTA and created all indexes and statistics that tool suggested.
Result:
- Way to many indexes
- user defined statistics
Also: having tables with only a single column being declared "not null" gives me some strange signals in the spine and make my left eye twitch
Eventually there will be a price to pay.
IIRC A filtered index will only be used if the query contains the exact filter definition as the index.
CREATE NONCLUSTERED INDEX [IDX_SAMPLE_DETAIL_1221Filtered] ON [dbo].[SAMPLE_DETAIL]
(
[Invord_key] ASC,
[orderdet_key] ASC
)
INCLUDE ( [CollectDate],
[ReceivedDate],
[CollectTime],
[CollectedBy],
[ReceivedTime],
[ReceivedBy])
WHERE ([invord_key]>(2436121))
Would only be used if the query uses
WHERE ([invord_key]>(2436121))
I haven't played around with filtered indexes for a while, so that may have been optimized with SQL2014 or 2016
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2017 at 6:12 am
ALZDBA - Thursday, April 13, 2017 6:04 AMFirst I believe someone just ran DTA and created all indexes and statistics that tool suggested.
Result:
- Way to many indexes
- user defined statisticsAlso: having tables with only a single column being declared "not null" gives me some strange signals in the spine and make my left eye twitch
Eventually there will be a price to pay.
IIRC A filtered index will only be used if the query contains the exact filter definition as the index.
CREATE NONCLUSTERED INDEX [IDX_SAMPLE_DETAIL_1221Filtered] ON [dbo].[SAMPLE_DETAIL]
(
[Invord_key] ASC,
[orderdet_key] ASC
)
INCLUDE ( [CollectDate],
[ReceivedDate],
[CollectTime],
[CollectedBy],
[ReceivedTime],
[ReceivedBy])
WHERE ([invord_key]>(2436121))Would only be used if the query uses
WHERE ([invord_key]>(2436121))I haven't played around with filtered indexes for a while, so that may have been optimized with SQL2014 or 2016
Regarding the condition for filter ([invord_key]>(2436121)) it's satisfied in query but through the inner join not explicitly, could that be the reason?
April 13, 2017 at 6:28 am
indeed
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2017 at 6:29 am
There was a missing script for one of the objects i reattached it here.
Sorry about that.
April 13, 2017 at 6:50 am
nadersam - Thursday, April 13, 2017 6:29 AMThere was a missing script for one of the objects i reattached it here.
Sorry about that.
Apologies. I get involved with a lot of post threads on this site. What is the URL for this? Also, I know it sounds strange, but I don't deal with RAR files. Just ZIP files.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2017 at 7:05 am
Sorry , which URL do you mean?
I posted it again as zip file.
Thanks
April 13, 2017 at 7:29 am
nadersam - Thursday, April 13, 2017 7:05 AMSorry , which URL do you mean?
I posted it again as zip file.Thanks
Thanks... not sure why I thought this was a PM this morning hence my question about the URL. Must really be in need of coffee.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply