March 11, 2015 at 4:43 pm
Hi All,
One of the servers in my company using SQL Express and the Autoclose option for user databases is TRUE ( as default) . This is not good . I want to change the value from TRUE to False but as part of company’s policy I need to prove that it will be fine after changing in TESTING SERVER.
Any idea how to prove that it will be fine? (The impact of performance probably ) . Because if I can’t prove it then it will not allowed to change in PRODUCTION
Many thanks..
Cheers
March 12, 2015 at 4:32 am
Proving it will be fine is easy. Show that the app works before and after the change. But, you want to prove a benefit. To do that, I'd show the time it takes to connect to the database. It's the start up time that will be slow because of the requirement to open the database after all the connections close.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2015 at 8:24 am
show them the current errorlog, I would expect you are seeing cachestore flushes as the databases change state. Hopefully enough to show them the error of their ways.
---------------------------------------------------------------------
March 12, 2015 at 4:48 pm
Thanks for the reply . Appreciate it !
How do I measure it ? using SQL profiler for 3 hours each - Before and after changing ?
Cheers
March 13, 2015 at 6:00 am
You could use event tracking I think to capture connection and start up times. I'd have to look that up to be sure. Better to use extended events than trace events.
But, for this, you'd be better off showing it from application point of view, tracking externally.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 17, 2015 at 8:18 pm
Thanks for your suggestion 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply