:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Aug 11 / 2008
  • 0
DBA best practices, dbDigger, Monitoring and Analysis

DBA Best Practices for Performance Monitoring

Not always hardware bottle necks are responsible for performance degradation. there are various possible reasons. And to detect the right culprit DBA should pay special attention to performance monitoring. For this purpose

  1. Regularly monitor your SQL Servers for blocked transactions.
  2. Regularly monitor system performance using System Monitor. Use System Monitor for both real-time analysis andfor historical/baseline analysis.
  3. If running SQL Server 2005, SP2 or later, install the free SQL ServerPerformance Dashboard. It can be usedfor real-time monitoring and performancetroubleshooting.
  4. Regularly monitor activity using Profiler.
  5. Be sure that traces are taken duringthe busiest times of the day so you get a more representative trace of what isgoing on in each server. When runningthe Profiler, do not collect more datathan you need to collect.
  6. Perform performance monitoring from a computer that is not the SQL Server you are monitoring. Run monitoring tools on a separate desktop or server.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Search for a column in all tables of a database

Today i read an interesting question on a blog, that how to search for a column in all tables of a database. Following script was given by author to search for columns with name like employeeID in adventureworks database.

USE AdventureWorks  
 GO  
 SELECT t.name AS table_name,  
 SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME,  
 c.name AS column_name  
 FROM sys.tables AS t  
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID  
 WHERE c.name LIKE '%EmployeeID%'  
 ORDER BY SCHEMA_NAME, table_name  

It served the purpose but i have a more simple approach for same purpose. My script generates same result in a more simple and flexible way

 
USE AdventureWorks  
 GO  
 SELECT Table_Schema, Table_Name, Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name in ( select name from adventureworks..sysobjects  
 where xtype = 'U' )  
 and column_name like '%EmployeeID%'  
 order by table_schema, table_name  

So both may be used as convenient.

  • Jul 29 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures, T-SQL Scripts

Column names and datatypes in a table

I was required to generate a list of column names along with their data types in a table. For information about a table i used sp_help.

 
USE DBNameHere  
 GO  
 sp_help tableName  
 GO  

And for more to the point result with any filtration required i used following script

Use DBNameHere
GO
SELECT Table_Schema,Table_Name,Column_Name,Data_Type
FROM information_schema.columns
WHERE table_name = ‘TableNameHere’
GO

This produced exact that i wanted.

  • Jul 21 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Get detailed information about a table with sp_helpDB

To get relevant information about a specific table, following stored procedure may be used

 
USE DBNameHere  
 GO  
 EXEC sp_help tableNameHere  
 GO  

For example in case of adventure works database, if we require information about vVendor table, then our script will be in following form

 
USE AdventureWorks  
 GO  
 EXEC sp_help [Purchasing.vVendor]  
 GO  

Valuable system stored procedure (sp_helpDB) may be used in all versions of SQL Server.

Consult us to explore the Databases. Contact us