I only recently discovered that SSMS will connect to different things. For instance, press the "New query" button. What were you connected to? The answer is the same server as your "current" server. But what is the current server? It is the server where you happened to have focus when the pressed the "New query" button. So, can you say whether you had focus in a query window, Object Exporer or Registered Servers?

This also applies to when you double-click a .sql file. And it doesn't stop there. Open the "Registered Servers" window. Now, click on a server group. Go to explorer and double-click a .sql file. What were you connected to? Yes, all the servers in that group. Now, don't get me wrong here; the ability to open the same query window against several servers can be a very useful thing. What I had no idea until just about now is how easily thsi can happen by mistake. Just by cklicking the New Query window, or even double-clicking an .sql file. So - be aware...

(FYI: SSMS 2005 doesn't seem to do this for clicking a file in explorer, and the functionality to have a query window against several server didn't exist in SSMS 2005...)

The old sysindexes table (as of 2005 implemented as a compatibility view) has a useful column named keycnt. This is supposed to give us the number of columns (keys) in the index. However, to make heads and tails out of the numbers, we need to understand how a non-clustered index is constructed. For a heap, the pointer to a row is the physical file/page/row address (aka "rowid"). This is counted as a key in the keycnt column:

IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
GO
CREATE TABLE T1 (c1 INT, c2 datetime, c3 VARCHAR(3))
CREATE INDEX ix_T1_c1 ON T1 (c1)
CREATE INDEX ix_T1_c1_c2 ON T1 (c1, c2)
CREATE INDEX ix_T1_c1_c2_c3 ON T1 (c1, c2, c3)
CREATE INDEX ix_T1_c2 ON T1 (c2)
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')

For the index on column (c2), you see a keycnt of 2. This is the key in the index plus the rowid.

For a nonclustered index on a clustered table, the row locator is the clustering key. Note, though, that if the clustered index is not defined as unique (PK, UQ etc), then another "uniqueifier" key/column is added. Building on above example:

CREATE UNIQUE CLUSTERED INDEX ix_T1_c1 ON T1 (c1) WITH DROP_EXISTING
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')
GO
CREATE CLUSTERED INDEX ix_T1_c1 ON T1 (c1) WITH DROP_EXISTING
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')

Consider the (non-clustered) index on column c2. For the first one, where the table has a unique clustered index, we see a keycnt of 2, the column c2 plus the clustered key. But when we define the clustered index as non-unique, we see +1 for the keycnt column; the uniqueifier. The uniqueifier is 4 byte, and only populated for rows which are duplicates of an existing clustered key (i.e. no extra cost if no duplicates).

But we want to stay away from compatibility views, right? Since we no longer have a key count column in sys.indexes, we need to grab that from sys.index_columns. This do not, however include the internal columns, only the explicitly defined columns:

I see a trend towards more and more focusing on response time; and less and less on resource usage (resource consumption). I've even seen blanket statements such as the only thing that matters is response time. I do not agree. I feel that by being a good citizen and consume as few resources and possible, we contribute to the overall welfare of the system.

For instance, I'm fortunate to have some 8 km (5 miles) to my main client. I can take the car, which often is about 15 minutes or I can bicycle, which is about 20 minutes. For many reasons, I prefer to bicycle. The thing here is that I compromise a little bit and accept a few more minutes when going by bicycle, but I feel I'm a better citizen and contribute to a more sustainable society. But not only that: ever so often, the traffic is congested, and now the car takes some 40-45 minutes (bicycle still 20 minutes). By using the bicycle I both consume less resources and I also have a higher degree of predictability. Now, is this analogy appropriate to database performance? I don't know, perhaps to some extent... But let me give you a database example, from real life, followed by a TSQL example:

I have a client who had this query which used to be quick (enough) and suddenly was very slow. Been there before, we know this can be just about anything. Anyhow, it was pretty quick for me to find the reason. The query had an ORDER BY and a FAST hint. The FAST hint tells SQL Server to return rows to the client as fast as possible, but possibly with a higher overall cost. The developer who added that FAST hint didn't really think that hard about it, and just "threw it in there". It sounds good, doesn't it? There was a non-clustered index (non-covering) on the sort column and also some restrictions (WHERE clause).

With the FAST hint, SQL Server used the index on the sort column to drive the query and for each row it did a "bookmark lookup" to fetch the row. This means a page access for each row, but rows can be returned to the client application immediately (think streaming). Without the fast hint, SQL Server first sorted the relevant rows into a worktable and then returned the rows in sorted order from that worktable. So we have a tradeoff between reading a lot of pages (possibly some from cache) or doing some work up-front to sort data and then just read that sorted worktable sequentially.

The worrying part here is that with a small table, it will fit in cache and the difference between the two might not be that drastic (either way). But as table grew larger, it won't fit in cache anymore and as we see logical I/O turning into physical I/O things really go south for the query with the FAST hint. This is what happened to my client. Table grew and a query which had OK response time suddenly was a disaster. If that FAST hint wasn't there in the first place, my client wouldn't have this slowness in the application over the two weeks it took until I had time to look over it and remove the FAST hint (I also added a couple of indexes, but that is beside the point).

Seeing is believing, right? At the end of this blog post, you will find TSQL that pretty much mimics my client's case. It populates a table with 800,000 rows and there's a non-clustered index on the sort column. We then try some variations to check response time, CPU seconds usage, I/O and duration. I measured response time using TSQL (as seen in the script). I also measured response time and the other metrics using Profiler.

The size of the table (clustered index on identity column) is 133MB and the non-clustered index to on the sort column is 11MB. This is a small table, but that makes things more manageable; and by setting the max server memory to a low value (60MB), we can still see the effect of logical vs. physical I/O.

We first run the query and have a filter that restricts to 4,000 rows out of 800,000 rows. Note that there's no index on the filter column.

• The query without a FAST hint was very consistent. The response time was either 0.05 seconds (without clearing cache first) or 1.9 seconds (if we clear cache first). This was regardless of if we configured with 500MB or 50MB memory for sp_configure 'max server memory'.
• The query with FAST hint was OK with memory setting of 500MB, so the table would fit in cache: 1.6 seconds to 4.4 seconds (depending on whether we empty cache before execution). But when we lower memory setting (think "large table"), the execution time jumped up to 73 seconds. That is a factor of between 48 and 1460.

Things got a bit different when we removed the WHERE clause to return all rows:

• Query without FAST hint took between 10 seconds and 23 seconds (depending on whether we empty cache first) for a memory setting of 500MB. Lowering memory to 60MB made this one take between 23 and 41 seconds. Note that I here got some error messages from SQL Server regarding insufficient memory in the internal memory pool (possibly SQL Server now did some fall-back strategy for the query execution, which added to execution time).
• The query with the FAST hint outperformed the one without for a memory setting of 500MB, with execution time between 2.2 and 5.6 seconds. Note that I configured SSMS to discard results so there is no processing of the returned 800,000 rows included here. With a memory setting of 60MB, we again bumped up execution time to some 74 seconds.

Here are the full numbers:

For the sake of completeness, I should add that having a good supporting index for the restriction (for the queries that had a restriction) made the query go equally fast regardless of memory config or FAST hint (in fact the FAST hint was irrelevant with a good index).

Here's the T-SQL if you want to play with it. As always, don't execute anything if you don't understand the code and the consequences of executing it!

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again. I.e., we can change the is_event_logged column in sys.messages. This is very valuable in general and specifically is you want to define Agent alerts (for which Agent polls the Eventlog). For instance:

SELECT * FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033

Notice the value for the is_event_logged column. Now, run below:

EXEC sp_altermessage
 @message_id = 1205
,@parameter = 'WITH_LOG'
,@parameter_value = 'true'

Now, re-run the select statement and see that you modified the behavior for the system message. Now, re-run the sp_altermessage with 'false' to reset to default.

The ability to modify this behavior for system messages was available prior to SQL Server 2005, but some re-architecturing in 2005 removed the functionality. kozloski informed me in this blog post that 2005 sp3 re-introduced the functionality and obviously as of sp1 the functionlity is back in 2008 as well.

I do quite a bit of posting on forums, and I have noticed that in many cases when you take your time to explain how something work, or why you should do something perhaps in a not so obvious way, that answer tend to be ignored and never replied to.

Instead the original poster replies to the short answer which perhaps ignored things like best practices, performance, readability etc. And, very often I see endless discussion based on that reply, where the original poster never gets a satisfactory solution or even perhaps a working solution. And there I posted an elaboration which held the answer days ago! I also explained why solution X doesn't work and solution Y only work in some circumstances etc.

In many cases, we have taken the time to write articles or blog posts where we have predicted most misunderstandings and explain why you should do it this or that way and what drawbacks workaround A has, and also what drawbacks workaround B has. So, we post the URL to the article only to find that it apparently never was read because the following discussions in the thread goes on for days and waste time to elaborate on things that could be understood by spending some 10 - 20 minutes to actually read the article or blog post we pointed to.

I find this state mildly depressing. Have we reached the sad state where we don't tend to be interested in why something works, how it works, or even understand what we are actually doing? Quick fixes, anyone? Only 10 cents each!

Is this something new? I've been on forums for > 10 years now, let me try to think..... I believe that tendency has always been there, but perhaps it has been getting worse with time. What are your experiences? Am I only having a bad day?

I've often been thinking that "somebody" should produce a list and perhaps some ready-made TSQL code of the SQL Server error messages we want to be alerted for. Something like a Management Pack, but not tied to any particular sys mgmt software. Essentially, what error messages would we typically want to be alerted for, and some ready made TSQL code which defines an alert (msdb.dbo.sp_add_alert, see also http://www.sqlskills.com/BLOGS/PAUL/post/Easy-monitoring-of-high-severity-errors-create-Agent-alerts.aspx) and operator for each of those.

Honestly, how many of you have been going through all SQL Server messages and determined which of those we want to be alerted for? Sure, we can decide to specify alerts for all high-severity level messages (like 19, 20, 21, 22, 23, 24 and 25). But unfortunately the severity classification isn't as coherent and we would hope.

For instance, Paul Randal just recently blogged about error 825, which has as low severity as 10! Do we want to be alerted for 825? You bet! Check out Paul's blog at http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx.

We can, however make it a little bit easier for us. Since Agent detects the message by sniffing the eventlog, we can immediately discard all messages which aren't written to the eventlog (there's no use defining alert for something which the alerting mechanism never detects, right?):

SELECT * 
FROM sys.messages
WHERE language_id = 1033
AND is_event_logged = 1
ORDER BY severity

Now we are down to 699 messages. It used to be that we could configure whether a message should be written to eventlog (sp_altermessage), but this functionality disappeared in 2005. I haven't checked Connect, but my guess is that if there is such a wish/entry in the first place, it doesn't have too many votes.

My suggestion is that we create some small community project somewhere where we add what messages we want to be alerted for. This can be as simple as a table with error numbers and some insert, and then we use this to generate our sp_add_alert commands based on that. Question is where to do this, so it can be maintained and refined over time? Or if it has already been done by somebody...

I've been learning a few things about troubelshooting login problems over the last few months. I first want to say that I've learned most of this through other blogs, comments etc. At the end of this blog post you will find an URL list where you can look for more details.

It started a few months back where I somewhere read a blog stating that we can use the state in a "login failed" error message to determine why the login failed. As you can imagine, there can be several reasons for login failure (user doesn't exist, pwd doesn't match, windows login name isn't a trusted login etc). For some reason, I didn't save that URL and of course I needed it some time later and couldn't find it.

One place where you can find such a list of states is... Books Online. BOL 2008 documents every error number, so it is a matter of knowing what error number to search for: 18456. You can also search for "login failed" (pretty easy to remember) and the first hit is a different page (from above) but with similar information. I just tried a search in 2005 BOL for the same and had similar hits. This information might have been added to BOL 2005 in a more recent BOL 2005 release, though.

Now, don't be surprised if the client app only receive state 1 for the login failed messages. This is a good thing, and on purpose. We don't want the system to disclosure too much information to a malicous user (app) about why a login fails. So what we do is to look in the errorlog or for the "real" state (the EventLog doesn't seem to carry this information, for some reason). Below is an example from SQL Server 2005, sp2, with timestamps removed:

Error: 18456, Severity: 14, State: 5.
Login failed for user 'gurka'. [CLIENT: <local machine>]

So, state 5 tell us, according to SQL Server 2005 BOL, that "User ID is not valid.".

Now, doing the same in SQL Server 2008 was interesting. Looking in the EventLog, we do see an explanation as to why the login failed. We still don't see the state in the EventLog, but we have an explanation so we might not need the state. And in the errorlog file, I had below message:

Error: 18456, Severity: 14, State: 5.
Login failed for user 'gurka'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

So, not only do we have a state, we also have a decription for why the login failed. Is it pathetic that such a thing can make you happy? Perhaps it is... ;-)

Here are some other blog posts on the subject:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
http://jmkehayias.blogspot.com/2008/03/understanding-login-failed-error-18456.html

Fire up your SQL Server 2008 Books Online (BOL) and check the date. You should find this in any article (except for the very start page).

RTM of SQL Server 2008 shipped with a BOL which has ... hmm,... doesn't have a date (just checked in a virtual machine). OK, so if you don't find a date (year month) you have the BOL which shipped with RTM.

The first BOL update was released 2008-09-30. You will see this as (August 2008). This was made available on the web as well as a download (http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&DisplayLang=en). This BOL did not list what was changed from prior release since this was considered RTM of BOL and the BOL that shipped with RTM was considered RC0 (which explains why this BOL lists Aug 2008 when it was released late Sept 2008).

Today I learned that MS has released yet another release of Books Online. This is only available on the web, and you see this as (October 2008). Now we do have listing of what section are new and what were expended on. I admit that I prefer locally installed BOL, but I guess that MS has resource limitations the same was as all of us do. Check it out at http://msdn.microsoft.com/en-us/library/dd239407.aspx.