SMO and Adhoc Queries

Start SQL Profiler, chose SQL:BatchCompleted event only and set filter on TextData to ‘%product%’. In SSMS, go to AdventureWorks database, right click Production.Product table–>Script Table as –>Create to–>Clipboard. Go back SQL profiler, you will find 23 adhoc calls sent to database. Some queries are pretty lengthy and total bytes sent via wire is about 15KB. If you are scripting a database with hundreds of tables, you can imaging the load it adds to the network and the server.
Besides, sine they are adhoc queries, SQL Server generates lots of adhoc plans and caches them but cannot be reused.
The following query lists the adhoc plans generated for the scripting query:

SELECT * FROM sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t
where p.objtype=’adhoc’
 and t.text like ‘%product%’
 and t.text not like ‘%dm_exec_cached_plans%’
 
The total cach memory used:
SELECT sum(size_in_bytes) FROM sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t
where p.objtype=’adhoc’
 and t.text like ‘%product%’
 and t.text not like ‘%dm_exec_cached_plans%’
 
It gives 4038656Bytes (about 4 MB)
It makes no sense to send large amount queries over the wire, especially when scripting morte than one tables sending the similar queries again and again. Create stored procedures and call them from smo makes more sense to me. It saves network bandth, makes reusable cach, and the server is more responsive during the scripting time.
 
Due to above mentioned reason, I made the suggestion on sql server connect:
 
Hopefully it can catch someone’s eyes in MS.
 
 
Advertisements
This entry was posted in SQL Server 2005 SMO. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s