Search results matching tags 'SQL Server' and 'suggestions'

Connect Digest : 2011-12-20

AaronBertrand — Tue, 20 Dec 2011 16:55:00 GMT

Make SSMS start faster

A couple of years ago, I blogged about some ways to make Management Studio start faster. With the latest builds of SQL Server 2012, it seems to be slower than ever, at least when starting SSMS for the first time after a reboot or install. So I've asked for them to do something in the background on Windows start-up to cover whatever initialization costs have to be paid on first launch. This is not something I expect to happen in this release, and perhaps it will be better by the time RTM comes around. But if you find it slow as well, in addition to voting, please post - in the comments on the Connect item - your machine config and how long it takes between clicking on the shortcut and being able to work. With enough stats in there they can probably extrapolate how much time is being lost waiting for the application to load. :-)

#714652 : SSMS : Background helper / pre-loader

Let me opt out of product updates during setup

I am a big fan of streamlining slipstreaming (say that 5 times fast) - in other words, letting SQL Server setup check for product updates *before* installation, rather than the current manual process of cobbling together a setup package with a service pack and/or a cumulative update. In SQL Server 2012 we finally have this. Unfortunately, it is currently not an option; so, if your machine is not connected to the Internet for whatever reason, it hangs indefinitely on that step, trying desperately to go find those updated files. This needs to be an option so that you can proceed, even if you want to install RTM (e.g. for testing purposes) or you can't get to the Internet (now or ever). Sadly most software companies think that high speed Internet is enjoyed by all, when in fact a lot of folks can't get it or intentionally block their servers from any kind of external access.

#714661 : Scan for Product Updates hangs on isolated system

Add a time limit option to index reorg

Greg Low had an interesting idea to add a time limit to index reorg. Since the most work you'll ever lose upon cancelling is the reorganization of a single page, unlike a rebuild which has to roll back ALL of the work, it is a common practice to do a little bit of reorg at a time. But it is quite tedious to set up some kind of watchdog or to wait and kill the process manually. I can see how the syntax could work - a simple TIME_LIMIT option with the same input format as WAITFOR DELAY. So, for example, if you wanted to allow this reorg to work for up to 45 minutes and then stop:

ALTER INDEX foo 
  ON dbo.bar
  REORGANIZE WITH (TIME_LIMIT = '00:45:00');

The Connect item is currently closed as won't fix, and I think that's partially because it's had only 2 votes in over a year. Please vote and add a comment indicating why this could be useful in your environment:

#611158 : Index Reorganize time limit

Gosh, I wish I could re-use that CTE

Several folks have filed suggestions to make it easier to work with the same query multiple times. Two of the popular ideas are temporary views and module-level table expressions. The current workaround of dumping data into a #temp table has significant overhead, and can still lead to errors if the same joins and where clauses need to be repeated over and over again. I like Erland Sommarskog's module-level table expressions idea best, but temporary views have a strong use case as well. I'm hoping to see some more comments and use cases on these items so that Microsoft can evaluate

#343067 : Module-level table expressions
#640863 : Please allow creation of temporary views
#714617 : [T-SQL] : Please provide temporary views

Truncate a table with foreign keys

We all know that you can't truncate a table that has foreign keys, but why should that be the case if all the referencing tables are empty (or have nullable referencing columns that are all NULL)? They've closed this one as won't fix, but if you can see the value in this for your environment, please add a comment indicating your use case, as they seem more than willing to revisit it...

#312074 : Permit TRUNCATE TABLE when referencing tables are empty

Want your bug fixed? File a good bug!

AaronBertrand — Tue, 20 Sep 2011 14:45:00 GMT

In some circles I'm known as "the Connect guy" because I've filed a heck of a lot of suggestions and bugs that I've envisioned or encountered, and I'm often willing to file an issue on someone else's behalf. In other circles, for very much the same reasons, I'm probably known as "that annoying Connect guy." On and off over the past few years, I've assembled what I've called "Connect Digests" here on this blog, calling out some of the issues that have been filed that I've deemed as deserving attention. At some points these digests have been more regular than others, and that is totally driven by travel, schedule and workload.

Today I thought I would share a little bit of wisdom with my fellow (and potential) bug-filers. I do see a lot of bugs that get closed as Not Reproducible ("no-repro") or Won't Fix, and I don't think that they all had to be closed that way. Part of the problem can often be pointed back to the quality of the bug itself. I'm not going to name names or call out any specific "bad" bugs, but just wanted to give some hints about what they are looking for. Note that for bugs you should be able to re-open them even if they get closed, but only do so if you have new information to add, not just because you didn't like the decision.

If your bug comes back as Not Reproducible

Try to ensure that you have included complete information about the repro. If there is a crash, capture the detailed stack trace information from the event log or the application's dialog (e.g. SSMS has these details). If screen shots help with the repro, include them. If there are certain "non-standard" aspects of your environment, such as a non-default collation, different compatibility mode, database name that is a keyword, different operating system language, etc., it is possible that those differences are influential in manifesting the bug and, without that information, it might not be possible for Microsoft to repro (because they aren't going to walk through your steps using a full matrix of every possible test combination). Without full, consistent and reproducible steps to exhibit the problem, it is also impossible to know whether you've fixed the problem, and can also be difficult to determine how effective the fix is and whether or not it caused regression elsewhere.

If your bug/suggestion comes back as Won't Fix

Hopefully there is a comment that goes along with it, and the comment will explain why the issue won't be fixed. Sometimes it is just not practical to fix a bug or implement a suggestion, or to do so in the current development cycle. The following factors are all used to guide where a bug or suggestion ends up on the pipeline:

how to reproduce the problem
the frequency the problem occurs
the likelihood of the problem occurring (number of customers potentially affected)
the actual impact on users
the root cause
the cost and risk of fixing it (e.g. backward compatibility)
available work-arounds

Yes, they certainly understand that an issue might be important to you; however, there are many other factors to consider. Depending on the reason(s) stated in the comment(s) by Microsoft, you might want to come back armed with more compelling information, or more people stating their business case qualitatively (see below).

If you haven't filed your bug/suggestion yet (or it's not yours)

Please keep the above in mind. Also note that the qualitative information is at least as important, and often more so, than the quantitative information. In other words, if you just state that x is broken, needs to be fixed, and look at all these votes that support it, that is not necessarily enough to convince Microsoft that it needs to be fixed. In the description of your bug, or in a comment on someone else's, state *why* it needs to be fixed. A real business case that demonstrates significant impact on a customer goes a heck of a lot further than a few extra votes. If your arguments involve standards compliance or keeping up with a competitive RDBMS product, it certainly can't hurt to include that information as well. If you have additional information that explains why the issue hurts, or how the workaround is painful, this can be factored into the decision (unlike an extra up-vote, which to Microsoft is just an <aol>me too</aol>). Including this data can only make it more likely that the item will be seriously considered.

Summary

Connect can be an invaluable tool in getting feedback back to Microsoft and affecting change in SQL Server. It can also be quite frustrating if the flow of information - in either direction - is not the highest in quality. I hope I've given you some things to think about to at least improve one direction of that flow.

Connect Digest : 2011-04-25

AaronBertrand — Mon, 25 Apr 2011 10:54:00 GMT

I wanted to change it up a little this week. In every digest, I've pointed out a set of Connect items that I believe deserve more attention. What I fail to point out every week is the number of Connect items that actually get resolved with a fix. These are even less visible than the active requests, but I think they deserve some attention as well. Why? Well, almost every single comment on Connect is a neutral or negative comment. I think we could all be better about going back to some of these items and thanking Microsoft - not so much for the bugs, which they should be fixing regardless, but more so for the suggestions that we make.

Here are a few items where the issue has been addressed. I call these "Connect wins":

#424800 : SSMS : Expose "Connect to Server" MRU list to users

#484732 : sqlps and PowerShell V2 Issues (granted, fixed for 2008, but no word on 2008 R2 yet)

#615766 : [Denali SSMS] : Execution plan tooltip causes very slow behavior
#615767 : [Denali Engine] : Regression in query hint behavior
#621445 : Extended Events to Trace Mapping Tables in sys schema in Master
#621656 : [SSIS Denali] Synchronous Executions (though closed as fixed, no official comment from Microsoft)

#631373 : Denali - Transaction_Log Extended Event Returning Incorrect Data

Now, there is still plenty of Connect activity that seems to get ignored completely, and several cases where the last comment (four years ago) was, "We'll look at that for the next release." Still others (like #524769) have lots of dialog, but almost all of it is useless, and of course there are countless items that have been closed as "Won't Fix" or "By [questionable] Design." But for the vast amount of content these folks have to sort through, I think it's worthwhile to thank them even for small victories.

Have a Connect win to share? Please post in the comments!

A better way to search Connect

AaronBertrand — Thu, 06 Jan 2011 14:21:00 GMT

I recently spotted a comment from Microsoft on a Connect item with 13 total up-votes. The comment went something like, "wow, due to the explosive response to this issue, we're going to deal with it right away." Okay, it wasn't that emphatic, it was actually: "I've brought the MVP customer vote count to the attention of dev, and a new owner of this DMV says he will dig up some info for us." Still, knowing that I had seen other items with a much stronger response and barely a note of acknowledgment (never mind a pledge to actually act in any way), I performed a search. I started with a search for "SSMS" in my own feedback and was overloaded. So I used the advanced search to whittle it down. I tried over 100 votes and didn't get any. I started getting results (well, 1) when I scaled back to 70:

Then I scaled back to 50, and nothing significantly changed. When I scaled back to 30, though, I saw a really, well, interesting result:

Why is this interesting? Well, the item with 78 up-votes doesn't show up in a search for >= 70. It also doesn't show up in a search for >= 50. But when you lower the threshold to 30, it suddenly appears. Another interesting thing is that no matter what number I put into the vote count, items (such as #311079) with more than 100 votes never show up:

This is just the most recent bit of fascinating search logic I've discovered on Connect; there are many others over the past few years that have really made me shake my head. I've asked multiple times if we could get an API into the data to write our own searches, or be able to store backups of the data so that we can load them on our own systems and run our own searches. The response was always, predictably, "no, not cool." And they wonder why activity on Connect has dwindled (well, there are several reasons for that, which I may highlight in future blog posts).

Fast forward to last week, when Aaron Nelson (blog | twitter) and Nic Cain (blog | twitter) let me in on a little secret - they were loading Connect's SQL Server data into their own SQL Server database, via the RSS feed. Nic is starting a blog series about it, but the important points are:

You can now get more direct access to the data. Rob Farley (blog | twitter), for example, has loaded up the Denali bugs into a pivot collection, allowing you to do lots of real-time visualization against a wide variety of filters. You can also click through to items that interest you.
Aaron has posted a PowerShell script (you weren't expecting anything other than PowerShell, were you?) that will give you read-only access to the data, empowering you to write your own queries against it. I've already used it to run the following, where I was much happier with the results:

SELECT * FROM dbo.ConnectItems
  WHERE Author = 'aaronbertrand'
  AND UpVoteCount >= 30;

Note, though, that the data is not currently complete - the loading is still in process. So I'm not 100% happy with the results, but I know they'll get there.

If you want to stay on top of Connect items for SQL Server, I strongly recommend adding the new items and recently modified items feeds to your favorite RSS reader. In combination with easier searching, I find this quite useful.