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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Indexing and YOU...

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

Posted - 2013-02-11 : 18:27:14
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 - 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.
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-02-19 : 15:50:57
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
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.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
Go to Top of Page

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;
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
   

- Advertisement -