use [master]
GO
GRANT VIEW SERVER STATE TO [Domain\Username]
GO

List the database name, logical names and locations of the DB log and database files;

EXECUTE sp_msforeachdb ‘use ?; select DB_NAME(); select name, filename from [?].sys.sysfiles’

To get the fragmentation level on a database table, we can use;


DBCC SHOWCONTIG ('[DatabaseName].[dbo].[TableName]')


… 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):

17 Insufficient Resources
18 Nonfatal Internal Error Detected
19 Error in Resource

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

for more information on the severity levels see http://msdn.microsoft.com/en-us/library/aa937483(SQL.80).aspx

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.
http://tutorials.sqlserverpedia.com/SQLServerPedia-20090330-IndexTuning3.flv

T-SQL Code

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.

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090330-IndexTuning3.flv

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

Original Post: http://sqlserverpedia.com/w/index.php?title=Find_Missing_Indexes

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

Original post http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx