July 6, 2017 at 8:47 am
Hi Guys,
Is it possible to cache the list of databases and their properties running on MS SQL 2012 Server? Every time someone connect with MS SQL Studio the following query creates physical reads:
SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC
Regards
July 6, 2017 at 10:30 am
When I try running this query after SET STATISTICS IO ON, it shows logical reads but not physical reads, even for a server that hasn't been logged into today. How did you determine it creates physical reads every time? Even if it did do physical reads, I can't imagine that it would be a very significant load, how often are you seeing it run?
July 6, 2017 at 11:08 am
I did some further reading and turned off the auto close on some databases, which seems to improve the behavior. I'm still monitoring to see if that fixes the issue.
July 6, 2017 at 11:53 am
Why are you using auto close?
One example of what is generally thought of this setting.
Unless you have a well documented reason with a lot of testing to back it up, I'd turn off that setting on every single database I manage.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2017 at 11:59 am
I'm not. These were databases restored from SQL Express, which sets the auto close to on by default.
July 6, 2017 at 12:09 pm
chris 39526 - Thursday, July 6, 2017 11:59 AMI'm not. These were databases restored from SQL Express, which sets the auto close to on by default.
I'd remove that. It causes pain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply