Wednesday, March 6, 2013 // Diskmanagement, Performance tuning
SQL Server tempdb is a database in SQL Server which is used to store temporary user objects and internal objects. Because the tempdb is used a lot by SQL Server it is important for the performance to configure it correctly. Tempdb is only using one data file by default. Multiple data files can improve the [...]
Tuesday, February 19, 2013 // Performance tuning
The query optimizer in SQL Server uses statistics. The data in statistics is used to create the query plans that improve query performance. If the data in the statistics is better the queries will perform faster. To ensure the queries will perform as fast as possible it is important to update the statistics. You can [...]
Wednesday, February 13, 2013 // Performance tuning
Background: A log file records all the changes that occur in a SQL Server database. This log file contains multiple virtual log files(VLFs) these are used to manage space reuse. If SQL Server needs more space to store this information the log file will increase by its Autogrowth size. Increasing the size of the log [...]
Monday, November 7, 2011 // Diskmanagement, Uncategorized
Background: A log file records all the changes that occur in a SQL Server database. Normally the log file doesn’t grow but reuses inactive portions of the log. If the Recovery model of the database is set to Full however the log file can grow very big if you forget to back up the log [...]
Wednesday, November 2, 2011 // Performance tuning
Background: When a query is executed on a database, indexes are used to physically locate the data on disk. Due to frequent insert, update and delete statements this data can become fragmented. This is a serious performance issue. To mitigate this schedeule this script in a SQL Agent Job to frequently defragment you indexes. Steps: [...]
Thursday, August 4, 2011 // Diskmanagement
Background: A database contains database users that are mapped to Logins (SQL or Windows) via a SID. When you want to move a database to an other server it is neccessary to also move the logins that are being used by the database. Without this you will end up with orphaned users. Steps: 1. Create [...]
Wednesday, August 3, 2011 // Diskmanagement
Background: A user database is attached to a certain SQL Server instance and consists of at least two files: mdf (data file) and ldf (log file). To move a database it needs to be detached from the SQL Server instance so the files are released. After detachment the files can be moved to an other [...]
Friday, July 29, 2011 // Backup/restore
Background: The master database holds all Instance settings, logins and registers which databases are connected to the server. If the master database gets corrupted SQL Server stops working and the service can’t be started. In this scenario the master database first has to be rebuild before a restore is possible. See article: How to rebuild [...]
Friday, July 29, 2011 // Diskmanagement
Background: All temporary results from stored procedures of all databases are temporary stored in the tempdb. Because of its central part in the execution of T-SQL statements it can easily become a performance bottlekneck. To mitigate this it is best to move the tempdb to a fast drive instead of its default location in c:\. [...]
Categories
Follow us
