In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design...
As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do so. Below is the TSQL I have used to obtain buffers...
Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database. Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation, in this regard review the blogs by SQLQuery...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
01-25-2008
Filed under: sql server, performance, indexes, cache, users, tsql, best practice, backups, create, online, monitoring, operating system, busy, creation
How can you calculate size of an index in a database whenever a capacity planning exercise if performed? By default the size allocated for each user objects depends on the user application and on the amount of space that is created by the application for user-defined tables, global and local temporary...
I don't believe you would need to reset the cache on a production server, it is not a best practice to perform in this case. Most of the times in development or test environments for the purpose of benchmarking you would need to reset the cache for optimum results. You may know that in this case...
We all know that the cached query plans are good to go to attain the performance and no doubt that inefficient query plan will cause more distress to performance with an increased spike in CPU consumption. At the same occurrence of recompilation of a stored procedure is not a good show either. So how...