Search results matching tags 'SSMS' and 'CTEs'

Connect Digest : 2011-08-08

AaronBertrand — Mon, 08 Aug 2011 12:30:00 GMT

This week I reported a few bugs - one with sp_helptext, one with Activity Monitor in Management Studio, and one in the new sys.dm_server_services DMV.

#683234 : sp_helptext treats non-system objects as system objects
For this one there is at least one weird case where running sp_helptext against on object prefixed with dbo ignores the dbo and tries to retrieve text for its sys counterpart (in this case, you can't run sp_helptext against a procedure you can create and execute called dbo.sp_hexadecimal; instead, you get error 15197, because the system thinks your object is a system object). I blogged about this situation over the weekend as well.

#683031 : SSMS : Activity Monitor, execution plan implies that .sqlplan file is saved
In testing the new Management Studio add-in we created for SQL Sentry Plan Explorer, it was discovered that the file handle SSMS associates with execution plans launched from within Activity Monitor is not valid - it says the file is in %USERPROFILE%\Documents, but the file is not actually there until you manually save it.

#683275 : sys.dm_server_services has two rows for SQL Server Agent in CTP3
This DMV is new in Denali and 2008 R2 SP1 (though the bug I've reported seems to only affect Denali CTP3). I had previously reported that last_startup_time is always NULL for SQL Server Agent; that bug does affect both 2008 R2 and Denali.

Then I have five others I'd like to highlight, only because they crossed my path for one reason or another. In no particular order:

#668871 : TSQL - Stop the STUFF function treating NULL as the empty string

While they've confirmed that they are not going to fix STUFF, the key here is to put in some comments suggesting why the ANSI-standard OVERLAY function would be a useful addition to T-SQL. (Or maybe create a new suggestion.)

#256665 : Extend OBJECT_DEFINITION to include more objects

I would love to be able to call a function that gives me the script for that object - be it CREATE TABLE, CREATE VIEW, CREATE SYNONYM, what have you. Currently this function only works for "script" objects, such as functions, procedures and views. If you've ever tried to manually/programmatically re-create the type of script Management Studio spits out for a table, you should have no problems voting this one up.

#640236 : The optimizer should considering materialising results of CTEs

Erland filed this one that properly suggests that a CTE *should* be able to be materialized by the optimizer only once, instead of up to (and sometimes more than) as many times as it is referenced in the rest of the query. For some insight into how much performance can be affected when the CTE is materialized only once, see this blog post from SQLCAT. For more viewpoints on the problem, see Connect #218968, Connect #377976 and Connect #483181.

#640863 : Please allow creation of temporary views

I don't think I would have very many uses for this one, but I can see the use case that is being presented. The workaround suggested by Microsoft (to create a view in tempdb, then drop it) is no more useful than creating a permanent view in the current database and then dropping it - the problem is that only one user could do this at a time.

#127219 : CREATE OR REPLACE

I've probably talked about this one before. Because of the complications with checking for existence of a stored procedure, for example, and the fact that a CREATE or ALTER PROCEDURE command must be the only statement in a batch, it is very difficult to determine the correct action without building the procedure body in dynamic SQL. On top of those, if you just blindly DROP and CREATE each time, you lose permissions, dependencies, etc. So the ability to say CREATE OR REPLACE or CREATE OR ALTER instead of performing these checks and executing one script or the other, would be welcome indeed.

Connect Digest : 2011-05-23

AaronBertrand — Mon, 23 May 2011 12:28:00 GMT

Next week I'll be up in Alaska for SQL Cruise, so I'll be skipping at least one digest. This week I gathered a few engine-related issues, two involving what the engine does while an index is being created. The most important one, though, at least IMHO, is the first one listed, involving a hashing algorithm fix that is in SQL Server 2008 R2 - but with no plans to fix it for SQL Server 2008. I wonder if someone should file a separate, 2008-specific bug?

#458091 : Change Lock Resource Hashing Algorithm to Reduce Likelihood of Collisions
#581056 : Indexed view creation causes queries on the base table to block
#478568 : SSMS Gets Blocked and Locks Up During Index Creation
#125549 : Poor execution plan with CTE

And finally, this issue ("high degrees of parallelism cause incorrect results to be observed more frequently" according to KB #981502) is one that has been fixed in SP1 for SQL Server 2008 R2, but it requires a trace flag (in Denali, it will be on by default, so you won't need a trace flag). I'm not telling you about this so you'll go and vote for this issue, but rather just to make you aware of it. The KB article states that the fix is in various CUs for 2005, 2008 and 2008 R2, but as the Connect item implies, the fix may not actually work in all cases, and requires the trace flag for others.

#642790 : Parallel query plan returns different results every time that you run the query

Connect Digest : 2010-02-16

AaronBertrand — Tue, 16 Feb 2010 04:52:00 GMT

Simon Sabin has asked for better usability in the SQL Server Agent log viewer: specifically, to allow you to get to job / step properties directly from the history viewer.

#531889 : Allow access to job details from the log file viewer to improve manageability

Guru Itzik Ben-Gan asks for a more powerful QUALIFY() option to allow filtering on the same kind of query elements that you can currently achieve with ROW_NUMBER(). This one already has over 80 votes!

#532474 : QUALIFY - Request for a New Filtering Clause

Chris Howarth points out even more problems with the custom connection coloring feature in SSMS (all the more reason to use Mladen's SSMSToolsPack).

#533667 : SSMS Connection Colouring Not Implemented Sensibly

MauriD asks for the ability to include a NOEXPAND hint for CTEs.

#533766 : Make NOEXPAND hint usable also with CTE

And last but not least, user "7gartner" asks for the ability to store tempdb on local storage in a cluster.

#532759 : Support local disk location for TempDB in failover cluster installation

When I read the title of this item, I thought, "oh, he must expect that someone with an active batch that is using a #temp table, might be able to continue working in the event of a failover." Which doesn't make sense because their connection would be severed anyway. But when I investigated the body, the want is much more simple than that: allow a system to avoid having to use shared storage for TempDB. This means not using valuable SAN space and using local hard disks, possibly SSDs. A light bulb went on when I saw that, because I know that outfitting SAN-based SSDs at reasonable capacity can get quite expensive, but an SSD dumped into a server - at a capacity to handle the typical sizing requirements of TempDB disks - would be a much more affordable option. (Paul Nielsen just wrote about using tempdb in RAM, and the implication on using this in a cluster resonates again.)