When you only have a few SQL Server Agent jobs, it’s easy to keep track of and make sense of them. But not so easy to understand what’s going on when there are many jobs starting around the clock and running varying lengths of time. Even if the jobs are neatly organized, over a period of time entropy sets in and you have an incomprehensible mess. Reading text to find starting times and run times isn’t fun – it’s just plain tedious. I’ve gotten some relief by using a free tool called SQLjobvis, available at http://www.sqlsoft.co.uk/sqljobvis.php. Go take a look. There’s a screen capture that shows you what it does for you.

Thanks to my fellow MVPs, I found out about this tool today. It made my day go much more smoothly and made it easy for my coworkers to get the big picture on what’s really going on with SQL Agent jobs.

Wouldn’t it be great if Microsoft offered a graphical UI for SQL Server Agent? I’d like Microsoft to provide graphical editing of SQL Agent job schedules. Please vote on this Connect item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=524854

Many of you use Virtual PC and Virtual Server, but not many people are aware of some new hotfixes and security updates released in 2009. Before getting to those, the latest versions of those two products are Virtual PC 2007 Service Pack 1 and Virtual Server 2005 R2 Service Pack 1. You first need to be at that level. Here is a hotfix rollup for Virtual PC 2007 SP1:

http://support.microsoft.com/kb/958162 released February 20, 2009

This is a security update for Virtual PC 2004, Virtual PC 2007, Virtual PC 2007 SP1, and Virtual Server 2005 R2 SP1:

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

If you are running Windows 7 on a machine that doesn't support hardware assisted virtualization, you'll need to use Virtual PC 2007 SP1. It works just fine. You don't have to run Windows 7 Virtual PC. Of course, if you can run the Windows 7 version, you should. Microsoft has a utility to let you know if your machine supports hardware assisted virtualization: http://www.microsoft.com/downloads/details.aspx?FamilyID=0EE2A17F-8538-4619-8D1C-05D27E11ADB2&displaylang=en

 

 

 

The Windows 7 Problem Steps Recorder is yet another reason to upgrade to Windows 7. It records what the user actually did, not what the user claims to have done. I used it to record the attached session of running an invalid T-SQL query in SSMS. When the recording was made, there was a step I added that’s not completely obvious from the recorded output. Notice that the query I executed was highlighted. I did a Control-C to copy the query into the past buffer. The Control-C event was recorded as User keyboard input in "Microsoft SQL Server Management Studio" [... Ctrl ... Ctrl-C]. The query was pasted into the Comment dialog box (not shown in the output – you’ll see it when you run the tool and select Add Comment), which left a textual version of the query in the record. I recommend using this approach when capturing a session. This provides the person analyzing the report a textual copy of the query instead of just the screen capture version of it.

Figure 1. One of the screen captures from the Problem Steps Recorder.

To start the Problem Steps Recorder, type psr in the Search programs and files box to find the psr.exe program. Run the psr.exe program.

Figure 2. Finding and running the PSR.

Complete output is in the attached file.

Try it out – it’s very simple to use. Once you use it, I’m sure you’ll  recommend it to others.

When reviewing a client's database, I suspected there might be indexes on low cardinality (i.e., low "uniqueness") columns. Typing COUNT(DISTINCT colName)) became tedious quickly. Time for a script. Oops, wait, in deference to proponents of test driven development, time for a test case and then time for a script. Here's the test case:

CREATE TABLE dbo.CardinalTest(

      a bigint NULL,

      b binary(50) NULL,

      c bit NULL,

      d char(10) NULL,

      e date NULL,

      f datetime NULL,

      g datetime2(7) NULL,

      h datetimeoffset(7) NULL,

      i decimal(18, 0) NULL,

      j float NULL,

      k geography NULL,

      l hierarchyid NULL,

      m image NULL,

      n int NULL,

      o money NULL,

      p nchar(10) NULL,

      q ntext NULL,

      r numeric(18, 0) NULL,

      s nvarchar(50) NULL,

      t nvarchar(max) NULL,

      u real NULL,

      v smalldatetime NULL,

      w smallint NULL,

      x smallmoney NULL,

      y sql_variant NULL,

      z text NULL,

      aa time(7) NULL,

      bb timestamp NULL,

      cc tinyint NULL,

      dd uniqueidentifier NULL,

      ee varbinary(50) NULL,

      ff varbinary(max) NULL,

      gg varchar(50) NULL,

      hh varchar(max) NULL,

      ii xml NULL

);

The test case has a column for every data type in SQL Server 2008.

A cursor loop is need to iterate through all of the tables. Notice that instead of iterating through INFORMATION_SCHEMA.TABLES, a different approach was used in case you want to limit the analysis to the largest n tables. If you want to do this, add a TOP n clause. If you have a lot of columns, you can exceed the 4000 character limit of the @TSQL variable. If you don't use Unicode characters for table and column names, you can change @TSQL to VARCHAR(8000).

Now for the script:

DECLARE @TABLE_SCHEMA NVARCHAR(128);

DECLARE @TABLE_NAME NVARCHAR(128);

DECLARE @COLUMN_NAME NVARCHAR(128);

DECLARE @TSQL NVARCHAR(4000);

 

DECLARE tableCursor CURSOR FAST_FORWARD FOR

/* The following query with a TOP n is useful by itself to show you the

   largest tables in your database. */

/* This query works for SQL Server 2005 and above. */

SELECT OBJECT_SCHEMA_NAME(object_id) AS schemaName  -- maybe add a TOP n 

     , OBJECT_NAME(object_id) AS tableName

FROM sys.dm_db_partition_stats

WHERE index_id < 2

AND OBJECTPROPERTY(object_id,'IsUserTable') = 1

ORDER BY sys.dm_db_partition_stats.row_count DESC;

/* This query works for SQL Server 2000. */

--SELECT TABLE_SCHEMA, TABLE_NAME

--FROM INFORMATION_SCHEMA.TABLES t

--WHERE T.TABLE_TYPE = 'BASE TABLE';

 

OPEN tableCursor;

 

SET ARITHABORT OFF     -- prevent divide by zero errors if table has no rows

SET ANSI_WARNINGS OFF  -- or if all values are NULLs

 

-- Perform the first fetch.

FETCH NEXT FROM tableCursor INTO @TABLE_SCHEMA, @TABLE_NAME;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

    DECLARE columnCursor CURSOR FAST_FORWARD FOR

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_SCHEMA = @TABLE_SCHEMA

    AND TABLE_NAME = @TABLE_NAME

    AND DATA_TYPE NOT IN ('geography','geometry','image','ntext','text','xml');

 

    OPEN columnCursor;

 

    SET @tsql = 'select ''' + @TABLE_SCHEMA + ''' as ''schema'', ''' + @TABLE_NAME + ''' as ''table'', count(*) as nrows';

 

    -- Perform the first fetch.

    FETCH NEXT FROM columnCursor INTO @COLUMN_NAME;

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

       SET @tsql = @tsql + ', cast(cast(count(distinct(' + QUOTENAME(@COLUMN_NAME) + ')) as decimal(15,3))/cast(count(*) as decimal(15,3)) as decimal(4,3)) as ' + QUOTENAME(@COLUMN_NAME);

       -- This is executed as long as the previous fetch succeeds.

       FETCH NEXT FROM columnCursor INTO @COLUMN_NAME;

    END;

    SET @tsql = @tsql + ' FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME);

    

    CLOSE columnCursor;

    DEALLOCATE columnCursor;

 

    EXEC sp_executesql @tsql;

 

    FETCH NEXT FROM tableCursor INTO @TABLE_SCHEMA, @TABLE_NAME;

END;

 

CLOSE tableCursor;

DEALLOCATE tableCursor;

Here's some sample output:

schema table            nrows BusinessEntityID CreditCardID ModifiedDate

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

Sales  PersonCreditCard 19118 1.000            1.000        0.059

As you can see, both BusinessEntityID and CreditCardID have the highest possible cardinality (i.e., every value is unique) and are suitable for indexing. ModifiedDate values have low cardinality (low selectively), so it wouldn't make sense to create an index on the ModifiedDate column.

Cardinality is not a term that's common in SQL Server literature, but it is common in Oracle literature. I suspect that Oracle people don't actually know what cardinality is. They do know that whenever somebody says "the indexes were created based on cardinality" that they must knowingly nod their heads in agreement. But internally they feel like they're back in college hoping that the professor doesn't call on them. So, the next time some Oracle people talk smack about SQL Server, nonchalantly throw out cardinality in your conversation just to make them squirm.

The script was written for use at a SQL Server 2005 customer. If SQL Server 2008 is available, take a look at this SSIS feature described here: http://sqlblog.com/blogs/eric_johnson/archive/2009/09/11/ssis-2008-data-profiling-task.aspx

 

 

I posted a suggestion for a minimally logged delete feature on Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=509341. There is a legitimate need for minimal logging of deletes. Just because deletes need to be able to be rolled back in most cases doesn't invalidate the need for minimal logging at other times. Bulk deletes are necessary at times. When large amounts of data are deleted, the transaction log grows. Sometimes deletes bloat the log so much that the server stops because it doesn't have any space left for the log to grow. If a minimally logged delete is made available, I won't care if it fails and it can't roll back. I'll just issue the command again. I don't want recoverability and I certainly don't want log growth when I'm doing a bulk delete. Imagine a TRUNCATE with a where clause - that's the general concept.

After having several people at SQL PASS ask me to explain what a Bill of Materials is, it seemed like a good idea for a blog post. A Bill of Materials, otherwise known as a BOM (don’t read this aloud in an airport lounge), is a listing of all of the components (i.e., parts) needed to assemble (i.e., make) something. Let’s start with a picture of our inventory of components.

Figure 1. Inventory of parts. (note: the colors were chosen because they are visible to people with any type of color vision deficit)

The inventory consists of:

32   1x1 yellow bricks

  4   1x3 black bricks

  5   1x4 blue bricks

These components are assembled into what are called assemblies. There are enough components to makes five assemblies. These assemblies form the letters E, F, H, I, and L. Here’s a picture of our five assemblies and the one leftover component.

Figure 2. Parts from Figure 1 assembled into five assemblies.

Several things should now be obvious. Although the inventory is the same in Figures 1 and 2, the entropy of Figure 2 is much lower. The components are subordinate to the assembly containing them – there’s a hierarchical relationship between an assembly and its components. If you focus on the letters H and I, you realize that another level can be added to the hierarchy. The two assemblies H and I can be used to assemble the word HI. When an assembly becomes a component in another assembly, it is called a subassembly. It doesn’t take much imagination to realize that letters can be assembled into words, words into sentences, sentences into paragraphs, paragraphs into chapters, and so on. The hierarchy can be made many, many levels deep.

There are multiple techniques for modeling a hierarchy. Organizational charts are hierarchical in nature and are implemented in the SQL Server sample databases Northwind, AdventureWorks, and AdventureWorks2008 and the Oracle sample schemas SCOTT and HR. I have read that a Bill of Materials and an organizational chart are similar. They are in the sense that they are both hierarchical. But they are also different. An employee table (e.g., Northwind Employee, AdventureWorks HumanResources.Employee, SCOTT.EMP, HR.EMPLOYEE) has one row per employee. Although a BOM will work with one row per component, it’s not just annoying, it’s a bad idea. Look at the two hierarchical displays shown below.

Figure 3. One row per part model on the left, part with quantity model on the right.

The list on the left shows what happens when one row is stored for each component. While it is necessary to know that the assembly E contains four 1x1 yellow bricks, it’s not helpful at all to see the 1x1 brick component description appear four times. Adding a quantity column to the data model results in a practical and useful BOM. Adding a quantity column to an employee table wouldn’t make sense, except when the employees are Imperial storm trooper clones.

The adjacency list pattern is the most common approach to modeling a hierarchy. It is used in the Production.BillOfMaterials table in AdventureWorks and AdventureWorks2008. A self-join is used to model the hierarchy.

Figure 4. The ParentPartId contains the PartId of the immediate parent (which is one level up in the hierarchy).

A top level assembly has a ParentPartId of NULL when an adjacency list pattern is used as the following output shows.

Figure 5. Each component (PartId values 17, 29) has a reference to the PartId 30, which is the PartId of the assembly named H.

When an assembly is used as a component of another assembly, it must have a ParentPartId instead of a NULL. The following output shows what happens when assembly H becomes a subassembly of assembly HI.

Figure 6. PartId 30 now has a ParentPartId of 47 instead of NULL.

When working with hierarchies, each item belongs to a specific level of the hierarchy. The parts lists shown in this post are examples of what is known as a Bill of Materials parts explosion. To format a parts explosion to show the hierarchical relationships, the level of each component must be known. If it isn’t stored in the table, it must be calculated from adjacency list references to the parents.

Figure 7. Parts explosion showing levels of components.

The levels of a hierarchy implemented using an adjacency list pattern are easily calculated with a recursive CTE.

;WITH BOMcte(PartId, Name, Color, Quantity, Lvl)
AS
(
    SELECT WQ.PartId,
    CAST(WQ.Name as nvarchar(50)),
    WQ.Color,
    WQ.Quantity,
    1 -- or use 0 if you prefer a zero-based hierarchy
    FROM AdjacencyList.WordQty AS WQ
    WHERE WQ.ParentPartId IS NULL
    AND WQ.PartId = 47
    UNION ALL
    SELECT WQ.PartId,
    CAST(REPLICATE ('| ', Lvl) + WQ.Name as nvarchar(50)),
    WQ.Color,
    WQ.Quantity,
    Lvl + 1
    FROM AdjacencyList.WordQty as WQ
    JOIN BOMcte AS cte
    ON WQ.ParentPartId = cte.PartId
)
SELECT Lvl, Name, Color, Quantity
FROM BOMcte;

Notice that the level is needed to indent the subordinate items according to their levels. The first level of the hierarchy is defined in the anchor query (hardcoded to 1 in this example) and the remaining levels are computed in the recursive query. The CAST function is required to prevent a run time error caused by the UNION ALL statement. The data types must be compatible between the two statements in a UNION. CAST is used to make the second column in the anchor query the same length as the second column in the recursive query. If they don’t match, the query fails with a Msg 240.

When the objective is to create nested results to represent the hierarchical relationships, a sort key needs to be computed.

;WITH BOMcte(PartId, Name, Color, Quantity, Lvl, ParentPartId, Sort)

AS

(

    SELECT WQ.PartId,

           CAST(WQ.Name as as nvarchar(50)),

           WQ.Color,

           WQ.Quantity,

           1,

           WQ.ParentPartId,

           CAST('\' + WQ.Name as nvarchar(50))

    FROM AdjacencyList.WordQty AS WQ

    WHERE WQ.ParentPartId IS NULL

    AND WQ.PartId = 47

    UNION ALL

    SELECT WQ.PartId,

           CAST(REPLICATE('|    ', Lvl) + WQ.Name as nvarchar(50)),

           WQ.Color,

           WQ.Quantity,

           Lvl + 1,

           WQ.ParentPartId,

           CAST(cte.Sort + '\' + WQ.Name as nvarchar(50))

    FROM AdjacencyList.WordQty as WQ

    JOIN BOMcte AS cte

    ON WQ.ParentPartId = cte.PartId

)

SELECT Lvl, Name, Color, Quantity, Sort

FROM BOMcte

ORDER BY Sort;

 

Figure 8. Parts explosion query with the computed sort key values shown.

As you can see, the sort key trick computes the full path from the root node to the node where the component is located. Ordering the output on the path (i.e., sort key) allows the indented results to appear in the desired order.

If you want to practice with AdventureWorks BOM queries yourself, I provided code samples for a parts explosion, a where used query, and an accumulated quantity query.

If you'd like to play with virtual Legos, download Lego Digital Designer from here. You can order what you built and Lego will ship you the parts.

AdventureWorks2008 uses the hierarchyid data type in a updated version of the HumanResources.Employee table, but not in the Production.BillOfMaterials table. Michael Coles provides the code for a clever CTE to create Production.HierBillOfMaterials  from Production.BillOfMaterials in his book Pro T-SQL 2008 Programmer’s Guide (which I recommend). Another way to create the table is by using the brute force approach of running the 8,827 insert statements I provided in the attached zip file. The reason I call attention to size of the table is that the original Production.BillOfMaterials table is 2,679 rows. The value in looking at the same data implemented two different ways is to compare and contrast the differences. Once you can see and understand the differences, then you can choose which approach best suits your needs.

I’m using the Production.HierBillOfMaterials  table in my SQL PASS 2009 presentation next week. It stores the bill of materials data using hierarchyid instead of an adjacency list pattern. Another good book is Paul Nielsen’s SQL Server 2008 Bible, which covers hierarchies in a chapter you can download for free.

The original Production.BillOfMaterials table has StartDate and EndDate columns to store historical data about a bill of materials. The Production.HierBillOfMaterials table uses the hierarchyid data type but does not have StartDate and EndDate columns because it doesn’t maintain history. Supporting history in a bill of materials isn’t a technical problem to be solved. It has significant business and even potential legal implications. If a customer is using widget X and orders another widget X a few years later after some of its subassemblies have changed, the customer might not be happy to see that widget X has changed. I met with people from one manufacturing company who told me they think it is better to define a bill of materials as immutable. If the bill of materials must be changed, they just create a new bill instead of creating historical versions of the same bill. Different customers will reach different conclusions on how to handle history.

After working with the same bill of materials implemented with hierarchyid and an adjacency list pattern, I prefer the adjacency list pattern for a bill of materials.