07Apr 2009 by Grant Fritchey 3 Comments

SQL Server Standard Article Available

PASS, SQL Server, T-SQL
Unfortunately PASS decided to put the SQL Server Standard to sleep right after I got an article published in it (and no, it wasn't my fault). However, PASS, being the service oriented organization that they are, has decided to make back issues of the magazine available online. So, to read the article I wrote comparing various methods of retrieving versioned data using different TSQL constructs, click this link. Go to page 14. Oh yeah, and you can see other peoples articles here too. There are other things coming out of the editorial committee soon (although I need to get one of them done myself... yikes).
Read More
07Apr 2009 by Grant Fritchey 6 Comments

NULL Is Not NULL

SQL Server, T-SQL
NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not "not equal" anything either. It can't be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used "WHERE nt.NullableString NOT LIKE 'null%'" was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or "not equal"…
Read More
26Mar 2009 by Grant Fritchey No Comments

INNER JOIN Experiment in Order

SQL Server, T-SQL
And a new blog. Christopher Stobbs, one of the frequent posters over at SSC decided to try his hand at blogging. His first post is up and it's pretty interesting. He ran an experiment with all different orders of tables, largest to smallest, smallest to largest to see if the order in the joins made a difference. For the answer, click
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
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
18Feb 2009 by Grant Fritchey 24 Comments

Refining the Missing Index Data

SQL Server, T-SQL
In my previous post I showed how you could determine if a query had missing index information, but it was difficult to get meaningful data out because I didn't know XQuery well enough. I spent some more time refining the query and here are the results. This new query will show some basic performance information gathered from the aggregate data in sys.dm_exec_query_stats. It combines this with the full data pulled from the Missing Indexes element in the XML of the execution plan. I've got it listing all the recommended columns and grouping. So this means that the performance data is repeated in order to allow for the full listing of groups & columns. It seems to work well. A couple of interesting points. My purpose is to provide a short-cut…
Read More
12Feb 2009 by Grant Fritchey 11 Comments

Missing Index Information and Query Stats

SQL Server, T-SQL
So the goal was to find a way to pull information from sys.dm_exec_query_stats so that we could identify poor performing procedures that were in cache at the moment and combine it with missing index information from sys.dm_db_missing_index_details. We're combining these because we're working with a Microsoft Dynamics CRM database that is almost all ad hoc queries and lots of them are against tables with missing indexes. The hope was to identify necessary indexes merely by looking at the longest running queries. Unfortunately there is no way to combine data from the missing indexes set of DMV's and all the execution DMV's that show query stats, execution plan, etc. None of the missing index tables has a plan handle or a plan hash column that would allow you to combine that…
Read More