7

In order to troubleshoot a problem, I have a one-time question as to whether a specific varchar(max) field contains non-printing ASCII characters (other than white-space). The following is my straightforward idea about how to determine if there are such characters stored in our production database.

SELECT TOP 10 [CaseNoteId]
      ,[CaseId]
      ,[CaseNote]
  FROM [DB].[XY].[ReferralCaseNotes]
  WHERE CaseNote LIKE ('%[' + CHAR(1) + '-' + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + '-' + CHAR(31) + CHAR(127) + ']%')

My hesitancy to actually run this stems from using wildcards in the LIKE pattern, that there are over a million records in the table in question, the lack of a full-text index on this column, and that this will likely be an exhaustive search because we do not believe that any such characters exist.

I am a neophyte. How can I estimate whether running this query will be a significant load on our production system? Also, is there a better way to get at the same information?

Possible Improvements:

  1. I'm not worried about data changing while my query runs. Can I change this query to look at a few rows at a time in a way that is beneficial?
  2. Can I set this query to somehow be a background operation that doesn't get in the way of any other queries?
  3. Can I run it for a limited time and determine what percentage of the table was searched, so that I can estimate the time required for a full search?
  4. Would WITH(READPAST) improve my performance?

Why?

The database in question involves sensitive data, the government, and security folks making rules. Restoring a backup to a different server makes a ton of sense, but would cost the taxpayer several orders of magnitude more than makes any sense.

If the answer is, "Don't worry, you're just doing a SELECT," then I say, "Great!"

migrated from stackoverflow.com Apr 30 '14 at 19:59

This question came from our site for professional and enthusiast programmers.

  • 3
    First thing, dont run it on your production system! Take last nights back up, restore it to your dev environment and run it there. If you dont have those things, you probably deserve the trouble. – paqogomez Apr 30 '14 at 16:32
  • The query is unlikely to cause any problems since it's just a select statement. If you're really concerned, you could restore a backup of the database on a test server and just run the query against that? – jrhutch Apr 30 '14 at 16:32
  • Using 'top' does not limit you to search only 10 rows. You still do a whole table search. I suggest you do this kind of search on backup server. (You obviously have backup server,right?). – vasin1987 Apr 30 '14 at 16:33
  • 1
    Knowing that there is over 1 million rows doesn't really help. Each varchar(max) value can be anything up to 2 gb so the impact could be anything from trivial to scanning 2 petabytes of data. – Martin Smith Apr 30 '14 at 21:21
  • 1
    There's no possibility that that much data could be in memory, so all reads will go to disk. Now, if it's just 1 million rows with 2-3 characters, that's a short scan. If it's 2 petabytes, like @MartinSmith said, that's gonna be a long day :-). That's why an answer can be given only when you know the real size of that table (because you will get full table scan) - sp_spaceused will give you the answer. – Marian May 1 '14 at 7:01
4
  1. If snapshot isolation is enabled you will not have any blocking issues. If not, you should probably run the query under READ COMMITTED or even READ UNCOMMITTED. It is a common myth that a READ COMMITTED scan locks the table.
  2. You can use Resource Governor for this. Or use a MAXDOP 1 hint. Controlling load of bulk operations is very hard with SQL Server. Depending on the situation you might be 100% fine leaving this running all day, or you might induce timeouts in other parts of the workload. It is not unreasonable to run the query for 10s and cancel it. Then determine whether the application workload was impacted or not.
  3. I like to do progress estimation by dividing the table size (in MB) by the observed disk read rate (in MB/sec). This gives an estimation for the total scan time.

Fulltext search cannot help you because it works on a per-word basis. You'd need to plug in a custom stemmer that knows how to split special characters. Unrealistic. Your query is fine.

0

If you're worried about the performance of your query, you can take full advantage SQL Server's built in Query Execution Plan tools which should tell you how much effort the query is expected to take and what parts of it will be the biggest drag on performance so you can fine tune it later.

You could try several scenarios of how you think the query would work best along with the code you posted here, then time the results and view the execution plans for each. This way you'll know the benefits and trade-offs of each query and can figure things out accordingly.

P.S.: Since this would be a production database, I would really advise to put this data in a testing environment which as as similar to production as possible since having a much less powerful server or less resources dedicate to your MSSQL instance could give you the wrong idea about expected performance time and lead to a lot of effort in optimizing something beyond the point of diminishing returns when it hits production.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.