Technical Editor

Uncategorized
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.
Read More

It’s a book!

Uncategorized
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!
Read More

Disabling Database Encryption

T-SQL
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…
Read More

PASS Call for Speakers, March 20th

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.
Read More

Code for 2008 Transact-SQL Recipes

T-SQL
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).
Read More

Bad Performance Tip

SQL Server, T-SQL
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,…
Read More

Pay for it!

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…
Read More

MSDN Magazine: Article on VSTS:DB

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…
Read More

More Refinements on the Missing Indexes Query

SQL Server, T-SQL
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…
Read More