January 26, 2016 at 2:41 am
Thank you for suggestions everyone! I have also asked the same question on another forum and the cumulative take-aways would be the following:
* MAXDOP value of 1024 with running value of 0 is generally bad
* The query from the app is inefficient
* Instead of changing MAXDOP I should look into Cost Threshold for Parallelism
* Network_Asynch_IO and PAGEIOLATCH_SH values are out of order and should be investigated
* Don't use profiler GUI (although in this case crash points to issues with parallelism)
January 27, 2016 at 8:29 am
electrostep (1/22/2016)
... I have checked the query plans and yes, the index scan is taking place instead of the Seek - but I have no control over the application.
Is the application in-house or third party? Is there any chance, if in-house, of convincing management to do a proper rewrite, or if it's third party, replacing it with something better written?
If nothing can be done about the queries hitting the system, which, as has been pointed out, are horrendous, then there might not be a lot that can be done. A faster server will improve things, but it's still going to be a dog.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 27, 2016 at 10:21 am
When faced with repeatedly doing pattern matching against the database (i.e. the LIKE and CONTAINS clauses), if you have any ability to restructure things or can at least make recommendations to do so, you can consider whether some of the work can be done up front on a one-time basis rather than every time a query runs. For instance, if the pattern sought is always the same, perhaps adding a persisted, computed column which extracts the pattern of interest or creates a simple Boolean field to indicate whether or not the pattern exists. This can be indexed and searched much more rapidly than repeatedly searching through strings. If the pattern is variable but it always exists in an identifiable substring of the original data (e.g. the original field contains the text "SOMETHING: xxx" and you are interested in finding a pattern in the "xxx" portion), consider doing a one-time extraction of that portion of the string, possibly into a separate table and doing your searching there.
- Les
January 28, 2016 at 7:23 pm
Table2.Type!=2
Also know that if that column contains nulls, those will be missed by the select. Something along the lines of
(table2.[type]<2 and table2.[type]>2) Or table2.[type] IS NULL /* if this is a nullable column */
can invoke use of an index and catch those NULL rows here too.
----------------------------------------------------
January 29, 2016 at 2:14 am
Hello Wayne,
The application is third-party. I am in contact with the developers to make the application better 😉
January 29, 2016 at 2:27 am
"(table2.[type]<2 and table2.[type]>2)"
That will match precisely 0 rows, as type cannot be both less than 2 and greater than 2 at the same time. It's not equivalent to (table2.type != 2)
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 29, 2016 at 10:56 am
GilaMonster (1/29/2016)
"(table2.[type]<2 and table2.[type]>2)"That will match precisely 0 rows, as type cannot be both less than 2 and greater than 2 at the same time. It's not equivalent to (table2.type != 2)
Yeah I gotta take better care of my mind. Thank you Gail.
(table2.[type]>2 OR table2.[type]<2)
----------------------------------------------------
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply