10Mar 2009 by Grant Fritchey No Comments

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
04Mar 2009 by Grant Fritchey 7 Comments

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
03Mar 2009 by Grant Fritchey 4 Comments

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
03Mar 2009 by Grant Fritchey 2 Comments

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
02Mar 2009 by Grant Fritchey 9 Comments

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
26Feb 2009 by Grant Fritchey No Comments

Shrinking Databases

SQL Server
Tim Ford put up a hilarious blog post outlining an instance where shrinking databases is acceptable. I'm pretty sure the development method he describes is well documented and in use in more than one location around the world, including somewhere in my company. Read it.
Read More