T-SQL Archives - Page 22 of 26

04Mar 2009 by Grant Fritchey 7 Comments

Bad Performance Tip

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,…

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…

27Feb 2009 by Grant Fritchey No Comments

Dissecting SQL Server Execution Plans Chapter 3

SQL Server, T-SQL

The third chapter has been published over at Simple Talk. So if you're interesting the book, you can go to Red Gate to get a free e-book copy of it, or you can see chapters one and two and now three over at Simple-Talk. And for those who just feel the need to kill a tree, I'm told we should have a print version real soon now and available from Amazon.

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…

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…

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…

04Feb 2009 by Grant Fritchey No Comments

Phil Factor and the Shootout

Misc, T-SQL

http://www.simple-talk.com/opinion/opinion-pieces/phrenetic-phil-and-the-sql-shootout/ Uh... OK. Thanks Phil. Nice one? At least pork chops were not involved.

27Jan 2009 by Grant Fritchey 3 Comments

New England Data Camp Evals

PASS, T-SQL, Tools

Anyone reading this who attended the New England Data Camp and filled out an eval, for any of the sessions, thanks. For those 63 evals between the two sessions that I received, thanks. Here are the aggregates on my sessions: Using Visual Studio Team System Database Edition: Average of Knowledge 8.344827586 Average of Presentation 8.482758621 Average of Preparation 8.103448276 Average of Interesting 8.172413793 Average of Overall 8.275862069 Number of Submissions 29 Understanding Execution Plans Average of Knowledge 8.647058824 Average of Presentation 8.617647059 Average of Preparation 8.705882353 Average of Interesting 8.529411765 Average of Overall 8.625 Number of Submissions 34 These are all on a scale of 1-9. I'm really quite happy with the results. Here are the average results for all the speakers and all the sessions at the Data Camp:…

19Jan 2009 by Grant Fritchey No Comments

Choosing the order of columns in an index

T-SQL

It's kind of scary to see someone else put down thought processes that could have been your own. That's what Gail did with this post. It's worth a close read because it's offering very good advice and supplying the reasoning behind that advice.

13Jan 2009 by Grant Fritchey No Comments

Red Gate Crib Sheet Compendium

SQL Server, T-SQL

Red Gate has compiled a bunch of it's Cribsheets into a single E-book, the SQL Server Cribsheet Compendium. It's pretty cool. I've got two entries in there, performance tuning and backups & restores, along with great articles from Robyn Page, Phil Factor, Robert Sheldon and Amirthalingam Prasanna, pretty heady company. It's worth a look.

Category: T-SQL