On the primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server:
Then execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job:
… for a further break down to see the fragmentation level per index, the following script will get this information for you;
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(‘[DatabaseName]‘),NULL, NULL, NULL, ‘LIMITED’) s
join sys.objects o on o.object_id = s.object_id
join sys.indexes i on o.object_id = i.Object_id
and s.index_id = i.index_id
WHERE o.type_desc = ‘[TableName]‘ and avg_fragmentation_in_percent > 5 and i.Name is not null
Alerts should be configured for Error 823, 824 and 825 (see previous articles).
It’s also advised to alert on SQL severity levels 17 to 25. Severity levels from 17 through 19 will require intervention from a DBA (although they’re not as critical as 20-25):
These are serious errors that will mean SQL Server is no longer working
20 SQL Error in Current Process
21 SQL Fatal Error in Database dbid Processes
22 SQL Fatal Error Table Integrity Suspect
23 SQL Fatal Error: Database Integrity Suspect
24,25 Hardware Error
This query returns indexes that SQL Server 2005 (and higher) thinks are missing since the last restart. The “Impact” column is relative to the time of last restart and how bad SQL Server needs the index. 10 million+ is high.
Use this only as a guide – remember that SQL Server isn’t considering the impact of too many indexes on a high-write table. If a table has too many indexes, then insert/update/delete activity will slow down.
Column order for key columns may be off. Generally, the most selective columns come first.
See Also: [[Main_Page]] – [[Transact SQL Code Library]] – [[Index Related DMV Queries|Index Performance Tuning]]
==Missing Indexes==
This query returns indexes that SQL Server 2005 (and higher) thinks are missing since the last restart. The “Impact” column is relative to the time of last restart and how bad SQL Server needs the index. 10 million+ is high.
Use this only as a guide – remember that SQL Server isn’t considering the impact of too many indexes on a high-write table. If a table has too many indexes, then insert/update/delete activity will slow down.
Column order for key columns may be off. Generally, the most selective columns come first.
The Included column order does not matter.
==Tutorial Video==
In this short tutorial video, Brent Ozar explains how to use the code to tune your environment.
===T-SQL Code===
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, ‘CREATE NONCLUSTERED INDEX ix_IndexName ON ‘ + sys.objects.name COLLATE DATABASE_DEFAULT + ‘ ( ‘ + IsNull(mid.equality_columns, ”) + CASE WHEN mid.inequality_columns IS NULL
THEN ”
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ”
ELSE ‘,’ END + mid.inequality_columns END + ‘ ) ‘ + CASE WHEN mid.included_columns IS NULL
THEN ”
ELSE ‘INCLUDE (‘ + mid.included_columns + ‘)’ END + ‘;’ AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’)=1
ORDER BY 2 DESC , 3 DESC
==Query Test Checklist==
* Works on SQL Server 2008: Yes
* Works on SQL Server 2005: Yes
* Works on SQL Server 2000: No – unfortunately, there’s no way to gather this data for SQL Server 2000.
* Works on Standard Edition: Yes
* Works on case-sensitive servers: Yes
Tests Updated by Brent Ozar, 2009-04-01
==SQLServerPedia Fan Contribution==
I really found Brent’s tutorial helpful in tuning my SQL Server indexes. SQL 2005/2008 DMVs are an awesome tool for the SQL DBA. Below is my modified version of Brent’s original query. It exposes the full table path so it’s easier to identify which database the index recommendation is for. As Brent stated above, consider the impact of the additional indexes before applying. I’ve found it best to allow about 30 – 60 minutes before adding or removing indexes based on DMV recommendations.
–
Clayton Kramer, 2009-04-09
===Modified T-SQL Code===
/* ——————————————————————
– Title: FindMissingIndexes
– Author: Brent Ozar
– Date: 2009-04-01
– Modified By: Clayton Kramer
– Description: This query returns indexes that SQL Server 2005
– (and higher) thinks are missing since the last restart. The
– “Impact” column is relative to the time of last restart and how
– bad SQL Server needs the index. 10 million+ is high.
– Changes: Updated to expose full table name. This makes it easier
– to identify which database needs an index. Modified the
– CreateIndexStatement to use the full table path and include the
– equality/inequality columns for easier identifcation.
—————————————————————— */
SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[', ''), ']‘,”), ‘, ‘,’_')
+ ‘ ON ‘
+ [statement]
+ ‘ ( ‘ + IsNull(mid.equality_columns, ”)
+ CASE WHEN mid.inequality_columns IS NULL THEN ” ELSE
CASE WHEN mid.equality_columns IS NULL THEN ” ELSE ‘,’ END
+ mid.inequality_columns END + ‘ ) ‘
+ CASE WHEN mid.included_columns IS NULL THEN ” ELSE ‘INCLUDE (‘ + mid.included_columns + ‘)’ END
+ ‘;’,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’) = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC
There are two pretty well-known I/O errors – 823, and 824 – but there’s also one called 825 which most DBAs do*not* know about, and definitely should.
From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL Server has actually tried that I/O a total of 4 times before it finally declares a lost cause and surfaces the high-severity I/O error to the connection’s console, killing the connection into the bargain. The idea behind this read-retry logic came from Exchange, where adding the logic reduced the amount of immediate downtime that customers experienced. While in concept this was something I agreed with at the time, I didn’t agree with the way it was implemented.
If the I/O continues to fail, then the 823/824 is surfaced – that’s fine. But what if the I/O succeeds on one of the retries? No high-severity error is raised, and the query completes, blissfully unaware that anything untoward happened. However, something *did* go badly wrong – the I/O subsystem failed to read 8KB of data correctly until the read was attempted again. Basically, the I/O subsystem had a problem, which luckily wasn’t fatal *this time*. And that’s what I don’t like – the I/O subsystem went wrong but there are no flashing lights and alarm bells that fire for the DBA, as with an 823 or 824. If read-retry is required to get a read to complete, the only notification of this is a severity-10 informational message in the error log – error 825. It looks like this:
Msg 825, Level 10, State 2, Line 1.
A read of the file ‘D:\SQLskills\TestReadRetry.mdf’ at offset 0×0000017653C000 succeeded after failing 2 time(s) with error: incorrect checksum (expected: 0×4a224f20; actual: 0×2216ee12). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Now, what this message is really saying is that your I/O subsystem is going wrong and you must do something about it. And unless you’re regularly scanning the error log looking for these, you’ll be none-the-wiser.
So – my recommendation is that you add a specific Agent alert for error 825, along with your other alerts (see following blog post).
One of the biggest challenges DBAs face within their role is how to perform index optimisation on very large databases (VLDBs) – particularly in situations where full recovery models are mandated for recovery point objectives / requirements. During my research into this problem, I’ve come across two excellent solutions which incorporate additional smarts into this process in order to make it more efficient and minimise the volume of logs generated.
The first is an award winning solution by Ola Hallengren who wrote the following database maintenance scripts;
DatabaseBackup – Stored procedure to backup databases DatabaseIntegrityCheck – Stored procedure to check integrity of databases IndexOptimize – Stored procedure to rebuild and reorganize indexes and update statistics CommandExecute – Stored procedure to execute and log commands DatabaseSelect – Function to select databases
Ola also makes this available as a single download for installing the solution in one script. For full details including usage instructions, getting started guide, documentation and updates, be sure to check out Ola Hallengren website!
The second solution I’ve come across which also does an excellent job of this is one by Michael Ufford at sqlfool.com, who’s written the following article on index optimisation for VLDBs;
The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
– To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
– To update the currently configured value for advanced options.
RECONFIGURE
GO
– To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
– To update the currently configured value for this feature.
RECONFIGURE
GO
Once enabled, you can then run batch files through a SQL T-SQL via;
