Splitting adhoc queries into a data structure

Question

Splitting adhoc queries into a data structure

JKSQL

SSCrazy Eights

Points: 8481
More actions
June 28, 2021 at 9:24 pm

#3901388

Issue - Current adhoc SQL can be generated by our reporting module that is difficult to maintain much less decipher. The TSQL is huge and almost un-manageable. This makes it extremely hard to figure out what to do since we can have hundreds of queries
Partial solution - instead of using profiler we have a tool that can run these reports in a loop and output the TSQL in .txt making it easier to debug where potential issues are. This is still extremely tiring trying to figure out which ones are duplicates and if we want to remove a field it is hard to know if it is used in a report.
Full solution - Break apart the sql string storing parts of the SQL into specific tables for easy query's. This will help the end techs diagnose things without escalating issues to higher DBA's.
Split strings and functions like this can be used, but I need something more like what clear trace used to be doing where it would substitute constants allowing us to find DML statements easily.
Any ideas or has anyone seen tools that can break apart queries

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

I don't know of anyone that breaks apart queries into their component parts for troubleshooting. I'd look to two things to help with this. Instead of capturing batches using Extended Events, capture individual query statements. You'll better be able to see what's going on. Second, take advantage of the query hash value. That can help you eliminate most duplicate queries right away. Good luck on the breaking apart process.

"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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

Just wondering if it is the column names and the tables they come from you want to find out if you could use a sql parser on the queries you have captured to get that.

If you've got Query Store enabled on the database that might also be a good starting point for you to identify the queries.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

Thanks for the responses. I already have a tool to extract the TSQL. I have the statement in a table as a NVarchar(Max). I was thinking of searching for commas and DML (FROM, JOIN, WHERE) kind of stuff. I guess I will start there.

ZZartin SSC-Dedicated Points: 30919 More actions · Answer 4

When you say adhob SQL do you mean that users can just free form write their own SQL to put into the reports or that this is SQL being generated by the reporting tool?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 5

It is not free form. It is similar to business objects that are simplified for a user to select. Then the code generates the links and the select to get at the data. Think of it like this:

Customer wants custom reports. Instead of us making the report we give them flexible reporting module to design there own queries. The designer enables things like dates, status, employees, cost, etc to be dragged on a report. Then they supply the criteria and the SQL is generated and run.

We can mine the TSQL generated and potentially break it up so we know what is being selected and how it is going about getting at the data. This task is to start breaking down the query so we know what is contained the query like Views, fields, where clauses.