RE: DB size is morethan 2 GB how to optimize

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Bandettini (JohnBandettini_at_discussions.microsoft.com)
Date: 02/14/05 

Date: Mon, 14 Feb 2005 02:53:05 -0800

Dinesh

2gb is not a very large database. You should be able to get very good
performance from it. So many things it could be.

A few things to try. Run profiler to capture your longest running queries.
Put those queries into query anayser and check the execution plans. If there
are problems with the code or lack of correct indexes, it should be easy to
spot. Here are some good tips on using profiler.

http://www.sql-server-performance.com/sql_server_profiler_tips.asp

Run dbcc showcontig on all your tables to check your tables for
fragmentation.

You do keep your database files, transaction logs and backups on seperate
disks, don't you? If you don't try to move them so you do.

Use perfmon to check your memory and disk I/O are not having problems. Here
is a link to a good article on using perfmon.

http://www.sql-server-performance.com/performance_monitor_tips.asp

There are lots of good articles on performance on
http://www.sql-server-performance.com/ have a good look through them and see
if you can identufy which areas may be affecting you.

If you are using a LAN or WAN, might be worth asking your network guys to do
a check, might not be a SQL Server problem at all.

These tips should at least get you started. There are so many things it can
be.

Hope this helps.

John

 

"Dinesh Bhandare" wrote:

> Dears,
>
> Is there any other settings for managing DB size is more than 2 GB, it seems
> it takes long time to retives the data. please give me idea.
>
> Thanks,
>
> Dinesh Bhandare

Relevant Pages

  • Re: Using an intranet webserver...would setting affinity mask help?
    ... in size, according to PerfMon. ... have control of the database. ... >Have you tried running Profiler (within the SQL Server ... >> second processor and you can even set affinity for it. ...
    (microsoft.public.sqlserver.setup)
  • With just one Disk I/O
    ... The address in filespace of a database page can be ... It looks for the relevant hash bucket, and by golly, there it is! ... By design it hashes to the same database page we already read! ... disk I/O we did read that page into memory, as well as the target page. ...
    (comp.databases.theory)
  • Re: Disk space usage
    ... Perfmon doesn't allow you to send emails by ... growing for the life of the database. ... and running a Backup Log with NO_LOG statement to truncate the log. ... you can specify a backup location and exclude the NO_LOG statement, ...
    (microsoft.public.sqlserver)
  • Re: PerfMon recording to SQL 2005
    ... I log the perfmon stats to a database table for exactly the smae reason -- ... Configuring the log file, choose the tested DSN as the System DSN ... The user account I created in SQL is an owner of the ...
    (microsoft.public.sqlserver.server)
  • Re: Exchange 2003 dumpster
    ... PerfMon has a counter that shows the total size of deleted items in a ... That'd be the "Keep deleted items for " value. ... If a database is managed by a policy then the policy supercedes the ... that, and if you apply it to a mailbox store, it replaces whatever settings ...
    (microsoft.public.exchange.admin)