We help IT Professionals succeed at work.

One database is fast, identical database is slow

435 Views
Last Modified: 2013-11-26
We have two identical SQL Server 2005 databases, both on the same server.  One is for operational use and the other is for testing purposes.  The test database was created using the SQL Server Management Studio Tasks | Copy Database option.  When our VB .NET application uses the operational database, the reports are produced within a few seconds.  When we use the test database, the reports take several minutes.  We've checked the connection strings and they are identical, except for the name of the database.

Does anyone have any ideas of why the test database would be so slow?
Comment
Watch Question

Top Expert 2007

Commented:
Check and make sure the tables are indexed correctly.  If they are not indexed, it's probably your problem.

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
after the copy, do update the statistics of the tables.
that should help
Top Expert 2007

Commented:
angellll:  What does that do, and how do you do it?

Author

Commented:
The databases are identical, including the indexes.
I also don't know how to update the statistics of the tables.
 
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
UPDATE STATISTICS (Transact-SQL)
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
msdn.microsoft.com/en-us/library/ms187348.aspx
Top Expert 2007

Commented:
Perhaps another helpful approach could be rebuilding the indexes:

    http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm



Top Expert 2007

Commented:
Here's another helpful link on rebuilding indexes:

    http://msdn.microsoft.com/en-us/library/ms187874.aspx

Author

Commented:
Okay, here's what I found out.  There are no indexes on either database (I didn't design the thing, I just copied the operational database so we could have a test database to use).  When a report is run using the original database, it takes 1:45 seconds to create the report.  When the same report is run using the test database, it takes about 6 minutes to create the report.
I did use the UPDATE STATISTICS on all tables in the test database but it still took 6 minutes to create the report.
Top Expert 2007

Commented:
creates some indexes... ;)

Top Expert 2007

Commented:
Honestly though, outside of indexes, I don't know why there would be such a drastic performance disparity.

Author

Commented:
I now have two test databases, both copied from the operational database (and both are SLOW).  I've added indexes to the second test database and ran a report against both test databases to compare the times.  It took 10 minutes to create the report using the database with no indexes.  It took 6 minutes using the indexed database.
While that's an improvement, it still doesn't explain why I can produce the same report using the operational database, which has no indexes, in about 2 minutes.
This one boggles the mind.
One issue that could contribute is a nearly-full hard drive with a large query result set. If you've filled the hard drive, your temp tables will have issues.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Here is an interesting trick to try.  Don't use the copy.  Backup the original database, then restore it using the overwrite option on another database.  I'll bet they both perform at the same speed.

Or you could detach the production database.  Do a file copy and rename, then reattach both.  Once again both will likely run at similiar speed.

I have a theory as to the issue.  I think the copy process creates a higher level of fragmentation than doing a backup/restore.

However, ultimately using a database without indexing will adversely affect performance.  Indexing is key to building responsive database applications.

Build your indexes based on your the columns used most often in your WHERE clauses.  Indexing columns with data that is updated frequently will cause index fragmentation so you have to be careful and not 'over index' the database.

I use the SQL profiler to find the most commonly run, slowest queries and begin optimization there.

By the way, a primary key is a clustered index.  Do you have primary keys?
The hard drive space issue that Chaosian mentioned is worth checking.

I would consider turning SQL Server off & doing a Defrag of the disk, then starting SQL Server & seeing if it works better.

Also ... have you updated the statistics as AngelIII suggested?  I haven't recently seen that problem, but just last month saw a problem that was solved by rebuilding the indexes -- and updating the statistics is a theoretically similar operation.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Guys, if it was disk related wouldn't it affect all databases?
Not if the production DB wasn't really growing and wasn't fragmented -- but the newer copies were.  At least I think that's the case.

Commented:
But wouldn't the copy be less fragmented than the original?
That depends on the state of the disk.

Author

Commented:
Thanks everyone for the suggestions.  I've checked the disk space and I have 56 GB free on the SQL Server partition.  Hopefully, that's more than enough to not be a problem.
Let me try the restore database option and see if that works.
 

Author

Commented:
Update.  I used SQL Server Management Studio to backup and restore the operational database.  When I did the restore, I restored to a new database called Test Backup.  Using the operational database, I ran the program and produced a report in 50 seconds.  I then ran the program using Test Backup and the report took 1 minute and 50 seconds.
It appears that doing a backup and restore to a new database doesn't help.  The reason  why the operational database runs so much faster is still a mystery.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Try the detach, copy files then attach the copy.

Author

Commented:
I was able to detach the operational database, copy the .mdf and .ldf files, and then reattach the operational database.  However, when I tried to attach the copied database I received an error saying that I can't attach a database with the same name as an existing database.
Before a RESTORE or REATTACH, have you tried a defrag?

Author

Commented:
Well, yes and no.  We use Diskkeeper on our servers which is constantly running in the background.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Ah, when you reattach scroll to the right and change the name there as well.  It's a good technique to learn.

Author

Commented:
I had to change the Attach as field as well as the Current File path fields for the Data and Log files to get the copied database to attach.
I reran the tests and this time the operational database was slower than the one I copied.  Not good!  I then detached the copied database and ran the test again.  This time the operational database ran much faster.
I believe we're on to something but I'm not sure what.  Why would attaching the copied database slow down the operational database?  Why would the operational database run faster when I detached the copied database?  Interesting.

Author

Commented:
After doing more testing, it appears that whatever database I use first for the day is the one that is fast.  So for example, if I use the test database the first time I run my program for the day then that's the database that produces the reports the quickest.  If on the other hand I use the operational database the first time I run my program, then that's the database that produces the reports the quickest.
What is strange is that we use the same approach in our other programs where we have an operational database and a test datatabase.  We don't experience this odd behavior in those databases where one database is fast and the other is slow.  However, those databases are much much smaller than this one so maybe they are behaving the same way but because there is so little data in those databases compared to our problem databases that we just don't notice the speed difference.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
How are you testing the speed?  Are using using SQL queries in Query Analyzer or are you running a front end web application?
How much RAM do you have?  And how big is your swap file?

Author

Commented:
Because there is a significant speed difference between the two databases, we use the simple approach - the second hand on a clock.  If the speed difference wasn't so noticble we probably wouldn't be having this discussion.
We have 2GB of RAM on the server.  The Page File on the C drive is 2,095,104 KB.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
I'd strongly recommend you run queries against each database in SQL Query Analyzer.  While you run the queries run 'Spotlight for Windows' from Quest Software (it's free)

See the enclosed code snippet.

This will give you a detailed breakdown of what your query is doing and you can match it to 'Spotlight for Windows' to find bottlenecks.

2GB is low for a SQL server especially if it starts to page to disk or steal memory from the O/S which can happen if you don't set a maximum for the SQL instance.
SET STATISTICS IO ON
 
-- Run your code here

Open in new window

Author

Commented:
I'm currently working with a Microsoft SQL Server expert to help find an answer to this problem.  I'll post whatever we find here.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.