You may find this query useful: SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0 To get the query itself use this one: SELECT text,* FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0
An index cannot safely be used in this case. A naive implementation would transform this: … WHERE word LIKE ‘search_string%’ into … WHERE word >= ‘search_string’ AND word < ‘search_strinh’ by incrementing the last character of the search string. The greater-than and less-than operators can use an index, where LIKE cannot. Unfortunately, that won’t work … Read more
This is likely not a problem of indexing, but one of data design. Normalization, to be precise. The fact that you need to query distinct values of a field, and even willing to add an index, is a strong indicator that the field should be normalized into a separate table with a (small) join key. … Read more
You can take the aggregations out into a CTE (common table expression): with minima as (select t.id, t.type, min(value) min_value from table2 t where t.type in (1,2,3,4) group by t.id, t.type) select a.id, a.name, (select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1, (select min_value from minima where minima.id = … Read more
I’m not an SQL tuning expert, but joining hundreds of millions of rows on a VARCHAR field doesn’t sound like a good idea in any database system I know. You could try adding an integer column to each table and computing a hash on the NAME field that should get the possible matches to a … Read more
There are a few possible explanations as to why you see this behavior. Some common ones are The subquery or CTE may be being repeatedly re-evaluated. Materialising partial results into a #temp table may force a more optimum join order for that part of the plan by removing some possible options from the equation. Materialising … Read more
Only if you add full-text searching to those columns, and use the full-text query capabilities of SQL Server. Otherwise, no, an index will not help.
Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you’re basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you’re still running them.
Converting to a DATE or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime? Posted by Aaron Bertrand From that article: DECLARE … Read more
SET STATISTICS IO ON SET STATISTICS TIME ON Run the queries and compare logical reads for the various tables and execution times.