Hot answers tagged maintenance
24
Restarting the server is probably one of the most damaging things for performance. It means you force a cold cache for data, a cold cache for query plans, and all SQL Server internal caches are also nuked in the process. Not to mention that by throwing away all the statistics collected in the operational stats DMVs, you diminish your chances of ever ...
15
While the other answers are good, they're missing an important piece: Windows' file cache.
On 64-bit Windows, there's no cap to the amount of memory Windows will use to cache files. Windows can drain your system completely dry of memory, and at that point, you'll start swapping to disk. It's been documented in a few places:
...
12
You can check out the Standalone SQL Agent open source project. It uses the native SQL Agent tables and procedures (which still exist in SQL Express) to store the jobs replacing the SQL Agent itself. The engine is pretty well done for T/SQL and Dos commands. Things like SSIS, etc aren't done yet. Also the editor is only about 1/2 done, but the engine to ...
11
At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly. However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.
This presents the question: when does an index require to be rebuilt or reorganized? Rolando touched on this nicely. ...
11
Executing the query "" failed with the following error: "The index ""
(partition 1) on table "" cannot be reorganized because page level
locking is disabled."
The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not ...
10
It sounds as if you simply want to pause and resume your mirror. Have you seen this link yet? http://msdn.microsoft.com/en-us/library/ms190664.aspx
The gotcha with this would be the length of time you expect to have the mirror paused. If you take too long then the principal server could experience a large amount of transaction log growth. if you do not ...
9
Josh,
This is a very common task for all DBA's and the right answer is NOT the same for every one and for each server. As lot of other things, it depends on what you need.
Most definitely you don't want to run "Shrink Database" as already suggested. Its EVIL to performance and the below ref will show you why? It causes disk and as well as index ...
9
You shouldn't be restarting SQL Server unless you have changed service properties or set startup traces that you want to take effect immediately.
As @RemusRusanu has stated many points, it clears a lot of caches and causes SQL Server to do a lot of unnecessary startup procedures.
It sounds like this server isn't a dedicated SQL Server/database server. It ...
9
No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to REBUILD or REORGANIZE.
Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Lock durations are short and will cause minimal blocking of queries.
Rebuild drops an index and builds a new one. With ...
9
Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a ...
9
If you don't have the maintenance window for it, updating statistics daily is probably a little overkill. Especially if you have Auto Update Statistics turned on for the database. In your original post, you said that users are seeing a performance degredation due to this maintenance plan. Is there no other time to run this maintenance plan? No other ...
8
Yes, you should look at the excellent whitepaper on this topic although it refers to the online index rebuild, it still has lot of good info
http://technet.microsoft.com/en-us/library/cc966402.aspx
If the log files are auto growing then you can find that information using the default trace after the action is completed.
DECLARE @filename VARCHAR(255)
...
8
(1) Yes, I usually have the backup process by itself. I would not be doing much of anything during my backup time if I can. You might have it take the backup and then do the update on stats. As it sounds it seems you are running two jobs (1 for backup, 1 for update stats) at the same time?
(2) Do you copy the backup to tape or other disk storage? If so then ...
8
Probably nothing. I'm sure MS knows better than you or I
I've worked on high volume OLTP systems and never felt a need to change the settings.
A deadlock should be retried because they'll happen anyway
Quoting from SQL Server Storage Engine blog, "Lock Escalation in SQL2005"n which is worth reading completely anyway.
By default, we have both ROW and ...
7
You could use the Windows built-in task scheduler to run a SQL script using the command line tools that are provided with SQL Express - I believe that http://msdn.microsoft.com/en-us/library/ms162773.aspx is included with all SQL 2005 and 2008 editions including Express.
I would recommend setting up a user specifically for this and running the script logged ...
7
Use ExpressMain, its a very handy solution for SQL Server Express users.
ExpressMaint
Project Description
This project
maintains two utilities for automating
the backup and maintenance of
databases for SQL Server
2005/2008/2008R2 Express Edition. They
are based on the sqlmaint application
that came with SQL Server 2000 but
updated for ...
7
You have to plan it with the business needs in mind. Sometimes it's a maintenance plan (like when a shop doesn't have full time DBAs or wants a minimum of complexity.) Other times it's Ola Hallengren's maintenance scripts. Whatever you do, it has to take into account the business needs for that data. I discussed it in my post Ozar's Hierarchy of Database ...
7
I can only answer question 2 here.
I recommend you take a look at compressed backups.
SQL Server can do this, if you have 2008 enterprise edition. If not look at the two below.
Quest Litespeed
RedGate SQL backup
7
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option ...
6
When should I rebuild the indexes in my relational database (e.g. SQL Server)?
You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table.
Is there a case for rebuilding indexes on a regular basis?
So what if your indexes are becoming fragmented on a ...
6
It is not only possible to detect if index DDL operations increase the log file, it is the default action line. All index operations are logged in a database that uses the full recovery model.
I'd advise you to read the following article on MSDN: Determining Index Disk Space Requirements.
From the 3rd chapter - Transaction Log Disk Space for Index ...
6
The sys.objects table keeps track of the last time a table's structure was modified but the closest you are going to get for finding out the last time a table's data was updated or used is by querying sys.dm_db_index_usage_stats. This keeps track of index updates (including heap's, or tables without an index). The catch is, it only keeps track of this data ...
6
I'll share my experience, even if you already accepted an answer. Maybe it will be helpful :-).
Full daily backup (daily) - great, but don't forget to check for space and remove old files after some predefined time.
Backup selected tables (hourly) - don't understand why you'd need this, you'd better go with differential backups. How do you backup only ...
6
1) I don't see a direct relation between the task of making a backup and the task of updating statistics. So you can split them without any problem. I'd see the updating statistics part more related to a job that would defrag/rebuild the indexes.
2) Even if it's for short time, you don't want to be without a backup. So you'll want to remove the last backup ...
6
Your update stats task should not be taking 4 hours for a 3-4 GB database. More than likely you have have some I/O issues or you have a heavily fragmented database that is creating I/O issues. Run a defrag or index rebuild on the database and see if that improves the performance. If not then fire up perfmon and check where your performance bottleneck is.
6
If it speeds up queries, parameter sniffing might be involved. If a crap plan is cached and applied to inappropriate subsequent calls, then the miracle of rebooting allows the common/correct plan to get cached. If that's the case, there are infinitely better ways of correcting the behaviour as other have indicated. But until they stop rebooting the box, ...
6
If the user never run REBUILD or REORGANIZE on the database, would the SqlServer engine defragment the indexes?
No.
Would running REORGANIZE multiple times do the same things as REBUILD
Also No.
A REORGANIZE reorders the leaf pages (the pages that hold the actual index data) to be in the correct order per the index specifications. Pages can also ...
6
Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible.
During the rebuild your queries will run slower, mostly due to the I/O overhead of rebuilding the index. How noticeable this is depends on the specifics of your system. The performance penalty is only during the rebuild - not forever ...
6
Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild?
Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action ...
6
Of the tasks that you've listed the creation and maintenance of the user accounts in production should be done by the DBAs. The developers shouldn't have the passwords of the production accounts at all.
Designing the tables, indexes and performance testing should all be done by the developer. The DBA should be handling the creation of the objects on the ...
Only top voted, non community-wiki answers of a minimum length are eligible
