SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Indexing and YOU...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cowski
Starting Member

USA
29 Posts

Posted - 02/11/2013 :  17:18:17  Show Profile  Send Cowski an ICQ Message  Reply with Quote
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

USA
36851 Posts

Posted - 02/11/2013 :  18:27:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
1. All tables should be looked at. If you need a starting point, see which queries have high reads and cpu.
2. This is an "it depends" situation. It really depends on your system, how many reads vs writes, your IO, etc. Some systems can support 100 indexes on a single busy table, some can't. Investigate.
3. I don't have it handy.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/19/2013 :  04:24:55  Show Profile  Reply with Quote
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.
Go to Top of Page

Cowski
Starting Member

USA
29 Posts

Posted - 02/19/2013 :  07:53:01  Show Profile  Send Cowski an ICQ Message  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 02/19/2013 :  15:50:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run a trace over a period of time, maybe an hour or two, and then use ClearTrace to churn through the data. ClearTrace is a free tool developed by Bill Graziano, the owner of SQLTeam.com. It is widely used in the SQL Server world. It really helps to do quick analysis of trace data. I use it frequently as my main expertise is performance troubleshooting.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/19/2013 :  17:31:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
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.aspx

Forgot 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

Edited by - jezemine on 02/19/2013 17:37:39
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/20/2013 :  20:00:55  Show Profile  Reply with Quote
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;
GO
SELECT 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_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO

There is also a way to trace the queries fired by users without using any monitoring tool.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000