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

Three Kinds of Execution Plans

SQL Server, T-SQL
You read that correctly, three kinds of execution plans. You may have thought that all you had to deal with are estimated and actual, but there is one more. The estimated plan is the plan that comes out of the optimizer. It's based on statistics and indexes and known objects within the system. The actual plan is the plan that was used to execute the query and will show all the actual number of rows processed, etc. It might be different than the estimated plan because the stastics were off or for any number of other reasons. Those were the ones you knew about. There is also the plan that gets stored in the plan cache, the compiled plan. I lied. The compiled plan and the estimated plan are the…
Read More
23Feb 2009 by Grant Fritchey 34 Comments

Public Speaking

PASS
A great post on the how's, why's, etc., of presenting by Paul Randal. I found it because of Database Weekly. Some of the discussion are things I've read about in other places with more more or less detail. But please, don't think I'm knocking it in any way. It's a unique look at presenting from someone who does quite a lot of it. There were a number of topics and ideas that I hope I can apply going forward. I understand that PASS is going to open the gates for abstracts soon. I think I've got three ready this year, although one of them is a bit rocky. Still, armed with a more knowledge than I had, thanks to Paul Randal's help, I think I can do better.
Read More