One of the things which I love about writing technical blogs is getting constant feedback from the community and most of the times, it helps me to improve and there is always something new to learn and a different perspective to look at things.
Few days back, I wrote an article on ‘Searching Data in SQL Tables in a Single Click’ by leveraging a free 3rd party tool called ‘ApexSQL Search’ —
Searching Data in SQL Tables in a Single Click
This tool makes searching data inside SQL tables very convenient – it abstracts the entire complexity of running complicated TSQL queries manually and provides the search results in a breeze.
I posted this article in the SQL Server groups in LinkedIn and got some great feedback from the group members —
That basically prompted me to come up with this blog. The biggest concern which was brought up was the negative impact of using this tool/functionality in Production database.
Searching for a value in the SQL tables is for sure a resource intensive operation. In Production databases, we should be careful while searching for a particular value – especially if we have lot of tables/columns to search. Generally to implement this search functionality we use a LIKE predicate with wildcards. And when we do so, there is a problem –
A LIKE predicate with a leading ‘%’ wildcard can lead to non-usage of suitable indexes, resulting in performance degradation. Even if you have an indexed column, you are most likely going to end up with a table scan if you use a WHERE clause that contains a LIKE predicate such as ‘%Search String’. That’s one of the reasons why we should always try to be careful when using this operation.
By using SQL Express Profiler, I captured the trace containing all the SQL queries fired against the database, when I search for a string(“Alabama”) using ‘ApexSQL Search’ tool. Looking at the SQL queries, we see that it uses LIKE predicate with both leading and trailing wildcard – probably the most easiest way to implement this search functionality.
One thing which I did not like about the above SQL query was that my search criteria was a string(“Alabama”), but it did scan all the columns for the search string – including columns with numeric and datetime datatypes.
However I noticed that the tool, out of the box, provides multiple filters to allow reducing the search scope —
If you are searching for a string value, you can uncheck the checkboxes to exclude ‘Numeric columns’ and ‘Date columns’. Since I was searching for the text – ‘Alabama’, I chose to just search the ‘Text type columns’ —
Capturing the trace again using SQL Express Profiler, we see that now it just searches the ‘Text type columns’ – thus reducing the search scope for an optimized performance —
Conclusion –
Irrespective of whether you are writing a T-SQL query to search for data in SQL tables or are leveraging some optimized tool set which provides the same functionality, be aware that it will be a resource intensive operation. From a performance standpoint, ideally we should be avoiding usage of LIKE predicate with a leading wildcard.
If you are not an experienced T-SQL developer or do not have complete knowledge about database objects, then you should avoid writing/using a T-SQL query for this operation – because a wrongly written query might cause more harm on the database performance. Using an add-in like ApexSQL Search makes your job easier – since it is already tested for optimal performance. But I would highly recommend to use the ‘filters’ available in the toolset to narrow your search scope.
Do you use any other free SSMS add-in to boost your productivity in the IDE?
Please do share the same in the comments section below. #sqlfamily
Related Posts –
Searching Data in SQL Tables in a Single Click
Categories: SQL Server, SQL Server 2016, SQL Tips
Searching made easier with SSMS 2016 
samirbehara 
I still stick to the coment I made to the initial post.
The comment was probably not clear enough.
The main reason u are using this tool is to help you optimise your work in a scenario where the business user ask you to replace all the text showing as xxxxxxx into the frontend website to show as yyyyyy.
I said that my aproach would be to ask the business user show me all the places in the website where he spoted xxxxxxxx and only then I would use SQL Profiler to see exactly what query is sent to SQL Server (if any) to retrieve that xxxxxxxx value for each and every scenario. This will either pin point the value inside the DB or surface the fact that DB is not even the source of the xxxxxxxx value. In either case you are in a way better situation than using this tool. Imagine this : u think the xxxxxxx value resides in the database uuuuuu which is “only” 300 TB. U start a “well configured” search of text columns using your tool and after 3 days the tool reports he found nothing. Your conclusion would be that the xxxxxxx value comes from outside the DB into the website right ? Well, what if the xxxxxxx value comes from a 500 TB bbbbbbbbb database upon making a join with the uuuuuuuuu database via a linked server ? You will never find it and will consume alot of your prescious time asking yourself sanity check questions (am I in a dream, is this real, etc). With the Profiler trace you will find the BMF Join in just minutes and you will discover application logic that you never suspected.
LikeLike