T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?

andyleonard — Mon, 19 Jul 2010 11:00:00 GMT

Introduction I've been meaning to blog about this for a while and a question from a team member prompted this post (thanks Allan!) SSMS Scripting Options In SSMS you can script a stored procedure - right-click a stored procedure, hover over Script Stored...(read more)

New Features Announced In SQL Server 2008

andyleonard — Thu, 13 Mar 2008 10:23:00 GMT

Buck Woody, Microsoft SQL Server Program Manager, recently posted a list of enhancements in the works for SQL Server 2008 RTM in the SQL Server MVP newsgroup. The usual suspects were included in the list: Policy-Based Management (formerly DMF), Resource Governor, Multi-Server Query, PowerShell, and IntelliSense - but there are also some interesting additions that I'd not heard about until reading his post.

The big addition: Activity Monitor, described in the post thus:

The new Activity Monitor was written from the ground up with the perspective of the DBA needing to chase down a performance problem in real time. Modeled after the new Windows Resource Monitor, DBAs can quickly see the active sessions, wait states, file I/O, and long running queries in a command console like UI.

I'm excited! I currently lug around a collection of scripts (... that I keep promising to add to an application one day) to check hither and yon for performance gotchas.

Another addition: Database Diagram / Table Designer Safety Additions which add safety checks "for operations that that would drop an object or cause data loss."

You will also be able to launch Profiler "from a Query Editor Results window to the SPID of the query." This sounds very interesting. If I'm reading this right I'll be able to execute sp_who2, obtain the SPID of a long-running query, right-click (or something) and start a Profiler trace pre-configured to capture activity of this SPID. You can also launch the SPID-centric Profiler from Activity Monitor. I will be using that a lot!

New right-click options in the Query Results Window will allow users to "select a range of rows in the Results Grid and copy the headings as well. Also, you can select individual cells and copy them with headers if you wish." This sounds like the copy functionality we have in SSIS Grid Data Viewers. I can tell you from expereience this is handy feature. It's very useful to be able to copy a few multi-selected rows - including row headers (especially when you're working with a couple hundred columns!) - and paste them into Excel for analysis.

A new "Info Bar" provides pre-validated navigation cues in many screens.

The Open Table feature limits the number of rows returned. Has this ever happened to you? You right-click a table in SSMS and select Open Table only to hog server resources just so you could take a peek at the data? It's happened to me... (Thank you, thank you, thank you!)

There are also a few changes to ShowPlan - mostly related to XML / graphics interchange. I remember seeing some of this in early SQL Server 2005 pre-releases but I believe it was cut from the 2005 RTM. Nice to see these options - they're great for documenting issues.

Performance Studio will contain a host of performance tuning reports - another exciting feature! The whole Data Collection database has me stoked - have you seen this feature in CTP6? It's pretty cool.

Last but not least, the team has decided to include - drum roll please - the T-SQL debugger in SSMS!

I can hardly wait to see these in action, and it's very cool of the SQL Server Team to give us visibility into these planned features along with permission to blog about them (thanks Buck!).

:{> Andy

Andy Leonard : SSMS

T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?

New Features Announced In SQL Server 2008