:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • May 13 / 2009
  • 0
DBA best practices, dbDigger, Monitoring and Analysis

Problem and Incident Management for SQL Server

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.

  • May 07 / 2009
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, T-SQL Scripts

Using sys.dm_exec_requests to monitor slow response of SQL Server

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  

sys.dm_exec_requests result
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.

  • Apr 21 / 2009
  • 0
dbDigger, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Considerations while using missing indexes information of SQL Srever

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.

  • Apr 16 / 2009
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures, T-SQL Scripts

get number of rows and size of a table through sp_spaceUsed

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

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

  • Apr 16 / 2009
  • 1
dbDigger, Dynamic Management Views DMV, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Get information about missing indexes in SQL Server 2005 and onwards databases

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.

Consult us to explore the Databases. Contact us