March 23, 2010 at 7:22 pm
Should the inequality_columns in the above DMV be included in the index keys or avoided?
Dan
March 23, 2010 at 10:37 pm
Index key.
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2010 at 12:23 pm
you make it look easy 🙂 I will include them in the key, but be careful of the order.
Do you take the missing_index_details recommendation at face value, or do you always dig up the query for which the reco is made, and then analyze the above scenarios as in the link and then implement?
March 24, 2010 at 1:33 pm
repent_kog_is_near (3/24/2010)
Do you take the missing_index_details recommendation at face value,
Never.
or do you always dig up the query for which the reco is made, and then analyze the above scenarios as in the link and then implement?
That and check existing indexes on the table, maybe one can be widened. Also, if I have time I check all other queries that run against the table. Maybe with a slight mod I can get an index that's useful for multiple queries, not just one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2010 at 8:46 pm
>>Also, if I have time I check all other queries that run against the table.
Do you run a profiler for this? How do you get exact queries running against a table?
March 24, 2010 at 8:57 pm
Profiler is one method. You could also search your stored procs to find any instances of that table.
If code is hitting your database from the application and is not in proc form, then profiler would be the better bet.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 24, 2010 at 9:08 pm
>>If code is hitting your database from the application and is not in proc form, then profiler would be the better bet.
Can you filter in profiler by tablename ( I know you can filter by database). I know searching by keyword after the profiling is possible, but is it possible to filter at the time of profiling?
March 24, 2010 at 10:09 pm
repent_kog_is_near (3/24/2010)
>>If code is hitting your database from the application and is not in proc form, then profiler would be the better bet.Can you filter in profiler by tablename ( I know you can filter by database). I know searching by keyword after the profiling is possible, but is it possible to filter at the time of profiling?
You should be able to use the ObjectName filter for that.
http://msdn.microsoft.com/en-us/library/ms979207.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2010 at 2:09 am
repent_kog_is_near (3/24/2010)
Can you filter in profiler by tablename ( I know you can filter by database).
Typically no.
The object name column is not populated for ad-hoc SQL (T-SQL Batch completed, T-SQL Statement Completed) and neither is the object_id column.
For stored procedures, the object name column is populated, with the name of the stored procedure.
You can do a complex filter on the textdata, but I would not recommend it. Rather trace, save and analyse.
p.s. This is one reason I recommend stored procedures over ad-hoc SQL. Because when I'm doing index tuning, it's trivial to find which stored procedures use a particular table (on SQL 2008, where the dependencies actually work, though it's not difficult on SQL 2005/2000 either)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply