Performance Issues

Question

Performance Issues

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

radek (3/6/2009)
Hi there,
which way is better to manage indexes (rebuilt,reorganization)?

You really cant say which way is better. It depends on the level of fragmentation. The rule of thumb is 30%- Rebuild your indexes.

Is better create maintenance plan by Wizard and schedule it for example twice too week..
or is better find fragmentation indexes "by hand" ? ("with sys view")

Yes, you should include this in your maintenance task. You need to see how frequent your your indexes are getting fragmented? and schedule the appropriate plan at off peak hours.

radekhatle SSC Eights! Points: 955 More actions · Answer 2

Maybe I wrote it not right, I know Rule of fragmentation 30%.

I can ask Which way use DBA,or which way prefer: create maintenance plans, or other way to maintaining indexes for example use sys.dm_db_index_physical_stats...

Thanks Radek

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

I don't like maintenance plans because they don't offer enough control. Instead, we generally write out our maintanenance plans and schedule. The creation is manual, but the execution is automated.

BTW, for 2005 & 2008, use the DMV's that are available for index fragmentation & definition.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

radek (3/6/2009)
Maybe I wrote it not right, I know Rule of fragmentation 30%.
I can ask Which way use DBA,or which way prefer: create maintenance plans, or other way to maintaining indexes for example use sys.dm_db_index_physical_stats...

My apologies, I was giving you the general info.

Yes, there are so many scripts to check the level of fragmentation to include in your maintenance task. Create a job which checks your level of fragmentation accordingly to rebuild/defrag them. Is that what you are asking?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Krishna ... I use the following outline:

Round II

DBCC DBREINDEX

UPDATE STATISTICS (with FULL scan) for all tables

Because I only do indexes that need it !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

radekhatle SSC Eights! Points: 955 More actions · Answer 6

Yes that is what I can think..

Thanks to Grand, Krishna

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Rudy,

Great, thanks for replying me.

How about the DBCC UPDATEUSAGE?

Just an inquiry please..:)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

I run it on the database daily after I defragment and reindex.

defragment individual indexes daily

- (not indid 0 & 1)

- when 10% or more of the rows have changed

reindex individual indexes daily

- (not indid 0 & 1)

- when 30% or more of the rows have changed

then

- updateusage

Weekly

reindex individual indexes

- (ONLY indid 0 & 1)

- when 30% or more of the rows have changed

then

- updateusage

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

radekhatle SSC Eights! Points: 955 More actions · Answer 9

So I have another question.

For find indexes column is better use DMV? or Profil?, of course I can create indexes where

I use "word" where or order by..

I think that profiler is for long time to catch threat to which is usable to create new indexes.

Thank a lot Radek

maechismo_8514 SSCoach Points: 17510 More actions · Answer 10

radek (3/7/2009)
So I have another question.
For find indexes column is better use DMV? or Profil?, of course I can create indexes where
I use "word" where or order by..
I think that profiler is for long time to catch threat to which is usable to create new indexes.
Thank a lot Radek

Sorry, I did not understand. Could you say that again please?

radekhatle SSC Eights! Points: 955 More actions · Answer 11

I can asked how did we find column to indexes?

I know when you use "where" than is good to create index...for big table.

And I read about profiler, where you can catch threat and then use it tuning advisor( tool of MS SQL) and it show where is good to create indexes and where not..

But its for long time, I think because you must catch trace.

And now I read this article with DMV http://www.sqlservercentral.com/articles/Indexing/64134/

Sorry for my English...

Thanks a lot

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 12

Yes, the best way to determine what needs indexing is to use Profiler, or TSQL, to create a server side trace to capture data over time. You can then run it through the DTA, but I don't like the recommendations that gives. Instead I aggregate the queries and go to work on the most frequently called or the longest running, or both, and use the execution plans of the queries to tune them.

A far less efficient method, but one you can put to work immediately, is to look at the missing index information that's in the cache on the machine. You can use this query[/url] that I've been working on.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

maechismo_8514 SSCoach Points: 17510 More actions · Answer 13

radek (3/7/2009)
I can asked how did we find column to indexes?
I know when you use "where" than is good to create index...for big table.
And I read about profiler, where you can catch threat and then use it tuning advisor( tool of MS SQL) and it show where is good to create indexes and where not..
But its for long time, I think because you must catch trace.
And now I read this article with DMV http://www.sqlservercentral.com/articles/Indexing/64134/
Sorry for my English...
Thanks a lot

No no its ok!, don't worry at all. I am a dumbho i wanted a bit clarity thats all:).

Yes as you said you should created indexes on the columns you filter in your query. There are many trade offs to consider which index you should consider and how many indexes. The more you practice the more you can grasp more into indexes. Yes, as Grant suggested even I don't rely on DTA, sometimes the recommendations are not efficient.

Using profiler is not recommended as there is performance overhead on the server. it's always advisable to go for server side trace.

You can check out articles and also to start with if you are just starting 🙂

cheerioo.......:D

radekhatle SSC Eights! Points: 955 More actions · Answer 14

Hi I have other question about indexes:-)

I read something about indexes, that indexes is good create in "big table"

But when I create table in MS SQL by GUI and define keys it create automatically cluster indexes on keys..

Why?

So I have many small tables about 100 rows with cluster indexes...

Im not sure if is better redefine it and leave small table like heap,without cluster indexes...

Which way is better, or faster?

Thank a lot Radek

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 15

A primary key and the index created in support of it are basically one and the same. Management Studio creates all the primary keys, by default, as the clustered index. You don't have to accept that, but every table, even the smaller ones with a hundred rows, should have a clustered index and if you haven't yet spent the time to define explicit clustered indexes, you could do worse than accepting the default.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning