MS SQL speed problems

Forum Moderators: open

Message Too Old, No Replies

MS SQL speed problems

I'm told...

giggle

10:25 am on Jul 26, 2006 (gmt 0)

We've been using an MS SQL database for a few years now and recently I've been told that updating the database is taking a long time.

It is possible that the table the process updates is quite big now.

Is there a similar function to the MS Access "Compact and Repair" function that will "compress" the database, or, is there any process that I can run that will in anyway speed up table access.

Apologies for incorrect technical wordage.

Thanks

Mick

syber

1:27 pm on Jul 26, 2006 (gmt 0)

I would start off by running the index tuning wizard for that database. Also, make sure that auto statistics is turned on. The problem most likely is in the indexes, not the table.

stajer

4:23 pm on Jul 26, 2006 (gmt 0)

Also look into the "shrink database" and "truncate log" functions. If you haven't done either of these in a long time, the files could be very large and taking up too much memory (causing the speed problems you are discussing). I don't think an index will improve write speeds (though it will significantly improve read speeds).

giggle

4:28 am on Jul 27, 2006 (gmt 0)

Thanks for the reply guys.

I've hunted around SQL Ent. Mgr. but I can't find the "Shrink Database" option. Could you give me an idiots guide to where I can find it.

Thanks

Mick

giggle

4:52 am on Jul 27, 2006 (gmt 0)

Ok, I found the Shrink Database option. (Google search showed me where - should have thought of that first).

When I go into this option the system reports allocated space 273Mb and free space 0Mb which could possibly account for the speed problem.

Is there any way that I can allocate more space to our SQL database?

Thanks again

Mick

aspdaddy

9:43 pm on Jul 29, 2006 (gmt 0)

Run a maintanance plan as part of a weekly backup, it has all the options to optimise and fix the database.

This should get you started:
Inside SQL Server Maintenance Plans [sql-server-performance.com]