Question about optimization

[SR]

Guys,

I have a 40GB database being hosted on a NAS device (Network Appliance -
http://www.netapp.com/). The log file for this database grows at a rate of
5GB per day. So I do a log cutting on this database on a weekly basis to
free up space on my NAS device.

I have created two separate Q-Trees on the NAS device and I store the main
data file on one of the Q-Trees and the log file on the other Q-Tree.

I ONLY have a PRIMARY FILEGROUP. Should I change this to optimize my
database performance?

I also have a table in the database with approximately 12 million records.
The indexing on the table is well done - using covered index for better
querying. The table gets defragged quite often. So I do defrag the Indexes
on this table once a month (using DBCC DEFRAG).

What else can I do better the performance of my database?

Thank you,

SR

[Greg D. Moore \(Strider\)]

Re: Question about optimization


"SR" <yosonu@socal.r r.com> wrote in message
news:ba4c21f6.0 310052349.283de aa@posting.goog le.com...[color=blue]
> Guys,
>
> I have a 40GB database being hosted on a NAS device (Network Appliance -
> http://www.netapp.com/). The log file for this database grows at a rate of
> 5GB per day. So I do a log cutting on this database on a weekly basis to
> free up space on my NAS device.[/color]

Not sure what you mean by "log cutting" Please explain.
[color=blue]
>
> I have created two separate Q-Trees on the NAS device and I store the main
> data file on one of the Q-Trees and the log file on the other Q-Tree.
>[/color]

Well, I'm not familiar with Q-Trees (should be by the end of this week
ironically :-).

But the real answer comes down to physical disks.

[color=blue]
> I ONLY have a PRIMARY FILEGROUP. Should I change this to optimize my
> database performance?[/color]

"Maybe". If your other filegroups would end up being on the same PHYSICAL
disks (even if logically they appear different) then no, you probably would
not notice a difference. (Assuming SQL 2000.)

[color=blue]
>
> I also have a table in the database with approximately 12 million records.
> The indexing on the table is well done - using covered index for better
> querying. The table gets defragged quite often. So I do defrag the Indexes
> on this table once a month (using DBCC DEFRAG).
>
> What else can I do better the performance of my database?[/color]

Use Profiler, find out where any bottlenecks would be.

It's hard to say how to improve performance when a) we don't know how slow
it is now and how fast you need it to be, B) what other bottlenecks may
be... disk I/O, CPU, memory, etc.

[color=blue]
>
> Thank you,
>
> SR[/color]

[Erland Sommarskog]

Re: Question about optimization

SR (yosonu@socal.r r.com) writes:[color=blue]
> I have a 40GB database being hosted on a NAS device (Network Appliance -
> http://www.netapp.com/). The log file for this database grows at a rate of
> 5GB per day. So I do a log cutting on this database on a weekly basis to
> free up space on my NAS device.[/color]

Don't you backup the transaction log more frequently than that?
[color=blue]
> I also have a table in the database with approximately 12 million records.
> The indexing on the table is well done - using covered index for better
> querying. The table gets defragged quite often. So I do defrag the Indexes
> on this table once a month (using DBCC DEFRAG).
>
> What else can I do better the performance of my database?[/color]

As Greg said, since we don't know what you think is slow, and how fast
you want it to be, it is difficult to say. But running Profiler to
catch queries with duration > 1000 ms (or what may be suitable) could be
a start.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp