Tag Info

Hot answers tagged

26

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 ...


20

I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.


18

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: ...


13

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. ...


13

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 ...


12

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.) The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...


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 ...


11

If sys.dm_exec_procedure_stats is not reliable for you (probably more because the information doesn't survive restarts than anything to do with the plan cache), SQL Server doesn't keep track of this in any other way. The only way to do this would be to add logging to your stored procedures (or to the app that calls them, if that is feasible and inclusive ...


10

Josh, This is a very common task for all DBAs 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 ...


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 ...


10

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 ...


10

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 ...


10

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 ...


10

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 ...


9

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 ...


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 work. It sounds like this server isn't a dedicated SQL Server/database server. It is ...


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

When To Update Statistics? if and only if auto update statistics feature is not good enough for your requirements. i mean if auto create and auto update statistics are ON and you are getting a bad query plan because the statistics are not accurate or current then it might be a good idea to have control over statistics creation and update. but if you are ...


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 ...


8

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the ...


8

One way would be to make a system procedure in master and then create a wrapper in your maintenance database. Note that this will only work for one database at a time. First, in master: USE [master]; GO CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required @tableName NVARCHAR(128) = NULL, @indexID INT = NULL, @partNumber INT ...


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

Most people rebuild them on a regular basis so that they never get to fragmented. When you need to rebuild them is based on how quickly they get fragmented. Some indexes will need to be rebuilt often, others basically never. Check out the script the SQLFool put together that handles a lot of figuring this stuff out for you.


7

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent. Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.


7

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 ...


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

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, ...



Only top voted, non community-wiki answers of a minimum length are eligible