October 1, 2015 at 11:32 am
Anyone knows how to use the query plan
I pressed the "Display estimated execution plan " but it did not tell me to create any new indexes.
How do we get to know what indexes to make ?
Or may be u can move my code around and show me
Select DISTINCT 'H' FILET,cd.ADMDT,cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,
cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN,
CAST('' AS VARCHAR(50)) EDI835RSN ,hh.CHKNO,cd.CHKDT,cd.HCPCS,cd.OICPD,cd.REVCD,cd.HLIID,cd.IDAMT,
cd.APCCD, cd.APPAYST,cd.BANCD,cd.RCVDT,
cd.APCSI, '' ESSNPI, '' SSNPI,
'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility, 0 Denied,
0 ENSVDT, '' Mod2, '' Mod3, '' Mod4,
'H' +
CAST(ISNULL(cd.ADMDT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.FORMN, '' ) as varchar(100) ) +
CAST(ISNULL(cd.SSVDT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.PCDCD, '' ) as varchar(100) ) +
CAST(ISNULL(cd.PRVNO, '' ) as varchar(100) ) +
CAST(ISNULL(cd.PCDQT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.BILAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.ALWAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.COPAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.WITAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.NETAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.PAYST, '' ) as varchar(100) ) +
CAST(ISNULL(cd.AJRSN, '' ) as varchar(100) ) +
CAST(ISNULL(cd.DCTAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.NCVAM, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.NCRSN, '' ) as varchar(100) ) +
CAST(ISNULL(hh.CHKNO, '' ) as varchar(100) ) +
CAST(ISNULL(cd.CHKDT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.HCPCS, '' ) as varchar(100) ) +
CAST(ISNULL(cd.OICPD, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.REVCD, '' ) as varchar(100) ) +
CAST(ISNULL(cd.HLIID, '' ) as varchar(100) ) +
CAST(ISNULL(cd.IDAMT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.APCCD, '' ) as varchar(100) ) +
CAST(ISNULL(cd.APPAYST, '' ) as varchar(100) ) +
CAST(ISNULL(cd.BANCD, '' ) as varchar(100) ) +
CAST(ISNULL(cd.RCVDT, 0.0 ) as varchar(100) ) +
CAST(ISNULL(cd.APCSI, '' ) as varchar(100) ) +
'000' as The_Key
fromdbo.hd835dp dd WITH (NOLOCK)
INNER JOIN
dbo.hh835DP hd WITH (NOLOCK)
ON
( (dd.FILET = hd.FILET) and (dd.FORMN = hd.FORMN) and (dd.PSVDT = hd.PSVDT) )
INNER JOIN
dbo.hh835hp hh WITH (NOLOCK)
ON
((hh.chkno = hd.chkno) and (hh.BANCD = hd.BANCD) and (hh.chkdts = hd.chkdt) )
INNER JOIN
dbo.hinsdp cd WITH (NOLOCK)
ON (
dd.PSVDT = cd.admdt
AND
dd.formn = cd.formn
and
hh.CMPCD = cd.CMPCD
)
where
(@VendorNumber is null or (hh.vndno = @VendorNumber))
and hh.chkdts = @CheckRunDate
and dd.DPSTF = 'N'
and hd.FILET = 'H'
and cd.EDI835Exclude = 0
and (@CompanyCode IS NULL OR ( cd.CMPCD = @CompanyCode))
-- this filter added may 29 2012
and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
October 1, 2015 at 12:36 pm
where
(@VendorNumber is null or (hh.vndno = @VendorNumber))
and hh.chkdts = @CheckRunDate
and dd.DPSTF = 'N'
and hd.FILET = 'H'
and cd.EDI835Exclude = 0
and (@CompanyCode IS NULL OR ( cd.CMPCD = @CompanyCode))
-- this filter added may 29 2012
and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
you've got yourself a catch-all query there, which will rarely perform well.
read about it at Gails' great post here:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
you should consider creating a master procedure, which calls specific child procedures with the right parameters when they are not null.
Lowell
October 1, 2015 at 1:12 pm
Are you absolutely, positively getting duplicate records is you don't include that DISTINCT. I bet that query will run relatively faster without it. If you do need that DISTINCT then there is a problem with your data model.
Don't rely on the SSMS to tell you what indexes to create. Sometimes SSMS get's it right, sometimes not. You have a lot of NULLable columns. Are do they seriously need all those columns to be nullable? if not, make them NOT NULL. The would surely help.
Lastly, those NOLOCK table hints make it possible for you to get the wrong answer. When you are using NOLOCK the correct answer to your query is no longer guaranteed.
-- Itzik Ben-Gan 2001
October 1, 2015 at 1:33 pm
To further what Alan stated about NOLOCK. When you use that hint you can and will get missing and/or duplicate data from time to time. If that is acceptable then fine, otherwise you might consider another option.
Don't be confused into thinking that hint is perfectly safe. It brings more to the table than most people realize.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
October 1, 2015 at 2:18 pm
Code like the following can be an absolute killer because the optimizer will always assume the worst case scenario and force a index scan when it would otherwise be able to use a seek.
(@VendorNumber is null or (hh.vndno = @VendorNumber))
and (@CompanyCode IS NULL OR ( cd.CMPCD = @CompanyCode))
and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
simply adding OPTION(RECOMPILE) will to the end of the query will fix the "unnecessary scan" issue.
(@VendorNumber is null or (hh.vndno = @VendorNumber))
and (@CompanyCode IS NULL OR ( cd.CMPCD = @CompanyCode))
and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
OPTION(RECOMPILE)
The down side is, of course, the code needs to recompile with every execution.
Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
October 1, 2015 at 2:31 pm
Ok thx
Can you help me with this then ?
I like to write a query where I pass a name of a table and column and then it will simply return 1 ( means that column is part of some index )
That will help me whether those columns in the joins are covered by an index.
October 1, 2015 at 2:39 pm
mw112009 (10/1/2015)
Ok thxCan you help me with this then ?
I like to write a query where I pass a name of a table and column and then it will simply return 1 ( means that column is part of some index )
That will help me whether those columns in the joins are covered by an index.
just because a column exists in an index for a given table does not mean the index would be used and that you are off the hook as far as tuning.
tuning requires a little deeper dive than that, with some analysis and understanding of the specific query being looked at.
you might query the DMV's for missing indexes, that would have a better review value, IMHO:
SELECT TOP 20
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact -- Query cost would reduce by this amount, on average.
, TableName = statement
, Tbl = REVERSE(LEFT(REVERSE(statement),CHARINDEX('.',REVERSE(statement)) -1))
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Columns] = included_columns,
'CREATE INDEX [IX_' + REPLACE(REPLACE(REPLACE(REVERSE(LEFT(REVERSE(statement),CHARINDEX('.',REVERSE(statement)) -1)),'],[','_'),'[',''),']','') + '_' + REPLACE(REPLACE(REPLACE(equality_columns,'], [','_'),'[',''),']','') +'] ON ' + statement + '(' + equality_columns + ')' + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE(' + included_columns+')' ELSE '' END
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
Lowell
October 1, 2015 at 2:50 pm
No need
found one
--select s.name, t.name as table_name , c.name as column_name, i.name
select distinct c.name as column_name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where i.index_id > 0
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key IN ( 0,1 ) -- do not include PK indexes
and i.is_unique_constraint IN ( 0,1) -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
and t.name IN ( 'hh835DP' )
ORDER BY 1
October 1, 2015 at 3:25 pm
Too complicated..
What is a DMV ?
October 1, 2015 at 3:27 pm
This is a great query but I am not sore I understand what those numbers mean to me .
Are you saying those indexes ( I mean the create index ,,,, ) have to be created ?
I found one particular column that is already covered by an index appearing in the result set of your query . So what does that mean ?
October 2, 2015 at 4:29 am
DMV = Dynamic Management Views
It's a way of accessing information about the system. You're focusing on the wrong things here. As others have pointed out, just because an index exists doesn't mean it will get used. Execution plans can make suggestions about indexes, but they're just suggestions. It sounds like you're just barely getting started at doing query tuning. I'd strongly advise picking up a few books and reading through them. For the DMVs, there's a great book by Tim Ford and Louis Davidson. For query tuning and reading execution plans, I have a couple that you can see below in my signature.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2015 at 4:41 am
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
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
October 2, 2015 at 6:25 am
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
October 2, 2015 at 6:53 am
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
This isn't anything to do with recompilation. The whole point of recompilation is to compile a new plan in case the existing cached plan is unsuitable - in circumstances where the likelihood is high enough to make recompiles cost effective.
Execution plans are cached and reused because the process of generating them is CPU-expensive. If you look at a cached plan, it's the "estimated plan". Run the batch associated with it to collect the actual plan and the runtime information is incorporated into it.
The operators won't change though - the actual plan is the estimated plan, with some runtime information added.
Wouldn't it be useful if the relative costs were recalculated from the runtime information each time the plan is used so you have estimated and actual costs side by side, like this: 92% (1.2%)
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
October 2, 2015 at 7:04 am
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
Nuts. I was looking forward to a different point of view.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply