Tibor Karaszi : Tools

Express Edition revisited, focus on SSMS

TiborKaraszi — Wed, 30 Jan 2013 17:01:00 GMT

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.) I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:...(read more)

Backup and the evil RETAINDAYS option

TiborKaraszi — Sun, 08 Jul 2012 12:25:00 GMT

"So what bad has this option done?", you probably as yourself. Well, not much, but I find it evil because it confuses people, especially those new to SQL Server. I have many times seen people specifying something like 3, and expect SQL Server to keep...(read more)

Analyzing the errorlog

TiborKaraszi — Thu, 05 Jul 2012 11:53:00 GMT

How often do you do this? Look over each message (type) in the errorlog file and determine whether this is something you want to act on. Sure, some (but not all) of you have some monitoring solution in place, but are you 100% confident that it really...(read more)

SQL Live Monitor

TiborKaraszi — Sat, 19 Nov 2011 08:46:00 GMT

I just found this one out there and wanted to share it. It connects to an instance and show you a bunch of figures. Nothing you can't extract yourself with SQL queries, but sometimes it is just nice to have one tool which is very easy to use. Here's what...(read more)

Ola Hallengren's maint procedures now supports logging to table

TiborKaraszi — Sat, 16 Jul 2011 01:19:00 GMT

This can for instance be very useful if you want to keep track how long time things take, over time. Check them out here .Version history here ....(read more)

Another bugfix for sp_indexinfo

TiborKaraszi — Tue, 09 Nov 2010 08:26:00 GMT

JackMcC reported correctly at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/02/18/sp-indexinfo-updated.aspx that sp_indexinfo will count rows double (or even tripple) if there are lob or row overflow allocations. Now fixed. Thanks Jack....(read more)

New util proc: sp_tableinfo

TiborKaraszi — Sun, 10 Oct 2010 11:59:00 GMT

Some of you might use my sp_indexinfo to easily get lots of details about the indexes in a database. I added an sp_tableinfo with info rolled up to table level. This allow to quickly see the largest tables in a database and things like that. Check it...(read more)

Util procedure to show database size

TiborKaraszi — Tue, 29 Dec 2009 12:17:00 GMT

One of the first things I want to do when I look at a new SQL Server is to get an idea of space usage details for each database, including total space usage. For this I have been using my own sp_db_space_usage for a while now, so I decided to add it to...(read more)

Spooky: What do you connect to?

TiborKaraszi — Fri, 26 Jun 2009 14:12:00 GMT

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...(read more)

Management Pack for SQL Agent Alerts

TiborKaraszi — Mon, 01 Jun 2009 19:05:00 GMT

I have finished my article about suggested SQL Server Agent Alerts. Perhaps calling this a "Management Pack" is OTT, but hopefully it will be useful to some of you folks.

We are talking about having Agent sending email to us when SQL Server writes messages to EventLog. The functionality has been in the product since version 6.0, but I have always lacked some good "default setting". I'm talking about some easy way to tell Agent to notify me for messages which I typically want to be notified for. (Many of you are already using various Management software that has this functionality, so this won't be for you :-) ).

I very much welcome feedback, you find all information in the article:

http://www.karaszi.com/SQLServer/util_agent_alerts.asp

PS: I've blogged about related topics here: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/02/23/where-s-that-sys-messages-management-pack.aspx, and here: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/05/14/sp-altermessage-is-back-in-business.aspx.

sp_altermessage is back in business!

TiborKaraszi — Thu, 14 May 2009 20:36:00 GMT

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.

Match those types!

TiborKaraszi — Tue, 28 Apr 2009 10:30:00 GMT

This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the column side - not the literal side of your query. What does that mean? Consider below:

WHERE Col = Val

Now, say that the types for above don't match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren't of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for "Data Type Precedence".

What we don't want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here's the repro script from that thread (slightly adjusted by me):

USE tempdb GO IF OBJECT_ID('t') IS NOT NULL DROP TABLE t GO CREATE TABLE t ( c1 int IDENTITY(10000001,1) NOT NULL ,c2 char(8) NULL ,c3 datetime NULL ) GO INSERT INTO t(c3) SELECT TOP 3000000 '20080203' FROM master..spt_values t1 CROSS JOIN master..spt_values t2 CROSS JOIN master..spt_values t3 UPDATE t SET c2 = CAST(c1 AS char(8)) CREATE UNIQUE CLUSTERED INDEX x ON t(c2) GO SELECT * FROM t WHERE c2 = N'10000009' SELECT * FROM t WHERE c2 = N'10000008'

Don't bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.

Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find "WHERE:(Convert([t].[c2])=[@1])". See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That's just because we were a bit sloppy... Compare to below:

SELECT * FROM t WHERE c2 = '10000009' 
SELECT * FROM t WHERE c2 = '10000008'

You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.

OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn't see any difference between the two original queries (N'10000009' and N'10000008'). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the "9" query was slower than the "8" query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the "8" version vs. the "9" version. I used Profiler and confirmed. The "9" version had consistently about 90000 microsecond duration where the "8" version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn't give me anything. Perhaps I didn't capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. :-)

My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N'10000009' compared to N'10000008'. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like "Constant Scan" and "GetRangeThroughConvert" for other blog post on this topic.)

The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!

Update of SQL Server 2008 Feature Pack

TiborKaraszi — Wed, 22 Oct 2008 15:56:00 GMT

I just learned (thanks Josefin) that we now have an update of the SQL Server 2008 feature pack - the October "edition". Stuff like download of command-line tools, driver for PHP, native client, sync framework, upgrade advisor, bunch of BI related utilities and such.

Check it out at: http://www.microsoft.com/downloads/details.aspx?familyid=228de03f-3b5a-428a-923f-58a033d316e1&displaylang=en&tm#QuickInfoContainer

Rebuild master in SQL Server 2008

TiborKaraszi — Fri, 29 Aug 2008 18:18:00 GMT

You might have read an earlier blog entry about my problems to remove a Data Collector (DC) information in SQL Server 2008. I still haven't found any info on how to do this, but my questions in the MVP group triggered some activity.

Bob Ward looked into how to rebuild the system databases. This was on my list to try (after removing DC config and if that didn't work rebuilding msdb). But Books Online had very little information on how to actually do the rebuild. Since there were quite many changes in setup between 2005 and 2008, I didn't feel like trial and error based on how we did this in 2005.

Bob helped me out with some information on how to do the rebuild and it is very easy! I now have a bat file which does rebuild of three instances - and it only takes 5 minutes. And even better: no installation media is required - and it also remembers the collation for my system databases!

Enough said. Check out Bob's blog post at: http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Missing F8 or ctrl-N in SSMS 2008?

TiborKaraszi — Tue, 26 Aug 2008 07:22:00 GMT

Short story: Turn on 2000 keyboard layout and then back to Standard layout.

Long story:

This topic has been discussed in both the MCT (MS Certified Trainer) as well as MVP groups. Also, see http://sqlblog.com/blogs/andy_leonard/archive/2008/08/08/sql-server-2008-management-studio-function-keys.aspx, including the comments.

The mystery seems to be that in some cases you do have F8 and Ctrl-N in Standard layout, where in other cases you don't. For instance I did a check on 4 installations where one had the desired layout (with F8) and the others didn't:

VPC. XP. Clean install. No prior SQL Server stuff. No F8 or ctrl-N.
My laptop, XP. I have 2000, 2005 and 2008 tools as well as 2000, 2005 and 2008 instances installed. No F8 or ctrl-N.
My desktop machine, Vista. I have 2005 and also 2008 instances. I have had 2005 SSMS which was uninstalled before I installed 2008 SSMS. Here both ctrl-N and F8 work.
VPC. XP. Had 2005 both tools and instance which were upgraded to 2008. No F8 or ctrl-N.

I was doing training on 2008 last week and I really needed to find my shortcut keys (I couldn't keep stumbling after menus all the time - too slow). So I switched to what I'm familiar with: the 2000 keyboard layout. I recall thinking for myself that perhaps if I now switch back I will have the desired Standard layout (F8 and Ctrl-N). I forgot all about it until today reading a post in the MVP group from Craig Beere suggesting exactly this. To confirm, I tried this in both a virtual machine (1 above) as well as my laptop (2 above) and it worked indeed.

One thing to watch out for: There doesn't seem to be a way to go back to Standard layout *without* F8 and Ctrl-N. For instance when you get F8 etc, you also get a different shortcut for comment code (or was it uncomment?). So you might want to think a little bit before setting to 2000 layout and back. I'm sure in the end that somebody finds a setting somewhere to control the behavior - and then we know how to switch between the two Standard alternatives...