Help with SQL Server Database Performance

Even if DBs are identical, queries require more/less time based on how much the DB is cached. Caching is automatically managed by SQL and is based on how much you use the DB. The more you use a DB the more RAM is allocated, thus, faster.

ASKER

Thanks for your explanation. I am sure the database which is slower is used more frequently then the one which is faster. because our front end developers develop on the database Spaces_SBDeployment and use. While the other one Spaces is used only by me and i only do development / testing when there is request for change or require new features.

Omitting real DB names, could you post the result of the following query?

SELECT      DB_NAME(database_id),
            COUNT (*) * 8 / 1024 AS MBUsed
FROM      sys.dm_os_buffer_descriptors
GROUP BY      database_id
ORDER BY      COUNT (*) * 8 / 1024 DESC

It returns RAM allocated for each DB.

ADDENDA: please check where are the files located on both DBs. Are they "host" on the same Logical Disk? You might be running a DB on a fast Disk and another on a slow Disk.

ASKER

MB Used for slower database = 16245
MB used for faster database = 8

OK, RAM allocation is not the reason. Did you check where are the files located?

ASKER

I beg a pardon about my knowledge about DBA. But do you mean the mdf and the log files? If yes then for both of the dbs they are on the d drive of the server
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

regards

Can you run both queries again but this time add the query execution plan before run the queries?

Then save the query execution plans and post them here so we can analyze them.

Yes, this is what I meant. So, even the files location is not the reason. Go for Vitor suggestion.

ASKER

Hi Vitor,

Thanks for your suggestion and i have attached the query plans for both the dbs
SlowDB.sqlplan
FastDB.sqlplan

Thanks for your suggestion and i have attached the query plans for both the dbs

Try again please :)

ASKER

sorry but the files are here again
SlowDB.sqlplan
FastDB.sqlplan

Can you provide the server configurations for both databases (DEV and PROD)?
Or both databases are in the same server?
If affirmative, are they at least at different SQL Server instances or they same the same MSSQL instance?

ASKER

Hi Vitor,

Both of them are on the same server and the same default instance.

regards,

What's the DB's log file size on both DBs and how is it set to autoexpand? % or size. No need to reply if both DBs have the same identical settings about the log file size and autoexpand.

ASKER

Well the setting of the log file is the same but there is massive difference in the size of the logfile. should i clear the log file of the db?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Not yet... Let's have a look at the Recovery Model on both DBs: Options section on the DB's Properties. How are they set?

ASKER

The recovery model for both of the database is set to FULL

Vitor i have rebuild all the indexes and the full text catalogue. Its not bringing back any results yet as i suppose it will take sometime for full text catalogue to rebuild. I will get back to you when i get back any results.

regards,

Depending on the size of the tables it might take a while, yes.

If the rebuild+catalogue shouldn't give you any results in terms of performance, you could consider setting the log file to it's default initial settings:
1. Change the Recovery Model to SIMPLE.
2. Shrink the log file.
3. Change the Recovery Model back to FULL.

Please do not loose the focus on the performance issue.
The transaction log file size and the recovery model does not have any impact in the query performance.

ASKER

Well thanks both,
by rebuilding the indexes and the full text catalogue it has improved the query performance. This resolves my issue for now but with another question.
When we go live and if i encounter the same performance issue would i need to rebuild the indexes and the full text catalogue again?
This means users would't be able to see any addresses during index rebuild process.

regards,

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial