Contents tagged with MS SQL
today I want to write some notes about hierarchy research in ms sql database.
Imagine you have following structure in your table
id | rootID | some additional fields
1 | 1 | ----
2 | 1 | ----
3 | 2 | ----
4 | 2 | ----
5 | 2 | ----
and image that you need to get info like who is below 1.
for this purpose following sql can be useful:
-- wrap up tree selection into tvf
create function HierarchyFunction(@ … more
some sql notes:
dbcc freeproccache; this one will clean plan of cached of sql.
Another note of how to find Sql code and plan by id from dm_exec_cached_plans
create function SqlAndPlan(@handle varbinary(max))
return select sql.text, cp.usecounts,cp.cacheobjtype,
sys.dm_exec_sql_text(@handle) as sql cross join
sys.dm_exec_query_plan(@handle) as qp
join sys.dm_exec_cached_plans as cp
on cp.plan_handle = @handle;
then you can use it in the following way:
select * from SqlAndPlan(0x06650D007852A00B40613C82000000000000000000000032)
And in order to see sql and plan simultanously you can use … more
some MS SQL internals.
Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans.
For example like this:
select * from sys.dm_exec_cached_plans
this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?
Take look at the screenshot :
the area of interest for us is column plan_handle.
You can use it for another sql request. Like this:
select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);
if you click at column with xml you'll see the following picture:
How to interpret those … more
Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.
For example like this:
exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND … more