Joseph Sack's SQL Server Blog

SQL Server 2008 Table of Contents

Per an earlier post - here is a link to a full version of the table of contents for "SQL Server 2008 Transact-SQL Recipes." Feel free to post comments if you have questions about the table of contents or topics listed.

Today I got the hard copy of SQL Server 2008 Transact-SQL Recipes

** Cross-posted from www.joesack.com **

I came home to find a hard copy of my new book waiting for me. This is always an exciting and strange moment to finally hold the finished product in my hand. I'm pretty happy with the result, and I feel it reflects the problem/solution theme I was aiming for. It is always a little daunting once a book is printed, because you have to hand it off to the world and hope that people find it useful. You kind of have to just give in, hope for the best, and move on to the next project.

It is a bigger book than last time (last book was 733 pages, this one is 839). Although it was an update - it still took 9 months, but they were high quality hours and I enjoyed a good portion of it.

Looking through it today - I was disappointed to see that two of my requested changes that I submitted to the publisher in June didn't get integrated into to Chapter 2 - so I'll blog about them here so you can know to look for them. There were some syntax changes that were made in the Release Candidate that differ from CTP 6. The two changes that didn't get in for whatever reason are as follows:

• Page 91 - the CTP6 version of the MERGE syntax, instead of the RC0 version was used. Here is the corrected, RC0 version of the syntax:

MERGE INTO HumanResources.CorporateHousing p USING dbo.StagingCorporateHousing s ON p.UnitNBR = s.UnitNBR
WHEN MATCHED AND s.IsRentedIND <> p.IsRentedIND THEN
UPDATE SET IsRentedIND = s.IsRentedIND
WHEN NOT MATCHED BY TARGET THEN
INSERT (UnitNBR, IsRentedIND) VALUES (s.UnitNBR, s.IsRentedIND)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

• Page 98 - the @partition_switch argument should be removed from sys.sp_cdc_enable_table

The rest of the RC0 changes I made and requested seem to be there, so I'm happy. We'll see if there are any RTM changes I need to blog about. With 800 pages - the possibilities are endless. :)

SQL Server 2008 Transact-SQL Recipes

** Cross-posted on www.joesack.com **

My new book, "SQL Server 2008 Transact-SQL Recipes" is coming out in a few weeks (estimated publication at the end of July). As of July 1^st, Amazon.com is selling it for $37.70. They don’t have a picture of the cover up yet, but it will have the standard bumble-bee color scheme Apress cover, so expect no surprises there. J

Although this was an update of my last book, it still took 9 months to write (plenty of weeknights and weekends holed up in my office). I updated existing content, and added content for both SQL Server 2008 and SQL Server 2005 SP2 additions.

Like my last book, I focus specifically on the Transact-SQL language. Even with the focus on T-SQL and not the GUI, the book wound up being greater than 800 pages. Also like the last book, I use a problem/solution format. The overall theme and mission statement for this book was:

"Look up what you need to do. Learn how to do it. Do it."

I'll have a downloadable detailed index link available after the publication date. That link will include the specific recipes for each chapter. In the meantime, here is a high level list of the chapters:

CHAPTER 1 SELECT

CHAPTER 2 Perform, Capture, and Track Data Modifications

CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking

CHAPTER 4 Tables

CHAPTER 5 Indexes

CHAPTER 6 Full-Text Search

CHAPTER 7 Views

CHAPTER 8 SQL Server Functions

CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors

CHAPTER 10 Stored Procedures

CHAPTER 11 User-Defined Functions and Types

CHAPTER 12 Triggers

CHAPTER 13 CLR Integration

CHAPTER 14 XML, Hierarchies, and Spatial Data

CHAPTER 15 Hints

CHAPTER 16 Error Handling

CHAPTER 17 Principals

CHAPTER 18 Securables, Permissions, and Auditing

CHAPTER 19 Encryption

CHAPTER 20 Service Broker

CHAPTER 21 Configuring and Viewing SQL Server Options

CHAPTER 22 Creating and Configuring Databases

CHAPTER 23 Database Integrity and Optimization

CHAPTER 24 Maintaining Database Objects and Object Dependencies

CHAPTER 25 Database Mirroring

CHAPTER 26 Database Snapshots

CHAPTER 27 Linked Servers and Distributed Queries

CHAPTER 28 Query Performance Tuning

CHAPTER 29 Backup and Recovery

I enjoyed writing this book; but most importantly I hope you find it to be a useful, practical reference.

Example of how SET options impact the Query Optimizer index choice

I put together a scenario that demonstrates how a query session’s SET options can impact which index is used by the Query Optimizer. In this example, I’ll start off by creating a schema-bound view in the AdventureWorks database:

CREATE VIEW dbo.vw_SalesOrderDetail
WITH SCHEMABINDING
AS
SELECT SalesOrderID, SalesOrderDetailID,
CarrierTrackingNumber, ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE LineTotal BETWEEN 35.000 AND 50.000
GO

Next, I’ll create two indexes on the view I just created. The first index is a unique clustered index on the composite key of the underlying base table, and the second index is nonclustered on the CarrierTrackingNumber column:

CREATE UNIQUE CLUSTERED INDEX
idx_vw_SalesOrderDetail_Unique ON
dbo.vw_SalesOrderDetail (SalesOrderID,
SalesOrderDetailID)
GO

CREATE INDEX
idx_vw_SalesOrderDetail_CarrierTrackingNumber ON
dbo.vw_SalesOrderDetail (CarrierTrackingNumber) GO

Now I’ll show the estimated query execution plan for a query that selects against the base table (on top of which I created the view and associated indexes):

SET SHOWPLAN_ALL ON
GO

SELECT SalesOrderID, SalesOrderDetailID,
CarrierTrackingNumber, ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = ‘8E3A-4564-99′ AND
LineTotal BETWEEN 35.000 AND 50.000

The argument column of the 4th row of the SHOWPLAN_ALL recordset reveals an Index Seek against the following object:

OBJECT:([AdventureWorks].[dbo].
[vw_SalesOrderDetail].
[idx_vw_SalesOrderDetail_CarrierTrackingNumber]), SEEK:([AdventureWorks].[dbo].
[vw_SalesOrderDetail].[CarrierTrackingNumber]=N’8E3A-4564-99′) ORDERED FORWARD

Even though I was just querying the base table - the query optimizer was able to figure out that the index on the CarrierTrackingNumber column for the indexed view would be a good choice for the query.

Now I'll show the estimated query again - this time designating a different SET option from my session default:

SET SHOWPLAN_ALL ON
GO

SET QUOTED_IDENTIFIER OFF
GO

SELECT SalesOrderID, SalesOrderDetailID,
CarrierTrackingNumber, ProductID, LineTotal FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = ‘8E3A-4564-99′ AND
LineTotal BETWEEN 35.000 AND 50.000

Looking in the SHOWPLAN_ALL result set - the Index Seek we saw before is missing - replaced by a Clustered Index Scan against the base table:

OBJECT:([AdventureWorks].[Sales].
[SalesOrderDetail].
[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID])

What happenend? Index views require that the QUOTED_IDENTIFIER be set ON when the view is created (as well as some other SET options documented in BOL). In order for the Query Optimizer to use the index on the view, these SET options need to match. If they don’t - the Query Optimizer doesn’t consider the index for the query execution plan.

Cross-posted from www.JoeSack.com.

Identify implicit data type conversions using XQuery

It has been a long time since I have posted, but in 2008 I will try to be better about it. I've been regularly blogging at www.joesack.com - however I'll be cross-posting to SQLJunkies.com again too (now that I found my login and password).

On to the topic at hand...

Implicit data type conversion is a non-obvious operation that can eat away at your query performance. Let’s say you have a column called “EmployeeID” in your database. This is recognized as the employer identifier, and it is understood by the application team that an EmployeeID in Table “A” is the same as an EmployeeID in Table “B”. However one database developer uses a bigint for Table “A” and maybe a varchar for Table “B”. You have just built in a hidden cost to all queries that join Table “B” and Table “A” on EmployeeID. Add millions of rows to those tables, and you have just added a potentially significant cost.

How do you identify data type mismatches for the same column then? One method is to look at INFORMATION_SCHEMA.columns and look for all matching names that have varying data types. We can also check INFORMATION_SCHEMA.parameters and see any parameter names that have varying data types. The flaw here though, is that we depend on matching names. If the database designer named a column “EmpID” and another “EmployeeID” - we will have to be very familiar with the database and application design in order to know for sure that these two columns really represent the same thing. We are also limited to checking persisted objects - tables and procedures for example - and not ad hoc queries that contain implicit data type conversions in temporary tables or operations passing values from one query to another…

One solution introduced in SQL Server 2005, is the ability to use XQuery to probe the contents of an execution plan. If an implicit data type conversion occurs for a query, we can identify it in the XML query plan. I created the following query that uses the “exists” method to check for any query plan that has the implicit attribute set to “1″. I order it by worker time, looking for those queries with significant CPU usage. I also look at execution count - for those queries that are executed frequently:

SELECT qs.total_worker_time,
SUBSTRING(st.text, qs.statement_start_offset/2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
as SQL_Text,
qp.query_plan,
qs.execution_count,
st.dbid,
st.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan
(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) st
WHERE query_plan.exist
(’declare default element namespace “http://schemas.microsoft.com/sqlserver/2004/07/showplan“;
//Convert/@Implicit[. = “1″]’) = 1
ORDER BY qs.total_worker_time DESC


Once you have identified the higher cost plans, you can validate the predicates and see where the implicit data type conversions are occurring and then address accordingly.

SQL Server 2005 Instant File Initialization

I was asked about instant file initialization for SQL Server 2005 yesterday, and also saw a similar question/response on a newsgroup, so I thought I would drop a quick reminder here as well.  As you may already know, in SQL Server 2005 data files are initialized immediately when created.  Unlike with SQL Server 2000, SQL Server 2005 does not fill the files with zeros during creation or expansion.  For large files, skipping this operation can save quite a bit of time.

Notice, however, that the instant file initialization feature applies to data and not transaction log files.  If you have large LDF files the must be created, expanded, or restored, you'll still take a performance hit.

Radio Silence

Not that I've been that great about posting to my blog, but I've been particularly silent lately due to a few big life changes.  One of the life changes is my new job with Microsoft.  I became an employee three weeks ago, working for the Consulting for IT Operations group. 

I'm having a great time and am busily trying to sift through the fire hose of information. As such, I'll be keeping radio silence on my blog for the forseeable future.

I have to say that SQLJunkies has been invaluable to me and I appreciate all the contributors (especially those who posted regularly - unlike myself)!  Thank you Donny and Doug!

- Joe

 

Quick Reference Cards in PDF format

Okay - although this is tangential to SQL Server itself, I thought this link may be of interest for those SQL Server professionals that dabble in other programming paradigms.  This site includes links to quick reference cards (in PDF format).  Syntax references for JSP, C, Java, C++, Perl, PHP, UML, XML and more.  I'm sad that Transact-SQL isn't in that list, nor .NET - but perhaps someone out there in web-land will fill that gap.

Don't use disk compression with SQL Server files

Using compressed volumes with SQL Server can impact both performance and recoverability.  For more details, check out the Microsoft Knowledge Base Article:

http://support.microsoft.com/kb/231347/en-us

 

 

SQL Profiler Events: The difference between RPC:Completed and SP:Completed

Ever wondered what the difference was between the RPC:Completed and SP:Completed SQL Profiler events?  Andrew J. Kelly, SQL MVP, gives an excellent explanation here.

Complex systems and SQL Server

"If you manage a complex system you will frequently, if not always, be wrong." This is a quote from Michael Crichton’s speech "Fear, Complexity, & Environmental Management in the 21st Century".

In his speech, he talks about how it is human nature to make linear assumptions about non-linear, complex systems.

Although he discussed this within the context of global, environmental, political, and biological issues – it got me thinking about the feature expansion of SQL Server 2005 and performance tuning/optimization. 

As SQL Server professionals we can make a certain amount of fair assumptions.  We can coin "best" and "worst" practices - and suggest design and architecture choices that, on average, yield optimal results under certain circumstances. 

SQL Server performance tuning, however, is one of the more slippery topics.  A "my query runs too long" issue from an end-user could be the result of several factors such as hardware, network, database design, sizing, query construction, and concurrent query traffic.  Embedded within each of these factors is an array of other considerations - statistics, fragmentation, cursor-usage versus set-processing, query hints, sp_configure settings, locking...

Unlike complex systems like weather or government, you do have much more control over how your SQL Server instance is configured. Also, over time a SQL Server professional develops a repertoire of troubleshooting patterns – problem/responses that build up over time, allowing him or her to fix issues quickly.  There is both danger and benefit from this gained experience.  The benefit is that you fix issues more quickly than someone who hasn’t experienced the problem before.  The danger/drawback is that you may lead yourself down the wrong path without considering other avenues.  Even after eight years of working closely with SQL Server, I still try to collect a sufficient amount of information before coming to a conclusion.  Snap-judgments based on a single email complaint or discussion can often lead to incorrect assumptions.

Michael Crichton says that when you make predictions on complex systems which are incorrect, that "you have to backtrack. You have to acknowledge error. You’ve probably learned that with your children. Or, if you don’t have children, with your bosses." 

Being a SQL Server professional, there is always more to learn (which is why I love this profession) - but if you are incautious in your troubleshooting approach, you'll soon be humbled.  This is the reality of a complex system.

RS T-SQL tip: Identify which reports are being emailed via subscriptions in Reporting Services 2000

If you need to identify which reports are emailed to specific users (either to remove them from the subscription - or just as an FYI), you can execute the following query in the ReportServer, SQL Server 2000 Reporting Services database:

SELECT [Name] ReportName
FROM ReportServer.dbo.Catalog
WHERE ItemID IN (SELECT Report_OID
   FROM ReportServer.dbo.Subscriptions
   WHERE ExtensionSettings LIKE '%joesack@test.com%')
ORDER BY Name

Wildcards are used as the email is embedded in an XML format in the ExtensionSettings column. The query example then returns any reports with a subscription email to joesack@test.com.

SSIS BUG - Migrate DTS 2000 Package fails when pulling packages with trailing blanks

So today I was testing out the SSIS "Migrate DTS 2000 Package" wizard.  I went through the following steps:

1.  In BIDS, I right-clicked the SSIS Packages folder in the Solution Explorer.

2.  Selected Next at the Wizard intro.

3.  Selected the source location (where I'll be pulling the packages from).

4.  Selected the destination location (the folder where the converted packages will reside).

On the next dialog box I'm supposed to see a list of DTS 2000 packages on the source SQL Server instance, but instead I get the error:

"Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index (mscorlib)"

After digging through the newsgroups, I found a tip that this was caused by trailing blanks in the DTS package names.  I ran the following query to identify the offending packages:

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

After renaming the packages (removing trailing blanks) - the List Packages dialog box worked!  Thanks Koni Kogan!

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach

 

My new book "SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach" has been printed and will be shipped in less than two weeks (December 12th).  You can pre-order it now. As of today, Amazon is selling it for $37.79 at this link. Amazon hasn't updated the book information with the latest page count and description - but this should be updated soon. In the meantime, you can check out the up-to-date book specs on Apress here.

On a side note - my Grandmother told me she doesn't like the author photo. I told her that luckily it will be on the back of the book, and not the front.

I wrote SQL Server 2005 T-SQL Recipes in a problem/solution style, including hundreds of "How To" scenarios for SQL Server development and administration tasks. The table of contents is as follows (I don't include the detailed chapter break-out here - as that's several pages long):

CHAPTER 1 SELECT

CHAPTER 2 INSERT, UPDATE, DELETE

CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking

CHAPTER 4 Tables

CHAPTER 5 Indexes

CHAPTER 6 Full-Text Search

CHAPTER 7 Views

CHAPTER 8 SQL Server Functions

CHAPTER 9 Conditional Processing, Control-Of-Flow, and Cursors

CHAPTER 10 Stored Procedures

CHAPTER 11 User-Defined Functions and Types

CHAPTER 12 Triggers

CHAPTER 13 CLR Integration

CHAPTER 14 XML

CHAPTER 15 Web Services

CHAPTER 16 Error Handling

CHAPTER 17 Principals

CHAPTER 18 Securables and Permissions

CHAPTER 19 Encryption

CHAPTER 20 Service Broker

CHAPTER 21 Configuring and Viewing SQL Server Options

CHAPTER 22 Creating and Configuring Databases

CHAPTER 23 Database Integrity and Optimization

CHAPTER 24 Maintaining Database Objects and Object Dependencies

CHAPTER 25 Database Mirroring

CHAPTER 26 Database Snapshots

CHAPTER 27 Linked Servers and Distributed Queries

CHAPTER 28 Performance Tuning

CHAPTER 29 Backup and Recovery

I hope that readers find it useful, and I'm looking forward to hearing the feedback.

SQL Server 2005 System Views Diagram in SQL Server Magazine

I just got the December copy of SQL Server Magazine, and it includes a big pull-out SQL Server 2005 System Views Diagram.  I'll be bringing it to work so I can scare and impress my co-workers.  It separates out the views using the following categories:

Objects, Types & Indexes, Linked Servers, Common Language Runtime, Partitioning, Trace and Eventing, Databases and Storage, Server-wide Information, Transaction Information, Server Wide Configurations, Security, Endpoints, Database Mirroring, and Service Broker.  Object types are orange for Dynamic Management Views, blue for Catalog Views, and purple for System Administration views. 

If anyone is aware of a digital copy of this diagram - let me know (I looked for it - but not very hard).

 

 

Reporting Services Annoyance #1

When designing a report in RS 2000, I noticed that when editing a query in the Data tab in Visual Studio that my query changes don't always "stick".  I put in the change, save it, deploy the report, and then see that my query changes didn't get saved.

Well it turns out if I make a change to an existing query, I need to press the "refresh fields" button.  This is the button that looks like the recycling symbol.  Once I press this, I see an asterisk appear by the name of my project on the window header, which means when I save the project the query change actually gets saved with it.

If anyone has a better method for making RS recognize a query change - please let me know.  I'm hoping RS 2005 doesn't have this same quirk.

Almost done...

I'm almost done with the book.  I've got one 2nd draft, two 3rd drafts, four copy-edit drafts, and ten production edit drafts left.  I know that may sound like a lot, but it isn't - compared to how things looked back in February. 

I'm really looking forward to working just 40 hour weeks again.  Since February I've been working 70 hours a week: 40 hours at my day job, and then 30 hours a week on the book. 

I've been thinking about the "simple" things I would like to do once I'm finished with the book... Things I took for granted before:

1. Buy food at a grocery store. 

2. Lose the ten pounds I gained from the extra 30 hours of desk time.

3. Visit out-of-state family members who think I'm using the book as an excuse not to visit.

4. Watch all the reality TV that has been stacking up on my Tivo.  It's time to dust off my treadmill and kill a few brain cells with America's Next Top Model.

5. Fill up on gas for my car and not think "but in the time it takes to fill up on gas, I could edit 2 pages? Wait until next week when you're running on vapors."

6. Enjoy the Minnesota winter.

7. Start blogging consistently, instead of just twice a month.

8. Read something that doesn't have anything to do with SQL Server 2005.  I'm sorry, SQL Server, but I think we really need to start dating other people.  It's not you, it's me.

Okay, back to Chapter 28...