December 2, 2015 at 11:08 pm
Thanks for the reply .
There has been a statistic in that column and yes I created index on that column as you can see below
CREATE NONCLUSTERED INDEX [IndexABC] ON [dbo].[ABC]
(
[Number] ASC
)
INCLUDE ( [ABC_Id],
[Status],
[XY_Create_Date],
[Name],
[XY_Edit_User],
[XY_Create_User],
[XY_Edit_Date]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Questions :
1.I don’t understand about how to filter index ?
2.How do I force an index seek upon that index ?
I had tried removing 1=1 in where criteria but no impact in performance
December 2, 2015 at 11:25 pm
1. Search https://msdn.microsoft.com/en-us/library/ms188783.aspx twice for filter_predicate .
2. Search https://msdn.microsoft.com/en-us/library/ms187373.aspx four times for index_value .
My concern was not about the presence of the AND 1=1 clauses - I expect SQL Server to be smart enough to optimize them out of the plan. My concern was instead about what they imply. They imply the WHERE clause is being dynamically built by a SQL Server client application. If that implication is correct, and if I were to see 9 "AND 1=1" clauses (I did not count your statement's), there would be up to 9 different parameters that could be passed (each in a different statement). In which case, the parameter permutations could create management problems (excessive number of indexes, etc.).
December 2, 2015 at 11:33 pm
Parameters or local variables? There is a difference. Parameters can be sniffed, sampled, so that the known value is used to retrieve statistics from your columns or indexes as part of building the execution plan. Local variables, except in the case of a recompile, are not sniffed, so average values across the statistics are used instead. The difference between averages and specific values can result in differences in the execution plans and differences in performance.
Without seeing the execution plans myself, I'm just making guesses as to what's going on.
"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
December 3, 2015 at 7:44 pm
Hi …
Here I attach the execution plans before and after adding index.
Before adding index , it uses clustered index scan and after adding index , it uses Index scan
Please kindly review
Thank you
December 3, 2015 at 9:30 pm
LIKE '%6301657%'
The best solution is to redesign the table. For the query in question, a ticket number should not be part of a whole column/field. Instead, a ticket number (with no other 'decorations') should be a whole column/field. It is the ticket number alone that this query should be seeking. There may be other needs for the current design (that I do not know) - you must decide which system needs are more important.
Consider whether a computed column can persist ticket numbers alone. Does that misnamed column have values in a specific format (a format which string functions can parse)? Index the computed column. Instead use the computed column with an equality (do not use LIKE).
Otherwise see http://www.sqlservercentral.com/Forums/Topic961057-65-1.aspx
December 3, 2015 at 10:43 pm
I'm doing something wrong. I can't get either plan to download to my machine.
"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
December 3, 2015 at 10:49 pm
Finally figured it out (I'm not sure Microsoft Edge is worth the trouble). Anyway...
These plans seem somewhat different from the earlier queries and there is no parameters or local variables in use, so I don't understand what I'm looking for.
You have a non-sargeable condition with the LIKE '%x%'. That will always result in a scan. You can't escape that. If you need to do that type of search, you may need a fulltext index. A traditional index will only ever support a scan.
"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
December 7, 2015 at 8:26 pm
Hey Grant Fritchey,
Can you please review the attached execution plan please ?
Many thanks
December 8, 2015 at 4:18 am
WhiteLotus (12/7/2015)
Hey Grant Fritchey,Can you please review the attached execution plan please ?
Many thanks
The one previously attached? I did look at them. You have a wild card search that will prevent statistics and index use and result in scans. That's what I said.
If you attached a new one on that post I don't see it.
"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
December 8, 2015 at 10:43 pm
Hi Grant
Thank you for the advice .
Unfortunately I got this error when I tried to create the Fulltext Index :
A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.
Then I remove all included columns from non clustered index ? Did’nt work
I removed that non clustered index
But it still didn’t work ..
Can you please help me point out the cause ?
Many thanks . Much appreciated
December 8, 2015 at 11:23 pm
The full text index create statement's KEY INDEX clause is looking for a unique index or a primary key.
Do you have a unique index or a primary key, and if not, can you create at least one of them?
December 9, 2015 at 2:00 am
Full text indexing isn't something that you just create and suddenly everything magically works better. You need to do some design work, planning, code changes, etc.
Grant said you may need full text indexing, not to go and create a full text index immediately.
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
December 9, 2015 at 4:45 am
SoHelpMeCod has already pointed out the issue. You don't have a defined primary key on the table.
We're all over the map here, bouncing from one thing to another. You need to settle down and approach this in a much more steady and methodical manner. The original issue was that memory is high, but now you seem to have focused on a procedure. That's fine, but the issues with the procedure have gone from something with variables or parameters to the wild card search. You need to step back and think it through.
For the really simple queries that were posted with those execution plans, are they the actual query you're attempting to tune or examples of a sub-set of that query? I ask because there were no parameters, so it makes me wonder where the parameter question came from.
If those are the queries, are you sure that you need the wild card search? Are you really trying to search for a string within another string? If so, are you sure you couldn't rearchitect the storage so that you have a separate field for that value type and we can then use traditional indexes and no wild cards at the front of the LIKE statement? If we have to search for a string in a string, then full text may be the way to go, but you should read through how that works in the documentation and apply it carefully. If you don't have a primary key on the table, well, that's a problem in and of itself. Just about every single table should have a primary key.
Let's try to approach this much more methodically and carefully. You'll get better results.
"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
December 10, 2015 at 8:13 pm
Hi ..
I have just talked to my team and it turns out that like is not necessary there so I can change it to = . Yayyyyy
Thanks for the advice friends .. very helpful
I hope memory usage also drops after this change .
December 10, 2015 at 8:53 pm
WhiteLotus (12/10/2015)
I hope memory usage also drops after this change .
No, it won't.
SQL Server will continue using all the memory you let it to use.
If you want to let Windows server and possibly other application running on it a bit more air to breeze then reduce the max amount of memory available for SQL Server.
But the query performance should increase dramatically as soon as you replace this
[Column name] LIKE '%6301657%'
with this
[Column name] LIKE '6301657%'
But on one condition: you must have at least non-clustered index having [Column name] as a first column in it.
Clustered would be preferable, unless you have a better use for a clustered index.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply