Technical Editor

March 14, 2009 at 6:20 am (SQL Server 2008)

Joseph Sack was the technical editor for my book. He also happens to be a great guy. I learned a lot from him as we went through the book. Where I didn’t learn from him, he kept me focused, always pointing out where I was slipping off the 2008 track. Whatever worth the book holds, a lot of it comes from him.

Oh, and he writes, all on his own. So it’s not like he needs me at all.

Permalink Leave a Comment

It’s a book!

March 13, 2009 at 7:03 pm (SQL Server 2008)

I just received my copies of the book. “SQL Server 2008 Query Performance Tuning Distilled” is an honest to gosh, in print, available on Amazon
and Barnes & Noble BOOK!
Sorry, just need a little time for a victory dance on the table. Huzzah!

Permalink 20 Comments

Disabling Database Encryption

March 13, 2009 at 10:48 am (SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , )

SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I’ve seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I’m working on:

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Chapter8Backup’;

GO

CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = ‘Chapter 8 Certificate’

GO

USE AdventureWorksLT

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate

GO

ALTER DATABASE AdventureWorksLT

SET ENCRYPTION ON

GO

That sets up the encryption and turns it on. Now when you run a backup, you need to move the certificate files with the backup in order to restore the backup to another machine. Cool stuff. What happens when you realize you don’t need to encrypt a backup? Simple, run the ALTER DATABASE to set encryption off. One of the posters at SQL Server Central, Amit Lohia, asked that same question and had the code to test it. What happens is, the backups taken after you disable encryption fails.

Msg 3283, Level 16, State 1, Line 1
The file “TDEDisabling_log” failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

Examining the logs just shows the same error message. So a little searching around on the web found this on Connect (I do love that site). Apparently the certificate would have to be on the other server unless you removed it prior to backing up. Fine. Let’s drop the certificate.

 DROP CERTIFICATE Chapter8Certificate

Unfortunately, that didn’t work either. You can check the status of encryption using the dynamic management view sys.dm_database_encryption_keys like this:

SELECT

db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

 

That will show your database is not encrypted, a value of zero in the encryption_state column, but still listed on the encryption list. If you were dealing with a really large database, you can check that view to watch the status of encryptiong being turn on or turned off. Still, it’s that listing on the encryption view that seems to be the problem. I’ve looked and looked and I don’t have an answer. So I posted my own issue on Connect. We’ll see what MS has to say about it.

I’ll post any answers I get. If anyone has any good ideas, I’m all ears.

Permalink 2 Comments

PASS Call for Speakers, March 20th

March 11, 2009 at 7:37 am (PASS) (, , , , )

In case you don’t read the Connector when it comes out (and delete it like I used to), they’ve announced that the call for speakers is going to open on March 20th and close on April 3rd. No links to the web site yet. That will be in the Connector and on the PASS web site when they open it.

I’ve got three presentations that I’m putting in. I hope everyone else is ready with theirs.

Permalink 8 Comments

A Fix for nHibernate’s Parameter Problem

March 11, 2009 at 6:26 am (nHibernate, Visual Studio) (, , , )

I had previously outlined a problem with nHibernate code that, in my opinion, was going to cause a lot of problems on production systems especially with memory and the procedure cache. However, it does appear that there is a possible solution. That doesn’t eliminate my concerns over nHibernate, but it sure does help.

Permalink 1 Comment

Code for 2008 Transact-SQL Recipes

March 10, 2009 at 6:44 am (SQL Server 2008, TSQL)

I’ve been reading through Joseph Sack’s book “SQL Server 2008 Transact SQL Recipes” for the past few days. Especially since he saved my bacon so thoroughly by being the technical editor on my new book (and might I add, the technical editor on the other book, Brad McGehee, saved my bacon as well and bacon is very important to me). Anyway, the code is availablefor the SQL Recipes book. If you’ve even looked at the book, the shear volume of code samples is amazing. Having Joe save you the typing is a second service (the first being the book itself).

Permalink Leave a Comment

Bad Performance Tip

March 4, 2009 at 2:23 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )

I saw a performance tip that just didn’t make any sense to me:

In cases where you are using the IN clause, try to order the list of values so that the most frequently found values are placed first.

That just didn’t make any sense to me. The IN clause is not like EXISTS where the query will stop as soon as it finds a good match. So I set up a test with AdventureWorks. I found a few different ProductId values, the highest, the lowest and a few in between and ran a very simple query to test this tip:

/*
ProductID        RowCount
870                        4688 rows
877                        1327 rows
972                        380 rows
823                        148 rows
723                        52 rows
897                        2 rows*/

DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT  sod.ProductID
       ,sod.SalesOrderDetailID
FROM    Sales.SalesOrderDetail AS sod
WHERE   sod.ProductID IN (870, 877, 972, 723, 897)
GO
DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT  sod.ProductID
       ,sod.SalesOrderDetailID
FROM    Sales.SalesOrderDetail AS sod
WHERE   sod.ProductID IN (897, 723, 972, 877, 870)
GO
DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT  sod.ProductID
       ,sod.SalesOrderDetailID
FROM    Sales.SalesOrderDetail AS sod
WHERE   sod.ProductID IN (972, 870, 877, 897, 723)

It resulted in three identical execution plans, every one of them a nonclustered index seek. In the three tests, the most frequently found values are first, last & mixed. All three queries had I/O that looked like this:

(6449 row(s) affected)
Table ‘SalesOrderDetail’. Scan count 5, logical reads 26, physical reads 7, read-ahead reads 37

And the execution times were:

Query     Compile     Run
1           45ms        47ms
2           14ms        28ms
3           4ms          30ms

I then ran all three again, in reverse order:

Query     Compile     Run
3           34ms        52ms
2           25ms        46ms
1           5ms          25ms

The times scattered around the same values, but were essentially the same. No change in the order of the data affected the query positively or negatively. So where does this tip come from?

If people post a simple list of tips but don’t explain what they mean, show some sample code or in any way attempt to educate you as to why you should do X in your queries, I would not trust it.

Permalink 7 Comments

Pay for it!

March 3, 2009 at 1:52 pm (Tools)

For those searching for the string “sql prompt 3.8 | rapidshare,”  that happened to somehow come by my blog, twice, I just looked it up on the web site. SQL Prompt is only $195. If you’re professional enough to need it, surely you can afford to pay the licensing fee. Seriously now. It’s not like Red Gate is some gigantic corporate entity that would be incapable of noticing that you just robbed them of a single license. Reg Gate is a company so small and personal that I, who have never worked for them a day in my life, have met the CEO. Great guy too.

I do understand where you’re coming from. Long, long ago I worked in tech support and only did programming on the side.  I “borrowed” a copy of the programming language I was using back then because I flat out couldn’t afford it and I needed time at night to practice. I appreciate that. But this tool is not an entire programming language, it’s a utility designed to make your full-time programming life easier. I understand why you want it, because, frankly, it works really well. But, since I know, if you need this tool, you can afford to pay for it, just pony up the cash. It’s not going to break the bank. Skip buying a copy of Halo Wars this week and pick it up used next week when you can save about $15.

Just stop trying to steal from a hard working bunch of people who’ve earned the right to charge you one heck of a lot more than they’re charging. Loser.

Permalink 4 Comments

MSDN Magazine: Article on VSTS:DB

March 3, 2009 at 9:49 am (Visual Studio) (, , , , )

Jamie Laflen and Barclay Hill have published an article in MSDN Magazine outlining the new features in in VSTS:DB GDR. The description of the intent and use of the server project is extremely enlightening. I wasn’t aware of the master.dbschema files available for use within a project. Luckily I haven’t needed them yet. Another thing I wasn’t aware of, if you use the refactoring tools, say rename a table, not only does it save you a lot of typing, but the project will remember that the table was renamed and instead of dropping and recreating it in the next deployment, it will issue SP_RENAME. I’m spreading that word to my team right now. Another good point is that you can make a configuration to work only on your local machine by setting the “Configuration deployment settings for:” to “My isolated development environment.”

Great article guys. Thanks for putting it out there.

Permalink 2 Comments

More Refinements on the Missing Indexes Query

March 2, 2009 at 8:03 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , )

Greg Larson posted a really nice query to find worst performing stored procedures in your system. He put in all kinds of attributes to make it customizable, changing the definition of “worst” to different measures,etc. Great query in general. In it he linked to sys.dm_exec_plan_attributes and got the db_id attribute. Duh! So instead of spelunking through the XML to retrieve the database name, I can pull the db_id and use the DB_NAME function. Cleans things up considerably. Thanks Greg.

Here’s the cleaned up code:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
AS sp)
SELECT DB_NAME(CAST(pa.value AS INT))
,s.sql_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
--,s.query_hash
,p.query_plan
,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]',
'NVARCHAR(256)') AS TableName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]',
'NVARCHAR(256)') AS SchemaName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]',
'DECIMAL(6,4)') AS ProjectedImpact
,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM (SELECT TOP 20
s.sql_handle
,s.plan_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
--,s.query_hash
FROM sys.dm_exec_query_stats s
ORDER BY s.total_elapsed_time DESC
) AS s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup')
AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) pa
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
AND pa.attribute = 'dbid'

Permalink 4 Comments

« Previous page · Next page »