It is often said that Free Advice is worth what you pay for it, but I hope that the answers I give to reader’s questions have some value. I answer a lot of questions, but that should not be surprising. It’s my job.
I may get more questions than most consultants because all of our clients and former clients know that we don’t charge them for a call to answer a question or two. If a few minutes of my time can help anyone avoid two hours of SQL research on the web, I am happy to help. We also advertise that new callers are welcome to a free 15 minute discussion with a SQL Server expert. because of this, II spend a significant amount of time each week answering questions for all those callers.
Why do I do all this for free? The simple answer is that it is good for business. I know from long experience that if I give someone a little help with their SQL Server problem now, they are going to call me when they have a bigger problem to solve. Another benefit to me is that it keeps me aware of new problems that come up ith each new version of SQL Server.
If you have a SQL issue you need explained, you too can take advantage of the free call by contacting me.
Here are three questions I answered recently (and many times in the past).
QUESTION 1 – Moving a database
When moving a database from one server to another, is it better to use detach\attach commands, or to create a backup and restore from that?
ANSWER
This decision often depends on why you are moving the database. If there is no compelling reason to do otherwise, I generally restore from a backup because it doesn’t require taking the production database offline as would have to be done before detaching. The downside to this method is the length of time it takes to back up the database, move the backup file across the network and then restore the backup to the destination server.
Detach\attach is best if time is a critical issue for you. It is faster because attach\detach operations take effect almost immediately. When you use detach\attach, moving the files across the network is the only operation that might take a significant amount of time.
Whatever method you choose, if your purpose is to move this database to the new instance and retain it there as the production instance, you will have to disallow users access to the old version just before you create the backup or detach the database. Otherwise, they may alter data in the old version of the database that will not appear in the new version.
There are other considerations involved in moving databases, but this is my simple answer to a simple question. For more information, you might want to look at this page for a point by point comparison of the two methods to move a database: Backup vs Attach
QUESTION 2 – MS Access Connected to SQL Server
Some people in my organization would like to use Microsoft Access to connect to our corporate databases. Are there any reasons why I shouldn’t allow that?
ANSWER
First, a little background is in order. MS Access was created to be a departmental-sized database which could be easily programmed by intelligent but untrained persons. The code created by drag and drop programming is fast to create and allows non-programmers to create amazingly creative applications. However, the code is not particularly efficient. That is usually not a problem for Access applications intended for departmental usage by a few people and containing a relatively small amount of data.
However, when the Access application is introduced into a full-scale production SQL Server environment and connected to a SQL Server database it can often affect performance for everyone. A report created in Access by a non-technical user can take a long time to run and often blocks other processes. A lot depends on how Access is connected to SQL Server.
If it is connected to SQL Server through linked tables (a common method of upscaling MS Access to SQL Server), it will perform joins by retrieving data from each linked table and bring it back to the Access client to join and filter the data. For example, if you try to execute a query that joins 4 tables, you might notice that access opens up 4 or more connections for a single report. This method of connection can also cause blocking and other problems.
If you connect the Access front end directly to the SQL server through a connection string, you have more efficient methods for querying the database. For example you can create stored procedures on the SQL Server and execute them from Access. You can also create views to simplify the queries your users need to write. However, this method requires skills that your users might not have.
These requests often come from semi-technical persons who want to create their own ad hoc reports and using Access is the only way they know how to do that. Often they are middle and upper management types and it is difficult for the poor DBA to deny them what they ask for.
However, consider that giving unrestricted access to a semi-technical user is dangerous. A poorly written delete or update query could destroy entire tables. If you decide to go ahead with this I would STRONGLY suggest that you limit the users to read-only access to the database unless you have absolute confidence in their experience and ability.
QUESTION 3 – Dropping Unused Indexes
How can I safely delete indexes that I think are unused?
ANSWER
There are some important things to take into consideration before deleting indexes. First of all, you need to find out for certain what indexes are currently unused. You can do that with this code.
- declare @tablename sysname
- declare @database_name sysname
- set @database_name = db_name()
- select getdate() as date
- , db_name (dm.database_id) as DBName
- , object_name(dm.object_id) as tablename
- ,i.name as indexname
- ,dm.user_seeks
- ,dm.user_scans
- ,user_lookups
- ,user_updates — this is the number of times the index had to be updated
- from sys.dm_db_index_usage_stats dm
- join sys.indexes i on i.index_id = dm.index_id
- and dm.object_id = i.object_id
- and database_id = db_id(@database_name)
- where object_name(dm.object_id) not like ‘sys%’ — exclude system tables
- and dm.user_seeks < 1
- and dm.user_scans < 1
- and dm.user_lookups < 1
- order by object_name(dm.object_id), dm.index_id desc
Although the indexes returned by this query are currently unused, that does not mean you can drop them with impunity. The index usage metrics are zeroed out each time SQL Server is restarted so you have to be sure that SQL Server has been running long enough to determine whether an index is truly unused and can be deleted.
For example, you may have important reports that only run at the end of the month. The indexes that support those reports might only be used at the end of the month. If SQL server has been restarted recently it may be that end-of-month code has not run since the last re-start of SQL Server. If you delete the supporting indexes prematurely, you may have a big problem when the accounting department next tries to run its end of month reports.
One way to reduce the risk of deleting important indexes is to query a list of unused indexes using the code above and pasting the results into an Excel workbook. Then wait for a week, a month or whatever length of time is appropriate for you, during which SQL Server is not restarted. Then re-query the same data and compare the two lists. Any index that appears in both lists can be considered unused and can be safely dropped.