sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Free SQL Server Advice

Issue 6 Number 5 May 2014

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 calling me at 877-201-3428 toll-free.

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.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Get Free SQL Server Help

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator