SQL Parse??

Question

Post reply

SQL Parse??

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
October 19, 2012 at 9:25 am

#265950

Hey everyone,
Trying to find a bit of help with a project I am working on.
Basically, we are trying to take a normal ad-hoc query statement:
select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = 'abc'
and t2.colc = 12345
and replace it with something that we could actual review:
select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = @var and t2.colc = @var
So we are pulling a trace an the first statement is what would get shown in the TextData of the trace. We want to be able to store off the trace into a table, but store if off like the 2nd statement so we can group all of them together.
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
Thanks,
Fraggle

Viewing 9 posts - 1 through 8 (of 8 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

http://www.scalesql.com/cleartrace/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 2

Actually parsing SQL is quite a simple thing, once you find the right .Net class

Here is one example of what you can do ..

http://tsqlsmells.codeplex.com/

Should be a relatively simple thing to parse the SQL and replace the values with @Var1 @Var2 etc.

Clear Sky SQL
My Blog[/url]

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 3

Yea, I have looked at this. However, it aggregates everything and within the system I am trying to do, add's a lot of unnecessary overhead. I am already putting the data into tables in an aggregation. I just need the parsing part.

Unless you are telling me there is a way to just do the parsing, which I haven't seen.

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

Sadly I am only just now learning OO and C#. And all of my developers are pretty busy for the next few months on a major project. So much so, that I have been told by the development manager that they don't have time for this until March, unless by chance we get the project done early (yeah, when was the last time that happened!).

Any other thoughts?

Thanks,

Fraggle

Lowell SSC Guru Points: 323504 More actions · Answer 5

i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;

using that, you can tokenize any SQL statement.

i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;

you can do it in a programming language, and there's a brief example on an SSC blog here:

http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/

here's the master list of tokens that can appear in a given SQL statement:

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125521 More actions · Answer 6

Fraggle-805517 (10/19/2012)
...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...

SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.

http://msdn.microsoft.com/en-us/library/ms189741.aspx

For example:

SELECT TOP 5 query_stats.query_hash,

SUM(execution_count) Sum_Execution_Count,

SUM(query_stats.total_worker_time)

/ SUM(query_stats.execution_count) AS Avg_CPU_Time,

max(total_logical_reads) Max_Reads,

MIN(query_stats.statement_text) AS Statement_Text

FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY Avg_CPU_Time desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 7

Eric M Russell (10/23/2012)
Fraggle-805517 (10/19/2012)
...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...
SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.
http://msdn.microsoft.com/en-us/library/ms189741.aspx
For example:
SELECT TOP 5 query_stats.query_hash,
SUM(execution_count) Sum_Execution_Count,
SUM(query_stats.total_worker_time)
/ SUM(query_stats.execution_count) AS Avg_CPU_Time,
max(total_logical_reads) Max_Reads,
MIN(query_stats.statement_text) AS Statement_Text
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY Avg_CPU_Time desc;

This does get me some stuff, but not everything we will be doing with it. Beside the fact that I can't pull Avg, Stdev, and Median, I am also using this process and an auditing system to see what is being called from specific users when the login to production.

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 8

Lowell (10/22/2012)
i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;
using that, you can tokenize any SQL statement.
i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;
you can do it in a programming language, and there's a brief example on an SSC blog here:
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/
here's the master list of tokens that can appear in a given SQL statement:
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx

Thanks, I will take a look at this.

Fraggle