Speeding up SQL

[Leesha]

Hi,
I have an Access DB that is linked to sql tables. The DB tables have become quite large due years of data entry and the DB is running slower. Is there a recomended way to optimize or speed up the DB?
Thanks!
Leesha

[HansV]

It's important that your tables (in SQL Server) are indexed correctly.
If you frequently sort, filter or search on a field (or a combination of fields), it will improve performance if you create an index on that field or combination of fields.

Keep in mind, though, that each index takes up space and causes some overhead because it has to be maintained as records are added, modified and deleted. So you shouldn't create an index on each and every field, only on those that are used most often to sort, filter or search.

For more information, see SQL Server Indexes.

If you have fixed queries that you use a lot, it will help if you define views for them in SQL Server. It will automatically optimize the performance of views. From your Access database, views will look like tables.

[Leesha]

Thanks Hans! I appreciate the link and the info,
Leesha

[Wendell]

You might also want to take a look at tuning the SQL Server installation if it is available to you. One of the things we try to do is to allocate as much memory as possible for SQL Server - and since our databases tend to be under 1 GB that or 2GB is usually sufficient. Doing that puts all of your data available in memory rather than having to do disk seeks to find it. You might also want to look at using "pass through" queries for things that are particularly sensitive, but need to be dynamic. We often use that technique for searches and for doing duplicate checks.

[Leesha]

Hi Wendell,
I've not used "pass through" queries that I am aware of. Is there a link that you would recommend that I can read up on this? Also, what is the maximum number of rows of data that would recommend in a table before archiving?
Thanks,
Leesha

[HansV]

A very short video introduction: MS Access Pass Through Query (it's for Access 2007, but it works similarly in Access 2010/2013).

A SQL Server table can contain hundreds of thousands of records without a problem. As long as it is indexed properly (see my first reply), performance will be fine.

Wendell will probably have more suggestions.

[Leesha]

Thanks Hans! I'll start reading :-)

One of thee tables which is used quite a bit has over 4,000,000 rows. Prior to this month it was prmarily used for storage. Now its queried against qutie frequently which is when the slow down really became apparent. 4,000,000+ seems quite excessive but the user wants access to all of the data and has not wanted to archive. The slow down has prompted them to consider is so I'm trying to get a feel for how much to pull out.
Leesha

[HansV]

I'd check the indexes on the table first, and perhaps create views for frequently used queries (if possible), before considering archiving.

[Leesha]

OK. So much new stuff to learn!!!!

[Leesha]

You might also want to take a look at tuning the SQL Server installation if it is available to you. One of the things we try to do is to allocate as much memory as possible for SQL Server - and since our databases tend to be under 1 GB that or 2GB is usually sufficient. Doing that puts all of your data available in memory rather than having to do disk seeks to find it. You might also want to look at using "pass through" queries for things that are particularly sensitive, but need to be dynamic. We often use that technique for searches and for doing duplicate checks.

I spoke with the IT engineer for the company with the sql DB. He's not done this before and wanted me to get info on the following:

The server is office server 2003. There is a full sql server 2000 which runs imaging software for their canon copier. This was installed prior to the sql Server Express 2005 which is being used for the DB. He is wondering how he goes about checking the memory allocation on the sql server 2000 as compared to the sql server express and then if it is possible to make each of them 50%, if they are not already.

Just as an FYI, even without making any changes to indexes etc. (I still have alot of reading to do to "get it") the database flys on my local computer using the backups of their data. I would expect this since its not running on a server with 7 people pounding on it, however the difference in the speed between my local laptop and on their server is quite different. When we look at the power usage on the server itself via task manager its not being maxed out at all. They've just installed more memory etc. recently. IT is thinking its the combo of the two differnt intsances of sql.
Thanks!
Leesha

[Wendell]

The memory allocation for an instance of SQL Server can be set in the properties for the instance using Management Studio. So you can set each a specific amount of memory in MBs. Since you are using SQL Server Express you may be encountering some issues with the resources on the server - the express versions can only use a single processor. Also note that a database is limited to 10GB in Express, and you may want to have him check the status of the log file - if it isn't being truncated regularly it may be huge.

[Leesha]

Hi Wendell!
For my own knowledge, when you say check the status of th log file, how do I do that?
Thanks,
Leesha

[Leesha]

Hi,
I found code to run against the server to see what the log size and file size is. I've attached it here. Since they are in 2005 express and have the 4 gig limit I'm concerned that they are dangerously close to crashing based on the total remaining in the end column. I'm not sure if I am interpretting this correctly esp. since the "space used" is at 2734.51 mb. This would leadd me to believe that there is still room to grow to the 4gigs.

If they were to upgrade to express 2008R, is there anything that should be taken into consideration in the upgrade?

Thanks,

Leesha

[Wendell]

Actually the limit is on the database file and I don't think there is any limit on the log file. But a 14GB log file is very large, and if you add a record or two and more space is needed in the log file, and it is set to 10% of the current size, which is the default, it would be trying to add 1.4GB to the log file. That would create a significant performance hit while it added the space. There are several ways to truncate a log file - the preferred one is to do it at part of a SQL backup and maintenance task. But for small databases, the more common procedure is to:

• Backup of the database to a disk file or tape
• detach the database (that means you take it out of service however, so not the way to go on 24/7 databases)
• then find and rename the log file
• Reattach the database

SQL Server will complain that it cannot find the log file and then create a new empty one. The detach and attach are done in SQL Server Management Studio. Note that you can also see the size of the database files and where they are located by looking at the properties of the database with a right-click on the database name in SSMS.

If you are contemplating upgrading your installation of SQL Server Express, I would choose either 2012, or the forthcoming 2014 if you can wait a few months.

[Leesha]

Hi Wendell
I know how to detach and reattach the DB but don't know to find the log file.
Thanks,
Leesha

[Leesha]

Hi,
I've managed to find the log for the database. I used the info on this link to try to remove the log.
http://technet.microsoft.com/en-us/libr ... SProcedure" onclick="window.open(this.href);return false;
When I tried to do it manually the remove button is not enabled. When I tried to do it via a query using the code below I got an error meassage stating the file couldn't be removed. I got this message whether the TTTSM database was attached or removed. Not sure what to do next.
BTW, I tested this on my sql 2012 developer editon and have not tested on the users sql express 2005 since I'm not really sure what I'm doing and don't want to blow things up.
Thanks,
Leesha

USE master;
GO
ALTER DATABASE TTTSM
REMOVE FILE TTTSM_log;
GO

[Wendell]

Sorry - been chasing gremlins since my last reply. I would not resort to using T-SQL queries to do that sort of thing - I depend on the SQL Server Management Studio. The reason is that a small typo or omission in such a procedure can do serious damage to your database. On the other SSMS has been tested literally millions of times, so you know what to expect.
Find the database you want to shrink the log file, and right-click on the name - choose tasks, and then the top choice (in 2012) is detach. Choose that, but make sure you don't have anything active or open from it. You will get a dialog, and if you approve it, the database name will disappear. (Be sure not to choose the drop option - that really makes it go away forever.) Now go find the folder where the log file resides, and rename the log file to something like zzz_MyLogFile.ldf. Then right-click on the instance name for SQL Server and you should get a menu that has Attach as the second item. Choose that and you get a dialog box that has an Add button - click that. You get a dialog that lets you pick a SQL Server .mdf file (the database). It will fill in both the database and the log file, and when you click the OK button, it will complain that the log file doesn't exist, and then create another one with the same name as the original log file, but it will be very small.

This was done using SQL Server 2012 Express, so the menus may be slightly different for 2005, but the principles are the same.

[Leesha]

Thanks Wendell! I'll give this a shot hopefully tonight when everyone is out of the database.
Leesha

[Leesha]

" Now go find the folder where the log file resides, and rename the log file to something like zzz_MyLogFile.ldf."

This is the part that messes me up. I can find SQL Server Logs under Management but there doesn't appear to be any way to rename them. Am I looking in the right place?
Leesha

[Leesha]

found it!!!

Thanks!
Leesha