Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cowski
Starting Member
30 Posts |
 Posted - 2013-02-11 : 17:18:17
|
| I'm working at learning how indexes work. I hear it's an "art" that must be mastered. So be it. But even a work of art starts with basics.My initial question regarding all of this starts at the beginning I guess.How is it determined, or how do most DBA's determine which table(s) to even look at that may require an index? Or even be over-indexed? In other words, how do you figure out which tables need the TLC of a index, or 2 or 10? I don't want to over index or under index. Does anyone use the "Recent Expensive queries" from within the Activity Monitor within SQL Server to flush out some out of control queries?Currently I'm watching a lot of videos from Brent Ozar, just got "SQL Server Execution Plans" by Grant Fritchey printed out today so will be reading that. So I am ready to dive head first into all of this. I'm looking for any constructive advice everyone here can provide.Thank you for your time. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-19 : 04:24:55
|
| What is the size of your table you wanted to Index?What are the most frequent queries fired by users?What columns are used in where condition?What is the ordering of data? (to go with CL and NCL)And lot many factors need to be considered to go with Indexing like tkizer said. |
 |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2013-02-19 : 07:53:01
|
quote:
Originally posted by srimami
What are the most frequent queries fired by users?
This is the $50,000 question. How would everyone else determine this? Right now I'm monitoring activity monitor as tkizer suggested. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2013-02-19 : 17:31:29
|
Adam Machanic wrote an invaluable proc called sp_whoisactive. It's a great tool for troubleshooting. It will tell you, among other things, the query plans for currently-executing queries on a live server. Bad plans can sometimes be fixed by adding an index.Details here: http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspxForgot to mention - post below by Kendra Little shows how to create a job that runs sp_whoisactive on a schedule and dumps results to a table for analysis later. It's a bit like running a trace, but just stores a snapshot of what's going on now instead of every little thing that happened. http://www.littlekendra.com/2011/02/01/whoisactive/
elsasoft.org |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-20 : 20:00:55
|
| You can get the most frequent fired queries by running the following script or you can get it from console (not a million dollar question anymore)USE DBName;GOSELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text"FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_statsGROUP BY query_stats.query_hashORDER BY 2 DESC;GOThere is also a way to trace the queries fired by users without using any monitoring tool. |
|
|
|
|
|
|
|
|
Indexing and YOU...