Problem and incident management with SQL Server is similar to problem and incident management with other server applications or the Windows operating system infrastructure itself. The SQL Server Logs (which should always be enabled), Analysis Services process log file , the DTS error and execution logs, and the Windows application log should be reviewed on a regular basis to attempt to detect potential problems before they become bigger problems. These same logs should be reviewed after incidents to attempt to associate events with incidents and identify patterns that lead up to the failure. The resolution of problems should be documented to help resolve future incidents and also used to train personnel in troubleshooting and understanding symptoms.
It is very important for a DBA to monitor the response time of SQL Server to connected users. Minute details like read, write and resource consumption may be analyzed later but first priority should be to point out slow response time. Several DMVs and other system data may be utilized to monitor the slow response of SQL Server. For this post I have
USE master GO SELECT start_Time,DB_NAME(database_id),st.TEXT, [Status], command, wait_type ,wait_time, wait_resource, last_wait_type, CASE transaction_isolation_level WHEN 0 THEN 'unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS [TRANSACTION ISOLATION],executing_managed_code FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) st ORDER BY wait_type GO
The field [wait_type] is important if some wait type is given for any transaction. And it become critical if wait type is due to any lock. Such wait types will begin with LCK prefix. T-SQL statement being used is also given, so DBA may get a clue that which statement is experiencing slow response.
Above script just provides a clue. If you note any problem then further fields from sys.dm_exec_requests may be included and other ways to track down the problem may be used.
For details of fields in sys.dm_exec_requests please click here
As a DMV is being used in above script so it is obvious that above script will be used for SQL Server 2005 and later.
In my recent article information about missing indexes we discussed that how to use SQL Server suggestions for missing indexes. It seems logical to discuss some major issues while considering to create missing indexes.
Clustered index is automatically created for a table when primary key is created. Most of the time primary key is auto generated number. As clustered indexes are related to physical arrangement of records, so there may be just one clustered index in a table. Now if SQL Server suggests any column to create index upon, then a non clustered index may be created on that column after proper testing and consideration.
There may be as many non clustered indexes as required in a table.
Another important aspect to consider is that although indexes are very use full for search, update/retrieve operations but on the other hand indexes may degrade performance for insert/delete operations. So if a table undergoes heavy insert/delete operations occasionally, then it would be better to limit the indexes to just clustered index on primary key.
sp_spaceused is system stored procedure. It provides number of rows and space used information. For example to get space used information for [Sales.SalesOrderDetail] in AdventureWorks database.
USE AdventureWorks GO EXEC sp_spaceused [Sales.SalesOrderDetail] GO
As it returns 6 columns.
- [Name] is name of table that we provided as parameter
- [rows] is number of rows in that table
- [reserved] is total space that is allocated to this table and its indexes
- [data] is amount of space used by data in the table
- [index_Size] is amount of space used by indexes in the table
- [unused] is amount of space that is allocated but currently not in use by table
data + index_size + unused = reserved
If no parameter is provided to sp_spaceused, it will generate space statistics for whole database overall. To make more use we may use sp_spaceused with sp_MSforEachDB or sp_MSforEachTable.
Indexes are core of optimization and performance of DBMS. Keen attention is required while planning indexes. Fortunately SQL Server contains a suggestion mechanism for creating indexes where required. While optimizing the submitted queries, Query optimizer notes down the columns where indexes are missing and their creation may boost the performance. First let us view the script to retrieve the missing indexes information in SQL Server 2005 databases
SELECT DB_NAME(database_id) AS [DATABASE], equality_columns, inequality_columns, included_columns, STATEMENT FROM sys.dm_db_missing_index_details ORDER BY 1
Result of above script will be generated in five columns.
- [Database] is name of database where missing index is detected
- [equality_columns] is list of columns that are used in equality comparison and index is required on these.
- [inequality_columns] is list of columns that are used for inequality comparison and index is required on these.
- [included_columns] is list of columns that are used in query for other than comparison (e.g. Select List). And covering index on these is suggested.
- [Statement] is name of table along with column where index is missing
It is important to mention that this information is geather since the time when SQL Server Service starts. Existing information about missing indexes will be vanished with service stop/restart. So DBA should backup missing indexes information time to time for later analysis.
Also note that all this information is based on queries submitted to SQL Server query engine either by user or application. So there may be other objects where indexes are required but these are not suggested because of not being used in scripts.
For deep and detailed monitoring/analysis i would suggest Database Engine Tuning Advisor.
sp_server_info is a very handy system stored procedure to get server level information for SQL Server. Following is the simple syntax to use it
EXEC sp_server_info GO
A list of various configurations will be provided as out put.
If you want any further information about description of any specific parameter, then please visit BOL link