159

In PostgreSQL*, you cannot drop a database while clients are connected to it. At least, not with the dropdb utility - which is only a simple wrapper around DROP DATABASE server query. Quite robust workaround follows: Connect to your server as superuser, using psql or other client. Do not use the database you want to drop. psql -h localhost postgres ...


37

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


29

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


28

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


27

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: https://blogs.msdn.microsoft....


26

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.


25

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


24

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.


22

I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server. First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, ...


22

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


21

Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of ...


20

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by: how big your database is how fast your IO subsytem ...


20

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are: It can improve read-ahead performance for large range scans; and It may improve the page density (number of rows per page) Both of these factors are less important for small indexes, by definition. The counter-argument to ...


17

Large objects (LOBs) are stored in different "pages". A page is a small section of a database, typically an 8kb chunk. For rows that contain LOB columns, the normal page will contain a reference to the LOB page. From the alter index manual page: WITH ( LOB_COMPACTION = { ON | OFF } ) Specifies that all pages that contain large object (LOB) data are ...


16

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


15

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


15

Well, there's bad news, good news with a catch, and some really good news. The bad news T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions: stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly ...


14

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


14

Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename. Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection ...


14

In general, a well designed cluster can live for YEARS without being touched. I've had clusters that ran for years hands-off. However, here are some guidelines: Monitoring is hugely important: 1) Monitor latencies. Use opscenter or your favorite metrics tools to keep track of latencies. Latencies going up can be signs of problems coming, including GC ...


14

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


13

A reorganise-and-shrink is never recommended really. If you can take the apps the database is serving offline, you can speed up the process and reduce index fragmentation by removing all indexes and primary/foreign key constraints before the shrink (this will mean there is less data to be moved around as only the data pages will be shuffled not the now non-...


13

if any table has page count more [than] 50 [pages] That is still extremely small. I typically don't take fragmentation into account for any index with less than 1000 pages. and avg_fragmentation_in_percent > 30 then the index is REBUILD That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5%...


13

If you detach a database from an instance, you will need to perform an OS-level delete of the file. The safer approach is to drop the database instead. What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of ...


13

I've upvoted John's answer; I'd just like to add some details about other items that you might want to clean up. SQL Server Agent jobs and alerts might reference the database. Cleaning them up will prevent unnecessary errors being reported. Remove any Logins that were created specifically for the database. The following T-SQL will identify possible ...


12

In addition to the recommendation by @Remus to use a SAMPLED scan, I don't know that this query can't start until your maintenance window starts. Why not pre-populate a table with the results? If you start this query (let's say a sampled scan takes 10 minutes) about 15-20 minutes before your maintenance window, and stuff the results in a table, the data ...


12

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...


12

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


12

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be done. Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or ...


11

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


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