Kalen Delaney : Tools

Did You Know? Troubleshooting Tools Update

Kalen Delaney — Fri, 23 Jan 2009 17:44:00 GMT

Last May, I blogged about free troubleshooting tools for SQL Server and today I received a comment that one of the tools listed seemed to no longer be available. So I thought I would publish a refresh.

There are dozens of great tools available, and I'm not even pretending that I am going to list them all. This post is really just an update to the tools I listed previously. So here is the list from my May post, with additional comments.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

RML Utilities for SQL Server

http://support.microsoft.com/kb/944837
http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

RML Utilities are still around and better than ever. You can read about the CU1 here:

http://blogs.msdn.com/psssql/archive/2008/11/12/cumulative-update-1-to-the-rml-utilities-for-microsoft-sql-server-released.aspx

And some comments from the SQLCAT team here:

http://sqlcat.com/technicalnotes/archive/2008/02/01/precision-performance-for-microsoft-sql-server-using-rml-utilities-9-0.aspx

SQL Nexus

~~http://www.sqlnexus.net/~~

The SQL Nexus tools have moved to codeplex:

http://www.codeplex.com/sqlnexus

And if you want more free tools than the ones listed here, check out everything else available at Codeplex!

PSSDIAG Data Collection Utility

http://www.microsoft.com/downloads/details.aspx?FamilyId=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

(This one is still here.)

SQL Server Performance Analysis Utilities Read80Trace and OSTRESS

http://support.microsoft.com/kb/887057

Although the KB article is still available, the links it points to are broken.

OSTRESS and Read%Trace are are incorporated into RML tools, so see the first link above!

SQL Server Health and History Tool (SQLH2)

http://www.microsoft.com/downloads/details.aspx?FamilyID=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

The KB articles is still there, but you can also get the Health and History Tool from Codeplex:

http://www.codeplex.com/sqlh2

Performance Dashboard

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

(This one is still here.)

I started out that post by mentioning:

I'm very excited about the new Management Data Warehouse coming in SQL Server 2008 (which was called Performance Studio at one point), but keep in mind there are lots of available tools in the current versions.

And now that I've had a chance to use the MDW a bit, I've even more excited. I think THIS is the reason to upgrade to SQL 2008 (if you haven't found another reason yet.)

Have fun!

~Kalen

Geek City: Reading the Transaction Log

Kalen Delaney — Sat, 23 Aug 2008 21:07:00 GMT

Sorry, I'm not actually going to tell you how to read the log. I'm just going to talk about it... and whether it's a good thing to be able to do or not, or whether it's an absolutely crucial feature that MS needs to provide for us immediately, in a hotfix, if not sooner. Forget about fixing bugs, I want to read the log because I forgot to set up a trace beforehand....

You may have noticed that my blogging frequency has fallen off. One or two of you also noticed that I am no longer writing a regular article each month for SQL Server Magazine. Those two facts are related. I have cut back on non-essential activities to try to get my next book out as soon as possible. It looks like I might even finish in time to get the book out on the shelves by early next year. Stay tuned...

Since I couldn't bear to not do anything for SQL Server Magazine, I started writing the commentary in the weekly email newsletter. Actually, I do it every week but the fourth week of the month. If you like, you can sign up for this free newsletter here.

My commentary last Thursday seemed to have rattled some cages. Before I even woke up Thursday morning, there were already two comments on the site, and someone sent me a personal email about what I wrote. By now, there are quite a few more comments. I basically wrote about the need for a log reader tool. It wasn't deeply technical; it's just a commentary after all. You can read it here:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=100076

But boy, did people get upset. They called me bad names... well, if 'mediocre' can be considered a bad name...

So I responded as follows:

Wow... I have never gotten so many comments so quickly about one of my articles. I must really have touched a nerve here!
There is a difference between the actual data rows referenced by the log, and the log format. It's the log format, and giving people full details about what is in the log, that is propriatary information. There is nothing specifically bad about giving people that information. However, calling me names because I don't stand up on a soapbox and DEMAND that MS add this functionality seems a little extreme. There are plenty of other things MS could do with the product and providing a log reader tool is way down on the list.
Yes I realize it is important to some people, but there are many other ways to get this information through tracing etc. If the developer resources are limited at MS, I would much prefer they spend their time on more important stuff. MS knows it's important that people have this information, that's why they added a great deal of additional tracing capabilities in SQL Server 2008.
Also, keep in mind that a log reader tool wouldn't help you debug problems with logic, or with bad reports due to faulty SELECTs. If your WHERE clause was written badly, a log reader tool could tell you which rows were affected, but not WHY. You'd need a tracing tool for that. Vogelm's comment that a log reader tool would help troubleshoot bad queries from 3rd party apps is not true; you need to see the statements for that, not just the affected data.
I do appreciate kbreneman's comment that the real problem is one of perception. MS should make clear that the transaction log is not an audit tool; if you want auditing, you need to set it up on your own, because you're the only one who knows what's important for you to capture.

(The only way to respond to comments is to write a comment of my own, and then the form insists that I rate the article I am responding to. I always feel a bit weird having to rate my own articles.)

Since I wrote the article, I have found out that Lumigent does have a log reader tool for SQL Server 2005, but I have heard less than stellar reviews about its ability to capture some of the more interesting datatype activities that are now possible in SQL Server 2005. And their website still doesn't list any version numbers.

I can't stop thinking about this, so I thought I would open up the issue to a wider audience.

How important do you think it is that Microsoft provide a log reader tool for us?

Thanks!

~Kalen

Did You Know? Free SQL Server Troubleshooting Tools

Kalen Delaney — Sun, 18 May 2008 03:47:00 GMT

I'm very excited about the new Management Data Warehouse coming in SQL Server 2008 (which was called Performance Studio at one point), but keep in mind there are lots of available tools in the current versions.

One of the students in my class last week put together this list of his favorites. Note that some of the tools can be used together. For example, the PSSDIAG collection utility output can be pumped into SQL Nexus for nice reporting and analysis.

RML Utilities for SQL Server

http://support.microsoft.com/kb/944837

http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

SQL Nexus

http://www.sqlnexus.net/

PSSDIAG Data Collection Utility

http://www.microsoft.com/downloads/details.aspx?FamilyId=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS

http://support.microsoft.com/kb/887057

SQL Server Health and History Tool (SQLH2)

http://www.microsoft.com/downloads/details.aspx?FamilyID=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

Performance Dashboard

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Have fun!

~Kalen

Geek City: Included Columns

Kalen Delaney — Mon, 31 Dec 2007 19:53:00 GMT

When an issue comes up more than once in a short period of time, I figure it is a message from the Universe. I might even assume it is a gift of blog fodder. So I won't turn down the gift, even if it means another juicy post before the end of year, much to Denis' disappointment.

The issue is SQL Server 2005 "Included Columns". There was a post on the public newsgroups about them a couple of days ago, and just yesterday, there was a similar post on the SQL Server MVP private newsgroup. Simply put, the questions were expressing concern that there were no built-in tools in SQL Server 2005 to list which columns were included columns. The information is of course available in the metadata views, but it can require a join of at least three of them to get a simple list of which columns are "included columns" in a given index. The poster on the MVP newsgroup wanted to know if someone had already written such a query and could share it, to save him some work.

There actually was a third post about included columns that I came across, that was asking about the reasons for using included columns. I will address that question first, and then provide some scripts to return information about included columns.

You need to be aware of two 'features' of indexes to fully appreciate included columns.

First: all indexes have a limit of no more than 16 key columns, with a combined total of no more than 900 bytes.

Second: a 'covering' index, which is a nonclustered index that contains all the columns referenced in a query from one table, can provide an incredible performance advantage. If all the information a query needs is contained in the index keys, SQL Server will never need to actually access the table data, and not having to do this table lookup can be a very good thing. (Disclaimer: covering indexes are not the solution to ALL query performance problems, and I don't have time today to provide a full discussion of covering indexes.)

SQL Server 2005 allows you to get around the 16 column and 900 byte limit and add additional column to a nonclustered index to provide greater opportunity for covering indexes. The syntax would look something like this:

CREATE INDEX bigindex on mybigrowtable(keycolumn) INCLUDE (bigcolumn1, bigcolumn2)

If all the columns you would like to have in your index fit within the limits, there is no technical reason for having included columns; all your columns could just be regular key columns. However, there are some other issues to consider.

1) Space requirements: Normal key columns, as part of the key, are propagated up through all levels of your indexes. So if you have some very large columns, even if they fit in the 900 byte limit, you can save space by defining them as included columns. Here is an example, using the AdventureWorks database:

USE AdventureWorks
-- set the database to bulk_logged recovery
-- prior to copying tables
ALTER DATABASE AdventureWorks
    SET RECOVERY bulk_logged
GO
---------------------
IF ( OBJECT_ID('Person.Address1') is not null)
    DROP TABLE Person.Address1
GO

IF ( OBJECT_ID('Person.Address2') is not null)
    DROP TABLE Person.Address2
--------------------------------------
-- Create two copies of the Person.Address table and enlarge one of the columns
SELECT * INTO Person.Address1
    FROM Person.Address
GO
ALTER TABLE Person.Address1
    ALTER COLUMN AddressLine2 nchar(350)
GO
-- Create an index with two included columns
CREATE INDEX IX_Address_City
    on [Person].[Address1] (City, StateProvinceID)
    INCLUDE(AddressLine1, AddressLine2)
GO
-------------------
SELECT * INTO Person.Address2
    FROM Person.Address
GO
ALTER TABLE Person.Address2
    ALTER COLUMN AddressLine2 nchar(350)
GO
-- Create a 'regular' index with no included columns;
-- All four columns are keys, and included at all index levels
CREATE INDEX IX_Address_City
    on [Person].[Address2]
        (City, StateProvinceID, AddressLine1, AddressLine2)
GO

-- Note that the Address2 index uses about 1.5 MB
-- more than the index on Address1, because it contains
-- all 4 keys in all levels of the index.
EXEC sp_spaceused 'Person.Address1'
EXEC sp_spaceused 'Person.Address2'
GO

2) Maintenance costs: Normal key columns are maintained in sorted order. An index on (lastname, state, city) would have 3 sort columns and every new row would have to be put in the proper location based on all 3 columns. But an index on lastname, with (state, city) as included columns, would not have to maintain sort order on state and city and that could improve the performance of data modification operations. I haven't done any tests yet to determine how much savings you might realize, but it will be faster with included columns.

The downside of included columns not being sorted, is that those columns will not be useful for searching if those columns are in the WHERE clause, in particular if those columns are using in an inequality expression.

So now, how can you retrieve information about which columns are included and which are key columns? Sp_helpindex does not display this information, in fact, it makes no mention of included columns. The information is available in a catalog view called sys.index_columns, in a column called 'is_included_column'. The code below creates a view that you can use to return information about all the columns in your indexes.

CREATE VIEW index_column_info
AS
SELECT object_name = object_name(ic.object_id),
                  index_name = i.name,
                  'column' = c.name,
                  'column usage' = CASE ic.is_included_column
                               WHEN 0 then 'KEY'
                               ELSE 'INCLUDED'
                   END
   FROM sys.index_columns ic JOIN sys.columns c
              ON ic.object_id = c.object_id
              AND ic.column_id = c.column_id
       JOIN sys.indexes i
              ON i.object_id = ic.object_id
              AND i.index_id = ic.index_id

You can select from this view and supply a table name and/or an index name or id in a WHERE clause:

SELECT * FROM index_column_info
WHERE object_name = 'Address1'

A student in one of my classes earlier this year wanted a way to get input back that looked like the sp_helpindex output. I am attaching a script to build a stored procedure called new_helpindex which will do that. However, the new procedure is used a bit differently than sp_helpindex. sp_helpindex requires a schema name and new_helpindex does not. So to get information about my new Address1 table created above, the two procedures would be called as shown:

EXEC new_helpindex 'address1'
EXEC sp_helpindex 'person.address1'

If you want the new_helpindex procedure to accept schema names, and to return exactly the same details as sp_helpindex (plus the included columns), it is certainly possible, and I leave that as an exercise to my readers.

Have fun!

~Kalen

Geek City: Christmas Present from PSS

Kalen Delaney — Tue, 18 Dec 2007 21:12:00 GMT

I rarely just make a post that is a link to someone else's blog post, but this one is too good to allow anyone to miss. So if you haven't seen any other announcements about this one, you'll see it here.

Two of the incredible guys in Product Support Services for SQL Server at Microsoft have released a set of tools that could change your life!

Check out the RML Utilities here:

http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-microsoft-sql-server-released.aspx

(RML stands for Replay Markup Language which is the XML used by these tools
to determine how to replay a set of SQLTrace files.)

Thanks Keith and Bob!

Have fun, everyone!

~Kalen

Did you know? Cloning is Legal (and Available in SQL Server 2005)

Kalen Delaney — Wed, 21 Nov 2007 18:50:00 GMT

I first heard about the possibility of database cloning way back in version 7.0 and thought it sounded like a great idea. Who wouldn't love to have a bunch of identical little databases running around, of smaller size and easier manageability?

Cloning a database means creating a statistics only copy of it, so that you can examine execution plans for poorly performing queries in a test environment that takes hardly any space at all. A clone database has all the metadata for all your objects, including function and stored procedure definitions. It has all the statistics for all the objects including the histograms and density information returned by DBCC SHOW_STATISTICS. Looking at space usage information shows you only the pages used, but not the pages allocated but unused.

So how can you get a little clone of your own? The original plan was to have a toolbar option to just click and presto, there would be your clone. That never happened. Later, one of the optimizer engineers at Microsoft created a stored procedure, called something like sp_clone_db that took a database name as a parameter, and created a new database with all the metadata and statistics, but none of the data, of the original. That procedure was never included with any version of the product.

Instead, what we have in SQL Server 2005 is the ability to script a database, and include the statistics and histogram information.

In SQL Server Management Studio's Object Explorer, right-click your database name and choose Tasks, and then Generate Scripts....

Choose the name of the database to script, and select the box to "Script all objects in the selected database". Next you'll see a "Choose Script Options" dialog with several dozen options to choose from, mostly indicating whether your want to include certain properties or objects in your database. Most of these you can leave at the default, or deselect the ones you're not interested in. I usually select the option to create the database, so it will include the CREATE DATABASE statement and any necessary filegroups. If you don't do this, and your database has objects created on filegroups other than the primary, your script will need a lot more manual editing.

There are two options that you must change in this "Choose Script Options" dialog. In the General section is the one called "Script Statistics". When you click in the list of options on the right side, you'll see three different options. The default is "Do not script statistics". Choose the option to "Script statistics and histograms." When you make this selection, you'll get a warning like the following:

If you want a clone, you'll have to use this option, so don't let this warning scare you.

In the Table/View Options section (you'll have to scroll down) change "Script Indexes" to True.

Click Ok, and after you click Next to leave this "Choose Script Options" dialog, the "Output Options" dialog opens. (If you forget to check the box for scripting all objects in the database in the very first dialog, you'll get about 10 different dialogs asking you to choose which objects you want to copy, before you get to the "Output Options" dialog. Don't say I didn't warn you.)

I usually just choose to copy to a New Query Window and click Next. I then get a chance to confirm my choices, and then I click Finish. The copy process begins.

WARNING: If you notice in the confirmation list that you forgot something, or want to go back and make any changes, the option to Script Indexes will be set back to false. You'll need to make sure to reset it to True before going forward again.

Once I get the script in the new window, I need to make sure I change all references to the original database name to a new name. (If you are creating the clone on a different server that doesn't have the original database, then you won't have to do this step.) Make sure you check the filenames and make sure they don't duplicate existing names. Execute when ready.

Or, you might want to spend a few minutes examining the script. You'll see UPDATE STATISTICS statements like you've never seen before, looking something like the following. (My clone was created on the AdventureWorks database):

UPDATE STATISTICS [Sales].[CreditCard]([AK_CreditCard_CardNumber]) WITH STATS_STREAM = 0x0100000002000144 <huge big long hex value> 000000000000510D0000000000, ROWCOUNT = 19118, PAGECOUNT = 93

So the UPDATE STATISTICS statements force a particular histogram into the statistics, as well as the page count and row count.

Once you've created the new database, you should be able run queries to look at sizing information.

EXEC sp_spaceused 'Sales.SalesOrderDetail'

returns the following:

name rows reserved data index_size unused
---------------- ------ -------- ------- ---------- ------
SalesOrderDetail 121317 0 KB 9880 KB 0 KB 0 KB

However,

SELECT count(*) FROM Sales.SalesOrderDetail

returns 0.

You can look at execution plans for queries involving any of the cloned tables and pre-existing indexes. You can create new indexes, but be careful. Any new indexes will have statistics created based on the real data in the clone database, which is 0 rows. So the statistics on your new indexes will not be very accurate.

Of course, your clone database cannot be used for timing tests, or troubleshooting concurrency issues, since there is no data to lock, but for examining how plans change as you rewrite queries, it's a great idea.

Have fun!

~Kalen

Geek City: Poor (Wo)Man's Load Testing

Kalen Delaney — Wed, 24 Oct 2007 19:28:00 GMT

Last July, I posted about a way to run a batch multiple times when you use GO as the batch separator. I usually take advantage of this technique when I am loading test data into a table.

I was mentioning this feature to my class last week in Minneapolis, and one of the students thought that this feature had another use. He thought it would be great to use it to simulate load testing. One connection could execute a procedure and terminate the batch with GO 100 and another connection could be started that did the same thing. You could open up multiple query windows using Management Studio (or Query Analyzer) and have each one run a procedure, or even a statement, over and over again. You could also have different connections run different stored procedures or statements. This will allow you to detect problems with concurrency, which most query tuning techniques do not take into account.

In the past, I had recommended using the Replay feature of SQL Server Profiler to simulate a "poor man's load testing" environment, but this great idea would make it even easier!

Of course, for the rich (wo)man, there is also very nice load testing software available. Whether rich or poor, it's crucial to make sure you test your queries and procedures with multiple users.

Have fun!

~Kalen

Did You Know? Run a batch multiple times

Kalen Delaney — Sun, 22 Jul 2007 22:08:00 GMT

At first I thought I would just add another sentence to my previous post, but then I realized that this feature is not even documented anywhere, and it might be useful to mention it in its very own post. I have no idea why it is undocumented, but it has been part of all command interfaces to TSQL that I have used for the last 20 years.

When you use GO as a batch terminator, you can follow it by an integer, which indicates that the batch should be executed N times.

I use this frequently when populating test tables. In my previous post, I ended with this suggestion:

-- You can add more rows by just rerunning the INSERT statement from above, as many times as you would like.

So, to run the INSERT 5 times, I would do this:

INSERT INTO details
(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
        SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
             UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
        FROM Sales.SalesOrderDetail
GO 5

Have fun!

~Kalen

Geek City: My New Favorite Tool - SQL Internals Viewer

Kalen Delaney — Fri, 20 Jul 2007 02:52:00 GMT

At least, I think it might become my new favorite tool. J

One of my readers created a tool to graphically display database storage internals, like allocation information, actual page contents, etc. This is just the tool I would have wanted to write if I ever got back to doing any programming. Here's what the author, Danny Gould, says:

The application acts as a viewer for database internals, including pages and allocation structures. I’ve created it with the aim of it helping people learn and investigate internals. There is more information about it at its website, www.sqlinternalsviewer.com I’ve really enjoyed creating the application and I continue to learn from developing it. I plan to create some online tutorials on how to use it.

Danny would love to get feedback on the tool, as well as suggestions for enhancements. As a sneak preview, here's an image of the tool from the main page of the web site:

Allocation Map showing IAM selected in the Database Browser

Have fun!

~Kalen