The theme for this month is Prox ‘n’ Funx (aka Procedures and Functions)… and I’m going to talk about a procedure that I wrote to help me deal with the lack of procedures.
You probably had to re-read that latest sentence, didn’t you? Well, this whole blog post might turn your brain inside-out a bit, so you might want to read it slowly or be prepared to re-read it after you finish it. My apologies in advance.
Here’s the situation… purely hypothetical, you understand… (wink, wink).
Imagine yourself faced with a SQL Server Database that is just a big dumb file cabinet. All it holds is data, and there isn’t a single procedure or function to be found… every single query that goes to the system is an ad-hoc query constructed within an enormous .NET application.
For example, a typical query in the C# code may be put together like this:
string SQL = "";Never mind the thoughts that are going through your head regarding readability. Never mind the maintenance nightmare. Never mind the potential for SQL Injection. Never mind the…
SQL += " SELECT soh.SalesOrderNumber ";
SQL += " ,soh.ShipDate ";
SQL += " ,soh.CustomerID ";
SQL += " ,s.Name ";
SQL += " ,soh.TotalDue ";
SQL += " FROM Sales.SalesOrderHeader soh ";
SQL += " JOIN Person.Address a ON soh.ShipToAddressID=a.AddressID ";
SQL += " JOIN Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID ";
SQL += " JOIN Sales.Store s ON soh.CustomerID=s.CustomerID ";
SQL += " WHERE soh.SalesOrderID IN ";
SQL += " (SELECT SalesOrderID ";
SQL += " FROM Sales.SalesOrderDetail ";
SQL += " WHERE ProductID=" + SQLUtils.SQLValue(ProdID) + ") ";
SQL += " AND soh.ShipDate>=" + SQLUtils.SQLValue(FromDate);
SQL += " AND soh.ShipDate<" + SQLUtils.SQLValue(ToDate);
SQL += " AND a.City=" + SQLUtils.SQLValue(CityName);
SQL += " AND sp.Name=" + SQLUtils.SQLValue(StateProvinceName);
DataTable dtResult = SQLUtils.ExecSQLQuery(SQL)
Oh never mind.
So hundreds of thousands of ad-hoc queries get sent to the system all day long.
My job? Make things go faster.
Okay, fine. However, there’s one other wrinkle. This particular database operates under Forced Parameterization. This may reduce the frequency of query compilations, but it makes my job a little more complicated.
Let me illustrate… Let’s set the AdventureWorks database to use Forced Parameterization:
alter database AdventureWorks set parameterization forcedWith that in place, every ad-hoc query sent to the system is parameterized… In other words, any literal within the ad-hoc query is converted into a parameter and the query as a whole is parameterized so that its plan can be reused by any subsequent ad-hoc query that has the same “code shape”.
Let’s execute the following query in AdventureWorks:
SELECT soh.SalesOrderNumber(Those of you who read my blog regularly may be shocked by my use of UPPER CASE keywords… I never do that… But I’m doing it here to illustrate a point).
,soh.ShipDate
,soh.CustomerID
,s.Name
,soh.TotalDue
FROM Sales.SalesOrderHeader soh
JOIN Person.Address a ON soh.ShipToAddressID=a.AddressID
JOIN Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID
JOIN Sales.Store s ON soh.CustomerID=s.CustomerID
WHERE soh.SalesOrderID IN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID=897)
AND soh.ShipDate>='20030801'
AND soh.ShipDate<'20030901'
AND a.City='Milsons Point'
AND sp.Name='New South Wales'
As you may know, there is a DMV called sys.dm_exec_cached_plans, which contains information about all the query plans that have been cached, along with a plan_handle column that you can use to acquire the actual text of the query that created the plan. There’s also a DMV called sys.dm_exec_query_stats, which contains performance statistics information for cached plans, but its plan_handle (or sql_handle) column points to a different kind of text when you’re dealing with Forced Parameterization.
Here is a simplified version of a Performance Statistics Query that I run that uses sys.dm_exec_query_stats to acquire performance information so that I can find plans that perform a lot of reads (the ones with the most reads are at the top).:
select Code=(select [processing-instruction(q)]=':'+nchar(13)+qt.text+nchar(13)Here’s what the result looks like in SSMS:
for xml path(''),type)
,qp.Query_Plan
,[#Reads]=qs.total_logical_reads
,[#Execs]=qs.execution_count
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(plan_handle) qt
cross apply sys.dm_exec_query_plan(plan_handle) qp
where qt.text not like '%sys.dm\_%' escape '\'
order by [#Reads] desc
In case you’re wondering about that “processing-instruction” and XML gibberish for the generation of the first column, you can read more about that here. But, in short, you can see that it creates a hyperlink to the query text. When I click on that link, I can see that the text looks like this:
(@0 int,@1 varchar(8000),@2 varchar(8000),
@3 varchar(8000),@4 varchar(8000))select soh . SalesOrderNumber ,
soh . ShipDate , soh . CustomerID , s . Name , soh . TotalDue
from Sales . SalesOrderHeader soh join Person . Address a on
soh . ShipToAddressID = a . AddressID join Person . StateProvince sp
on a . StateProvinceID = sp . StateProvinceID join Sales . Store s
on soh . CustomerID = s . CustomerID where soh . SalesOrderID in
( select SalesOrderID from Sales . SalesOrderDetail where ProductID
= @0 ) and soh . ShipDate > = @1 and soh . ShipDate < @2 and
a . City = @3 and sp . Name = @4
I have split it into multiple lines for this blog post… In reality it’s one looooonnnng string. But you can see that this is NOT the actual text of the query that I executed. In parameterizing this query, SQL converted all my UPPER CASE keywords into lower case, and it got rid of any extraneous white space (multiple spaces, tabs, carriage returns, line feeds), and it inserted a single space between all commas and periods/full-stops and operators.Now when another query comes along that is exactly like the first query (except querying for different values)…
SELECT soh.SalesOrderNumber…and we once again execute our Performance Statistics Query to look at the plan cache...
,soh.ShipDate
,soh.CustomerID
,s.Name
,soh.TotalDue
FROM Sales.SalesOrderHeader soh
JOIN Person.Address a ON soh.ShipToAddressID=a.AddressID
JOIN Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID
JOIN Sales.Store s ON soh.CustomerID=s.CustomerID
WHERE soh.SalesOrderID IN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID=870)
AND soh.ShipDate>='20030101'
AND soh.ShipDate<'20040101'
AND a.City='London'
AND sp.Name='England'
…we see that the same parameterized query has been reused (note the #Execs is equal to 2).
(Those of you with a quick eye may have also noticed that the #Reads skyrocketed… I’ll talk about that in a later post).
So my problem is this: In order to improve the performance of the application, I can use my Performance Statistics Query in order to find those plans that exhibited the most reads and see if they can be tuned via either the addition of an index or via a recommendation of rewriting the query a different way.
But unfortunately all I get is that parameterized version of the query text. I have no indication what the ACTUAL ad-hoc queries were that were sent to the system. Yes, I can see the text of the ad-hoc queries via the sys.dm_exec_cached_plans DMV, but how do I find the exact ones that have to do with this parameterized query? It’s like looking for a needle in a haystack.
So I wrote a procedure to do that for me. It essentially takes the parameterized query text and creates a LIKE pattern out of it so that I can find its original ad-hoc code via the plan_handle of the sys.dm_exec_cached_plans DMV.
I create this procedure in a database called DBATools (which houses other utilities and data that I use for monitoring the server). It starts like so, receiving the text of the parameterized query code and putting it into a local @Text variable:
use DBAToolsIts first step is to get rid of the parameter declarations at the beginning of the query. Well, luckily, all these parameterized queries are of the pattern “(@0 … )xxxxx”, where “xxxxx” is one of the following: SELECT or UPDATE or INSERT or DELETE or MERGE or WITH (in the case of a CTE). So let’s use that knowledge to strip off the parameter list:
go
create procedure GetAdhocQueries
@ParameterizedQuery nvarchar(max)
as
declare @Text nvarchar(max)
set @Text=@ParameterizedQuery
/*Get rid of parameter list*/So that makes our @Text variable looks like the following, stripped of its parameter list (again, I’m artificially word-wrapping the text here… it’s just one loonng string):
select @Text=substring(@Text,isnull(min(Pos),0)+1,len(@Text))
from (select charindex(N')select ',@Text)
union all
select charindex(N')insert ',@Text)
union all
select charindex(N')update ',@Text)
union all
select charindex(N')delete ',@Text)
union all
select charindex(N')merge ',@Text)
union all
select charindex(N')with ',@Text)) x(Pos)
where Pos>0
select soh . SalesOrderNumber , soh . ShipDate ,
soh . CustomerID , s . Name , soh . TotalDue
from Sales . SalesOrderHeader soh join Person . Address a on
soh . ShipToAddressID = a . AddressID join Person . StateProvince sp
on a . StateProvinceID = sp . StateProvinceID join Sales . Store s
on soh . CustomerID = s . CustomerID where soh . SalesOrderID in
( select SalesOrderID from Sales . SalesOrderDetail where ProductID
= @0 ) and soh . ShipDate > = @1 and soh . ShipDate < @2 and
a . City = @3 and sp . Name = @4
Next I need to find all the parameters in the text. They are all surrounded with a single space, so they are easy to find because they have the pattern “ @[0-9] ” (for a single digit parameter) or “ @[0-9][0-9] ” (for a two-digit parameter), etc. So the following will find all 4-digit, 3-digit, 2-digit, and 1-digit parameters in the text and substitute them with a single percent sign (%):/*Substitute all parameters (i.e. @0 or @1 or ...) with Percents (%)*/And now our @Text variable looks like this:
declare @NumDigs tinyint
,@Pos int
set @NumDigs=5
while @NumDigs>1
begin
set @NumDigs=@NumDigs-1
while 1=1
begin --Continue substituting until there are no more
set @Pos=patindex(N'% @'+replicate(N'[0-9]',@NumDigs)+N' %',@Text)
if @Pos=0 break --No more... we're done with the loop
set @Text=stuff(@Text,@Pos,3+@NumDigs,N' % ')
end
end
select soh . SalesOrderNumber , soh . ShipDate ,
soh . CustomerID , s . Name , soh . TotalDue
from Sales . SalesOrderHeader soh join Person . Address a on
soh . ShipToAddressID = a . AddressID join Person . StateProvince sp
on a . StateProvinceID = sp . StateProvinceID join Sales . Store s
on soh . CustomerID = s . CustomerID where soh . SalesOrderID in
( select SalesOrderID from Sales . SalesOrderDetail where ProductID
= % ) and soh . ShipDate > = % and soh . ShipDate < % and
a . City = % and sp . Name = %
Now, I need to take care of those spaces that had been inserted by the optimizer between every operator and keyword in the query. So I replace all those spaces with percent signs. At the same time, I also replace any normal pattern-matching characters (like a carat or left square bracket or right square bracket) with percent signs. I don’t want them screwing up my final query into the plan cache. Finally, I place a percent sign at the beginning and the end of the text:/*Replace all spaces and pattern characters (i.e. "[", "]", "^") with Percents (%)*/At this point our @Text variable looks like this:
/*Also place a Percent at the beginning and the end*/
set @Text=N'%'
+replace(replace(replace(replace(@Text
,N' '
,N'%')
,N'['
,N'%')
,N']'
,N'%')
,N'^'
,N'%')
+N'%'
%select%soh%.%SalesOrderNumber%,%soh%.%ShipDate%,%
soh%.%CustomerID%,%s%.%Name%,%soh%.%TotalDue%
from%Sales%.%SalesOrderHeader%soh%join%Person%.%Address%a%on%
soh%.%ShipToAddressID%=%a%.%AddressID%join%Person%.%StateProvince%sp%
on%a%.%StateProvinceID%=%sp%.%StateProvinceID%join%Sales%.%Store%s%
on%soh%.%CustomerID%=%s%.%CustomerID%where%soh%.%SalesOrderID%in%
(%select%SalesOrderID%from%Sales%.%SalesOrderDetail%where%ProductID%
=%%%)%and%soh%.%ShipDate%>%=%%%and%soh%.%ShipDate%<%%%and%
a%.%City%=%%%and%sp%.%Name%=%%%
Just to be neat and tidy, I get rid of all the multiple percent signs and replace them with a single one:/*Transform all multiple Percents into single ones*/And that results in this:
set @Text=replace(@Text,N'%%%',N'%%')
set @Text=replace(@Text,N'%%',N'%')
%select%soh%.%SalesOrderNumber%,%soh%.%ShipDate%,%
soh%.%CustomerID%,%s%.%Name%,%soh%.%TotalDue%
from%Sales%.%SalesOrderHeader%soh%join%Person%.%Address%a%on%
soh%.%ShipToAddressID%=%a%.%AddressID%join%Person%.%StateProvince%sp%
on%a%.%StateProvinceID%=%sp%.%StateProvinceID%join%Sales%.%Store%s%
on%soh%.%CustomerID%=%s%.%CustomerID%where%soh%.%SalesOrderID%in%
(%select%SalesOrderID%from%Sales%.%SalesOrderDetail%where%ProductID%
=%)%and%soh%.%ShipDate%>%=%and%soh%.%ShipDate%<%and%
a%.%City%=%and%sp%.%Name%=%
Finally, since LIKE patterns are not allowed to be more than 4000 characters, I truncate the @Text variable to that length if needed:/*Truncate to 4000 characters max*/So finally, at this point, we can now find all the ad-hoc queries in the cache whose text is LIKE our @Text variable:
if len(@Text)>4000 set @Text=left(@Text,3999)+N'%'
/*Find our Adhoc queries that match the pattern*/Here is the result:
select Code=(select [processing-instruction(q)]=N':'+nchar(13)+qt.text+nchar(13)
for xml path(''),type)
,DB=db_name(QueryDatabaseID)
,[#Bytes]=cp.size_in_bytes
,cp.usecounts
from sys.dm_exec_cached_plans cp
cross apply (select QueryDatabaseID=convert(int,value)
from sys.dm_exec_plan_attributes(cp.plan_handle)
where attribute='dbid') F_DB
cross apply sys.dm_exec_sql_text(cp.plan_handle) qt
where objtype='Adhoc'
and qt.text like @Text
order by cp.usecounts desc
And when we click on one of the hyperlinks, we can see the original ad-hoc query in all its glory, just as it was originally submitted.
<?q :So, to take advantage of this new procedure, I just incorporate it into my original Performance Statistics Query, like so:
SELECT soh.SalesOrderNumber
,soh.ShipDate
,soh.CustomerID
,s.Name
,soh.TotalDue
FROM Sales.SalesOrderHeader soh
JOIN Person.Address a ON soh.ShipToAddressID=a.AddressID
JOIN Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID
JOIN Sales.Store s ON soh.CustomerID=s.CustomerID
WHERE soh.SalesOrderID IN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID=870)
AND soh.ShipDate>='20030101'
AND soh.ShipDate<'20040101'
AND a.City='London'
AND sp.Name='England'
?>
select Code=(select [processing-instruction(q)]=':'+nchar(13)+qt.text+nchar(13)In short, if the query text (in sys.dm_exec_sql_text) starts with a “(@0 “, I know it’s a parameterized query, so I will have the AdHocStmts column populated with the text to call my GetAdhocQueries procedure. When I execute the above query, I get the following result with the new AdHocStmts column:
for xml path(''),type)
,qp.Query_Plan
,[#Reads]=qs.total_logical_reads
,[#Execs]=qs.execution_count
,AdHocStmts
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(plan_handle) qt
cross apply sys.dm_exec_query_plan(plan_handle) qp
cross apply
(select AdHocStmts=case
when qt.text like '(@0 %'
then N'exec DBATools.dbo.GetAdhocQueries '''
+replace(qt.text,N'''',N'''''')+N' '''
else null
end) F_Adhoc
where qt.text not like '%sys.dm\_%' escape '\'
order by [#Reads] desc
And I can copy/paste the AdHocStmts column into a query window and execute it (again, I only word-wrap it here for clarity… it’s actually a looonng single line of text).
exec DBATools.dbo.GetAdhocQueries '(@0 int,@1 varchar(8000),@2 varchar(8000),And that will find the ad-hoc queries that are associated with that particular parameterized query.
@3 varchar(8000),@4 varchar(8000))select soh . SalesOrderNumber , soh .
ShipDate , soh . CustomerID , s . Name , soh . TotalDue from Sales .
SalesOrderHeader soh join Person . Address a on soh . ShipToAddressID =
a . AddressID join Person . StateProvince sp on a . StateProvinceID =
sp . StateProvinceID join Sales . Store s on soh . CustomerID = s .
CustomerID where soh . SalesOrderID in ( select SalesOrderID from
Sales . SalesOrderDetail where ProductID = @0 ) and soh . ShipDate
> = @1 and soh . ShipDate < @2 and a . City = @3 and sp . Name = @4 '
So this GetAdhocQueries procedure allows me to find the ad-hoc query needles in the plan cache haystack. Now if I only had a way to find those $#&@ queries in the C# code as easily… oh well, that’s somebody else’s problem.
Thanks for going through all the work and posting this. I am messing around with it now.
ReplyDeleteWhile the whole article is brilliant as usual Brad, I am most impressed by [processing-instruction(c)] trick.
ReplyDelete:)
@Steve:
ReplyDeleteThanks for the feedback... hope you find it useful.
@Piotr:
That processing-instruction trick is cool isn't it? I can't tell you how much I use it and how much time it has saved me... It's a life-saver.
Just spent a few minutes to tweak my DDL Audit query so it doesn't escape > and < , thanks :)
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDelete
ReplyDeleteBest CBSE School in Bulandshahr
Best school in bulandshahr
I must thank you for the efforts you’ve put in writing this blog. I am hoping to view the same high-grade blog posts from you later on as well. In fact, your creative writing abilities has inspired me to get my very own blog now ?? Thank you for the auspicious writeup Feel free to visit my website; 토토
ReplyDeleteWhats Happening i am new to this, I stumbled upon this I’ve discovered It absolutely helpful and it has aided me out loads. I am hoping to give a contribution & assist other users like its helped me. Great job. 경마
ReplyDeleteAs a Newbie, I am permanently exploring online for articles that can help me. Thank you 토토사이트
ReplyDeletewebgirls With regards to fighting candidiasis, victims often times have their operate cut out for them. This is because infections can readily turn out to be constant and continuous. With that in mind, on this page, we are going to current a wide range of the best verified candida albicans treatment and reduction tips all around.
ReplyDeletehttps://gameeffect.xyz A lot of people have liked the video game of baseball for several years. You can find fans around the globe, from devoted very little-leaguers to pass away-challenging spectators. This information has tips to confirm how satisfying baseball really is.
ReplyDeletehttps://gamezoom.xyz Getting a exercise routine spouse can drastically enhance your muscle mass-building outcomes. Your spouse can be quite a valuable method to obtain motivation for adhering to your workout period, and driving you to maximize your attempts as you exercise. Having a reputable lover to work through with will also help make you stay secure because you will usually use a spotter.
ReplyDeletehttps://gameboot.xyz You can see them on mags and also on TV, people who appear to be their forearms and hip and legs will explode as his or her muscle tissue are incredibly huge! There is absolutely no require that you can consider your body to that levels in the event you don't want to, since the simple tactics in the following paragraphs will assist you to build muscle mass in a healthier approach.
ReplyDeleteGreat share! Keep posting!
ReplyDeleteجدة
Hi.
ReplyDeleteThank you so much for your efforts for this valuable blog.
Good blog informative for readers such a nice content keep posting thanks for sharing
Here is sharing some Salesforce Lightening information that may be helpful to you.
Salesforce Lightening Training
pg slot ปิ้งยิ่งที่สุดแล้วก็ฝากถอนเงินในแต่ละครั้งได้ไม่มีอย่างน้อย สิทธิพิเศษสำหรับในการวางเดิมพันที่จะสามารถทำให้ทุกๆคนใกล้ PG SLOT เงินจำนวนเป็นอันมาก
ReplyDeleteI found this paragraph at this website.
ReplyDeleteYou could certainly see your skills within the article you write.
ReplyDeleteThe world hopes for even more passionate writers like you.
ReplyDeleteBelow are some webpages really worth checking out
ReplyDeleteHere is a good Weblog You might Come across Fascinating that we Encourage You
ReplyDelete